tag:blogger.com,1999:blog-62525376474559741092024-08-28T16:58:03.481-05:00Unknownnoreply@blogger.comBlogger12125tag:blogger.com,1999:blog-6252537647455974109.post-78838425136240493112011-01-21T16:05:00.003-06:002011-01-21T16:07:58.855-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

Unknownnoreply@blogger.comtag:blogger.com,1999:blog-6252537647455974109.post-31656622584995872732009-02-18T11:54:00.003-06:002009-05-26T10:23:14.993-05:00

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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-36799788380457313922008-11-12T08:07:00.002-06:002008-11-12T08:12:09.433-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

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6252537647455974109.post-56102521312403980042008-11-11T12:54:00.003-06:002008-11-11T15:00:35.126-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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-86705939017275608942008-11-11T09:37:00.005-06:002009-02-09T05:59:51.978-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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-22235699756700046862008-11-11T07:00:00.003-06:002008-11-11T07:23:48.981-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/

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-23946776959300790552008-10-30T19:53:00.002-05:002008-10-30T20:00:01.236-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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-29192998396381195282008-10-30T13:27:00.004-05:002008-10-30T13:37:50.958-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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-91178155669908273712008-10-28T10:40:00.000-05:002008-10-30T11:12:51.486-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

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-6252537647455974109.post-29843281328148460202008-10-21T07:26:00.000-05:002008-10-30T11:11:43.178-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

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-51714609095605353112008-10-10T10:40:00.001-05:002010-03-16T17:03:42.287-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 =

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6252537647455974109.post-51813926679020720832008-10-09T17:40:00.000-05:002008-10-30T11:08:23.192-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

Unknownnoreply@blogger.com1