Skip to main content

Count the Occurrence of a Phrase or Word in a Column

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

Snippet: Drop all connections to a database

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

Rebuilding and Restoring Master Database

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

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

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

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