Interesting Blog Post

As a person who frequently has to review and clean up others TSQL I always spend a lot of time formatting to review. Additionally, I can have to format it to the clients TSQL standard. This is a script for colour formatting the output to a file that will be easier to read and distribute. It does require command execution from the SQL Server instance and this is something that I do not give to many of the servers I manage so I can't quickly test it hear.

I am putting hear so I can try it as I think it could solve several issues:

Simple Talk Website - Prettifier




----------------
Now playing: American Public Media - Marketplace Money for September 12, 2008
via FoxyTunes

Guarantee a temp table is dropped

/*
The pupose of this post is to demonstrate the correct method of creating a temp table.
The key is to make sure that the drop statements are wrapped with an if statement and
placed both prior to table creation and after its use.
This is another argument for using temp table variables and common table expressions
*/

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#tModelItems%') DROP TABLE #tModelItems;

CREATE TABLE #tModelItems(
ItemNbr varchar (15),
QuantityPer decimal(11,3),
GroupCode char(1),
GroupCodeDesc varchar(50),
DrawingInd char(10),
);

--Use table

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#tModelItems%') DROP TABLE #tModelItems;


----------------
Now playing: NPR - 0808243: Car Talk 0835 August 30, 2008
via FoxyTunes

Raising errors in the history

/*
I have been looking into raising customer errors in a large collection of file copy routines I am creating to provide better logging for a collection of PDFs we use for various websites. This is a start of what I am looking for and will be added to as I find new features.

The error will be posted in the SSIS history so it will be easier to find when doing the morning audit.

*/

Dim filename As String = Dts.Variables("PDF_filename").Value.ToString
Dim sourceFilePath As String = Dts.Variables("SourceFilePath").Value.ToString
Dim destFilePath As String = Dts.Variables("DestinationFilePath").Value.ToString
Dim destFilePathArchive As String = Dts.Variables("DestinationFilePathArchive").Value.ToString
Dim purgeSourceFile As String = CBool(Dts.Variables("PurgeSourceFile").Value.ToString)
Dim ArchiveFilePath As String = Dts.Variables("archivePathExtension").Value.ToString

Dim sourceFileName As String = sourceFilePath & "\" & filename
Dim destFileName As String = destFilePath & "\" & filename
Dim destFileNameArchive As String = destFilePathArchive & "\" & filename

Dim CopyDest As String = destFilePath & ArchiveFilePath & "\" & filename

Try
File.SetAttributes(destFileName, FileAttributes.Normal)
'Make sure that if the file exists at the destination it is not read only
If File.Exists(CopyDest) Then
File.SetAttributes(CopyDest, FileAttributes.Normal)
End If
File.Copy(destFileName, CopyDest, True)
File.SetLastWriteTime(CopyDest, Now())
File.Copy(sourceFileName, destFileName, True)
Catch ex As Exception
'An error occurred.
Dts.Events.FireError(0, "Error in file copy", _
ex.Message & ControlChars.CrLf & ex.StackTrace, _
String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

Joining 2 files in SSIS with an outer join

This post is to demonstrate the step necessary to perform an outer join on 2 record sets. the goal of this join was to find all expiring transactions from an authorization table. Outer join them to any settlements against the transaction. Then finally create an in memory record set that can be used to create an email to notify the users.


This leads me to complain about the stupid syntax for if then in the derived column expression editor as shown below:

[Expression the create a true false result] ? [Then data value] : [Else data value]

I have no idea where this came from and it took me forever to find this. This is the link to MSDN.

This can all be accomplish with a single transact SQL statement using a temp table variable and this makes it look really complicated.

SQL Restore Database with Move

/*
This is my standard restore to a different location. This is basically the script from the help file so I don't have to find it any more.

The script assumes that the data base has been created and will be overwritten.

The first script gets the file lay out and names which needs to be inserted into the second call. It demonstrates more than one logical location in a mdf file. It can use UNC file navigation.
*/

Restore FileListonly from disk = '[File location][file name].BAK';

/*
Run the first query to get the logical name prior running the second

The file location must be relative the machine that the restore is being done so it must be a local drive as demonstrated in the sample. I have not found a way to restore from a remotely located drive.
*/

Restore Database [database name] FROM DISK = '[File location][file name].BAK'
WITH MOVE '[LogicalName]' TO 'C:\restored_databases\[filename].mdf'
, MOVE '[LogicalName_1]' To 'C:\restored_databases\[filename_1].mdf'
, MOVE '[LogicalName_log]' To 'C:\restored_databases\[filename_log].ldf'
, stats = 5
, REPLACE



----------------
Now playing: NPR - 0807195: NPR: 07-13-2008 Wait Wait... Don't Tell Me!
via FoxyTunes

SQL get the column definitions from a table

--This script is used to get the column definitions from the same table in 2 different databases
--it is primarily to demonstrate how to get column definitions from a table

DECLARE @table_name nvarchar(128)

SET @table_name = '[table_name]'

SELECT *
FROM [db_name].sys.all_objects
INNER JOIN [db_name].sys.all_columns
on all_objects.object_id = all_columns.object_id
INNER JOIN [db_name].sys.systypes
ON all_columns.system_type_id = systypes.xtype
WHERE all_objects.type = 'U'
AND all_objects.name in ( @table_name)


SELECT *
FROM [db_name_2].sys.all_objects
INNER JOIN [db_name_2].sys.all_columns
on all_objects.object_id = all_columns.object_id
INNER JOIN [db_name_2].sys.systypes
ON all_columns.system_type_id = systypes.xtype
WHERE all_objects.type = 'U'
AND all_objects.name in ( @table_name)

SQL Script to get definitions for procedures, functions, and views

-- This procedure will allow you to get the definition for the raw SQL
-- that is used to create the stored procedures, functions, and views

USE [db_name];

SELECT TOP 200
all_objects.object_id
, name
, definition
, type_desc
, type --P=Stored Procedure, V= View, FN = Function
FROM sys.all_objects
INNER JOIN sys.sql_modules
on all_objects.object_id = sql_modules.object_id
--Use the to find specific definitions
WHERE definition like '%nolock%'