x

SQL 2008 CmdExec Job with Multiple Parameters

I am using a CmdExec job to execute an .exe that handles alot of our companys repeated tasks. The .exe accepts a parameter that tells it was module to run. I have created a module that requires a second parameter. I created a CmdExec job and in the steps->Command: field I entered the following:

"file path to .exe" parameter1 parameter2

The first parameter is recognized fine. When it gets to the second parameter I get an error from the application telling me that the index was outside the bounds of the array. I have a job on a 2000 server that executes this job using a DTS Package and runs fine. We are converting and this is giving me an issue. Please let me know how to pass multiple parameters to an .exe from the sql job. thanks

more ▼

asked Feb 11, 2010 at 04:10 PM in Default

Jason gravatar image

Jason
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I suspect it's because the cmdexec step is only passing in the first parameter and your exe is trying to access the second one.

I had a problem with calling an exe in a job. It was more about capturing the result but I think you might find this method helps you with your problem too. Make the step a T-SQL step and call your exe like this:

declare @CmdLine    nvarchar(4000)
    , @ReturnCode	int

set @CmdLine = '""C:\Program Files\sqlmetrics\sqlmetrics.exe" drives databases"'

exec @ReturnCode = xp_cmdshell @CmdLine
if @ReturnCode <> 0
    raiserror('xp_cmdshell failed (return code was %i).', 16, 1, @ReturnCode)

Note the double quotes around the exe path because it has a space in and the double quotes around the whole command line. The exe is written in VB starts in Module1.Main which, instead of being a Sub, is function that returns an Integer. The convention is 0 means success and non-zero means failure.

This way you should crack your parameter problem and get the job to fail if the exe has a problem.

more ▼

answered Feb 12, 2010 at 04:50 AM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 34

Once I corrected my timeout setting this worked like a charm. Thanks
Feb 12, 2010 at 03:16 PM Jason
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1841
x86

asked: Feb 11, 2010 at 04:10 PM

Seen: 2961 times

Last Updated: Feb 11, 2010 at 04:10 PM