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 🙂

datetime(offset)-to-(n)varchar-print

datetime(offset)-to-(n)varchar-select

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)









Advertisements

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:

SSIS-Error-Description-Component

SSIS-No-Status-Is-Available-NonColumnSpecificError

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:

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