To my shame, I only recently realised there was a single character wildcard available in SQL ( _ ) as well as the mutliple character wildcard ( % )!  really handy for substituting single quote characters (or other weird ones) to ensure a match when doing a ‘like’ lookup

I've recently inherited the integration support for a web project being built by a third party which is about to go live.  Amongst the numerous odd's and ends which are being tied up are a bunch of SSIS packages which have been updated, but not run for some time.  When the moment came to finally push the button, as is my luck, nothing happened. 

The package clears a bunch of data from some book and author tables, then replaces it with nice fresh data from our internal data repository- the trouble is, the developer has a "shopping basket" and "shopping basket items" table, which had the "book id" from the books table as a foreign key- so in order to maintain referential integrity the DBMS (Sql 2000) moaned up when we tried to bin everything from the books table. The exact error i got was;

Error: DELETE statement conflicted with COLUMN REFERENCE constraint ‘FK_ShoppingBasketItem_Book’.  The conflict occurred in database ‘BookDB’, table ‘ShoppingBasketItem’, column ‘BookID’.

Error: Executing the query “Delete from Book” failed with the following error: “The statement has been terminated.”  Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

After having googled about for a bit it seems there is no nice way to do this- but the cleanest work around I could find was to disable the CHECK constraint before the package ran, then re-enable it when complete- the syntax was;

ALTER TABLE <Tablename> NOCHECK CONSTRAINT <constraint name>

ALTER TABLE <Tablename> WITH CHECK CHECK CONSTRAINT <constraint name>

For example, in my case;

ALTER TABLE ShoppingBasketItem NOCHECK CONSTRAINT FK_ShoppingBasketItem_Book

ALTER TABLE ShoppingBasketItem WITH CHECK CHECK CONSTRAINT FK_ShoppingBasketItem_Book

Phewf- that is a lengthy title! 

The ongoing battle with the corporate machine to invest a bit of cash on some new tech is ongoing- as a result our primary RDBMS is still MS Sql 2000!  I recently had a fairly large database built by a third party which we need to host- i restored from the backup they supplied and setup a new user with no permissions and started gradually building access only to what was neede- but with 923 stored procedures to grant execute permission to, this suddenly looked like a total chore.

Luckily a bit of googling by my colleague Paul, turned up this little nugget from sqldbatips.com.  Just incase the article is ever taken down, this is what it said;

SQL2000

A common answer to the question posed by the
title of this article is to run a query such as the one below in Query Analyzer
and copy and paste the results into a query window and execute them. The query
uses the INFORMATION_SCHEMA views to generate a list of GRANT statements for
each procedure in the database.

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO <insert_username>' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
	

However, what we can do is shortcut the copy and
paste step by using a system extended stored procedure called xp_execresultset (Note that after SQL2000 Service Pack 3,
xp_execresultset is no longer an extended stored procedure but simply a wrapper
for the system stored procedure sp_execresultset) This extended stored procedure
allows you to pass in a SQL query that generates a result set that it will then
execute as long as the result of the query is a legitimate SQL batch. Thus we
can take the query above and pass it to xp_execresultset and it will generate a resultset containing
GRANT statements for each procedure in the database which will then be executed
thus neatly avoiding the need to copy and paste. I have created a stored
procedure called sp_grantexec
(click to follow link to code) that takes a user name or role as a parameter and
grants execute permissions on all stored procedures in the current database to
wrap this up and make it easier to use. An example of using xp_execresultset is shown below.

declare @sql nvarchar(4000)
declare @db  sysname ; set @db = DB_NAME()
declare @u   sysname ; set @u = QUOTENAME('<insert_username>')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

exec master.dbo.xp_execresultset @sql,@db