October 8, 2012 at 8:49 pm
Hi,
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
October 8, 2012 at 9:24 pm
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.
October 8, 2012 at 10:16 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply