title: How can I have Sql Server 2005 asynchronously call a DOS batch file from a DDL trigger?
body: <p>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.</p>
  
  <p>Running the batch file via xp_cmdshell works fine outside of a trigger, like this:</p>
  
  <pre><code>exec master..xp_cmdshell 'd:\dev\db_triggers\generatedataclasses.bat', no_output
  </code></pre>
  
  <p>But when it runs as a trigger, it always times out connecting to the database, causing all DDL to fail.  Here's my trigger:</p>
  
  <pre><code>CREATE TRIGGER [Trig_SqlMetal]
  ON DATABASE
  FOR DDL_DATABASE_LEVEL_EVENTS 
  AS
  exec master..xp_cmdshell 'd:\dev\db_triggers\generatedataclasses.bat', no_output
  </code></pre>
  
  <p>I'm looking for advice on two points:</p>
  
  <ol>
  <li>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.</li>
  <li>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 :) ).</li>
  </ol>