Jan 29

Find and Replace in a Column

Here is a quick and simple way of finding a value in a particular column and removing it:

This looks for a quote () in the column named ID and removes it.

Jan 23

Bulk Insert

Bulk Insert is a fairly common function, but not so common that I can remember the syntax every time. Here I’ve posted the version I use most:

Note that Bulk Insert does not support skipping headers. The source file should not include headers otherwise the load will fail.

More about Bulk Insert:

Transact-SQL : BULK INSERT

Jan 22

Grant execute permissions to ALL stored procedures in a database

I’ve found many scenarios where there are certain users or groups that require execute permissions for all stored procedures in a database. Rather then granting access to all the objects individually, not to mention new objects that are created after you’ve granted access, I prefer to create an executor role and assign their user account to the role. The following works for SQL 2005 and above:

Then grant the db_executor role to their user account. Done!