TSQL Unit Testing

TSQL Unit Testing

http://tsqlt.org/

TSQL to LDAP

Script using Master table to create the Link – Step 1

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'

GO

Step 2 – Set to Domain Admin account

Then Query

SELECT *

FROM OPENQUERY(ADSI,

'SELECT mail

FROM ''LDAP://DC=orgname,DC=org''

WHERE objectCategory = ''Person''

AND objectClass = ''user''

AND sAMAccountName = ''***''')

SSRS URL parameters

This article shows you some of the most common reporting parameters to use in Microsoft SQL Server Reporting Services.

When you go to the [http://reportserver/reportserver/foldername/reportname]

The last command in the url is &rs:Command=Render

You can add some other commands as follows:

&rc:Parameters=Collapsed 

This collapses the parameter bar but sends the parameters in the URL in the browser

&rc:Parameters=true 

This shows the parameter bar and is the default

&rc:Parameters=false 

This prevents the parameters from being passed in the browser and expanded

&rc:toolbar=false 

This hides the toolbar

&rc:format=PDF 

This will open up the report as a PDF

&rs:Format=EXCEL&rc:OmitFormulas=true 

This will open the report in Excel

rs:ClearSession=true 

This clears the session state for the user. Used where the caching of the report prevents the designer from seeing their updated report and/or data.

* This is a very important parameter to know as more often than not, the report data doesn't update immediately unless parameters are changed, and your report may show cached data if this is not set.&rc:StyleSheet=MyCustomStyle 

Note don’t add the .css extension. And this style sheet must be in the folder Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerStyles folder.

Use the HTMLViewer.css as your base template.

ReportServer Parameters:
If you add ?/FolderName/ReportName after the [http://servername/reportserver] you can run the report.

The same applies if you want to list the reports, then just type ?/FolderName after the [http://servername/reportserver].

This is commonly used when the user only has permissions at the folder level, not the root level.