SQL – Create a custom Stoplist

Monday, April 16th, 2012 Uncategorized No Comments
USE [my_db_name]
GO

--View the content of the system sefault stoplist
SELECT * FROM  sys.fulltext_system_stopwords;
GO

--Create a new stoplist called heStoplist1, by copying the system stoplist
CREATE FULLTEXT STOPLIST heStoplist1 FROM SYSTEM STOPLIST;
GO 

--Get the ID of the new custom stoplist (heStoplist1)
SELECT stoplist_id, name
FROM sys.fulltext_stoplists;
Go

--View the content of the new custom (heStoplist1) stoplist by name
SELECT * FROM  sys.fulltext_stopwords
WHERE stoplist_id = (SELECT stoplist_id FROM sys.fulltext_stoplists WHERE [name] = 'heStoplist1')
GO

--Add a new word to the custom (heStoplist1) stoplist
ALTER FULLTEXT STOPLIST [heStoplist1] ADD 'test' LANGUAGE 'English';
Go

--Remove an existing word from the custom (heStoplist1) stoplist
ALTER FULLTEXT STOPLIST [heStoplist1] DROP 'test' LANGUAGE 'English';
GO

--Relist the content of the updated new custom (heStoplist1) stoplist by name
SELECT * FROM  sys.fulltext_stopwords
WHERE stoplist_id = (SELECT stoplist_id FROM sys.fulltext_stoplists WHERE [name] = 'heStoplist1')
GO

Orphaned Users Following a SQL DB Restore

Wednesday, February 29th, 2012 Web Development No Comments

When you restore a SQL database, whether the backup you used was from the same server you are restoring to or not, SQL will ‘orphan’ the users assigned to that restored DB.

I assume this is because SQL is erring on the side of caution/security. For example, if the source of the backup has users with the same name as logins on the destination for the restore, they shouldn’t really inherit/assume the rights assigned to that login, just because they have the same name.

Anyway, to fix these orphaned/broken/detached users following a DB restore, run this script:

sp_change_users_login 'update_one', 'username', 'username'

Tags:

How to Kill Open User Processes in SQL

Friday, August 12th, 2011 Web Development No Comments

Sometimes when I attempt to restore an SQL database, I get a message pretty much saying ‘the DB is in use’. So I use this, to ensure any open connections to the DB are disconnected (killed):

DECLARE @dbName NVARCHAR(50)
SET @dbName = N'NameOfYourDB'

DECLARE @sqlKill VARCHAR(max)
SET @sqlKill = ''

SELECT @sqlKill = @sqlKill + 'Kill ' + CONVERT(VARCHAR, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@dbName) AND SPId <> @@SPId

EXEC(@sqlKill)

Tags:

Falling Skies – Religious Hang Ups

Thursday, August 4th, 2011 Atheism, TV No Comments

I’m really enjoying Falling Skies, I have to say. I was drawn to it for the Steven Spielberg connection, so I thought it can’t be that bad (despite The 4400) and so far it isn’t. It’s a very similar set up to The Walking Dead, but I think I prefer Falling Skies.

What I don’t like, is that at the end of the last episode I watched (Season 1, Episode 4, Grace), it went all religious with hand-holding and praying.

I hope this is just a tip of the cap (although the necessity of this is bad enough) to keep Christian America happy and doesn’t progress into an overriding moralistic sub-plot.

Rant-over.

Doug Stanhope’ll make me feel better.

My New Favourite Band, The Decemberists

Thursday, August 4th, 2011 Music No Comments

A great acoustic performance from the lead singer of my new favourite band, The Decemberists

Use TSQL to Create a Text File

Friday, July 29th, 2011 Web Development No Comments

I recently had to shift the responsibility of creating a text file from the web server, to the DB server, this is how I did it:

DECLARE @OLEfilesytemobject INT
DECLARE @OLEResult INT
DECLARE @FileID INT  

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF (@OLEResult <> 0)
	PRINT 'Error: Scripting.FileSystemObject...'
ELSE
BEGIN  

	-- check if file exists
	EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @filePathAndName   

	-- if file exists
	IF (@OLEresult=1)
	BEGIN
		PRINT 'File exists, abort...'
	END
	ELSE IF (@OLEresult=0)
	BEGIN
		PRINT 'File does not exist, continue...'
		-- open file
		EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT, @filePathAndName, 8, 1
		-- write text to the file
		EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @fileContents
	END

	EXECUTE @OLEResult = sp_OADestroy @FileID
	EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject 

	SET @fileCreated = 1

END

If you want to append to an existing text file, simply remove the if statement on line 6 of the code.

Also, there are some settings on the SQL instance you need to update, in able to access the file system on the server:

Enable OLE Automation

  • Open Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration > Click Surface Area Configuration for Features
  • Under Database Engine > OLE Automation, check Enable OLE Automation

Give DB user rights to EXECUTE relevant file system functions

  • In SQL Server Management Studio, add the SQL login that has been created for use by this project to the ‘Master’ SQL System Database
  • Browse to the database Master > Programmability > Extended Stored Procedures > System Extended Stored Procedures
  • For each of the following system stored procedures, add the user (applied in step 1 above) to the permissions list and ‘Grant’ the Explicit > Execute right:
    • sys.sp_OACreate
    • sys.sp_OADestroy
    • sys.sp_OAMethod

Tags:

How to rename a table in SQL, using SP_RENAME

Tuesday, July 19th, 2011 Web Development No Comments

This is an easy way to rename a table in SQL:

EXEC SP_RENAME '[existing_table_name]', '[new_table_name]'

I’ve used this to create archives of tables, you can rename the existing table name, by appending a timestamp as follows:

DECLARE @archiveTableName NVARCHAR(50)
SET @archiveTableName = 'tbl_myRecords_archived_' + CONVERT(VARCHAR(50) ,CURRENT_TIMESTAMP, 120)
SET @archiveTableName = REPLACE(@archiveTableName, ' ', '')
SET @archiveTableName = REPLACE(@archiveTableName, '-', '')
SET @archiveTableName = REPLACE(@archiveTableName, ':', '')
EXEC SP_RENAME 'tbl_myRecords', @archiveTableName

Tags:

New (to me) .NET Type ‘List’

Tuesday, September 14th, 2010 Web Development No Comments

I need to use this more, I’m sure it’s (not noticably) slower than ArrayList but it is strongly-typed and it inherits standard collection functions such as .IndexOf()
Dim myListOfStrings As List(Of String)
Dim myListOfMojinators As List(Of MyCustomClass.Mojinator)

Tags:

Cool CSS3 and jQuery Animation Demos

Wednesday, September 8th, 2010 Web Development No Comments

www.webdesignersblog.net/css3/35-best-awesome-css3-animation-demos

Tags: ,

Inserting a new blank row in a DataTable

Monday, September 6th, 2010 Web Development No Comments

This always escapes me when I need it, so here it is for posterity:
Dim myDataRow As DataRow = myDataTable.NewRow()
myDataTable.Rows.InsertAt(myDataRow, 0)

The second arguement is the index at which the new row should be added.

An alternate short-hand way of writing this:
myDataTable.Rows.InsertAt(myDataTable.NewRow(), 0)

Tags: ,