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

[former_SQLA_member1694988]

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]

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

[former_SQLA_member1694988]

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]

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...)

[former_SQLA_member1694988]

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

[VolkerBarth]

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

[former_SQLA_member1694908]

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 )

[former_SQLA_member1694988]

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

[former_SQLA_member1694908]

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

[former_SQLA_member1694988]

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]

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

[former_SQLA_member1694908]

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)')