OPENQUERY DYNAMIC SQL PROBLEM

Question

OPENQUERY DYNAMIC SQL PROBLEM

PaulSp

SSCrazy

Points: 2499
More actions
October 27, 2008 at 9:53 am

#70845

Hi
I'm having a problem updating an Oracle table using a linked server from SQL Server 2000, and hope someone can help. It works fine if I just use Openquery and type values, but I have to use parameters. I've tried the code below and get the error messages indicated. Does anybody have any idea what I'm doing wrong?
Many thanks
Paul
--gives error message 'incorrect syntax near ')'
DECLARE @sql NVARCHAR(2000)
DECLARE @AC varchar(5)
set @AC = '1' --IDFIELD
DECLARE @myvalue varchar(5)
set @myvalue = 'VV'
SET @sql = N'
UPDATE OPENQUERY(MYLINKEDORACLESERVER,''SELECT FIELDNAME FROM ORACLETABLE
WHERE IDFIELD = ' +
CAST(@AC AS VARCHAR(100)) +
' SET FIELDNAME = ' +
CAST(@myvalue AS VARCHAR(200)) + ''')';
EXEC(@sql);
--gives error message 'incorrect syntax near 'VV'
DECLARE @sql NVARCHAR(2000)
DECLARE @AC varchar(5)
set @AC = '1' --IDFIELD
DECLARE @myvalue varchar(5)
set @myvalue = 'VV'
SET @sql = N'
UPDATE OPENQUERY(MYLINKEDORACLESERVER,''SELECT FIELDNAME FROM ORACLETABLE
WHERE IDFIELD = ' +
CAST(@AC AS VARCHAR(100)) +
' SET FIELDNAME = ''' +
CAST(@myvalue AS VARCHAR(200)) + ''''')';
EXEC(@sql);

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Use this to check your strings before you try to exec them. PRINT @sql. Then you can see what you are actually submitting, which right now, has a few different errors that I can spot. The VV issue comes from the fact that it is not wrapped in single quotes.

Your current string is this:

UPDATE OPENQUERY(MYLINKEDORACLESERVER,'SELECT FIELDNAME FROM ORACLETABLE

WHERE IDFIELD = 1 SET FIELDNAME = VV')

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

PaulSp SSCrazy Points: 2499 More actions · Answer 2

Hi Seth

Thanks a lot for replying. Yes, I have been printing @sql. However, like an idiot, I've got the error messages above the wrong way around. The first(top) example above gives what you have shown with VV' - with a missing opening '. To correct this I added apostropes as per the second(bottom) example. This give the error message 'incorrect syntax near ')'. So, something is amiss somewhere??

Many thanks again

Paul

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Paul,

Try this one:

SET @sql = N'

UPDATE OPENQUERY(MYLINKEDORACLESERVER,''SELECT FIELDNAME FROM ORACLETABLE

WHERE IDFIELD = ' +

CAST(@AC AS VARCHAR(100)) + ''')' +

' SET FIELDNAME = ''' +

CAST(@myvalue AS VARCHAR(200)) + '''';

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

PaulSp SSCrazy Points: 2499 More actions · Answer 4

Hi Seth

This appears to work, however returns the message:

'The command(s) completed successfully'

rather than '1 row affected'. I've checked several ways, and the update hasn't been performed. However, it seems closer that I have been getting...

Thanks

Paul

PaulSp SSCrazy Points: 2499 More actions · Answer 5

Hi Seth

It's the end of a long day. Your code works fine...

I can now go home in peace. Brilliant!!

Very many thanks

Paul

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Glad we could help. Thanks for the feedback.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

PaulSp SSCrazy Points: 2499 More actions · Answer 7

Hi Seth

Could I pick your brains a little more..?

If I wanted to batch update an oracle table from a SQL Server 2000 table, joined on an id field and updating with a value from the SQL Server table, is this possible in a similar way.

My thinking is that although I can now, thanks to yourself, have a user update Oracle directly through the OpenQuery code in a stored procedure - if there is a connection problem at any time, this update will not be effected.

If I am able to run a daily batch update, this would correct this problem.

Thanks in anticipation

Paul

Garadin One Orange Chip Points: 29613 More actions · Answer 8

This is a bit beyond me, but my thinking is that you should be able to do this via fully qualified names and a linked oracle server.

Here is an article on setting up oracle as a linked sql server:

http://www.databasejournal.com/features/mssql/article.php/3290801

Here's another interesting article on the topic:

http://articles.techrepublic.com.com/5100-10878_11-1054237.html

Once you have oracle set up as a linked server, it should just be a matter of joining the tables via Fully qualified names and performing your updates. That said, this is just my idea of how it would work, I'm not positive on any of this.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

PaulSp SSCrazy Points: 2499 More actions · Answer 9

Hi Seth

I have tried to achieve this before - without a great deal of success. I'll work through, as you suggest, and see if I can make it happen this time...

Thanks for all your help - which is very much appreciated.

Best wishes

Paul