Trigger with OSQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » Trigger with OSQL

Trigger with OSQL

Rate Topic

Display Mode

Topic Options

Author

Message

Compassionate

Posted Monday, October 08, 2012 8:49 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:52 AM
Points: 11, Visits: 28

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

Post #1370139

happycat59

Posted Monday, October 08, 2012 9:24 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:55 PM
Points: 2,471, Visits: 2,066

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

opc.three

Posted Monday, October 08, 2012 10:16 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 6,724, Visits: 11,771

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 »

Permissions