troubleshooting Question

Accessing Sybase via Linked Server (Joins and Stored Procedures)

Avatar of ablesa
ablesa asked on
Sybase DatabaseMicrosoft SQL Server 2005
24 Comments6 Solutions2381 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
ablesa

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 6 Answers and 24 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 6 Answers and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros