SQL Server Wave

Archive for the ‘Uncategorized’ Category

I very much liked the idea of Contained Databases as it would make it much easier to move databases across different servers and even use this feature as part of a disaster recovery strategy. Here is really good article that explains how Contained Databases work in Denali:

http://www.sqldbadiaries.com/2011/02/01/contained-databases-in-sql-server-denali/

While very excited about this feature, I came across a big issue as it is not supported on databases that are either part of Replication, Change Data Capture or Change Tracking.

Now, as most of the databases I have in production are replicated in one way or another, it simply will not be possible to use it.

I recently had to delete records from a table regularly, every 10 minutes. I first obviously thought of setting up  a SQL Agent Job, easy!!!

Now here is my problem, I am using SQL Server Express Edition, which doesn’t come with SQL Server Agent features. So the SQL way wasn’t an option. I then realised that I could setup a scheduled task in Windows (XP) that would run a batch file every 10 minutes, job done!

Here is an example of the batch file:

@ECHO OFF
cd C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
SQLCMD -SserverName\instanceName -Uusername -Ppassword -Q"delete from DatabaseName.dbo.TableToDelete"
@ECHO OFF

Save it as something like Query.bat and call it via a scheduled task.