دانش متفرقه TSQL

-اگر قصد کوئری از طریق لینک سرور روی جدولی که فیلدی از نوع XML داشته باشید با پیغام خطای زیر روبرو خواهید شد. در این شرایط کافی است تا از دستور openquery در خط فرمان select استفاده کرده و صرفا به فیلدهای مورد نیاز با نوع غیر XML ی اشاره کنید:

Xml data type is not supported in distributed queries. Remote object '192.168.1.1.Framework.Prs.Person' has xml column(s).

- حذف جدولی که نمیدانیم هست یا نه بدون دریافت خطا با فرمان زیر

drop table if exists #BasicPattern

-استفاده از ROWS 3 PRECEDING در Windows Function  ها

گاهی مجموع (یا سایر موارد Windows Function ی) یک فیلد برای چند رکورد قبل (یابعد) مورد نیاز است. در این شرایط مشابه مثال زیر می توان عمل کرد.

sum(Components) over (partition by BranchKey order by RoundEffectTime ROWS 3 PRECEDING) as AggComponents

-در مثال قصد ایجاد 2 لیست از ایمیل آدرسهای zone با مقدار A و B را داریم. از ورژن SQL 2016 به بعد با استفاده از فانکشن STRING_AGG که در این مثال از آن استفاده شده است به راحتی و با سرعت مناسب قابل انجام است.

zone emailaddress

A [e-mail]

A [e-mail]

B [e-mail]

A [e-mail]

create table #emailList (zone varchar(10), emailaddress varchar(50))

insert #emailList values ('A', '[e-mail]'),('A', '[e-mail]'),('B', '[e-mail]'),('A', '[e-mail]')

 select zone, STRING_AGG(EmailAddress, ';') AS emails

 from #emailList

 group by zone

خروجی به شرح زیر خواهد بود:

zone emails

A [e-mail];[e-mail];[e-mail]

B [e-mail]

- برای اتصال از MSSQL به فایل اکسل بدون استفاده از  LinkedServer  به روش زیر عمل کنید:

* ابتدا Microsoft Access Database Engine را از مسیر زیر دانلود و نصب نمایید:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

*به مسیری که فایل اکسل خود را در آن قرار داده اید دسترسی دهید (جزییات بیشتر را بعدا میگم)

*دستورات زیر را در  SQL اجرا کنید:

--Enable Ad Hoc Distributed Queries:

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

EXEC master.dbo.sp_MSset_oledb_prop 

    N'Microsoft.ACE.OLEDB.12.0', 

    N'AllowInProcess', 1;

EXEC master.dbo.sp_MSset_oledb_prop 

    N'Microsoft.ACE.OLEDB.12.0', 

    N'DynamicParameters', 1;

*با استفاده از فرمان زیر به فایل اکسل دسترسی پیدا کنید بهتر است مانند مثال زیر از آدرس شبکه ای استفاده نمایید:

SELECT *

FROM OPENDATASOURCE(

    'Microsoft.ACE.OLEDB.12.0',

    'Data Source=\\192.168.30.11\Folder\SampleFile.xlsx;Extended Properties="Excel 12.0;HDR=YES";'

)...[Table1$]

*اگر در محیط SSMS خواستید که intellisense مجددا فعال و به روزرسانی بشه از دکمه های Ctrl+Shift+R استفاده کنید