Runnung Batch File from Stored Procedure

I am trying to execute a batch file from within a stored procedure on SQL Server 2005

Here is the command I am using

EXEC master.dbo.xp_cmdshell 'C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat'

Here is the error message I receive

ERROR MESSAGE
'C:\Program' is not recognized as an internal or external command, operable program or batch file.

I also tried

EXEC master.dbo.xp_cmdshell "'C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat'"
ERROR
The filename, directory name, or volume label syntax is incorrect.

EXEC master.dbo.xp_cmdshell "C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat"
ERROR MESSAGE
'C:\Program' is not recognized as an internal or external command, operable program or batch file.

EXEC master.dbo.xp_cmdshell '"C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat"'
The filename, directory name, or volume label syntax is incorrect.

What am I doing wrong?
tim44202Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
You need to make sure that batch file is on the sql server.
0
 
NVITCommented:
Have you tried single quotes ' instead of double ""?

EXEC master.dbo.xp_cmdshell 'C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat'
0
 
Steve WalesSenior Database AdministratorCommented:
It doesn't like the spaces in the directory name

Either of these will work (tested both, both appear to work):

EXEC master.dbo.xp_cmdshell 'C:\"Program Files"\"Advanced Query Tool v10"\jHaven_batch_sample.bat'

Open in new window

Or replace the directory names with the old 8.3 format (dir /x from a CMD prompt to see them):

EXEC master.dbo.xp_cmdshell 'C:\Progra~1\Advanc~1\jHaven_batch_sample.bat'

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
tim44202Author Commented:
Yes that is the first example in the body of the question
0
 
tim44202Author Commented:
Steve

I tried both methods.  Received this error:
"The system cannot find the path specified."
0
 
Steve WalesSenior Database AdministratorCommented:
Hmmm, I tested both locally and they worked.

Does the service account that SQL Server is running under have permissions to read from the path specified ?
0
 
ZberteocCommented:
Use this:
EXEC master.dbo.xp_cmdshell '"C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat"'

Open in new window

In SQL the single quotes delimit a string but inside you have to wrap the command in double quotes because of the spaces in the folder names.
0
 
ZberteocCommented:
More than that I encourage you to actually grab the result output of that command like this:
create table #output (id int identity, result_line varchar(1000))
insert into #output (result_line)
EXEC master.dbo.xp_cmdshell '"C:\Program Files\Advanced Query Tool v10\jHaven_batch_sample.bat"'
select * from #output where result_line is not null

Open in new window

This way you can check the result inside SQL and control what happens next.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I was wondering, why the grade B was given
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.