Stored Procedure: Automatically ‘Generate Scripts’ for local or linked server

Normally you would have done this manually:

Time for a change!
Schedule it in SQL Agent as a Stored Procedure and export it to a TXT-file.

SP GenerateScripts on LocalServer

On execution:

To export it to a TXT-file:
SQL Agent Job – ExportDatasetToTXT

Make sure that xp_cmdshell is enabled:

EXEC master.dbo.sp_configure 'show advanced options', 1
EXEC master.dbo.sp_configure 'xp_cmdshell', 1

List columns and attributes for every table in a SQL Server database

A nice addition for your Cookbook (Code Snippets):


sys.tables provides one row for each table in a database. This does include user tables and system tables that exist in each database. There is a column within sys.tables: [is_ms_shipped] that identifies system tables. This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view’s type column (where sys.sysobjects.type = ‘U’) in order to do so.

sys.all_columns offers a row for each column for every object in a database. Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.

sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties. The only field from this view we will need is the data type’s name, as it is the only field in our desired result set we can’t return from sys.all_columns as it pertains to column metadata.

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]

SSRS: Formatting Date and Time

Something for your cookbook! (source)

The Format function is locale aware and can be used as follows [not the case senstivity]
Set Language=User!language in the Report properties and use the Format function with the following codes:
Standard Format Specifiers for Dates and Times:
The table below shows the standard date and time formatters.

Format Description
d – Short Date
D – Long date
f – long date & short time
F – long date & long time
g – short date & short time
G – short date & long time
M or m – month & day
Y or y – year & month
t – short time
T – long time
s – displays in ISO 8601 format using local time
u – displays in ISO 8601 format using universal time
U – date and time in unversal time
R or r – displays in RFC 1123 format

Custom formatting sequences:
There are also specific character sequences that can be used to achieve custom formatting of dates and times.

Format Description
d – day of month (1 or 2 digits as required)
dd – day of month (always 2 digits, with a leading 0 if needed)
ddd – day of week (3 letter abbreviation)
dddd – day of week (full name)
M – month number (1 or 2 digits as required)
MM – month number (always 2 digits, with a leading 0 if needed)
MMM – month name (3 letter abbreviation)
MMMM – month name (full name)
y – year ( last 1 or 2 digits, no leading 0)
yy – year (last 2 digits)
yyyy – year (4 digits)
H – hour in 24-hour format (1 or 2 digits as required)
HH – hour in 24-hour format (always 2 digits, with a leading 0 if needed)
h – hour in 12-hour format (1 or 2 digits as required)
hh – hour in 12 hour format
m – minutes (1 or 2 digits as required)
mm – minutes (always 2 digits, with a leading 0 if needed)
s – seconds (1 or 2 digits as required)
ss – seconds
t – first character in the am/pm designator
tt – am/pm designator
z – time zone offset, hour only (1 or 2 digits as required)
zz – time zone offset, hour only (always 2 digits, with a leading 0 if needed)
zzz – time zone offset, hour and minute
/ – default date separator
: – default time separator
\ – escape characters

Common SSRS URL Commands

Another page for your cookbook 🙂 (source)

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

• This shows the parameter bar and is the default

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

• This hides the toolbar

• This will open up the report as a PDF, other examples are: HTML3.2, HTML4.0, HTMLOWC, MHTML, IMAGE, EXCEL, CSV, PDF, XML

• This will open the report in Excel

&rc:Zoom=Page Width
• This will open up the report in full width of the page

• 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.

• 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.

More info on MSDN.

SSRS: Passing multi-value parameters in an URL

There is a lot of discussion on the forums about passing parameters to an SSRS report using an URL however one of the items that you rarely read about is the passing of multi valued parameters in an URL.

To do this, just pass the name/value pair, separated by the ampersand.


An actual example:

I made a very basic report that displays the actual Parameter Value that was fed to the report and split it with my MultiValue-function for use in a table:

Available Values set for the @MultiParam:

The URL I’m going to use to select two values out of the five default values:


SQL Server “Denali” (2011) CTP3 is available for download!

CTP3 of SQL Server Code Name “Denali” was made available for download this morning. You can download the 32-bit and 64-bit versions here. Official announcement from the SQL Server team blog and the announcement from the Analysis Services and PowerPivot Team Blog.

The Feature Pack for CTP3 can be download here.

See CTP3 Books Online and Project Crescent (also included in CTP3).

Thanks to James Serra for the fast info!
More posts soon!

Column Formatting: Amount with currency

Amounts may be displayed in different currencies, but it’s always difficult to combine it in a nice way (for example with the correct currency displayed and regional settings).

Format example for Euro:
‘EUR’ #,0.00;(‘EUR’ #,0.00)

When you have the type of currency in your dataset, you may want to use this formatting code:

="'"+CSTR(Fields!Currency.Value)+"' #,0.00;('"+CSTR(Fields!Currency.Value)+"' #,0.00)"

It dynamically pastes the currency name or symbol (according to your data) into the format:

Function: Calculate working days

I stumbled upon a very nice T-SQL function to calculate working days.
I’ve created one before myself, but this one is very short and slick 🙂

This example seems to be copied multiple times (here and here…anywhere else?)

Well…here it is!

Download the function’s CREATE SCRIPT here: fn_CalculateWorkingDays