Solved

Executing A Sybase SQL Anywhere 5.5 Stored Procedure From SQL Server

Posted on 2004-03-30
4
1,510 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:sherbug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10718769
Have you tried running the package using DTSRUN commnad line utility. You can pass global variables using /a parameter. See books online for other parameters. If excuting package thru DTSRUN works then you can schedule DTSRUN to run along with command line parameters, instead of the scheduling the package itself.

regards-
0
 

Author Comment

by:sherbug
ID: 10723882
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.




0
 

Author Comment

by:sherbug
ID: 10724990
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-56B83986ACF0}" /A "PARAM":"8"=@var /A "PARAM1":"8"=@var1 /A "PARAM2":"8"=@var2 /W "0" /E '


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?
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 10728202
Well. I thought /a option exists in SQL 7. I do not have SQL 7 installed, thus i could not verify.

I think DTS approch is the best way. but i could not test your first method since i do not have sql 7.

Approach 1)

Could you not output the from sql anywhere table into a text file and then bcp in into sql server table. To do this automatically you will need a C program that can connect to both sql anywhere and sql server.

SELECT *
FROM employee;
OUTPUT TO employee.txt
FORMAT ASCII

Approach 2)

If you have powerbuilder then you can create a program to excute a pipeline object that transfers data from sql anywhere to sql server. You can then schedule the exe created from pb to from sql server.

Approch 3)
Create a VB program that connects to both using ado and transfer the data. Create exe and schedule it.

Hope that helps.

Regards-




0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do we check sybase license in ASE 1 3,212
dbisql 5 688
Sybase conversion 2 81
install sybase 15.7 over 15.0.x 2 95
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Changing a few Outlook Options can help keep you organized!
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question