Trigger that launches a script using xp_cmdshell
-
I wrote this fairly simple trigger
USE [MYDB]
GO
/****** Object: Trigger [dbo].[CALCULATED_FIELDS] Script Date: 05/11/2016 14:18:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CALCULATED_FIELDS]
ON [dbo].[custom_values]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
DECLARE @CUSTOMIZED_ID VARCHAR(15)
SELECT @CUSTOMIZED_ID = (SELECT TOP 1 customized_id FROM inserted)
BEGIN
DECLARE @OUTSTRING varchar(300)
BEGIN
IF NOT @CUSTOMIZED_ID IS NULL
SELECT @OUTSTRING = '\\SERVER\Folder\script.vbs '+ @CUSTOMIZED_ID
--EXEC master..xp_cmdshell @OUTSTRING, NO_OUTPUT
END
END
When it runs it runs indefinitely and I have to restart the server. I've run the script via xp_cmdshell through a query window and it executes fine, it's only when it's in the trigger that it seems to run indefinitely. What is causing this?
-
It's possible the vb script is throwing an error for whatever reason and hanging indefinitely, you can try changing the execute command to 'cscript \\SERVER\Folder\script.vbs ' and adding a + ' >> someoutput file' to the end.
Or 'cscript //B \\SERVER\Folder\script.vbs ' if that doesn't work.
-
there's a few things that can be improved here;
your code is assuming one row from inserted exists, it should be modified to handle multiple rows.
calling xp_cmdshell requires sysadmin privileges, and normal end users don't have that. the trigger will fail for normal users
the service account SQL runs on will have to be a domain account, to get to the servername at '\\SERVER\Folder\script.vbs '
As someone else notices, if the vbs hangs, you could tie up your table so that it waits on a never-to-close transaction.
if you really needed to do something for each row, you might want to create a service broker, and have the trigger call the service broker so it's asyncronous, and errors don't kill the trigger and rollback any data.
if service brokers are kind of unknown territory for you, you could probably just have a SQL job that iterates through items that were not processed yet instead; so if you added an IsProcessed flag for tracking, a job could run a cursor once every few minutes, and call your vbs on a per row basis, and then update the IsProcessed flag.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I ran it from a query editor using xp_cmdshell and it works, it only ever breaks in the trigger
-
I can safely assume only one row is inserted because the application that does the update or insert can only modify one record at a time luckily. I launched the app and watched in activity manager and it seems it establishes it's connection as the sa user. I also have an identical script running on a different database and it has no issues. I may have to rewrite the script in the trigger itself if I can't get this working.
-
alexferreira (5/12/2016)
I can safely assume only one row is inserted because the application that does the update or insert can only modify one record at a time luckily. I launched the app and watched in activity manager and it seems it establishes it's connection as the sa user. I also have an identical script running on a different database and it has no issues. I may have to rewrite the script in the trigger itself if I can't get this working.i would bet the issue is the account SQL runs under is not a domain account, and cannot access the unc fileshare.
one of the consultants here has a horror story of a big company going out of business, becuase they did not follow best practices on trigger designs, and assumed single row updates. that eventually lost rows during back end processing, made their data out of whack, and they went out of business.
developers and DBA's move on, coding for multi rows is the sign of a professional who knows better.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Would this explain why I can run the xp_cmdshell from the query window but not within the trigger? How do I fix it? Would this also explain why an extremely similar trigger works fine?
-
yes, it would explain it.
Lowell (5/11/2016)
calling xp_cmdshell requires sysadmin privileges, and normal end users don't have that. the trigger will fail for normal users
the service account SQL runs on will have to be a domain account, to get to the servername at '\\SERVER\Folder\script.vbs '
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Can I force the trigger to run as a domain account? Is the fact I log in as sa and can run it and that the web app also uses sa to connect irrelevant?
-
Is your trigger that works running on the same server and same instance of SQL server?
-
Same server and instance, different database. All I did for this DB was right click on the server and create a new database
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply