Archive for the 'SQL' Category

Synchronize a SQL Server Database with an SVN Repository

November 23rd, 2009 Comments(10)

Yes, it is possible to automatically synchronize a SQL Server database with an SVN repository. Every change to a table, stored procedure or other database object will automatically be committed to the repository. New objects will get added, deleted objects deleted, etcetera. As a bonus, why not monitor the contents of (some) database tables ? You probably have a few tables that contain parameter values that influence the applied business logic ? It may be useful to put their contents under source control too!

Read more »

A Stored Procedure to Launch a SQL Agent Job and Wait

August 18th, 2009 Comments(8)

If you need to launch a SQL Agent Job from within a stored procedure you can simply use msdb..sp_start_job @job. But what if you need to wait for that job to finish before you can continue whatever you are doing in that stored procedure ? Here is the code for a stored procedure that does exactly that: it launches a SQL Agent job and waits for it to finish. The name of the job and the maximum time to wait are its input parameters.

Read more »

Deploying a Microsoft BI Solution via Script

December 19th, 2008 Comments(2)

At the end of a phase of a business intelligence project, when it has been accepted and it is time to move into production, comes the deployment on the production environment. The deployment itself should also be testable, reproducible and of course be completely debugged. The method largely depends on the customer’s IT team’s way of working. Some will insist on Installshield executables, some will prefer moving a server from acceptance to deployment, some will restore backups, some will work script based. I prefer that last approach and go into some detail on how to do this for all the typical parts of a MS BI solution: the database, integration services packages, SQL Agent jobs and the SSAS cubes.

Read more »

Convert an Integer Date to a Smalldatetime

December 21st, 2007 Comments(1)

A common way to represent a date – often in mainframe systems – is in the format yyyymmdd. Unfortunately it is not easy to do calculations on such a representation. To be able to use the native T-SQL date functions such as datediff and dateadd you will need to convert it to the datetime or smalldatetime type.

Read more »