on 2019 Nov 14 5:50 AM
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.
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 )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
What does happen if you do not use OPENQUERY but the EXEC ... AT <linkedserver> syntax?
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)')
User | Count |
---|---|
62 | |
10 | |
10 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.