Specify IIS Application Pool User in Windows Folder Security

Friday, April 4th, 2014 Web Development No Comments

For example, if your application pool was created with the name mydomain.com, then the way to reference this user in standard windows security settings is:

IIS APPPOOL\mydomain.com

Use jQuery to Find and Replace in HTML

Thursday, November 1st, 2012 Web Development No Comments

The following code will perform a find and replace (‘.png’ and ‘.gif’ respectively) on the HTML of (in this case) an element whose id is equal to ‘myDiv’


$(document).ready(function () {
    $("#myDiv").html( $("#myDiv").html().replace(".png", ".gif") );
});

Tags:

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:

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: ,