Link to home
Start Free TrialLog in
Avatar of ablesa
ablesa

asked on

Accessing Sybase via Linked Server (Joins and Stored Procedures)

I have a Linked Server setup on SQL Server 2005.  It is linked to a Sybase database (Adaptive Server Enterprise/12.5.0.3).  I’m trying to either do a join on the linked server OR call a stored procedure and pass a string…  I can successfully do both of these things locally in Sybase, but not via the linked server.

Here’s my join that runs fine in Sybase, but fails with linked server in SQL Server:
select distinct ev.EQUIPNAME, dv.DETAILNAME
from  DTICDW1.uptime_new.dbo.EQUIPVAL ev,  DTICDW1.uptime_new.dbo.MFGUNIT m,  DTICDW1.uptime_new.dbo.DETAILVAL dv
Where m.MFGNM like 'Cam P140%'
And m.MFGID = ev.MFGID
And ev.EQUIPID = dv.EQUIPID

The error is:
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT Col1021,Tbl1005.DETAILNAME Col1011 FROM uptime_new.dbo.DETAILVAL Tbl1005,(SELECT Tbl1001.MFGID Col1019,Tbl1001.EQUIPID Col1020,Tbl1001.EQUIPNAME Col1021,Col1014,Col1015,Tbl1001.BUID Col1018,Col1013 FROM (SELECT Tbl1003.MFGID Col1014,Tbl1003.MFGNM Col1015,Tbl1003.BUID Col1013 FROM uptime_new.dbo.MFGUNIT Tbl1003 WHERE Tbl1003.MFGNM like 'Cam P140%') Qry1016,uptime_new.dbo.EQUIPVAL Tbl1001 WHERE Col1014=Tbl1001.MFGID) Qry1022 WHERE Col1020=Tbl1005.EQUIPID GROUP BY Col1021,Tbl1005.DETAILNAME" against OLE DB provider "ASEOLEDB" for linked server "DTICDW1".

So, I tried putting it in a stored procedure and calling that.  Here’s the stored procedure:

CREATE PROCEDURE SP_AUDB_RetrieveEquipment
                (@MFGNM VARCHAR(100))

WITH RECOMPILE
AS
    BEGIN
        select distinct ev.EQUIPNAME, dv.DETAILNAME
        from EQUIPVAL ev, MFGUNIT m, DETAILVAL dv
        Where m.MFGNM like @MFGNM  
        And m.MFGID = ev.MFGID
        And ev.EQUIPID = dv.EQUIPID
    END

Again, I can run the stored proc from Sybase with this statement:
execute dbo.SP_AUDB_RetrieveEquipment 'Cam BCF%'

But, when I try to execute from SQL Server via the linked server:

Exec DTICDW1.uptime_new.dbo.SP_AUDB_RetrieveEquipment 'Cam BCF%'

I get this error:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SP_AUDB_RetrieveEquipment' on remote server 'DTICDW1'.

Any help would be appreciated!
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

1. Are you sure the linked server is set up correctly? In theory if the linked server is setup correctly you can do all this, exactly as you are trying to.

2. Try using instead an openquery()

Regards
  David
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
of course, you would set up your linked server to your SYBASE box / instance

the above is an example only using SQL and the Outlook Business Contact Manager as the server.

So, how have you set up your linked server ? what provider are you using ? using ODBC maybe ? 32 bit or 64 bit ?
Hi,

Just pointing out that Sybase can't be linked to using SQLNCLI as a provider ...

Hence my question above about the linked server being set up correctly.

For sybase it will go something like:
Install the Sybase client
Check via odbc admin
configure the linked server in SQL
check


Regards
  David
Avatar of ablesa
ablesa

ASKER

Thanks to you both for your responses.  I'm using Sybase 64 bit driver (my machine is Windows 7).  I have the properties (rpc and dataacess) set as dtodd suggests and still no luck.  Mark, I have tried openquery with no luck there either...

I can execute this locally on the sybase database:
execute uptime_new.dbo.sp_version

But I cannot execute this via that linked server through SQL Server:
SELECT * FROM OPENQUERY(DTICDW1, "execute uptime_new.dbo.sp_version")

I have attached all of the properties I have set on the Linked server just in case either of you see something that you think needs to be specified differently.

Thanks for your suggestions...
Linked-Server-Settings.doc
Yeah I realised after I posted the example that it might have confused - the example was to show the type of steps involved, hence my follow up post.

The sybase ASE linked server is often using the MS odbc driver pointing to a sybase odbc DSN, but thought we would get to the basics first. See what kind of connection there is to sybase.

Sorry about the out of sequence post...

Have you tried with an explicit user as remote login ?

SQL 2005 64 bit - there has been some "fun" getting that to talk properly.
Avatar of ablesa

ASKER

I changed to remote login (as shown in the attached document...make sure I did it right!) and I get the same thing...
This query:
SELECT * FROM OPENQUERY(DTICDW1, "execute uptime_new.dbo.sp_version")
Results in this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'execute uptime_new.dbo.sp_version'.

Linked-Server-Settings-with-Remo.doc
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh, and found this walkthrough : http://sql-articles.com/articles/dba/creating-linked-server-to-sybase-from-sql-server/

It is for SQL2008 (more 64bit friendly than 2005) but the steps are the same.
Avatar of ablesa

ASKER

Well, first off, my sql server installation on my workstation is 32 bit.  I have the 64 bit version of the sybase drivers installed.  Do you think I should install the 32 bit version (the first link you refer to gives me that impression)?
I did try to follow the walk thru, but I cannot successfully create an odbc connection.  When I test the connection, it fails with "Unable to establish connection".  All of the entry to create the odbc connection is pretty self-explanatory except for maybe the port number.  I used 5000 as in their example, but I'm not sure if that's the right port and don't know how I would find out what the right port is...
I don't have any sort of support plan with Sybase, so it won't let me see the 2nd link you refer to....

Anyway, let me know if you think I need to install the 32 bit driver (and whether I should un-install the 64 bit one)...  perhaps that is my problem....

thanks!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ablesa

ASKER

Thanks, David.  Should I un-install the 64 bit sybase driver before installing the 32-bit?  If so, how would I go about doing that?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ablesa

ASKER

Well, I installed the 32 bit.  I don't see it show up in ODBC...only 64 bit driver shows up.  I looked at the version of ODBC that runs from the ADmin tools and it appears to be the 32 bit version (windows\system32\odbcad32.exe)...

Not sure how to make the 32 bit version show up there...



Avatar of ablesa

ASKER

Maybe linked server is the wrong answer here..  Is there a better way to get a lot of data from Sybase into SQL Server?  BCP seems like an option, but it appears to pull the whole table instead of just  certain records.  I didn't really want to do that...that would pull about 650,000 records versus the 75,000 I really need...  Are there any other options for me other than the linked server to get these records if I don't want to pull the whole table?

Thanks!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ablesa

ASKER

I found the WOW one and ran that, but I still cannot create an ODBC connection...  I get the same lame error as with the 64 bit one ("cannot establish connection").  At this point, I think I'm just going to backup from this a bit.  I can do simple queries over the linked server I have.  For some of the data, I think I will extract it to a file on teh Sybase side and load it into SQL Server and then process it locally.  For the lower volume data, I will just use cursors and things like that to keep my queries simple.
If there is an easy way to extract up to 100,000 records to a file from this version of Sybase, I would appreciate knowing how to do that.  If not, I guess I will just extract it in chunks via Sql Advantage.
I appreciate the help of both of you!
Hi,

Can you send a screenshot or two of the odbc connection that you are trying to create that can not connect?

In the meantime, for completeness, I've attached a screenshot showing a server desktop (via rdp) and the two shortcuts to the different versions of the odbc administrator applet. Im showing the properties of each shortcut.

I think this is a fairly standard default installation of Windows 2008R2 64bit edtion, so the directories and paths should be similar for most.

Regards
  David
2011-08-16-32bit-64bit-odbc-admi.doc
Avatar of ablesa

ASKER

I am running the WOW version now...  I left the 32 bit driver installed and uninstalled the 64 bit version.  The screenshots I've attached shows you what I enter to create the odbc datasource...

I found a way around my issue of extracting data.  I didn't know you could put a command line parameter on SQL Advantage.  I am now able to run SQL Advantage with an output command line parameter (-o) and it spits all of my records to that output file.  This is exactly what I needed.  So, I think I am good to go on this now.  Again, thanks for all your help!  I guess ODBC wasn't in the cards...


odbc-sybase-connection.doc
Hi,

I compared your settings with those on our sql box that links to sybase and I cant see anything obvious in the odbc settings that are wrong.

Have you got a windows firewall running on the sql server?

Regards
  David
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ablesa

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for ablesa's comment http:/Q_27247211.html#36414911

for the following reason:

While the experts didn't help me find the real problem in why I couldn't use joined queries or stored procedures via the linked server, they did help me dig enough to find a solution that would work for my situation.
Avatar of ablesa

ASKER

even though i didn't get this to work on my workstation, the digging around helped me find a solution that would work for me.  I appreciate all the comments and help!