Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

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


Group: General Forum Members
Last Login: Sunday, June 09, 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 08, 2012 9:24 PM


Group: General Forum Members
Last Login: Yesterday @ 9:23 PM
Points: 2,841, Visits: 2,422
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 08, 2012 10:16 PM



Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1370152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse