sherbug
asked on
Executing A Sybase SQL Anywhere 5.5 Stored Procedure From SQL Server
I am trying to run a SQL Server 7.0 DTS package containing several Tasks. I have a DataPump task
that executes a stored procedure on the server running Sybase SQL Anywhere V 5.5
and writes the resulting dataset to a table on a SQL Server 7.0 box.
The stored procedure takes 2 parameters and I am using placeholders and creating 2 global variables inside the DTS to hold the values. example: Exec myreport ?,?
When I run this DTS package interactively then it runs fine.
Also when I hardcode the values, it runs fine.
Example: Exec myreport '2004-03-25','2004-03-25'
When I try to schedule it then the package within SQL Server it fails with an error = Error: -2147217904
(80040E10); Provider Error: 0 (0) Error string: [Sybase][ODBC Driver]Wrong number of parameters Error source: Microsoft OLE DB Provider for ODBC Drivers.
I am also running a SQL Server 2000 Developer edition as a personal server.
When I schedule the identical package to run from my personal server then it
runs fine.
Why can't Sybase read and pass the Global Variables in a scheduled package?
that executes a stored procedure on the server running Sybase SQL Anywhere V 5.5
and writes the resulting dataset to a table on a SQL Server 7.0 box.
The stored procedure takes 2 parameters and I am using placeholders and creating 2 global variables inside the DTS to hold the values. example: Exec myreport ?,?
When I run this DTS package interactively then it runs fine.
Also when I hardcode the values, it runs fine.
Example: Exec myreport '2004-03-25','2004-03-25'
When I try to schedule it then the package within SQL Server it fails with an error = Error: -2147217904
(80040E10); Provider Error: 0 (0) Error string: [Sybase][ODBC Driver]Wrong number of parameters Error source: Microsoft OLE DB Provider for ODBC Drivers.
I am also running a SQL Server 2000 Developer edition as a personal server.
When I schedule the identical package to run from my personal server then it
runs fine.
Why can't Sybase read and pass the Global Variables in a scheduled package?
ASKER
No I have not tried this. Can the global variables be dynamic? I have been using RDMS for a number of years, but not so much DTS.
Let me look up the rules for dtsrun and give it a try. If all goes OK I will accept this solution and give the points to you.
Thanks, you're a life saver.
Let me look up the rules for dtsrun and give it a try. If all goes OK I will accept this solution and give the points to you.
Thanks, you're a life saver.
ASKER
Parameters can only be specified when using SQL Server 2000. I am still running off a SQL Server 7.0 box.
Here is what I tried:
declare @var sysname,@var1 sysname,@var2 sysname
Set @var = (Select PARAM from DB..TABLE_NAME)
Set @var1 = (Select PARAM1 from DB..TABLE_NAME1)
Set @var2 = (Select PARAM2 from DB..TABLE_NAME2)
EXEC master..xp_cmdshell 'DTSRun /S "servername" /N "pkg_name" /G "{EB8955DC-9074-44A0-BF96-
Executing the above returns this error:
output
DTSRun: Invalid switch: /A "startdate":"8"=@var The parameter is incorrect.
DTSRun: Invalid command options
Any other ideas that may work with SQL Server 7.0?
Here is what I tried:
declare @var sysname,@var1 sysname,@var2 sysname
Set @var = (Select PARAM from DB..TABLE_NAME)
Set @var1 = (Select PARAM1 from DB..TABLE_NAME1)
Set @var2 = (Select PARAM2 from DB..TABLE_NAME2)
EXEC master..xp_cmdshell 'DTSRun /S "servername" /N "pkg_name" /G "{EB8955DC-9074-44A0-BF96-
Executing the above returns this error:
output
DTSRun: Invalid switch: /A "startdate":"8"=@var The parameter is incorrect.
DTSRun: Invalid command options
Any other ideas that may work with SQL Server 7.0?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
regards-