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.

asked 14 Nov '19, 05:50

Mattias's gravatar image

Mattias
26113
accept rate: 0%

edited 14 Nov '19, 05:54

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

(14 Nov '19, 09:15) Volker Barth

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.

(14 Nov '19, 09:23) Mattias

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

(15 Nov '19, 13:59) Volker Barth

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

(18 Nov '19, 01:52) Mattias
Replies hidden

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

(18 Nov '19, 03:46) Volker Barth

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 )

permanent link

answered 15 Nov '19, 02:59

SamuelCosta's gravatar image

SamuelCosta
71338
accept rate: 20%

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

(15 Nov '19, 03:58) Mattias

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

(15 Nov '19, 04:11) SamuelCosta

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'. "

(18 Nov '19, 01:55) Mattias
Replies hidden

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

(18 Nov '19, 03:44) Volker Barth

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

(18 Nov '19, 05:54) SamuelCosta
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243
×41

question asked: 14 Nov '19, 05:50

question was seen: 1,313 times

last updated: 18 Nov '19, 05:54