| Author |
Topic |
|
subtex
Starting Member
9 Posts |
 Posted - 2004-08-09 : 16:25:40
|
I am dynamically building a bcp string and then passing that to xp_cmdshell inside a trigger. Problem is when I do an update to the table (to set off the trigger) osql just hangs there, never completing the task. The bcp output file gets createdm but never filled with data.Running my bcp string directly from the cmd line works fine.Here's the relevent code:quote:
SET @bcpCommand = 'bcp "SELECT content_html FROM testdb..gonzo WHERE content_id="' + @cont_id + '"" queryout ' + @path + @tmp_filename + ' -T -c'EXEC master..xp_cmdshell @bcpCommand
if I print out the @bcpCommand in the trigger and comment out the execution of it, i can see that it's formatted correctly. But if i uncomment the cmdshell, it hangs.The wierd thing is this worked a few days ago! I changed small things in other places of the trigger, but nothing that I think would effect this.Any help would be great.thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 17:12:37
|
| You'll need to post the trigger code.Tara |
 |
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 17:18:29
|
quote:
CREATE TRIGGER [gonzo_update] ON [dbo].[gonzo] for INSERT, UPDATEAS IF @@ROWCOUNT=0 RETURNIF @@ROWCOUNT>1 RETURNBEGINdeclare @cont_id varchar(10)declare @bcpCommand varchar(500)declare @path varchar(200)declare @tmp_filename varchar(50) set @path = "C:\dev\test\"select @cont_id = content_id from insertedset @tmp_filename = "_" + @cont_id + ".tmp"set @bcpCommand = 'bcp "SELECT content_html FROM testdb..gonzo where content_id="' + @cont_id + '"" queryout ' + @path + @tmp_filename + ' -T -c'EXEC master..xp_cmdshell @bcpCommand
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 17:21:28
|
| Does the user who ran the UPDATE have permissions to run xp_cmdshell? Does C:\dev\test exist on the database server which is where xp_cmdshell runs from?What did the trigger look like when it worked properly? What has changed?Tara |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 17:27:42
|
quote:
Originally posted by tduggan
Does the user who ran the UPDATE have permissions to run xp_cmdshell?
I just ran the update from osql using the -E argument (trusted?) so I'm assuming yes? If they didn't, wouldn't I get an error of some kind instead of the never ending hang?quote:
Does C:\dev\test exist on the database server which is where xp_cmdshell runs from?
Yes it does.quote:
What did the trigger look like when it worked properly? What has changed?
That I can't answer. I didn't make a copy of it before I had edited it today.Another interesting bit, though: I took the bcp command stuff out and made a stored procedure out of it with params for cont_id and path.If I run that procedure directly from osql, it works perfect. If I run that SP from inside the trigger, it hangs again.Now I'm a bit confused. . .My code is obviously working on it's own in that SP. But inside the trigger, it won't.  |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-08-09 : 17:45:04
|
You've got two quotes at the end of your SELECT within your BCP command. That smells bad to me.Methinks you might want:SET @bcpCommand = 'bcp "SELECT content_html FROM testdb..gonzo WHERE content_id='''+ @cont_id + '''" queryout ' + @path + @tmp_filename + ' -T -c' -PatP |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 17:47:15
|
the double quotes are there in the book I used as reference, and also, the command works when done straight (also passes syntax checking)quote:
'bcp "SELECT content_html FROM testdb..gonzo where content_id="10"" queryout C:\dev\test\_10.tmp -T -c'
Also, the bcp statement works flawlessly when called from the stored procedure directly. |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-08-09 : 17:50:26
|
| Just to amuse me, could you try doing a copy and paste of my code to see what that does?-PatP |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 17:51:19
|
| sure, one sec. . |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 17:57:20
|
| hangs just like my code does.:( |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 18:22:18
|
| Ok, just thinking out loud here:Are you not allowed to have a bcp operation happen on the same table the trigger is on? It's the only thing I can think of since the script runs when it's not attached to the trigger.Possible? |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-08-09 : 18:27:17
|
| Ah-ha! I think you've run into a locking/blocking issue. It never occured to me that you might want to BCP data out of the same table that you are triggered from (I know, my bad!).Just for the jolly factor, change your "BCP" to "start BCP" as a test. This probably isn't a long term answer, but it might get you a clue what is happening.-PatP |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 18:45:56
|
"start BCP"?Just add start to it like this?quote:
'start bcp "SELECT content_html FROM testdb..gonzo where content_id="10"" queryout C:\dev\test\_10.tmp -T -c'
What will that do? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 18:48:24
|
| I've been testing this out on my machine. I have been getting the same results as you. And yes you are having this problem due to bcp'ing the data from the same table as the trigger is affecting. You are getting blocked by it. You can see this from sp_who2. Not sure how to workaround this though.Tara |
|
|
|
subtex
Starting Member
9 Posts |
Posted - 2004-08-09 : 18:56:25
|
| hmmm....How about this: I create a Queue_table to hold references to the rows in the gonzo table that need to be bcp'd. Sort of creating a task list. On insert/update the trigger updates the queue_table with the row from gonzo.Then I do the bcp on the queue_table, deleting that row when I'm done. That shouldn't give me any lockups, right? Since it's a different table that is being used for the query? |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-08-09 : 23:49:11
|
quote:
Originally posted by subtex
"start BCP"?Just add start to it like this?quote:
'start bcp "SELECT content_html FROM testdb..gonzo where content_id="10"" queryout C:\dev\test\_10.tmp -T -c'
What will that do?
It's a deviant trick that us "old folks" remember. Start is an NT command that basically starts a child process (like fork in *nix) to run a command.Try it at a command prompt to get the idea.-PatP |
|
|
|
|
Trigger help!