Category Archives: SQL 2005

SSIS Package Upgrade then error “Cannot use the OUTPUT option when passing a constant to a stored procedure”

I have been recently upgrading SQL Server 2005 SSIS packages to 2008 R2 using the automated wizard. The wizard performed the upgrades without error, however on executing the packages I would get the following error in a data flow OLE … Continue reading

Posted in SQL 2005, SQL 2008 R2 | Tagged | Comments Off on SSIS Package Upgrade then error “Cannot use the OUTPUT option when passing a constant to a stored procedure”

Blocked SQL Sessions and Blockers

Validated against: SQL Server 2005, 2008, 2008 R2. Below is a SQL script that returns SQL sessions waiting (blocked) for one second or more, and also the associated SQL sessions that are doing the actual blocking. The script returns pretty … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Blocked SQL Sessions and Blockers

Very Quick Accurate Row Counts

Validated against: SQL Server 2005, 2008, 2008 R2.

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Very Quick Accurate Row Counts

Current Database Update All Table Statistics with Fullscan

Validated against: SQL Server 2005. Updating statistics against each table in the current database by using the undocumented stored procedure [sp_MSforeachtable].

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Current Database Update All Table Statistics with Fullscan

Efficient Numbers Table

Validated against: SQL Server 2005. Just performed a quick Google of the various methods of acquiring a list of sequential numbers and recommend the approach by Itzik Ben-Gan. The approach uses a small footprint of code and allows numbers up … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Efficient Numbers Table

Disk Subsystem Benchmarking

When benchmarking disk subsystems, I generally benchmark against the most challenging task; which is random writes, with no buffering! The tool I use is the Microsoft SQLIO tool that measures IO performance. The SQLIO tool can be acquired from: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163 … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Disk Subsystem Benchmarking

Random String

Validated against: SQL Server 2005.

Posted in SQL 2005 | Tagged | Comments Off on Random String

Random Character

Validated against: SQL Server 2005.

Posted in SQL 2005 | Tagged | Comments Off on Random Character

Using INSERT to Remove Duplicate Rows (IGNORE_DUP_KEY)

Create a duplicate structure of the table concerned and build an IGNORE_DUP_KEY index over the second table that includes all the original table candidate keys and insert rows into it. forename surname age ————— ————— ———– David Selwood 40 Paul … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Using INSERT to Remove Duplicate Rows (IGNORE_DUP_KEY)

Rounding a Date (removing time)

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Rounding a Date (removing time)