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 […]

If you want to drop all the connections on you sql server that are accessing a particular database, here’s some code: USE master go 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) WHILE @spid IS […]

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 […]

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 […]

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 […]

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 […]

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 […]

Earlier on I wrote about how you can Select just the date part from a DateTime or SmallDateTime data type. (See [story:t-sql-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 […]

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. […]

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 […]