Linked server - openquery error

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

SQL Server Administration (2000)

Linked server - openquery error

Author

Topic

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2005-09-13 : 06:06:29

really pulling my hair out with this so any suggestions appreciated.

I've got SQL Server 2000, running on Windows Server 2003.
I have added a linked server (Oracle) and the link is fine. I can see all the tables and can select from most of them perfectly well. Some of the tables however are throwing back the following error and returning just 1 row. I'm not trying to do any updates etc, just a simple select * for the moment.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].

I've done a few searches for this error and but have not found anything so far that really applies to my situation.

any ideas?

thanks
Em

Kristen
Test

22859 Posts

Posted - 2005-09-13 : 08:28:38

Anything unusual about the datatype or the columns you are trying to pull, or the overall size of rows or the size of the total resultset?

I think I'd have a go with subsets of columns to try to narrow it down - but I'm only clutching at straws, you've probably already tried that.

I've had this type of error from an OPENQUERY error with Oracle. How come the folk that wrote the drivers didn't include enough feedback to point developers in the right direction? Annoying - to say the least!

Kristen

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-13 : 08:33:57

open query processes the query on the linked server. Why not try a direct reference which will process the query on the SQL Server? I have not used Oracle, but it should be something like...

linkedservername.dbname.owner.table

or

linkedservername.table

or

linkedservername.dbname.table

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2005-09-13 : 09:11:15

Kristen - I had been going through the individual columns to try and narrow it down already but it's just making the problem wierder. I thought at one point I'd got it down to 1 'problem column', but it actually just makes it intermittent. run it once and fails, run it again and it works (sometimes anyway).

I'm just concentrating on 1 table so far but I've got the same problem a few tables. In this 1 there are 56 columns, up to 6 columns and it returns a result no trouble, the more columns I add into the select the more frequently it fails, at 39 columns it never works.

Sean - I'm using openquery because I was not able to get a straight query to run at all, returns...

the provider does not expose the necessary interfaces to use a catalog and/or schema

thanks so far, any other suggestions?

thanks
Em

Kristen
Test

22859 Posts

Posted - 2005-09-13 : 09:43:06

Could it be that its running OK the second time because Oracle has it cached by then? Or maybe the "connectoid" is shared and that's causing grief? More long shots I'm afraid!

Can you run the self-same query direct on the Oracle box? That might expose some deadlock or other grisly error that Oracle is trying to raise, but isn't getting through the COMMs layer.

Is it worth looking for newer drivers?

Sorry ... just running down a mental checklist, not sure if its much help.

Kristen

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2005-09-27 : 05:06:02

I know this is an old one now but I've still not resolved it and thought I'd give it another go.

Since then I've been selecting just the minimum columns I need from the 'dodgy' tables rather than the lot. Whilst it works ok, it's not really practical, as I need more of the columns now. I've been playing with different combinations of columns and am wondering if it could be something to do with null values?

The key columns select fine, but when I add in a column containing revenue which apparently contains some nulls (why nulls? don't get me started?!?!), the error comes back.

Any thoughts?

thanks
Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2005-09-28 : 06:06:42

ok, I've narrowed down a work around but I don't like it and I'm still no wiser as to why it's a problem in the first place.

The error occurs when you get a nullable numeric or datetime field (character strings are fine). So I'm working around it by doing a case statement for each affected field and putting a value in instead. messy I know, but I can't think of a better way at the the minute.

Obviously I'd rather just stop it happening in the first place so if anyone has a brainwave, please let me know

Thanks
Em

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 06:12:38

So:

SELECT TOP 1000 MyDateColumn
FROM MyTable
WHERE MyDateColumn IS NULL

is slower than

SELECT TOP 1000 MyDateColumn
FROM MyTable
WHERE MyDateColumn IS NOT NULL

??

Kristen

anaji
Starting Member

15 Posts

Posted - 2012-01-12 : 05:25:19

i'm sorry but i'm facing the same issue here any clear solution for it?

thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources