question

SurajV avatar image
SurajV asked

I am trying to execute a vbscript program from a sql after insert trigger by passing parameters to my vbscript file using xp_cmdshell but I am getting run time error

My SQL Trigger code is IF OBJECT_ID('TRG_InsertEmp1') IS NOT NULL DROP TRIGGER TRG_InsertEmp1 GO CREATE TRIGGER TRG_InsertSyncEmp1 ON dbo.Emp_details AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here DECLARE @cmd varchar(20),@empid as Int, @empname varchar(10), @empsal int Set @empid= (select EmpID from inserted) Set @empname=(select EmpName from inserted) Set @empsal=(select EmpSal from inserted) set @cmd='Cscript.exe C:myTestScript.vbs"/parameter1:@empname/parameter2:@empname/parameter3@empsal"' exec xp_cmdShell @cmd END GO My vbscript program which tries to print the parameters passed from SQL logic is 'VBScript program myTestScript.vb if WScript.Arguments.Count = 0 then WScript.Echo "Missing parameters" end if Wscript.Echo Wscript.Arguments(0) WScript.Echo Wscript.Arguments(1) WScript.Echo Wscipt.arguments(2) But I am getting the following error on SQL Server Management Studio Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated. I Will be grateful for any help/example code on how to resolve this or accomplish my task of passing above parameters to a vbscript file
triggerxp_cmdshellvbscript
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oh, and calling `xp_cmdshell` from a trigger? Not something I would recommend.
2 Likes 2 ·
Triggering a synchronization process (guessing based on the trigger name) with a service outside of SQL sounds like the canonical use case for Service Broker - https://technet.microsoft.com/en-us/library/ms166049(v=sql.105).aspx Replace the vbscript with an External Activator service to consume your service broker queue and you're set - https://blogs.msdn.microsoft.com/sql_service_broker/2008/11/21/announcing-service-broker-external-activator/ You could even just dump the relevant data into some sort of holding table and just come along later with a scheduled vbscript, poll that table, and process any results. Getting one of these options set up might not be trivial, but holding a transaction open while an external process runs could be disastrous for both database performance and availability.
2 Likes 2 ·
Also... what if you have multiple rows inserted in one statement? The trigger, as written, will fail. Check out https://msdn.microsoft.com/en-us/library/ms190752.aspx
1 Like 1 ·
I couldn't agree more with Thomas and Ken's concerns regarding a trigger calling xp_cmdshell. It sounds like something interesting to try in a lab, but I really wouldn't recommend it for anything beyond experimental fun.
1 Like 1 ·
Thanks all of you for the answers, shall try them out and get back
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
You've got `@cmd` declared as a `varchar(20)`, and are then setting it to be rather longer than 20 characters... You might also want to check on the `@empname` parameter...
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I agreed with all of the comments, but there were no official answers. Since this one seemed to directly answer the original question, I converted it to an answer.
2 Likes 2 ·
Thanks to all of you for your help,The runtime error got resolved with the help of the above answer by Mr. Thomas Rushton,Thanks again Mr. Thomas for the guidance provided
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.