SQL Kit Tips and Scripts for T-SQL, BI

SQL Tidbits http://sqltidbits.com/ or follow @sqltidbits

2011-01-21T16:05:00.003-06:00

I'm launching a new SQL Server website called SQL Tidbits. Visit http://sqltidbits.com/ or follow @sqltidbits and let me know what you think. I'm in the process of transferring the content from this site to there as well as uploading some of my best T-SQL scripts. With some frequency, I'll also be doing short screencasts when it's easier to show you than just tell how to do something.

Free SQL Training Videos

2009-04-06T10:10:00.002-05:00

Brent Ozar had a good post listing some free SQL Training resources including a couple I didn't know about. Find his list on his blog post http://www.brentozar.com/archive/2009/04/free-sql-server-dba-training-videos/.

T-SQL Arrays

2009-02-18T11:54:00.003-06:00

Here's a good link compiling various methods for processing information in an array-like method.http://www.projectdmx.com/tsql/sqlarrays.aspx

SQL Server Service Packs and Hotfixes

2009-02-18T07:33:00.014-06:00

UPDATE: Looks like someone else has this idea covered. Check out a list that is updated more frequently. http://www.sqlteam.com/article/sql-server-versionsHere are some links that I reference often. You can find the latest service packs and hotfixes for a particular version of SQL.Version & Service PackHotfix linksLatest buildSQL Sever 2000 SP4http://support.microsoft.com/kb/8949058.00.2249SQL

Making a SQL Server Instance Listen on Multiple Ports

2008-11-12T08:07:00.002-06:00

Sometimes when upgrading a SQL Server instance to 2005 or 2008 named instance there is still a need to support a legacy application which can only talk to the default instance on port 1433. Normally I handle this scenario in the following way.(1) Install a named instance and set it a static TCP port (under IP All). (for example, 4532)(2) Configure a "default" alias to point to the named

Get a List of Unique Servers from a list of SQL Instances

2008-11-11T12:54:00.003-06:00

Here's an example of pulling just the server name from a list of SQL Server instances.CREATE TABLE [dbo].[Instances]( [QID] [smallint] IDENTITY(1,1) NOT NULL, [Instance] [varchar](128) NOT NULL --additional fields and table options not shown)GO--load the table with dataSELECT DISTINCT --eliminate duplicates CASE WHEN charindex('\', [instance]) > 0 THEN --if the instance name includes a slash

Enterprise Software - Scalability, Flexibility and Integration

2008-11-11T09:37:00.005-06:00

In yesterday's SSWUG newsletter, Stephen Wynkoop posed the following question.(excerpt from the SSWUG newsletter 11/10/2008)What, Exactly, Makes "Enterprise" Software?The next version of SQL Server takes more steps toward supporting enterprise-class installations and BI solutions. Great!What is "enterprise" - what does that mean? I'm curious because since even the 7.0 days, and certainly 2000 and

SQL 2005 Log Reader - Default Trace

2008-11-11T07:00:00.003-06:00

I just read a great article at SQLServerCentral.com about the Default Trace. It's a low-impact trace that's running all the time and writes DDL and other events to a file which can be queried in SSMS. Here's the link. http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Load XML data into SQL 2000

2008-10-30T19:53:00.002-05:00

I recently had the need (read "somebody wanted a big favor") to load XML data into a SQL Server 2000 database. I came across an easy way for them (being a developer to push the data into SQL) per the article (http://support.microsoft.com/kb/316005).After creating the table on SQL, you have to create a mapping file to tell it which fields go with which columns in the SQL database. The article

List Table Rowcount and Size

2008-10-30T13:27:00.004-05:00

Here's a handy way to get a list of table rowcounts and space used. If you've not used the sp_msforeachtable just plug in the question mark (?) as a token for the name of the table.--create a temporary table to hold the resultsCREATE TABLE #TableStats ( [name] varchar(128), [rows] varchar(128), [reserved] varchar(128), [data] varchar(128), [index_size] varchar(128), [unused] varchar(128))GO--loop

List of DTS packages

2008-10-28T10:40:00.000-05:00

I was recently asked by a fellow DBA if I had "one of my scripts" to give a list of DTS package on a SQL Server 2000 instance. Since the majority of my work is in 2005 and 2008, I just ran the following query.select * from msdb..sysdtspackagesThe results were not helpful because most of the packages had multiple versions stored so instead of listing the 200+ packages, it listed the 7000+ versions

SQL Server Profiler Tools

2008-10-21T07:26:00.000-05:00

I learned several things about SQL Profiler by watching this video. I found it especially interesting that you can view the ShowPlan and the Perfmon data right from profiler.You have to setup a free account to view the video but it's worth it. There are dozens of great tutorials like this.http://www.jumpstarttv.com/using-sql-server-2005-profiler_59.aspx

Move TempDB, Recreate missing TempDB

2008-10-10T10:40:00.001-05:00

You can move tempdb by running the following script. If tempdb is missing (due a drive failure, for example), you will need to start the SQL Service with -T3608 for the startup option and then run the script./*If tempdb is unavailable, stop and start the SQL service with thestartup option -T3608This allows you to start SQL without tempdb.*/USE masterGOALTER DATABASE tempdbMODIFY FILE (NAME =

Insert Picture into Image Field using T-SQL

2008-10-09T17:40:00.000-05:00

Here's an example of how to quickly load a picture into an image field using T-SQL.INSERT INTO [hr].[NTUsers] ( [userid], [username], [title], [reportsto], [pic])SELECT '00001', 'Chip Porter', 'Technology Services', '00000', BulkColumn FROM OPENROWSET(BULK 'c:\noimage.bmp', SINGLE_BLOB) AS ImageLoadGO