I created a batch file to run SqlMetal and generate Linq2Sql data classes, check into source control triggering a build, etc... I'd like to have this script run anytime there is a DDL change in Sql Server 2005.
Running the batch file via xp_cmdshell works fine outside of a trigger, like this:
exec master..xp_cmdshell 'd:\dev\db_triggers\generatedataclasses.bat', no_output
But when it runs as a trigger, it always times out connecting to the database, causing all DDL to fail. Here's my trigger:
CREATE TRIGGER [Trig_SqlMetal]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
exec master..xp_cmdshell 'd:\dev\db_triggers\generatedataclasses.bat', no_output
I'm looking for advice on two points:
- Make this work. For some reason it always fails when in the trigger, and doesn't when not in a trigger. Doesn't appear to be security related since it runs as LocalSystem in both cases.
- Make this happen asychronously so that failures and timeouts in SqlMetal don't cause DDL update failure. I've tried wrapping the batch file with another and a "start cmd.exe /c otherbatch.bat", but when running through sql server it seems to ignore the start (works fine from DOS). I could certainly write a polling process to look at some table and pickup events, but I'd prefer this be trigger based to make it less complex (or am I doing the opposite :) ).