CREATE BLOG dbo.SqlServer

Iit's huge. I haven't even gone through most of it yet. But it's welcomed. And, if you are looking at migrating your SQL Server 2000 to SQL Server 2005, you need to read it. Go here - http://www.microsoft.com/downloads/details.aspx?familyid=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&displaylang=en

---

Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server

I ran into a weird bit today while trying to write a generic "Update SQL Server 2000 database to SQL Server 2005" script. What I was trying to do was to set all of the database options and settings to the "normal" SQL Server 2005 when you restore a database. Basically the process would be (1) restore SQL Server 2000 database onto SQL Server 2005 server, and (2) run a stored procedure to ALTER DATABASE .. SET <Property> ON|OFF|ETC. You know - SET ANSI_NULLS ON - that sort of thing...

Anyway, I ran into a problem with sp_dbcmptlevel - one that I'd never seen before (because I'd never tried to execute it from within another context). Read this from BOL:

Each sp_dbcmptlevel call must be submitted individually. sp_dbcmptlevel cannot be called from within other contexts, such as a:

Stored procedure.

Transact-SQL string executed with the EXEC(string) syntax.

Batch of Transact-SQL statements.

Well sonuva... There goes my idea for having a generic DBA_UpdateSqlServer2000DatabaseSettings proc.

I read through the rest of the docs on sp_dbcmptlevel and I can understand the logic now. I won't bore myself with reposting all of BOL but feel free to read up on for yourself.

Damn. I really wanted a single stored procedure to do everything... Anyway, here's one script to upgrade your sql server 2000 databases to 2005. I posted it into the SQL Server training videos SQL Server Code Samples repository this morning...

---

Technorati Tags: Scott Whigham, TSQL, SQL Server, SQL Server 2005, SQL Server 2000, Upgrade SQL Server, SQL Server Upgrade

I've been taught since I was a wee SQL Serverite to always put SET NOCOUNT ON at the beginning of stored procedures that modify the database so that you don't have to send those pesky "1 row affected" messages back and forth between the client and server. John Galloway made an interesting post today titled "How NOCOUNT ON affects ADO.NET applications" and I've responded with a test script that shows, at least appears to show, just how much faster it is to use SET NOCOUNT ON instead of the default. Anyway, check it out - http://weblogs.asp.net/jgalloway/archive/2006/08/30/How-NOCOUNT-affects-ADO.NET.aspx

---

Technorati Tags: John Galloway, ADO.NET, NOCOUNT, SQL Server 2005, SQL Server, Microsoft SQL Server

Thought I'd ping the blog with a cool script - A replacement for sp_lock/sp_lock2. This is only for SQL Server 2000/7.0 but very helpful indeed.

---

Technorati Tags: sp_lock, sp_lock2, Q255596, SQL Server 2000, SQL Server, Microsoft SQL Server

Saw this on one of the SBS MVP's blog this morning and I thought I'd pass it along. Great deal - buy up to 20 of syngress.com's books for $9 each. Tons of titles - check it out: http://msmvps.com/blogs/paleo/archive/2006/08/29/110886.aspx

---

Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server

I've started putting my SQL Sevrer 2005 code samples and SQL Server 2000 scripts onto the LearnSqlServer.com website (http://forums.learnsqlserver.com/CodeSamples.aspx). I only have five scripts up there now but I promise to upload lots more!

---

Technorati Tags: sql, sql 2000, sql 2005, sql code sample, sql script, sql server, sql server 2000, sql server 2000 code sample, sql server 2000 script, sql server 2005, sql server 2005 code sample, sql server 2005 script, sql server code sample, sql server script, TSQL code sample, TSQL script

I'm doing some maintenance on our websites today and am running into a CSS Validator error message and I don't know how to fix it or whether or not it's an ASP.NET 2.0 bug.

Here's the code:

<%@ Control Language="c#" %>
<!-- you must close the TD inline or else IE breaks a new line -->
<table cellpadding="0" cellspacing="0">
    <tr>
        <td height="17" colspan="2" style="background-image: url(/images/Generic/toplines.gif)"></td>
    </tr>
    <tr>
        <td width="235" align="center" bgcolor="white" style="padding-right: 8px; padding-left: 8px;border: 2px solid black;">
          <asp:HyperLink ID="parentSite" runat="server" ImageUrl="/images/Generic/logo.gif" Height="57" Width="235" NavigateUrl="http://www.learnitfirst.com"><img src="/images/Generic/logo.gif" width="235" height="57" alt="LearnItFirst.com" border="0" /></asp:HyperLink>
        </td>
    </tr>
</table>

Now for the web.config:

<xhtmlConformance mode="Transitional"/>

(I also tried with "Strict" but had same issue)

Where's all this going? Well, when it renders the header, it adds "style=display:inline-block;" to the hyperlink causing it to fail CSS 2.0 validation. AS of this writing, you can run this against the validator: Validate LearnDTS.com CSS and the first line is the error about the inline-block.

I *think* this is an ASP.NET 2.0 bug since I've read comments from the ASP.NET folks (on blogs, of course) that say they tried to be 100% compliant with known standards at RTM.

Am I wrong - is this not a bug but a design? I don't know and a Google search comes up empty.

Any ideas?

---

Technorati Tags: CSS, CSS Validation

I was running Transact SQL videos through the CSS Validator this morning and got a very confusing warning.

Here's my original .headerAdText:

.headerAdText
{
background-color: #B6D2CD;
border-bottom: thin solid #586872;
padding-left: 10px;
padding-right: 10px;
height: 47px;
}

I ran it through the W3C's CSS Validator and it reports

(Level : 1) You have no color with your background-color : .headerAdText"

Okay, so I tried this instead:

.headerAdText
{
background: #B6D2CD;
border-bottom: thin solid #586872;
padding-left: 10px;
padding-right: 10px;
height: 47px;
}

But I get the same warning... How about this hack?

.headerAdText
{
background: #B6D2CD;
background-color: #B6D2CD;
border-bottom: thin solid #586872;
padding-left: 10px;
padding-right: 10px;
height: 47px;
}

This is where it gets strange...

# Line : 22 (Level : 1) You have no color with your background-color : .headerAdText
# Line : 22 (Level : 2) Redefinition of background-color : .headerAdText

lol

Anyone have any clues on the logic behind this?

---

Technorati Tags: Scott Whigham, Whigham, css, CSS Validation

A bit of history revealed, really. One great quote:

Keith did want to license. Apparently his burn rate was higher than Mick's , and wanted the money. Keith pushed Mick hard and finally prevailed on Mick, who wanted to help Keith out.

A good read - thanks for sharing, Michael:)

What a great idea - Ken Henderson, one of the SQL Server luminaries, has said that he'll give out a free signed book to the best Photoshop of Joe Celko as an action figure lol. Don't know Joe? You should. Don't know Ken? Shame, shame... Google away...

---

Technorati Tags: Ken Henderson, Joe Celko,
Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server

I found a fun little myspace-y type site today called Frappr. Check out my map - http://www.frappr.com/SqlServer and add yourself! I think it will be fun to look at the map six months from now and see where everyone is :)

---

Technorati Tags: Scott Whigham, frappr, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server

I was doing some performance-related data collection for a client today and thought I'd share one thing I wrote - a very cool script.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DBA_GetForeignKeyColumnsNotInAnyIndex] ()
RETURNS TABLE
AS
/*
Author: Scott Whigham from http://www.LearnSqlServer.com/

Description: This is a helpful script to run on a new database or even periodically. Since JOIN
candidates can usually benefit from an index, this query identifies any ChildTable.ForeignKeyColumns
that are not in any index whatsoever. It does not matter to this script whether or not the column
is the leading, middle, or last column in the index. You could easily modify this to find foreign key columns
that were not the leading column in an index, find those not in a clustered index, et al.
Feel free to modify this at will!

Misc Notes: I like this in the "model" database so that it will be propagated to any new databases on the server.
We can't just dump it in "master" since INFORMATION_SCHEMA views do not change context.
I wrote this as a function instead of a view because I may wish to add parameters to it later on.
I prefer this to be a function instead of a proc so that I can write scripts like this:

SELECT 'CREATE INDEX [nci_' + REPLACE(REPLACE(REPLACE(ChildTable, '[', ''), ']', ''), 'dbo.', '') + '_' + Column_Name + '] ON ' + ChildTable + ' ([' + Column_Name + '])'
, 'DROP INDEX ' + ChildTable + '.[nci_' + REPLACE(REPLACE(ChildTable, '[', ''), ']', '') + '_' + Column_Name + ']'
FROM DBA_GetForeignKeyColumnsNotInAnyIndex()

Versions: SQL Server 2005+

Executing this function:
SELECT * FROM dbo.DBA_GetForeignKeyColumnsNotInAnyIndex ()
ORDER BY ChildTable, COLUMN_NAME

Creation Date: August 24, 2006

For more scripts like this one, visit http://www.LearnSqlServer.com/
*/
RETURN (
-- Foreign Key Columns
SELECT OBJECT_ID ( tc.TABLE_SCHEMA + '.' + tc.TABLE_NAME ) AS ObjectId
, '[' + tc.TABLE_SCHEMA + '].[' + tc.TABLE_NAME + ']' AS ChildTable, cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc -- Returns child table info
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

EXCEPT

-- Columns in Indexes
SELECT DISTINCT c.object_id
, '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']' AS TableIdentifier
, c.name AS ForeignKeyColumnsNotInAnyIndex
FROM sys.all_columns c JOIN sys.index_columns sc
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN INFORMATION_SCHEMA.TABLES t
ON sc.object_id = OBJECT_ID ( t.TABLE_SCHEMA + '.' + t.TABLE_NAME )
)
GO
SELECT * FROM [DBA_GetForeignKeyColumnsNotInAnyIndex]()

---

Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server

I just wanted to blog here to note that I just updated the http://www.learnwindows2003.com/FreeVideos/
Windows 2003 training videos Free Videos page. These are nice little (free) 10-20 minute video tutorials (.avi) on everything from simple topics like how to add users to the Active Directory to more advanced topics like implementing software-based RAID 5 arrays. Also, just like SQL Server training videos, they have subscriber videos.

---

Technorati tags:
free tutorials, grant moyle, windows 2003, windows 2003 tutorials, windows training, windows tutorials

I'm really putting this here so that I'll be able to find it easily :)

Cumulative hotfix post-SP1: http://support.microsoft.com/default.aspx/kb/918222

Note: you must already have SP1 installed... And this is a pain to install, just like SP1...

---

Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server, Service Pack, SP1, Hotfix, kb918222

I wrote this today just so that I could get a nice snapshot of the SERVERPROPERTY values. Feel free to use at will!

-----------------------------------------------------------------------

CREATE VIEW dbo.GetServerInfo
AS
/*
Author: Scott Whigham ( http://www.LearnSqlServer.com )
Description: Returns a list of every property exposed by SERVERPROPERTY()
Create Date: Aug 17, 2006

Last checked against July, 2006 Books Online for every option of SERVERPROPERTY
*/
SELECT SERVERPROPERTY('BuildClrVersion') AS [BuildClrVersion]
, SERVERPROPERTY('Collation') AS [Collation]
, SERVERPROPERTY('CollationID') AS [CollationID]
, SERVERPROPERTY('ComparisonStyle') AS [ComparisonStyle]
, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS
, SERVERPROPERTY('Edition') AS [Edition]
, SERVERPROPERTY('EditionID') AS [EditionID]
, SERVERPROPERTY('EngineEdition') AS [EngineEdition]
, SERVERPROPERTY('InstanceName') AS [InstanceName]
, SERVERPROPERTY('IsClustered') AS [IsClustered]
, SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
, SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly]
, SERVERPROPERTY('IsSingleUser') AS [IsSingleUser]
, SERVERPROPERTY('LCID') AS [LCID]
, SERVERPROPERTY('LicenseType') AS [LicenseType]
, SERVERPROPERTY('MachineName') AS [MachineName]
, SERVERPROPERTY('NumLicenses') AS [NumLicenses]
, SERVERPROPERTY('ProcessID') AS [ProcessID]
, SERVERPROPERTY('ProductVersion') AS [ProductVersion]
, SERVERPROPERTY('ProductLevel') AS [ProductLevel]
, SERVERPROPERTY('ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime]
, SERVERPROPERTY('ResourceVersion') AS [ResourceVersion]
, SERVERPROPERTY('ServerName') AS [ServerName]
, SERVERPROPERTY('SqlCharSet') AS [SqlCharSet]
, SERVERPROPERTY('SqlCharSetName') AS [SqlCharSetName]
, SERVERPROPERTY('SqlSortOrder') AS [SqlSortOrder]
, SERVERPROPERTY('SqlSortOrderName') AS [SqlSortOrderName]
GO
SELECT * FROM dbo.GetServerInfo

Tony Rogerson just posted this post about his wishlist. How about a USB-powered, 4 port hub that features a coffee warmer? Here you go - link

I just posted an interview with Brad McGehee here. Brad was a good sport and answered some questions that I'm sure he didn't want to!

Thanks, Brad!

Check it out at http://www.learnsqlserver.com/Interviews/LearnSqlServer/BradMcGehee_20060813/Sql-Server-Performance-1.aspx

I just posted a rather lengthy interview with one Jesse Hersch, author of SqlSpec (great data dictionary software for only $20). Check it out - Interview with Jesse Hersch

If you didn't already know, you can store your own error messages inside SQL Server's sysmessages table (in the master database). This is a handy feature because it allows you to store custom error messages that you can now invoke from any database on the server. In this post I want to show you how you can create your own error messages (using the stored procedure sp_addmessage), how error messages work in SQL Server, and how you can customize your error messages using parameter substitution. Let's see an example:

First, here's just a basic example of how to add a message and then invoke that message:
EXEC master.dbo.sp_addmessage 50001 -- the error number must be higher than 50000
, 16 -- the Severity Level you want for this message (most of the time you'll use 16)
, 'This is the world''s greatest error message!' -- your error

RAISERROR (50001, 16, 1 ) -- the "1" is the State and is mostly unused

You can make your error messages even more dynamic by using parameters in the error message string. Parameters in SQL Server error messages generally will use the %s marker (string parameter) for easier formatting and are very easy to use and retrieve. One thing: you cannot pass functions into the message when you call it therefore you must declare local variables, store your function's results in these variables, and then pass the variables into the message call.

-- EXEC sp_dropmessage 70859 -- removes the message from the system

IF NOT EXISTS ( SELECT * FROM master.dbo..sysmessages WHERE error = 70859)
-- This line adds a new error message to the database. It accepts four string parameters (all denoted by the "%s")
EXEC sp_addmessage 70859, 16, 'The user - %s - was trying to do something really horrible in the %s database on %s at %s. Go punch them in the arm and tell them to stop!'
GO
-- To call this error message, use RAISERROR:
DECLARE @UserName NVARCHAR(128), @DatabaseName NVARCHAR(128), @CurrentDate VARCHAR(20), @CurrentTime CHAR(5)

-- You must declare local variables to store the results of functions:
SELECT @UserName = USER_NAME(), @DatabaseName=DB_NAME(), @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 101)
, @CurrentTime = CONVERT(CHAR(5), GETDATE(), 8)

-- For each "%s" in the error message, pass in a variable
RAISERROR (70859, 16, 1, @UserName, @DatabaseName, @CurrentDate, @CurrentTime)

In a future post I'll show you how to store multiple versions of the same error message so that your error message can be used in multiple languages. Stay tuned!

I haven't really seen people using this but somehow I came across it last month. I honestly can't remember if it's an original though or whether I accidently stole it from someone else's code but, either way, it's a way cool trick :)

From time to time, I need to run some setup/modification scripts in my database but, unless I'm in that database, I don't want the code to run. For example:

USE [learnsqlserver.com]
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')
Go

Yes, yes, yes the code is silly but it works for what I'm trying to show you here. Here's the problem with the above code: if the database [learnsqlserver.com] is not available for any reason, the code continues processing and will, depending on permissions, create a new table in the current database and dump lots of rows into it.

What I wanted was a way to write my SQL scripts so that, if [learnsqlserver.com] was unavailable, it would not execute any more of the script. It turns out that it's actually kind of easy to do this but you have to be wary of how one connection-level setting works.

SET NOEXEC

The key to doing this is to use the command SET NOEXEC . If it is turned on, SQL Server will parse the code but not execute it (which is ideal for the above situation).

Here's a revised version of our code:
USE [learnsqlserver.com]
GO
IF DB_NAME() != 'learnsqlserver.com'
SET NOEXEC ON
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')
GO

Problem solved! If the database is anything other than [learnsqlserver.com] then your following code will not execute. One helpful idea is that SET NOEXEC ON honors SET NOEXEC OFF so, even if SET NOEXEC is turned on, you can still turn it off so that you can run other code (example below):

USE [learnsqlserver.com]
GO
IF DB_NAME() != 'learnsqlserver.com'
SET NOEXEC ON
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
SET NOEXEC OFF
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')

I will offer one suggestion: once you SET NOEXEC ON, it remains ON until you explicitly set it OFF (on a per-connection basis). I would suggest that, if you plan on using this in your scripts, that you prefix all scripts with SET NOEXEC OFF to prevent any future runs of the script from being affected by previous settings. Here's what I mean:

SET NOEXEC OFF -- to prevent a future execution from not running script
USE [learnsqlserver.com]
GO
IF DB_NAME() != 'learnsqlserver.com'
SET NOEXEC ON
GO
CREATE TABLE BigTable (MyColumn Char(8000))
GO
WHILE 1=1
INSERT BigTable VALUES ('Hey there!')
GO

Hope this helps you like it has me - it's really been a big help :)

# posted by Scott Whigham @ 3:44 PM 2 comments links to this post

Anyone who has attended my SQL Server 2005 classes over the past years or watched any of the "What's New in SQL Server 2005" type videos up on SQL Server videos have heard me rant, rant, RANT about the new Books Online model and how it is almost worthless today. I'm not the only one - the esteemed William Vaughan has a great post on how the documentation today is terrible and Microsoft wants specific feedback about what's wrong.

Whether they want to hear that it's just unusable because Google/Yahoo is so much faster and better, I don't know...

Anyway, drop by Bill's post and post your own thoughts.

Hehe - that's what I thought when I first started playing with SQL 2005 lol. The best example out of this is the last paragraph:

"The bottom line is that even if we do the sort as part of the TOP operation in a sub-select, it does NOT guarantee anything about the output order of the query.

SELECT TOP 99 PERCENT * FROM T ORDER BY col1

is not the same as:

SELECT * FROM (SELECT TOP 99 PERCENT * FROM T ORDER BY col1) AS A

The top query guarantees the output order of the query. The bottom query does not (even if the rows happen to come back in sorted order)"

Great post - thanks QueryOptTeam!

Someone help me - I'm obviously missing something here... In SQL Server 2000 if you use the Query Analyzer with Ctrl+K to include the "actual" execution plan, the execution plan nodes do not say the word "Estimated" in from of CPU Cost and I/O Cost.

When you move to SQL Server 2005's SQL Server Management Studio and choose the "Include Actual Execution Plan" option, it adds the word "Estimated" in front of each ("Estimated CPU Cost" and "Estimated I/O Cost" among others).

Has there been a change in architecture? In both versions, SET STATISTICS PROFILE ON always uses the word "Estimated" in its output which leads me to think that it isn't an architecture change, but rather it's just a decision to add the term "Estimated" to the SSMS output.

The reason I post this is because I had a student ask about the difference in wording in the graphical plans and, to the best of my googling/BOL'ing, I could find nothing that said there's been any change in the use of "estimated".

Anyone have any more details? Maybe I'm just rambling... The documentation only mentioned the "estimated" versions - here

I think it stands for Sql Server Migration Assistant since there are a few mentions of SSMA and Migration Assistant on Microsoft Downloads page. I saw this - http://www.microsoft.com/downloads/details.aspx?FamilyID=d842f8b4-c914-4ac7-b2f3-d25fff4e24fb&DisplayLang=en and there are Migration Assistants for Oracle as well.

I haven't really played with it but I wonder how different/same it is from the Upsizing Wizard built into Access 97+? From the description, it handles the schema migration only (no forms/reports/etc a la the Upsizing Wizard).

From the description: "Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server.

SSMA for Access supports Access 97 - Access 2003 databases."

Brian Moran did the world a favor by putting up this list of TechEd 2006 links.

Thanks, Brian :)

Jamie Thompson has a good blog post about how you can use/change the DSVs (Data Source Views) to make life much simpler. He loves the DSVs, I love the DSVs, we all love the DSVs. Viva abstracción!

I just thought I'd post what I was reading today while creating a cube for LearnItFirst.com. I'm brushing up on the AS 2005 and how to manage the new features of Analysis Services:

Analysis Services 2005 Migration Whitepaper - Also includes discussion of Project REAL

UDM, New Dimension Types and Analysis Services 2005 - Christian Wade has a good breakdown of some of the new features complete with screen shots.

This is my first post on my new blog and I want it to be special - so I'm just going to come out and say it: I miss my Northwind database in SQL Server 2005. There's nothing wrong with AdventureWorks but the simplicity of Northwind sure does make for a good database for newbies.