Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Trigger with OSQL Expand / Collapse
Posted Monday, October 8, 2012 8:49 PM


Group: General Forum Members
Last Login: Sunday, June 9, 2013 11:51 AM
Points: 13, Visits: 31

I have a requirement as below,

1) whenever a column in a table gets updated with a value then i have to join few more tables and get related data and generate a file with that.
2) file should be created in windows server at a specified path

Our approach:
1) we created a trigger which does data fetching and calls OSQL - not working as it hangs
2) A trigger which calls an SP which inturn calls a OSQL to generate a file - not working as it hangs

Any ideas?

Thanks in advance
Post #1370139
Posted Monday, October 8, 2012 9:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:20 PM
Points: 3,529, Visits: 3,063
I would not be attempting to execute an external program from within a transaction - there are many potential problems including security (which I suspect may be your current problem) and, potentially, performance.

Personally, I would log sufficient information for the file to be written asyncronously and use completely separate process (that read the log) to actually write the files. There are many options for implementing this including SSIS and also OSQL perhaps scheduled for execution using SQL Server Agent. Another option is to implement the writing of the files using Service Broker.

Post #1370143
Posted Monday, October 8, 2012 10:16 PM



Group: General Forum Members
Last Login: Friday, December 2, 2016 3:25 AM
Points: 7,933, Visits: 14,355
I agree with the advice to use something other than a direct call inside the transaction. Write the data you need to export to a table and then have an external process do the actual export. Service Broker would work. A plain-old "work table" could work too.

As an aside, osql.exe has been deprecated since SQL 2005 was released. sqlcmd.exe is the replacement for it. Further to that osql is not really the right tool for exporting data. If you want a command line tool use bcp.exe but SSIS is usually my preference for this type of work.

There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1370152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse