May 12, 2009 at 5:20 am
Hello.
I'm trying to insert registers in a table (sql server 2005 in w2003Server).
The registers are originally in a Linked Server (Progress DDBB, HP-UX, ODBC Sequelink driver).
I can find the original registers without problems by means one of these options:
1. Select * from openquery(LINKEDserver, 'SELECT * FROM table')
2. If I need a 'where' parameter, I parse it into a varible that contains the whole sentence (@sql = 'select * from table where qty =' + @qty), and use next:
EXECUTE (@sql) at LINKEDserver
Both options work fine, so the connection with the linked server works, and i can get & see the registers.
But when I try to insert these registers in my table:
insert table EXECUTE (@sql) at LINKEDserver
then I get the error:
OLE DB provider "MSDASQL" for linked server "LINKEDserver" returned message "[INTERSOLV][ODBC SequeLink driver]Optional feature not implemented.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDserver" was unable to begin a distributed transaction.
I have checked all the options in the ODBC driver setup; the MS DTC is working; so I'm absolutelly confused.
Does anyone know how to fix the error??
Or maybe anyone can suggest another solution to get the data inserted.
Please, I need your help.
Thanks in advance.
May 12, 2009 at 5:55 am
Did you try a different syntax such as:
INSERT INTO MyTable
SELECT *
FROM linkedserver.catalog.schema.table
or:
INSERT INTO MyTable
SELECT *
FROM OPENQUERY(linkedserver, 'SELECT * FROM catalog.schema.table')
I don't think this is a distributed transaction issue, but first you should test these different approaches to narrow down possibilities.
You could also check if two phase commit is enabled on the linked server properties. Maybe this is the "optional feature" mentioned in the error message.
-- Gianluca Sartori
May 13, 2009 at 8:09 am
Thanks for your quick answer.
It seems I am close to the solution:
DECLARE @sql NVarChar(4000),
@sto int
set @sto = 4200
SET @sql = N'INSERT INTO prueba
SELECT * FROM OPENQUERY(JTDSLINKED, ''SELECT *FROM table where
id = ' + cast (@sto as nvarchar(4)) + ''')'
exec sp_executesql @sql
This query does work !!
But I'm running into the problem of casting variables.
When the parameter in clause WHERE is a string:
DECLARE @sql NVarChar(4000),
@sto nvarchar (50)
set @sto = '''Pepe'''
SET @sql = N'INSERT INTO prueba
SELECT * FROM OPENQUERY(JTDSLINKED, ''SELECT *FROM table where
id = ' + cast (@sto as nvarchar(50)) + ''')'
exec sp_executesql @sql
I get the error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Pepe'.
I've tried without the 'cast', but I get the same error. So it seems to be a problem of variable conversion (sqlserver - progress).
Any idea will be great apreciated!!
Thanks.
May 13, 2009 at 8:19 am
you need even more quotes!
set @sto = '''''Pepe'''''
This will double quote the Pepe within the variable so when you add it to the other string it has the right number of quotes.
Basically, every time a string variable with quotes is consumed by another function like you're doing it looses a set of double quotes
Hope this makes sense 😀
May 14, 2009 at 2:01 am
Hi,
it may help to keep your code more readable if you reserve the ' to build the sql statement en use char(39) for the quotes you want inside your sql statement, like
set @sto = char(39) + 'Pepe' + char(39)
May 14, 2009 at 2:48 am
It works !!!
As you explained, a couple of quotes were missing.
Thanks very much, guys !!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply