Linked Server OpenQuery Update Error

[cLFlaVA]

Hi all.

I'm having a hard time here. I know this is kludgy, but I'm trying to make it work.

I have a DTS package on SQL Server 2000, attempting to update data in an Oracle 9.2 database.

I've already had to unwillingly use the Exec statement along with the OpenQuery statement, which I hate because it seems archaic, like this:

    select @strSql = 'UPDATE openquery(KINTANA, ...)
        SET ...
      WHERE PARAMETER1= ''' + cast(@SUB_ID as nvarchar(5)) + ''''

    exec (@strSql)

I replaced exec with print for debugging purposed and the generated SQL looked fine. I'm getting this error:

OLE DB provider 'MSDAORA' reported an error.  
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: ORA-00904: "PARAMETER7": invalid identifier
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::SetData returned 0x80004005:   ].

can anybody help me? i removed all fields one at a time in an attempt to determine which was causing the issue. each time the error reported the very last SET clause as having the error.

please help, i'm at a complete loss. i know, i copied/pasted/eliminated some code. let me know if you want to see the whole thing.

thanks,

cory



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

Cory,

This question is a little out of my comfort zone so please bear with me.

You said, [tt][blue]"I replaced exec with print for debugging purposed and the generated SQL looked fine."[/blue][/tt]

Did you try running it? Sometimes looks can be deceiving.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cLFlaVA]

yeah, that's when i was getting the error. i assumed it was something with the SQL - that's when I used the print to see what was going on. no dice - the query looked fine.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[AlexCuse]

I think it would probably help if you posted the query that @strSql represents.

Also, I'm sure you've checked this already but what is the character length of your query vs. character length of @strSql (although if the printed query worked alright in Query Analyzer I doubt this is the root of your problem).

Good Luck,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[cLFlaVA]

query generated is:

UPDATE openquery(KINCONN, 'select d.* from projects p join project_details d on p.project_id = d.project_id where p.project_id = p.master_project_id')
   SET PARAMETER7 = '771',    
       VISIBLE_PARAMETER7 = 'Baseline Web and Other Applications',
       PARAMETER14 = '272000',
       VISIBLE_PARAMETER14 = '272000',
       PARAMETER13 = '26854',
       VISIBLE_PARAMETER13 = '26854',
       PARAMETER20 = '272000',
       VISIBLE_PARAMETER20 = '272000',
       PARAMETER2 = '30008',
       VISIBLE_PARAMETER2 = 'Joe Schmoe',
       PARAMETER19 = '64',
       VISIBLE_PARAMETER19 = 'Baseline',
       PARAMETER8 = 'bapps',
       VISIBLE_PARAMETER8 = 'Bus. Appl. Supt.',
       PARAMETER4 = '9',
       VISIBLE_PARAMETER4 = 'GRC',
       PARAMETER3 = '26',
       VISIBLE_PARAMETER3 = 'GITS',
       PARAMETER10 = '26',
       VISIBLE_PARAMETER10 = 'GITS',
       PARAMETER18 = '100',
       VISIBLE_PARAMETER18 = '100',
       PARAMETER5 = 'INFOMG',
       VISIBLE_PARAMETER5 = 'Information Management'
 WHERE PARAMETER1= '1459'

each of the fields being updated are of type varchar2(250).

thanks for your help.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[cLFlaVA]

the error i'm getting is actually pointing towards VISIBLE_PARAMETER20 now, although the error is the same.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

Is it possible that your data has apostrophes in it? This would certainly cause problems.

    select @strSql = 'UPDATE openquery(KINTANA, ...)
        SET ...
      WHERE PARAMETER1= ''' + [!]Replace([/!]cast(@SUB_ID as nvarchar(5))[!], '''', '''''')[/!] + ''''

    exec (@strSql)

I think I got the apostrophes right, but you get the idea.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cLFlaVA]

hmm.

the data on my end has already been cleaned of that stuff. even further, parameter20 is a numeric value.

however, something about the openquery tells me maybe i should try a replace on every single field i'm selecting - only because openquery seems to be terrible, and this table holds multiple types of data in every column (don't look at me - third party vendor).

i'll try and get back.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[cLFlaVA]

i've added replace statements for each and every column. i then added aliases to reduce the string length, because it was coming close to 8000 (the max).

now i'm getting a new error:

[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
Server: Msg 7344, Level 16, State 2, Line 1
OLE DB provider 'MSDAORA' could not UPDATE table '[MSDAORA]' because of column 'C1'. The data value violated the schema for the column.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::SetData returned 0x80040e21:  Data status sent to the provider: [COLUMN_NAME=C1 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=C2 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=C3 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=C4 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=C5 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=C6 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=C7 STATUS=DBSTATUS_S_OK]...

there absolutely MUST be a better way of doing this. does anyone have any ideas?



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

Whenever I get this error...
[tt][blue]Multiple-step operation generated errors. Check each status value.[/blue][/tt]

in VB, it's because I tried to add data that doesn't 'fit'. For example, a varchar(10) field, but data that is larger than 10 characters long.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cLFlaVA]

i'm lost, then.

the longest value i'm trying to enter in this sql is 35 characters. each of the parameter and visible_parameter fields have a varchar2(250) type.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

Double check your apostrophes. Imagine this query...

Update table
set Field1 = 'blah,
Field2 = More blah'

Because of the apostrophes, field 1 will get updated, but not field 2 becuase there is no ending apostrophe on the end of field1 and no beginning apostrophe at the start of field 2.

I can't say for sure that this is what is causing your problem, but it is the first thing I would look in to.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

If it's not your apostrophes I think I'm lost too. What's your new @strSql look like?

Take a deep breath,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[cLFlaVA]

i've taken a step back and commented everything i don't need out. i even took a step back from the complexity of the SQL string. here's my new code:

    update openquery(KINTANA, 'select d.parameter1, replace(d.parameter7, '''''''', '''''''''''''''') parameter7 from kdrv_projects p left join kdrv_project_details d on p.project_id = d.project_id where p.project_id = p.master_project_id and d.parameter1 is not null')
       set PARAMETER7 = cast(@PROGRAM_ID as nvarchar(250))
     WHERE PARAMETER1 = cast(@SUB_ID as nvarchar(250))

error:

OLE DB provider 'MSDAORA' could not UPDATE table '[MSDAORA]' because of column 'PARAMETER7'. The data value violated the schema for the column.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]

*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[AlexCuse]

I had another thought -

Because you want to run your query on a linked Oracle server, have you tried parsing your SQL Statement in [Oracle's Equivalent of Query Analyzer if there is one]?

There may be subtle differences in syntax that are causing the error.

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[cLFlaVA]

just verified - works fine in Oracle.





*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[AlexCuse]

Man, this is a killer!

I'm not an expert on the OPENQUERY syntax by any means, but have you considered moving this piece:

 where p.project_id = p.master_project_id and d.parameter1 is not null

outside your parentheses? So you'd use something like this:

    update openquery(KINTANA, 'select d.parameter1, replace(d.parameter7, '''''''', '''''''''''''''') parameter7 from kdrv_projects p left join kdrv_project_details d on p.project_id = d.project_id')
       set PARAMETER7 = cast(@PROGRAM_ID as nvarchar(250))
     WHERE PARAMETER1 = cast(@SUB_ID as nvarchar(250))
[COLOR=red] AND p.project_id = p.master_project_id and d.parameter1 is not null[/color]

I think that because openquery is treated like a table, you may need to place the where clause outside the parentheses?

Let me know if this gets you anywhere (at least to a different error:-()

Alex




Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[cLFlaVA]

the where clause outside the openquery would not be an option, because this thing is inside of a cursor, so it will be run approximately 500 times. selecting ALL the data in the openquery table each time will make this process disastrous.

UPDATE:

I thought outside the box. Instead of using the openquery UPDATE, which was clearly causing numerous problems, I created a new table in Oracle to be used as a temporary storage facility.

I then created a trigger on this table to update the table I was originally trying to update, after insertion of a new record.

Lastly, from my DTS job, I used openquery to delete all records in this storage table, then insert the new records.

This works perfectly.

Thanks for all of your help, guys. I appreciate you taking the time to try and get me up and running.

Cory



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[AlexCuse]

Instead of using the openquery UPDATE, which was clearly causing numerous problems, I created a new table in Oracle to be used as a temporary storage facility.

That was my next suggestion

Glad you got it to work!

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.