DTS using xp_cmdshell
-
Hi,
I have a DTS package (TestingAgain) which calls a Stored Proc that accepts Global parameters like
exec TestUser.Test ?, ?, ?, ?
This DTS package is called by the following Stored Proc:
CREATE procedure doMonthly as
DECLARE @var sysname
SET @var = 'c:\test.bat 046, TEST1, 01/01/2005, 04/01/2005'
EXEC master..xp_cmdshell @var
GO
"test.bat" has the following:
dtsrun /S SQL1 /N TestingAgain /U TestLogin /P password /A CNum:8=%1% /A DBName:8=%2% /A FD:8=%3% /A Ed:8=%4%
************************
Apparently the variables in the above dtsrun statement is replaced by the parameters passed in the doMonthly Stored Proc, but with a "8" appended to each except the last one as follows:
dtsrun /S SQL1 /N TestingAgain /U TestLogin /P password /A CNum:8=0468 /A DBName:8=TEST18 /A FD:8=01/01/20058 /A Ed:8=04/01/2005
When I read the individual parameter values before substituting in the DTSRUN stmt, they look fine.
Appreciate any help on this.
Thanks.
-
Rather weird convoluted way of running a package.
I believe that the antiquated way to use parameters in batch files is %1 not %1%
--------------------
Colt 45 - the original point and click interface
-
I use a command procedure to wrap DTS packages, and call that command procedure from a scheduled job in SQL Server. The job step includes the server, catalog, error file, and maybe a query parameter or two. The command procedure can add some additional info and give me flexibility in controlling what DTS packages are run, and in some cases there are dozens in one command procedure. It's not a bad way to do it, especially if there are multiple DTS packages to run. If you create a job from a DTS in the GUI, the job calls DTSRun anyway, so there's not much additional overhead. Calling it from a stored procedure is just another layer up.
I'm perplexed by the additional '8' being appended. Phill is correct about the double percent symbols being correct.
Is the command procedure stored in ASCII, DOS, or Unicode characters?
Use ECHO %1%, etc. to echo them just before the DTSRun line to verify the values (maybe that's what you've already done).
-
Phill/Peter Appreciate your response.
%1 worked!!!
Thanks.
-
Command line arguments still use the single percent sign. I should have recognized that those are different. Sorry that I didn't catch that.
My command procedures check for their existance and assign them to environment variables so I'm used to wrapping the variables in percent signs.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply