19 August 2009

ANSI SQL Compliance Validator

Of course, most of us don't write "pure" ANSI SQL queries because vendors don't fully implement the standard. But, it might be interesting to see how well your queries comply with the ANSI SQL standards.

Here is a web-based validation tool into which you can paste a query and it will tell you the query's level of compliance give corrective suggestions...




12 August 2009

Replicating Stored Procedures

For occasionally connected client replication, we are publishing stored procedures so all the clients have what they need to interact with their local data. So, what happens when you need to modify a procedure? Let's just alter one and see what happens. Ready? ALTER PROCEDURE usp… Now, let's wait and see what happens at the client... okay, nothing yet. Let's check again; still nothing. Okay, we'll wait a few minutes. Now? Still nothing. What's going on? Let's ask BOL.

Me: Hey, BOL, what happened to my stored procedure definition?

BOL: Well, the ability of merge replication to replicate stored procedure definitions (or views or user-defined functions), is merely a convenient mechanism for deploying these components to subscribers.

Me: What, exactly does that mean?

BOL: Subsequent changes to the definitions of these objects are not copied automatically to Subscribers.

Me: Thanks, BOL. You sure know your stuff.

So, there you have it. If you use replication to send procedures to your clients, it only works once. After that, you're on your own. Good thing we learned how to use sp_addScriptExec last time!

Replicating Database Security

When setting up an occasionally connected database to work off-line, I had a great thrill the first time the replication snapshot actually made it to the client with all tables, data, and stored procedures intact. In fact, after days of frustration, I was so happy I went home for the day determined to put the entire replication experience (unpleasant) out of my mind for the evening!

Fast-forward to our iteration review demo the next day: "Hey, let's see if the application works off-line, now that we have replication working." "Okay, here goes…" Of course, we immediately got an error telling us that the user could not open the database specified in the login: our new off-line client was broken.

After spending the rest of the morning sobbing in the corner, I thought I'd look into the problem a little bit. It turns out that the DisconnectedClient_Role database role and associated permissions had not replicated to the client and Domain Users did not have access to the database. Only tables, procedures, views, and functions can be replicated.

My first resource, Google, failed to return anything useful so, I went to my second resort/newsgroup aggregator, groups.google.com. A quick search on microsoft.public.sqlserver.replication turned up the solution; sp_addScriptExec. This is basically a carte blanche post-initialization script deployment tool. I took my role creation script and stored procedure permission script and saved them in a file where both the publisher and subscriber could see and access it. Then, I executed the sp_addScriptExec against the publisher database like this:

, @scriptFile = '\\snaphot_storage_location\DisconnectedClient_role.sql'
, @skipError = 1

It immediately sent the role and permission changes down to all active subscribers and any subscriptions that were created subsequently received the permissions as part of the snapshot -- we now had security. This time, I actually logged into a client and verified that the off-line client could access the local copy of the database. Success!

Easily Create Database Snapshots

Creating database snapshots can be a bit tedious, especially on databases with multiple files.

A workmate put the following script together to take away the drudgery. Just give your database name and desired snapshot name to the following script and it creates a database snapshot for you using the same data directories as the live database.

If you want the snapshot data files somewhere else, you can comment out the EXECUTE statement then modify the generated script with your preferred file location.

Notice the $CURSOR$ keyword. This makes a handy SQLPrompt snippet!

This script will create a snapshot of the source DB, it will handle multiple
files in the DB.  It also supports multiple snapshots of the same source by
changing @SSName.
   Set @SourceDBName to the name of the DB you want a snapshot of.
   Set @SSName to what you want to call your snapshot.
You can disable the Execute statement at the end if you just want to get
the TSQL code for later use.
@SSName        AS SYSNAME,
@SQL           AS VARCHAR(4000)

@SourceDBName = '$CURSOR$',
@SSName = 'snapshot_name'

+ @SSName
+ ' ON '
+ LEFT(script,LEN(script)
+ @SourceDBName
+ ', ' AS [text()]
WHEN RIGHT(mf.physical_name,4) = '.ndf'
+ mf.name
+ ' ,FILENAME = '''
+ REPLACE(mf.physical_name,'.ndf','_'
+ @SSName
+ '.ss')
+ mf.name
+ ' ,FILENAME = '''
+ REPLACE(mf.physical_name,'.mdf','_'
+ @SSName
+ '.ss')
END AS col
sys.master_files AS mf
INNER JOIN sys.databases AS db
ON mf.database_id = db.database_id
WHERE    db.name = @SourceDBName
AND mf.type_desc = 'ROWS'
ORDER BY [file_id]) AS a
     FOR XML
AS script) b