Insert into openquery error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert into openquery error

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-06 : 22:53:50

Hello All,

I have an insert openquery statement that is giving the below error; does anyone know what I'm doing wrong? Please advice. Thanks.

 
declare @Report_Date as DATETIME

set @Report_Date = '09/05/2007'

--** DECLARE VARIABLE **--
DECLARE @sqlstring  nvarchar(1000)

--** DELETE POSSIBLE DUPLICATES **--
PRINT 'DELETING DUPLICATE RECORDS'
DELETE FROM TEST_DATA
WHERE REPORT_DATE = @Report_Date

--** QUERY AND LOAD DATA **--
SET @sqlstring='INSERT INTO TEST_DATA SELECT ''' + CONVERT(VARCHAR(11), @Report_Date, 101) + ''', SAMPLE.* FROM OPENQUERY(ORC1,''SELECT ENTERED_ID,
NEXT_VALUE_DATE FROM TEST_DATA where NEXT_VALUE_DATE = ('''''+ CONVERT(varchar(11),@Report_Date,20) + ''''')'') AS SAMPLE'

PRINT 'LOADING TEST_DATA'

EXEC sp_executesql @sqlstring

Error Message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12801: error signaled in parallel query server P004
ORA-01861: literal does not match format string
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ].

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 03:31:08

CONVERT(varchar(11),@Report_Date,20)

if you want "2007-09-07" that is 10 characters, not 11, so the about will give you a trailing space (which it would be nice if Oracle ignored in its implicit date conversion, but there we go !!

Best with these types of dynamic SQL problems to output the @sqlstring so you can debug that separately

Kristen

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-07 : 09:02:44

quote:
Originally posted by Kristen

CONVERT(varchar(11),@Report_Date,20)

if you want "2007-09-07" that is 10 characters, not 11, so the about will give you a trailing space (which it would be nice if Oracle ignored in its implicit date conversion, but there we go !!

Best with these types of dynamic SQL problems to output the @sqlstring so you can debug that separately

Kristen

Kristen,

Thanks for you response, but I changed CONVERT(varchar(11),@Report_Date,20) to CONVERT(varchar(10),@Report_Date,200 it still was giving me the same error. Maybe i did not do actually what you told me to do, please explain further or please rewrite my script. Thanks once again.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-07 : 09:49:16

Take the string that is being executed and run it on oracle - if it doesn't work then change it until it does.
Next hard code the string in the openrowset statement
Then construct it from the parameters - checking the result before you run it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 15:15:33

"Maybe i did not do actually what you told me to do"

No, I reckon you understood what I meant exactly!

But basically you need to follow the steps Nigel has outlined to find out what's causing the problem, and then resolve it.

Kristen

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-13 : 07:29:04

I have rewritten the code:

declare @Report_Date as DATETIME

set @Report_Date = '09/13/2007'

--** DECLARE VARIABLE **--
DECLARE @sqlstring  nvarchar(1000)

--** DELETE POSSIBLE DUPLICATES **--
PRINT 'DELETING DUPLICATE RECORDS'
DELETE FROM TEST_DATA
WHERE REPORT_DATE = @Report_Date

--** QUERY AND LOAD DATA **--
SET @sqlstring='INSERT INTO TEST_DATA SELECT ''' + CONVERT(VARCHAR(12), @Report_Date, 101) + ''', SAMPLE.* FROM OPENQUERY(ORC1,''SELECT ENTERED_ID,
NEXT_VALUE_DATE FROM TEST_DATA where NEXT_CALLTIME = ('''''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''')'') AS SAMPLE'

PRINT 'LOADING TEST_DATA'

EXEC sp_executesql @sqlstring

Now I'm getting a different error which is shown below

Error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-01797: this operator must be followed by ANY or ALL
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80004005: ].

Does anyone know what I'm doing wrong? Please advice. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:32:22

Listen to nr's advice.
Write the query in ORACLE and execute it until it gives you the desired result.
When accomplished, then copy the code to SQL Server and pick the statement apart into a dynamic query.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 12:59:56

If you need a query tool for Oracle then you might like to try TOAD - assuming there is still a free version.

Its pretty similar to Query Analyser.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources