November 25, 2014 at 5:42 am
We moved buildings over the weekend, and had to set new IP addresses for some of our servers. Our SQL Server box was given a new IP. It has a linked server that points to an Oracle database (whose IP was unchanged).
Since then, some queries have been returning the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "VMFG" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "VMFG".
The error seems to be related to the size of the data set returned. Queries that return smaller data sets return fine with no error.
The query I first found this problem was:
DECLARE @query NVARCHAR(4000), @finalQuery NVARCHAR(4000)
SELECT @query =
'
select ID, COMMODITY_CODE, PRODUCT_CODE, ANNUAL_USAGE_QTY, ABC_CODE, USER_10, DESCRIPTION from PART
'
PRINT @query
SET @finalQuery = 'SELECT * FROM OPENQUERY(VMFG, ''' + @query + ''')'
EXEC(@finalQuery)
This will return 1601 rows (of a 28,000 row table) and then error. I tried selected the TOP N rows, and it doesn't error if I N=1700 (but only returns 1631 rows), and it does error if N=1701. If I return just the ID column, I get all the rows. If I choose half of the columns, I get more rows but it still errors.
What's curious is that:
SELECT ID, COMMODITY_CODE, PRODUCT_CODE, ANNUAL_USAGE_QTY, ABC_CODE, USER_10, [DESCRIPTION] FROM VMFG..SYSADM.PART
This query returns all the rows. Still, it generates the same error if I try to SELECT *, so it appears to be tied to the same problem but has a larger threshold of data throughput.
I switched a couple queries over to 4-part direct references just to get things working, but they are noticeably slower. This isn't a good long-term solution because it holds table locks for much longer. There's a reason I've set up all of my linked server queries through OPENQUERY. Further, some of my queries return more data than even the four-part reference can handle.
I've found a lot of web links that suggest that I should enable "Allow Inprocess" on the provider. This was already enabled on the provider. I've tried disabling it, restarting the server, enabling it again, and restarting the server again. This had no effect on the error.
This didn't happen before we changed the IP on our SQL Server. Could someone provide some insight into what might be going on here?
November 25, 2014 at 12:59 pm
I was able to run huge queries through SQL*Plus from both my computer and our SQL Server box. This rules out a network issue and an issue with Oracle. The issue appears to be coming from SQL Server and the linked server. I've tried deleting and recreating the linked server, messing with the provider settings, restarting the service, everything I can think or find through Google. Nothing seems to be working, but I have narrowed it down to a SQL Server linked server issue.
December 3, 2014 at 10:11 am
The issue ended up being a firewall problem. I found it accidentally, but maybe the steps I took will help someone else solve a similar problem:
I decided to try using ODBC for the linked server instead of OLEDB. It's a roundabout way of doing things that you wouldn't normally do, but I was just trying to get some reports working again any ways I could. When I ran above query through the ODBC linked server, the results where very similar. I returned some of the rows and then threw an error. The big difference was that the error message was different. OLEDB gave a basically useless error message, but for some reason the error message through ODBC was actually useful. In our instance, the error message was 'bad packet'. I gave the error to our network admin, and he was able to find a solution within minutes.
So, if you run into this error, consider running the linked server through ODBC and see if it gives a different error message.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply