cancel
Showing results for 
Search instead for 
Did you mean: 

error executing stored procedure from SQL Server where SQL Anywhere is a linked server

1,666

Hi all

I have a SQL Anywhere 17 database that i have connected as a linked server on my SQL Server 2016 and i'm trying to execute a stored procedure but i'm getting an error message when doing so, this is my query:

SELECT * FROM OPENQUERY(MONWIN, 'exec sp_gummilagring')

and i get error:

The OLE DB provider "SAOLEDB.17" for linked server "MONWIN" supplied inconsistent metadata for a column. The name was changed at execution time.

The procedure outputs a temporary table that has been populated by the procedure and is declared like this:

CREATE LOCAL TEMPORARY TABLE #TempGummilagring ( OrderNr varchar(15),Rowkey varchar(32),ArtikelNr VARCHAR (16),ArtBeskrivning varchar (35),Amount INT, LevDatum varchar(10),calcAmount INT )

i've read that it could be due to a collation missmatch and i've tried setting Collation Compatible to true and Use Remote Collation to false in server options for the linked server but neither option has made any difference.

VolkerBarth
Contributor
0 Kudos

FWIW, did you setup the Linked Server as documented here?

0 Kudos

Hi Volker yes i followed that guide when i set up the linked server, i have set the options according to the advice from that guide.

VolkerBarth
Contributor
0 Kudos

Does the SP have a result set clause as part of its definition, and if so, do the column names of the result set match the column names of the final SELECT from the temporary table? (When calling a SP within SQL Anywhere, these do not need to match, but I'm not sure that's also true when called from MS SQL...)

0 Kudos

hi, no it doesn't, here is the complete SP https://pastebin.com/NX0AEPeF

VolkerBarth
Contributor
0 Kudos

Could you provide the code or code snippets here (within a "pre" tag pair)?

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

You have to use the with clause in the select that returns from the sp:

Select * from #TempGummilagring with ( OrderNr varchar(15),Rowkey varchar(32),ArtikelNr VARCHAR (16),ArtBeskrivning varchar (35),Amount INT, LevDatum varchar(10),calcAmount INT )

0 Kudos

if i change from

Select * from #TempGummilagring

to

Select * from #TempGummilagring with (OrderNr varchar(15),Rowkey varchar(32),ArtikelNr VARCHAR(16),ArtBeskrivning varchar(35),Amount INT, LevDatum varchar(10),calcAmount INT)

i get a "Syntax error near 'OrderNr'" when i try to save the procedure

0 Kudos

You right, the with clause must be used outside the procedure. Select * from spname() with (bla bla bla)

0 Kudos

hi that doesn't work either, i tried this:

SELECT * FROM OPENQUERY(MONWIN, 'exec sp_gummilagring') with ( OrderNr varchar(15),Rowkey varchar(32),ArtikelNr VARCHAR (16),ArtBeskrivning varchar (35),Amount INT, LevDatum varchar(10),calcAmount INT )

"Incorrect syntax near the keyword 'with'. "

VolkerBarth
Contributor
0 Kudos

What does happen if you do not use OPENQUERY but the EXEC ... AT <linkedserver> syntax?

0 Kudos

why you don't use select instead of exec? and the with clause in the openquery sql statment?

try this: SELECT * FROM OPENQUERY(MONWIN, 'select * from sp_gummilagring with ( OrderNr varchar(15),Rowkey varchar(32),ArtikelNr VARCHAR (16),ArtBeskrivning varchar (35),Amount INT, LevDatum varchar(10),calcAmount INT)')