Calling Batch file using SQL Script

Question

Calling Batch file using SQL Script

Minnu

SSCertifiable

Points: 6451
More actions
June 3, 2013 at 7:03 am

#278441

Hi Team,
Am calling a batch file from sql script, passing parameters to batch file and trying to print the parameters in batch file,
declare @bat varchar(200)
set @bat = 'E:\Test_batch\Test.bat'+ ' ' + 'UPDATE' + ' ' + 'DELETE'
exec master..xp_cmdshell @bat
Parameters are printing, but throwing below error msg
UPDATE' is not recognized as an internal or external command,
operable program or batch file.
'DELETE' is not recognized as an internal or external command,
operable program or batch file.
Please help me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

looking at the errors I guess the parameters are passed as commands instead of parameters. Are you sure the "test.bat" accepts parameters?

I use below code a lot, so passing parameters should work:

declare @location VARCHAR(255)

declare @statement NVARCHAR(4000)

set @location = 'E:\Databases_files'

SET @statement = N'dir /O-S /S /A-D "' + @location + '\*.*"'

EXEC master.dbo.xp_cmdshell @statement

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Minnu

SSCertifiable

Points: 6451

June 3, 2013 at 7:52 am

#1620553

Hi Team,

Its not working...!

Minnu SSCertifiable Points: 6451 More actions · Answer 3

Hi Team,

Can u please tell me that the msg "UPDATE' is not recognized as an internal or external command,"

is coming from the SQL exec call or from the batch file??

Lowell SSC Guru Points: 323504 More actions · Answer 4

Minnu (6/3/2013)
Hi Team,
Can u please tell me that the msg "UPDATE' is not recognized as an internal or external command,"
is coming from the SQL exec call or from the batch file??

the error is being returned from the operating system, because it's trying to call UPDATE as if it were a command line object.

i have a bat file which accepts no parameters; the bat file does not raise an error for the parameters passed, it just ignores them /doesn't use them:

C:\HDS\Bin>C:\data\CopyXML.bat UPDATE DELETE

your command line CANNOT HAVE CrLf in them, which i'm guessing is the problem... if UPDATE appears on a separate line, it's treated as a command, and not a parameters.

this would Fail:

C:\HDS\Bin>C:\data\CopyXML.bat

UPDATE

DELETE

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!

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Minnu:

Does this code work?

DECLARE @bat varchar(200)

SET @bat = 'E:\Test_batch\Test.bat UPDATE DELETE';

EXECUTE master..xp_cmdshell @bat

If so, then you've got some unwanted characters in your @bat variable that's causing it to be interpreted incorrectly.

If not, then this should show you how batch file parameters work:

You have the following batch file called test.bat:

@echo off

echo param 1=%1

echo param 2=%2

From a command prompt, you execute it on your local computer and get the following output:

C:\Temp>test.bat update delete

param 1=update

param 2=delete

So, you copy it to the server and call it from SQL:

DECLARE @bat varchar(200);

SET @bat = 'd:\temp\test.bat';

EXECUTE xp_cmdshell @bat;

You would get the following returned:

param 1=

param 2=

NULL

Or you call it from SQL passing in the two parameters:

DECLARE @bat varchar(200);

SET @bat = 'd:\temp\test.bat UPDATE DELETE';

EXECUTE xp_cmdshell @bat;

You would get the following returned:

param 1=UPDATE

param 2=DELETE

NULL

I hope this helps you. If you're still having problems, please clarify for us if you're trying to pass in the string UPDATE as parameter #1 and DELETE as parameter #2. It looks like this is what you're trying to do, but I'd just like to be sure of your intent before we continue.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions