November 24, 2014
Hot Topics:

Consider Database Triggers in Your .NET Enterprise Application Design

  • September 22, 2005
  • By Scott Robinson
  • Send Email »
  • More Articles »

A Menu of Enterprise Triggering Options

Now, you get to the nuts and bolts of enterprise database triggering.

Send messages to a queue from SQL Server

Among the various Microsoft technologies suitable for enterprise, MSMQ is the unsung hero. Message queuing is freely available with XP and 2003 systems, and has advantages over transferring data between systems in folders because MSMQ has all the message tracking built-in. If you send a message to another application or system via MSMQ, MSMQ will make sure it's delivered.

How do you hand a message off from the database to an MSMQ queue? There are several steps because you can't tell SQL Server to do it directly.

  1. Create an application to do the queuing. This can be a console app, for simplicity's sake. The app's sole function is to drop the file into a specified queue. No logic is needed at this point; it should take whatever is handed to it and just drop it in.
  2. Use the extended stored procedure xp_cmdshell to execute the console app. The extended stored procedure xp_cmdshell can do command-line application execution from a stored procedure, and it's a simple matter to load your console app within the stored proc:
  3. DECLARE @cmd VARCHAR(255)
    SET @cmd = 'c:enterpriseappmyconsoleapp.exe '
    EXEC master..xp_cmdshell @cmd
    
  4. Create a trigger to execute the stored procedure. Execute it every time there's an INSERT or and UPDATE or both in the table that will feed data to the downline app.
Word of warning! If you use xp_cmdshell, you need to consider security. To execute it, you need to have a sys admin role or the xp_sqlagent_proxy_account on the SQL Server where it's used!

Note that you can use this same series of steps to write to a file in a folder, rather than to a queue, if you like—but there's a less-complicated way to get data into a file in a folder. Read on.

Use OSQL to write to a file. Ever used osql? It's a nifty command-line program that runs SQL statements. It saves you a step when compared to the technique described above.

  1. Create an osql statement that generates the data you want to pass to the next application. This is simple to do. The command line format for such a statement is:
  2. osql  /U  /sa  /P  {password, if desired}  /d  {directory}
          /S  {server}  /Q  {the SQL statement that retrieves the
                             data you wish to hand off}
          -o  newmsg.txt
    
  3. Execute that statement via the extended stored procedure xp_cmdshell, as before:
  4. DECLARE @cmd VARCHAR(255)
    SET @cmd = 'c:enterpriseappmyosql.bat '
    EXEC master..xp_cmdshell @cmd
    

    Or

    Condense steps 1 and 2 by embedding the entire text of the osql statement into the @cmd, rather than storing it as a .bat. That is,

    SET @cmd = 'osql  /U  /sa  /P  {password, if desired}
        /d  {directory}  /S  {server}
        /Q  {the SQL statement that retrieves the data you wish
             to hand off}  -o  newmsg.txt'
    EXEC master..xp_cmdshell @cmd
    
  5. Create a trigger to execute the stored procedure. Same as before.

Use BCP to copy a result set to a file. This may be the option to consider if you're passing a new or updated table to another application or system. In this variation, a stored procedure fills in a table, and a trigger initiates BCP to export the table—and the export is to a target folder that is a hand-off to the receiving app or system. A disadvantage of this approach is that the table then needs to be cleared.

Conclusion

With these three techniques alone, you have enough tools in your enterprise toolkit to come up with various ways of tying applications and systems together with data-layer triggering mechanisms. Mix and match if you like, but don't fail to capitalize on these inexpensive solutions to a core enterprise design problem!





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel