Export inserted value to a text file. 1

[lewatkin]

I have an insert trigger that is working fine. I have a need to export the primary key value that was inserted into the table to a text file. My attempts thus far have resulted in an error.

Here is the code:

BEGIN
DECLARE @MySql varchar(1000)
SELECT @MySql = 'osql -Q "SELECT NewRec_id FROM inserted" -E -o E:\SQLData\Backups\temp\data.txt'
EXEC xp_cmdshell @MySql
END

And here is the error that is getting exported to the above txt file:

Msg 208, Level 16, State 1, Server XXSPR40, Line 1
Invalid object name 'inserted'.


This is a third party application so we are really limited to what we can do. The above code is at the end of the the existing insert trigger. But where I put it in the existing code has resulted in the same error each time. I have enabled xp_cmdshell.

Thoughts?

Thanks in advance

Lee

 

[gmmastros]

This is gonna be painful because there are several challenges involved here.

First, you code isn't working because when you use cmldshell, you are essentially starting a new "batch" operation, so things within the trigger (like the inserted table) are no longer available to you.

Think of it like this... if you open SQL Server management studio and have 2 query windows open, one window has trigger code. Would the trigger code be accessible from the other query window? No.

That being said, there are a couple ways you can handle this.

1. You could create a string of ID's, separated by Carriage Return/Line Feed and then use that within your code.

2. You could insert these id's in to a new table built just for this purpose, and then have a SQL Agent job that exports this data to a file. You would want to schedule your job to run relatively often so that the data in the file is not "stale". For example, running the job on a weekly basis would probably not be acceptable, but running the job every second is probably too often.

Basically, when you write a trigger, you want to write it in such a way that it will not adversely affect your performance. If you try to write to a file from within a trigger, you may run in to performance problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lewatkin]

George,

Thank you for that clarifying that for me. It makes perfect sense now. I can work with the second suggestion you made.

Thanks again,

Lee

 

[TheBugSlayer]

Since you are limited in your options by the third-party app, a trigger looks like the only place you could implement either one of George's suggestions. I like option 2 better too.

If you were writing your own app, you could use the OUTPUT clause with your INSERT\UPDATE\DELETE statements.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)