Strange behaviour with OPENQUERY and Oracle Driver

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 2008 Forums

Other SQL Server 2008 Topics

Strange behaviour with OPENQUERY and Oracle Driver

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 19:49:21

We have a query which we run on Oracle using OPENQUERY. We sometimes encounter an error, and once it occurs we don't seem to be able to process any more queries until we reboot.

Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "MyOracleServer" reported an error. The provider did not give any information about the error.
Server: Msg 7305, Level 16, State 1, Line 1
Cannot create a statement object using OLE DB provider "OraOLEDB.Oracle" for linked server "MyOracleServer".

After getting that error again today we tried some simple queries to the Oracle server and they still worked, and we then tried altering the Oracle query that was failing and discovered that removing a comment in the query, or a line break, enabled the query to run.

The query is in this style:


SELECT *
FROM	
OPENQUERY(MyOracleServer,
'SELECT
		vfs.Col1,
		vfs.Col2,
		vfs.Col3,
		NULL AS Col4
	FROM	MyOracleDatabase.MyTable1 vfs,
		MyOracleDatabase.MyTable2 p
	WHERE	    vfs.Col1 = p.Col1
		AND p.Col5 = 0
		AND 
		(
			    vfs.Col3 <> 0	-- In stock
-- 			OR 
-- 			(
-- 				    p.Col6 = 0	-- Direct delivered
-- 				AND p.Col7 = 1
-- 			)
		)
		AND vfs.Col2 = ''200'' 
')

If I removed the 5 comment lines, or the final line break before the closing:

')

the command then worked. The full OPENQUERY command has been running for years without change, and works just fine until we hit this Oracle error - and then it won't work until we reboot.

Any ideas as to why removing the comment, or line-break, works? Or perhaps why having the comments in the query, or the final line-break prevents it working some of the time (given that the rest of the time it works fine with comments and line break included)

Oracle Driver is v. 11.2.0

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-28 : 02:26:24

I have no clue whatsoever about your problem except that it seems like a bug in the driver. I also noticed that you're using the OLEDB-driver...have you considered switching to ODBC instead?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-28 : 02:27:08

quote:
Originally posted by Lumbago

I have no clue whatsoever about your problem except that it seems like a bug in the driver. But I noticed that you're using the OLEDB-driver...have you considered switching to ODBC instead?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 03:52:27

"I also noticed that you're using the OLEDB-driver...have you considered switching to ODBC instead?"

I regard ODBC as being primitive - as in deprecated - compared to OleDB? or maybe you were suggesting to try it and see if that does not have this problem?

I can't recreate the problem at will, so we have to wait until it next fails, and then we can try alternative queries - so I could try ODBC at that time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources