January 24, 2008 at 8:57 am
I'm trying to set up a linked server between SQL2K5 SP2 64-bit and a SQL2K RTM 32-bit server. (Yes, I know I really should just upgrade the SQL 2K boxes, that's a long story)
The linked server sets up just fine, but when I try to use it for a query, I get:
OLE DB provider "SQLNCLI" for linked server "pfc-dev-srvr" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "pfc-dev-srvr" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "pfc-dev-srvr". The provider supports the interface, but returns a failure code when it is used.
I found the following KB article: http://support.microsoft.com/kb/906954 that basically tells you that you need to have at least SP3 installed for it to work.
But I have another SQL2K RTM server with a linked server created to it and it works just fine. Both are using the same OS and hardware.
So does anyone know what the deal is? It doesn't make sense why one would work and the other doesn't.
The Redneck DBA
January 24, 2008 at 9:04 am
There are some known problems using linked servers between 2005 and 2000. The fix is to re-apply the instacat.sql found in SP4. What is the objection to upgrading to SP4?
January 24, 2008 at 9:08 am
Can I run that script against RTM without upgrading to SP4 first?
The objection? I said it was a long story 😛 ... We have a perfectly good list of objections:
1) Upgrading to SP4 would remove bugs.
2) Upgrading to SP4 would make the server more secure.
3) Upgrading to SP4 would make sense.
4) Upgrading the RTM servers to SP4 would make all of our 2K servers the same version, which would ease administration.
As you can see, we have several valid reasons to not upgrade to SP4 🙂
The Redneck DBA
January 24, 2008 at 9:12 am
Jason Shadonix (1/24/2008)
Can I run that script against RTM without upgrading to SP4 first?The objection? I said it was a long story 😛 ... We have a perfectly good list of objections:
1) Upgrading to SP4 would remove bugs.
2) Upgrading to SP4 would make the server more secure.
3) Upgrading to SP4 would make sense.
4) Upgrading the RTM servers to SP4 would make all of our 2K servers the same version, which would ease administration.
As you can see, we have several valid reasons to not upgrade to SP4 🙂
Honestly, I do not know ... I just know that I experienced this problem quite a bit until I found resolution. Yet all of our 2000 machines were on SP4 ... so I can't answer that question. Best bet would be to bring up a QA instance on RTM and give it a shot.
On the topic of not wanting to upgrade, trust me I know where you're coming from. Everyone was very weary and hesitant of doing it the first time ... it took Microsoft telling us to do it to finally convince management to sign off.
January 24, 2008 at 9:15 am
We don't even have that excuse 🙂 of the 25 some-odd SQL2K boxes we have, only 3 of them are RTM, the rest are all SP3 or SP4.
The Redneck DBA
January 24, 2008 at 9:17 am
Jason Shadonix (1/24/2008)
We don't even have that excuse 🙂 of the 25 some-odd SQL2K boxes we have, only 3 of them are RTM, the rest are all SP3 or SP4.
Wow, just do it then! Don't tell 'em ... just do it 🙂
Also, I looked over the instcat.sql file ... and I would be very nervous applying it to a non SP4 server.
January 24, 2008 at 10:00 am
Made me nervous too...but I stumbled onto this: http://msdn2.microsoft.com/en-us/library/aa215459(SQL.80).aspx which made is sound OK. So I crossed my fingers and let it rip, and it fixed the issue. (It's only a development box, so I figured the worst that would happen was It would totally blow up, in which case I would have had an excuse to just reinstall the whole thing and apply SP4 🙂 )
Thanks for letting me bounce it off of you.
The Redneck DBA
January 24, 2008 at 10:04 am
Glad it worked! Now just get them servers updated 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply