From DATETIME(OFFSET) to VARCHAR(x), a world of differences :)

Always use the smallest data type, but in some cases this can really bite you.
In my example, I’ve both DATETIME and DATETIMEOFFSET types in a single table.
‘Simply’ converting/casting both to strings and expecting similar results, isn’t an option.

Instead of the ‘Jan 2 2012 9:15AM’, it would be nice to just have it like ‘2012-01-02 09:15:48’
But…you’ll have to do that yourself 🙂

Code

PRINT CAST('2012-01-02 09:15:48' AS datetime)
PRINT CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset)
PRINT CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset)
PRINT ''
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

SELECT '1| -- '+CAST(CAST('2012-01-02 09:15:48' AS datetime) AS VARCHAR(100)) AS Results
UNION
SELECT '2| -- '+CAST(CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '3| -- '+CAST(CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '4| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
UNION
SELECT '5| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
UNION
SELECT '6| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)


SSIS – No status is available (NonColumnSpecificError)

In case your SSIS package fails to insert new records into a destination table and you receive an error message like:

No status is available.
[NonColumnSpecificError]

In my case, the Error Description (custom) component returned this message and wrote it to my errors-table:

More info about this component can be found here.

It’s probably a Foreign Key (FK) constraint, you might want to check those.

SSIS: Text to ProperCase (derived column)

How to format text to ProperCasing?
Found this SSIS Expression at SQLJunkieShare:


(UPPER(SUBSTRING(TRIM(Name),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),2,FINDSTRING(TRIM(Name),” “,1) – 1 < 0 ? LEN(TRIM(Name)) : FINDSTRING(TRIM(Name)," ",1) – 1)))) + " " + (FINDSTRING(TRIM(Name)," ",1) – 1 < 0 ? " " : UPPER(SUBSTRING(TRIM(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1),LEN(TRIM(Name)))),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1) + 2,LEN(TRIM(Name))))))


SQL Monitor on CodePlex (free standalone tool)

A few days ago someone posted a link to this very nice tool on LinkedIn:

SQL Live Monitor @ CodePlex

Description
a .NET application that provides realtime performance data on the target SQL Server instance.
No installation required, data displayed in realtime, and can also be logged to CSV for offline analysis. Will also capture SQL data for processing uing PAL.

It needs quite a bit of permissions to run, guess also registry access??

The tool itself in action:

Selection of all parameter values – (All)

Another addition to your cookbook!
A handy trick to simplify your report header information is by displaying ‘(All)’ instead of literary all parameter values.

An example:

By using this expression in the textbox:

=IIF(Parameters!Statusses.Count = COUNTDISTINCT(Fields!StatusName.Value, "dsListOfStatusses"),"(All)",CSTR(Join(Parameters!Statusses.Label,", ")))

Will result in:

SQL Server – Rare Event error

Today I received a SQL Sever error that was caused by a ‘rare event’.

Simply adding just a single parameter to the same Stored Procedure triggered this event.

By removing that single parameter, I suddenly ‘fixed’ it 🙂

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
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

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

A nice addition for your Cookbook (Code Snippets):

Source

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]

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

Filter out NULL values in Indexes

Received a great tip from my colleague on how to filter out NULL values in Indexes.

How to use a WHERE-clause within an Index? Well take a look!

CREATE NONCLUSTERED INDEX [IX_NC_ColumnName] ON [dbo].[TableName]
(
ColumnName ASC

)
WHERE ColumnName IS NOT NULL
WITH ...