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.EQU IPVAL ev, DTICDW1.uptime_new.dbo.MFG UNIT m, DTICDW1.uptime_new.dbo.DET AILVAL 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,Tb l1001.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.EQU IPVAL 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_RetrieveEquipm ent 'Cam BCF%'
But, when I try to execute from SQL Server via the linked server:
Exec DTICDW1.uptime_new.dbo.SP_ AUDB_Retri eveEquipme nt '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!
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.EQU
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
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_RetrieveEquipm
But, when I try to execute from SQL Server via the linked server:
Exec DTICDW1.uptime_new.dbo.SP_
I get this error:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SP_AUDB_RetrieveEquipment
Any help would be appreciated!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
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
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
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
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.
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.
Have you tried with an explicit user as remote login ?
SQL 2005 64 bit - there has been some "fun" getting that to talk properly.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
It is for SQL2008 (more 64bit friendly than 2005) but the steps are the same.
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
Not sure how to make the 32 bit version show up there...
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!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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!
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