-1

I have a query that uses sqlcmd to write a file in a certain folder.

 EXEC master..xp_cmdshell'sqlcmd -q "set nocount on; use [myDB] SELECT top 1 T.Field1, T.Fieldn FROM [dbo].[myTable] AS T" -oc:\Outputfolder\FileName.csv -E -m 1 -h2 -s","'

The EXEC Master instruction works fine by it self by inside the trigger, it generates a loop: The file is created but sql got stuck in 'Executing query'

The structure of the trigger is:

 ALTER TRIGGER CreateCsvFile_Trigger ON [dbo].[TempProjectCsv] AFTER INSERT 
 AS 
 <Variables>
 BEGIN
 SET NOCOUNT ON;
 EXEC master..xp_cmdshell'sqlcmd -q "set nocount on; use [myDB] SELECT top 1 T.Field1, T.Fieldn FROM [dbo].[myTable] AS T" -oc:\Outputfolder\FileName.csv -E -m 1 -h2 -s","'
 END
 GO

Anyone can tell me why it works fine alone but it breaks from the trigger? As always, thanks!

DenLun
  • 194
  • 2
  • 16
  • Does it have to be implemented as a trigger? Could it be done running a batch process periodically that queries the database and outputs the result in a csv file? – Ola Ekdahl Mar 06 '15 at 03:50
  • Hmm the idea is to run each time a record is inserted. What do you mean? A batch process that runs every hour and process each record, for example? – DenLun Mar 06 '15 at 14:36
  • Something like that. Having a trigger that outputs to a file for every record that gets inserted will probably introduce some scalability challenges. – Ola Ekdahl Mar 06 '15 at 15:32

2 Answers2

1

When a trigger is executed it executes under the security context of the person who did the insert.

By default, both DML and DDL triggers execute under the context of the user that calls the trigger. The caller of a trigger is the user that executes the statement that causes the trigger to run. (MSDN)

So when you test the command shell you are probably a DBA who has permission to do all kinds of things that the user running the insert (who has permission to insert on a table) does not have permission to mess with the file system from SQL Server. You know that the file will be written on the server?

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • Right now, I'm running the insert with the same user (an admin) through SQL Management. Yep, the file is being written in the server. – DenLun Mar 06 '15 at 14:35
  • I would then try using the bcp command to copy out the data. Whenever I do selects at the dos (command) level I use BCP and usually works. – benjamin moskovits Mar 06 '15 at 14:40
  • Oh, I didnt use bcp because I read that it doesnt allows you to use headers. I'll search a litte bit more to see if bcp cause the same loop or not – DenLun Mar 06 '15 at 15:25
0

make sure all the changes in your SQL script is COMMITTED prior to your EXEC xp_cmdshell part, otherwise it will hang your trigger due to tables being locked.

Toto
  • 1