Welcome to Julian Kuiters Friday, October 19 2018 @ 09:29 AM AEDT

Count the Occurrence of a Phrase or Word in a Column

SQL Server 2000

Simple little bit of code that will show you the number of times the value of @PhraseOrWordToCount is found in TextColumn.

SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, @PhraseOrWordToCount, '')) / LEN(@PhraseOrWordToCount)) AS Occurrence
FROM dbo.MyTable

I noticed this on Simon Sabin's blog, I think it has more applications than just FullText searches so I thought I'd do a modified re-post.

Snippet: Drop all connections to a database

SQL Server 2000

If you want to drop all the connections on you sql server that are accessing a particular database, here's some code:

USE master

DECLARE @dbname sysname

SET @dbname = 'name of database you want to drop connections from'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid

Another method you could use would be to take the database offline for a moment:

USE master


Resetting IDENTITY (SQL Server 2000)

SQL Server 2000

I've been asked plenty of times, how do you reset an IDENTITY column? After inserting data into a table, and then deleting everything, how do I make the IDENTITY to start from 1 again?

Personally I think is a bad idea to reset identity columns to any value lower than they currently are. To find out why, read on. But first, let me show you two ways you can reset an identity column.

Reseeding an IDENTITY column to a new value

-- syntax
DBCC CHECKIDENT ('mytablename', RESEED, new identity value)

-- example
DBCC CHECKIDENT ('Customer', RESEED, 1000)

Resetting IDENTITY to the next highest number not in use

-- syntax
DBCC CHECKIDENT ('mytablename')

-- example

Now why would it be a bad idea to reset an Identity column to a lower number? To say change it from 5,000 to 1? Because if your table is not empty, you might create a duplicate. If you have a unique index, you may recieve an error when inserting. And reusing numbers can cause confusion for users/developers.

Identity columns are typically used as unique references. Order Numbers, Customer Number, Receipt Number, Invoice Number, Patient Number, etc. Things where you don't want to confuse two different entities. Used as surrogate keys with referential integrity they are quite handy. Reusing a number means they no longer represent unique things.

But if you're using a unique index, your safe right? Not entirely. Say you only have 100 rows in your table, identities 5,000 to 5,100. For some reason they start at 5,000, and you decide to reset to 1 without truncating the table, because it looks nice. What happens after 4,999 inserts? Identity is set to 5,000. But you already have an 5,000 record. So you next insert will fail. As will all other insert attempts until IDENTITY reaches a number that is not already in use. If those were sales that you missed out on, that could be a serious loss.

What about in your development environment? I still think you should not reset them there either. Can all your applications handle high numbers? If you've declared the identity as an INT, but have used it as a smallint somewhere else, you might never realise until you hit a number smallint doesn't handle. What about testing procedures? Are they expecting certain low values to always exist? What about reporting? Can that handle numbers being reused, or does it change historical reports?

There is one instance where I DO support resetting identity, but only using the DBCC CHECKIDENT ('mytablename') method: after making inserts with IDENTITY_INSERT ON. In this case you ARE very likely to create duplicate numbers / insert errors. Immediately after making inserts with SET IDENTITY_INSERT ON, you should reset the identity. Don't specify a new value and the DBCC CHECKIDENT command will reset it to the maximum current value of the identity column. This way the next insert will be a unique number.

But please, don't reset IDENTITY to a number lower than it's current value. You really don't gain anything by having identity columns that start at 1. It doesn't use any less resources to start at 1 than it does to use 1,000,000.

How to Use ORDER BY in Derived Tables

SQL Server 2000

I was asked today how to select distinct columns while ordering by another column that does not appear in the column output list. For example:

select distinct [name]
from master..syscolumns
order by xtype

If you try to run this code, you will get the error message:

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

One way to get around this is to place the ORDER BY in a derived table, used as the source for the DISTINCT query. eg:

select distinct [name]
from (
select [name]

from master..syscolumns order by xtype
) as dt
However you will get this error:
Server: Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

But what if you don't want to limit your results to just the X TOP rows? What if you want all the rows?

Well not only can TOP be used to limit the number of rows a query returns, it can be used to instruct (some what redundantly) that you want all the rows: TOP 100 PERCENT.

select distinct [name]
from (
select top 100 percent [name]
from master..syscolumns order by xtype
) as dt

And there you have it! Distinct results ordered by a column that is not output!

Here's the SHOWPLAN_TEXT that shows the sort takes place:

  |--Hash Match(Aggregate, HASH:([syscolumns].[name]), RESIDUAL:([syscolumns].[name]=[syscolumns].[name]))
       |--Sort(ORDER BY:([syscolumns].[xtype] ASC))
            |--Clustered Index Scan(OBJECT:([master].[dbo].[syscolumns].[syscolumns]))

Rebuilding and Restoring Master Database

SQL Server 2000

Over the holiday break one of our servers suffered a disk failure in the main raid set. The disk was replaced and the raid set repaired. All seemed to be going well, until SQL Server wouldn't start. Checking the latest ERRORLOG for SQL Server showed:

2005-12-28 10:44:26.17 spid3     Starting up database 'master'.
2005-12-28 10:44:26.18 spid3     Error: 823, Severity: 24, State: 6.
2005-12-28 10:44:26.18 spid3     Error: 5173, Severity: 16, State: 1.
2005-12-28 10:44:26.18 spid3     Error: 5180, Severity: 22, State: 1.
and nothing more. Looking up the error numbers in BOL shows:
Error 823 - Microsoft® SQL Server™ encountered an I/O error on a read or write request made to a device. This error usually indicates disk problems. However, additional kernel messages in the error log, recorded before error 823, should indicate which device is involved.

Error 5173 - Cannot associate files with different databases.

Error 5180 - Could not open FCB for invalid file ID %d in database '%.*ls'.

With a master database this corrupted, SQL Server will not start. So the first task is to rebuild the master database, and then restore the orginal from a backup before the disk corruption occured. Once SQL Server is working again I'll check all the other databases for corruption.

Continue on to Rebuilding the Master Database.

Rebuilding the Master Database

SQL Server 2000

Rebuilding the master database is done using the Rebuild Master utility "rebuildm.exe" which is located in the 80\Tools\Binn directory of the SQL Server installation.

Select the server and instance that you want to rebuild. Now you are going to need access to the files from the install CD for SQL Server. Browse to the Data dircetory in the SQL Server install files. Set the collation you want SQL Server to be using, and hit Rebuild.

If you are run the utility using the CD as the source, you might encounter the error:

Rebuild Master failed with error -1:

Microsoft Support Q273572 lists the error as a bug in the utility. As it copies the read-only files from the install CD it does not turn off the read-only flag. To work around this simply copy the Data directory from the CD to the harddrive and turn off the read-only setting for the files. Then go ahead and rebuild again.

With the master database rebuilt, verify that it worked by starting SQL Server. The instance should start, but all the previous settings and databases will be missing. Restoring the Master database will bring back all the previous instance settings.

Read on about how to Restoring the Master Database.

Restoring the Master Database

SQL Server 2000

Stop the SQL Server instance. And start it in single user mode. Open a command prompt window. Change into you SQL Server Binn directory for the instance you are repairing (the default is c:\Program Files\Microsoft SQL Server\MSSQL\Binn) and run:

sqlservr.exe -c -m
The -c option tells SQL Server to run independantly, and not as a service. The -m option tells SQL Server to run in single-user mode.

Your sql server instance will then start up and you can connect to it with Query Analzyer.

If you get the error message:

Server: Msg 18461, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failer for user 'x'. Reason: Server is in single user mode. Only one administrator can connect at this time.
then you need to make sure that no other administrators are accessing the server. If you have Enterprise Manager or the SQL Server Service Manager open, close them, and try again. Make sure that any other services (like SQL Agent) that access SQL Server are also stopped.

Once you have successfully connected to your SQL Server instance in Query Analyzer, restoring the master database simply requires running the RESTORE DATABASE command:

RESTORE DATABASE master FROM DISK='N:\Backups\master.bak'
SQL Server should return:
The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.

Once your master database is restored, SQL Server will reboot and attempt recovery on the other databases listed in your master database. Check the error log again to see what databases have SQL Server is unable to recover.

2005-12-28 16:16:00.66 spid11    Starting up database 'HHH'.
2005-12-28 16:16:27.85 spid11    Error: 9003, Severity: 20, State: 1
2005-12-28 16:16:27.85 spid11    The LSN (72984:107911:2) passed to log scan in database 'HHH' is invalid..
2005-12-28 16:16:28.07 spid11    Error: 3414, Severity: 21, State: 1
2005-12-28 16:16:28.07 spid11    Database 'HHH' (database ID 7) could not recover. Contact Technical Support..

During the recovery of this server I found four databases were unrecoverable. Again, restoring these databases from their latest backup resolved the problem.

To ensure that all the databases were infact ok, I ran DBCC CHECKDB. A quick way to run it on all databases is with this script:

EXEC sp_MSforeachdb 'PRINT REPLICATE(CHAR(13),3) + REPLICATE(''='',80) + CHAR(13) + ''Checking: ?''

Snippet: Select the Time portion from a datetime

SQL Server 2000

Earlier on I wrote about how you can Select just the date part from a DateTime or SmallDateTime data type. (See Snippet: Select a Date or Current Date Without Time)

Using the CONVERT( ... , ... , style ) function we can specify the style attribute, that lets us specify to SQL Server (using style 14 or 114) that we just want the time portion.

-- Get current time (without date) in 24-hour time:
SELECT CONVERT( CHAR(8), GetDate(), 14)

By changing the number of CHAR characters you can select the time with or without milliseconds and seconds.

-- Get current time (without date) in HH:MM:SS:mmm format:
SELECT CONVERT( CHAR(12), GetDate(), 14)

-- Get current time (without date) in HH:MM:SS format:
SELECT CONVERT( CHAR(8), GetDate(), 14)

-- Get current time (without date) in HH:MM format:
SELECT CONVERT( CHAR(6), GetDate(), 14)

Also see Snippet: Select a Date or Current Date Without Time

Q&A: Why does WHERE LEN(columnname) = 0 skips NULL values?

SQL Server 2000

Q: When I use the condition LEN(columnname) = 0, I don't get columns where columnname value is null. Why? Shouldn't LEN(NULL) = 0 ? Are my ANSI_NULLS or ANSI_DEFAULTS setting to blame?

A: No. LEN(NULL) will always return NULL. From SQL Server Books Online Null Values

A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE.

Ansi settings don't have any affect on this behaviour. You can check this with:

Both queries return the same results:
------------ ------------
NULL         0

(1 rows(s) affected)

------------ ------------
NULL         0

(1 rows(s) affected)

To correctly identify if your column contains a zero-width string or NULL, you can use either of these two sets of condtions:

WHERE LEN(columnname) = 0 OR columnname IS NULL
WHERE LEN(COALESCE(columnname,'')) = 0

SQL Server Agent Error: [LOG] The data portion of event 17052 from MSSQLSERVER is invalid

SQL Server 2000

If you're following Microsoft's Best Practice recommendations for securing SQL Server 2000, you should be running the SQL Server and SQL Server Agent services under separate domain user accounts (not the Local System account). Whilst setting this up you may encounter this error:

[LOG] The data portion of event 17052 from MSSQLSERVER is invalid

Adding the SQL Server service user account to the Administrators group fixes the problem, but does not adhere to Best Practices.

More info: