T-SQL Call Batch File and Executable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

T-SQL Call Batch File and Executable

Author

Topic

rajkap
Starting Member

6 Posts

Posted - 2007-04-19 : 14:35:38

Hi,
I have T-SQL code

declare @command varchar(200)
set @command = 'C:\Sim\test.bat'
exec master..xp_cmdshell @command

test.bat has C:\older\file.exe 1234

file.exe is the executable that takes in parameter 1234.

when I double click the test.bat file directly the execuatble in it works just fine.
but calling it from T-Sql outputs the lines below

NULL
C:\WINDOWS\system32>C:\older\file.exe 1234
NULL

but the executable does not seem to do its job.
any inputs/help
thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 14:45:36

When you double click on the bat file, is there anything that you need to click on once the executable launches? Are there any message boxes or fields that you have to fill out?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rajkap
Starting Member

6 Posts

Posted - 2007-04-19 : 14:47:17

No, The executable launches and prints out a few lines showing the progress of the process. It does not require any other user input.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 15:27:52

That's the problem then. You won't be able to see the output when you run it via xp_cmdshell. xp_cmdshell does not run in the same session as you.

So what you should do is modify the executable so that your output goes to a file that way you check the progress.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rajkap
Starting Member

6 Posts

Posted - 2007-04-19 : 15:39:00

I now changed my application not to output any statements/progress and still it does not execute. I tried to get the return code
using
EXEC @result = master.dbo.xp_cmdshell @command
and the @result has 128 in it.

Do I have to change anything in SQL settings because the bat file did not run the executable even from a DTS package.
anything special need to be done to the SQL Server to be able to run an executable?

quote:
Originally posted by tkizer

That's the problem then. You won't be able to see the output when you run it via xp_cmdshell. xp_cmdshell does not run in the same session as you.

So what you should do is modify the executable so that your output goes to a file that way you check the progress.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 15:51:24

You aren't going to see the executable launch via xp_cmdshell. Is there some way to modify your app so that it outputs to a file so that you know if the exe even launched?

@result would be the output of xp_cmdshell and not from your bat or exe.

When your bat file works, are you double clicking on it on the same server as where SQL Server is installed?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rajkap
Starting Member

6 Posts

Posted - 2007-04-19 : 16:18:10

Yes, I can verify whether the EXE launched by looking at the output in a different location but it did not.
When I double click the Batch file it is run from the same server as the SQL server and it works fine with the expected output.
But Query Analyzer does not run the executable directly or the batch file that has the executable.
When I schedule a job to run the DTS that has this Win32 process it runs forever.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 16:37:35

Perhaps it's permissions. What account is being used for the SQL Server service?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rajkap
Starting Member

6 Posts

Posted - 2007-04-19 : 16:46:20

Account used for SQL Server Service is different from my account that I use to login to Query Analyzer. But Imy account is specified as Admin account in SQL Server.

quote:
Originally posted by tkizer

Perhaps it's permissions. What account is being used for the SQL Server service?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 17:02:09

When you run anything from xp_cmdshell it uses the account that the service is using. I suggest logging into the database server using that account and try double clicking on your batch file.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-04-20 : 07:43:30

also is the C: Local to your PC...or Local to the SQL server???

KerriLK
Starting Member

1 Post

Posted - 2007-05-04 : 12:30:17

The problem seems to be that the bat file does not want to change directories. I am having the same problem with a multiple line bat file that changes directories. If i run the bat file from sql query analyzer as:
Declare @results varchar(1000)
set @results = 'd:\Data\MSSQL\Scripts\rename_db_backups.bat'
exec master..xp_cmdshell @results

I get the below output:
NULL
C:\WINDOWS\system32>cd d:\Data\MSSQL\BACKUP\DatawarehouseUserData
NULL
C:\WINDOWS\system32>for /F "tokens=1-4 delims=" %1 in ('dir /b d:\Data\MSSQL\BACKUP\DatawarehouseUserData\DataWarehouseUserData_backup*') do ren %1%2%3%4 DataWarehouseUserData_backup.bak
NULL
C:\WINDOWS\system32>ren DataWarehouseUserData_backup123456789.bak DataWarehouseUserData_backup.bak
The system cannot find the file specified.
NULL

As you can see from the output it is keeping the directory at c:\windows\system32, not the new directory specified in the bat file. Someone had suggested putting the lines in the bat file on one line with & or && between each line, however, this just caused another error and did not work.

Any help on this would be greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources