Category Archives: MS SQL Server

SSIS: Use Two OLE DB Destination Adapters to Catch Insertion/Constraint Errors

If you are using SQL Server Integration Server (SSIS) packages to insert large volumes of data, you have undoubtedly encountered incorrectly formatted or typed data in your data source including precision errors, conversion errors, and primary key/foreign key constraint errors etc. To … Continue reading

Posted in MS SQL Server, SSIS | 5 Comments

MS SQL Server: Quick T-SQL to Delete All User Table Data

Here’s a quick (hack) way to delete all user table data from a Microsoft SQL Server database using the undocumented procedure sp_MSforeachtable. As the name hints, this procedure repeats a SQL statement against all user tables within a database. This … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

Creating a SQL CLR User Defined Function

Starting with SQL Server 2005, Microsoft added the awesome ability to reference .Net assemblies from your T-SQL procedures.  Here’s a quick 5 step overview on how to get up and running with your code-based User Defined Function. 1) Create a … Continue reading

Posted in C#, MS SQL Server, T-SQL | Leave a comment

T-SQL: Backup All User Databases

Here’s an easy, generic way to back up all user databases on a SQL Server instance:   DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName VARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

Shrinking your Transaction Log with SQL Server 2008 to Free Disk Space

If you have been used to clearing up disk space on your development and test SQL server instance file systems with SQL Server 2000 or SQL Server 2005 by using the famous TRUNCATEONLY option on your transaction logs, you might be … Continue reading

Posted in MS SQL Server, T-SQL | 1 Comment

Don’t get caught by ANSI PADDING, VARCHAR, and trailing whitespace!

One thing to be aware of when you have VARCHAR columns and are using ANSI PADDING: trailing whitespace is trimmed and not counted in your equality ( = /  / LIKE) statements, so you might not get the results you are looking to be … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment