THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

Issue with 64-bit SQL Server using SQL 2000 linked server

Recently we started adding SQL Server 2008 64-bit servers to our production set and we ran into the following issue. When we ran queries on a linked 2000 server, we were getting the following error:

OLE DB provider "SQLNCLI10" for linked server "XXXXXX" 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 "SQLNCLI10" for linked server "XXXXXX". The provider supports the interface, but returns a failure code when it is used.
 

This article from MS website describes the issue pretty well, although it says the issue applies to 2005 but we are using 2008. As suggested, we ran the Instcat.sql file on our development system first, and we ended up getting errrors left and right, so we didn't dare to run it on our main production server. In the end, this workaround worked for us - we needed to create a procedure in the master database on the linked 2000 server. The proc is called sp_tables_info_rowset_64 and it is needed because it is called by 64-bit servers when running remote queries.

 Here is the text of the proc in case you ever need to do the same, create it in the master database:

create procedure sp_tables_info_rowset_64

      @table_name sysname,

      @table_schema     sysname = null,  

      @table_type nvarchar(255) = null

as

  declare @Result int set @Result = 0

  exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
go

 

Published Sunday, May 10, 2009 11:24 PM by roman
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Venkat said:

Good work around.  In our case, we had similar problem as you mentioned.  Running the Instcat.sql worked just fine.  All our 2000 servers are 32-bit and 2005/2008 servers are 64-bit.

May 11, 2009 4:21 PM
 

Ryan said:

Thanks! This was exactly what I needed.

July 1, 2009 2:08 PM
 

Nikola@paunovski.net said:

Thank you

July 13, 2009 3:05 PM
 

€ric said:

Thank you!  I ran into this same problem and KB fix, but didn't want to run that massive script on a live, in use system.  I also granted exec rights to public on the sproc.  That matched with the permissions on sp_tables_info_rowset.  All is working well now.

July 21, 2009 12:03 PM
 

Robert said:

This solution worked for me after instcat.sql option failed.

July 31, 2009 10:07 AM
 

rohan said:

thanks a lot for this simple workaround. i didn't even try ms method

good information

November 17, 2009 8:06 PM
 

Jamie said:

Excellent.  Running instcat.sql on a production server was a scary proposition.  This solution is direct and low-risk.  Thanks.

December 18, 2009 4:32 PM
 

AmosFiveSix said:

Works great. Thanks!

December 29, 2009 1:43 PM
 

Bent Andre said:

It worked for me as well. Thanks.

January 8, 2010 4:00 PM
 

Matt said:

This worked Great.  Save me having to testing and shutting everything down to install.  Low Risk.

January 20, 2010 11:31 AM
 

Andy Foster said:

Great fix - got us out of a hole! Thanks for posting this

January 21, 2010 4:36 AM
 

Steve Davis said:

I am so glad I found this post.  After running Instcat.sql on my dev server (and watching it update a huge number of objects and taking over a minute), I was not looking forward to the prospect of running on a production server. Thanks

January 27, 2010 6:45 AM
 

Wil said:

Wow - soooo much nicer than running that big nasty script on prod - thank you very much!!

February 9, 2010 1:23 PM
 

Andy McNally said:

Excellent.  Thank you very much for posting.  You have saved me hours of head-scratching.

March 24, 2010 10:52 AM
 

James Cornell said:

Thanks! Worked perfectly and saved me a lot of hassles looking for a fix.

March 29, 2010 12:31 PM
 

Vik H said:

You sir, are THE MAN.  Like many others, the prospect of running instcat on a live server gave me the willies.

April 27, 2010 11:07 AM
 

NepromM said:

Thanks! This should work fine...

June 28, 2010 9:47 AM
 

Anand Poolla said:

Thank you, worked for me!

July 9, 2010 11:57 AM
 

dm said:

Brilliant.  Good job.

September 8, 2010 10:24 AM
 

Kristy said:

Thank you. Exactly what I needed.

September 12, 2010 8:07 PM
 

K9 said:

Thanks; works great!

November 1, 2010 4:28 PM
 

Olu said:

Thanks - I was jittery about messing with our live server but creating that proc solved the problem - nice and easy.

December 9, 2010 6:30 AM
 

DJ said:

Thanks! Worked for me.

December 17, 2010 11:18 AM
 

Thorsten said:

Thanks for your solution. It worked for me!

January 4, 2011 8:21 AM
 

Miki said:

Thank you, great work! Works!

January 5, 2011 5:56 AM
 

Guy D said:

Great workaround! works perfectly for me!

February 2, 2011 3:13 AM
 

dxb said:

Thanks. It worked for me.

February 11, 2011 9:18 AM
 

Carla said:

Thank you! Thank you! Thank you! =)

March 10, 2011 11:38 AM
 

Tayfun said:

Thanks for solution

April 14, 2011 8:50 AM
 

bwcatpeeler said:

Thank you for the great workaround!

April 18, 2011 10:10 AM
 

Eric said:

Thanks!  Worked great!

April 19, 2011 9:17 AM
 

Sudir Babu said:

Brilliant work. I appreciate you.

April 28, 2011 2:00 PM
 

Carlos Fleck said:

Great!!! Work Fine!!!

April 28, 2011 4:15 PM
 

Joanna said:

Thank you!

May 23, 2011 9:55 AM
 

Raul said:

Thanks for taking time to post.  It worked great for me also.

June 2, 2011 5:20 PM
 

NifflerX said:

Worked very well for me connecting SQL 2008 SP2 with SQL 2000 SP4.  Originally tried the instcat.sql but that didn't solve the issue, this did.  Thank you.

June 6, 2011 10:55 AM
 

Richard said:

Thanks Roman, lifesaver for me too!

June 27, 2011 3:53 AM
 

Doug H said:

Spectacular!

July 2, 2011 9:26 PM
 

JC Castro said:

Old SQL Server 2000 sp4 32bits linked to New SQL Server 2008 R2 64bits: Worked like a charm!

no instcat.sql used, just your stored proc.

After created, grant access:

GRANT  EXECUTE  ON [dbo].[sp_tables_info_rowset_64]  TO [public]

Fantastic! Thank you Roman!

July 6, 2011 7:16 AM
 

MicheleG said:

Excellent alternative to running instcat.sql on an older production system.  It worked for us.

July 7, 2011 11:39 AM
 

Tj said:

Worked Like a charm. Perfect solution and an alternative to run instcat.sql. Thanks for putting it here

July 8, 2011 4:30 PM
 

marengga said:

Thanks, you're rock maaan, fantastic !

July 12, 2011 12:20 AM
 

Richard said:

Thanks - how did we ever fix problems before the internet!

July 26, 2011 11:46 PM
 

knk said:

thank alot

August 10, 2011 5:38 AM
 

jnsnfl said:

Just what was needed... old production server couldn't afford to be patched due to old admins hacking the registry... house of glass!

August 16, 2011 5:28 PM
 

Nalin said:

Welldoneeee..........it's working for me......

August 23, 2011 7:34 AM
 

Barbara said:

THANK YOU!!!!  This is just what I needed too !!!

September 2, 2011 2:56 PM
 

uday said:

Thanks it worked like Gem...

September 21, 2011 3:13 AM
 

Jan said:

Great work!!! Thanks!

September 30, 2011 5:11 AM
 

Ben Joyce said:

Excellent - thank you.

October 24, 2011 4:52 AM
 

Haritha Nagam said:

thank you somuch it's working fine

November 6, 2011 1:24 AM
 

Nav said:

Great fix..creating that proc worked fine for me...thanks again...

November 7, 2011 11:48 PM
 

Aamir said:

Awesome, you are the beshttt...

November 30, 2011 5:02 AM
 

Aamir said:

Awesome, you are the beshttt...

November 30, 2011 5:02 AM
 

Acacio said:

You are simply the best !

December 5, 2011 1:44 PM
 

Michael Marini said:

Unlike Microsoft this is an Excellent Solution. Thank you very much

January 9, 2012 6:35 AM
 

Vengatesh said:

Thanks a lot. It worked like charm !

January 23, 2012 10:43 AM
 

J.J. said:

Add me to the list of people who were helped thanks to this post!  Gracias!

January 31, 2012 2:06 PM
 

Randy Keeping said:

Awesome... Thanks

February 9, 2012 4:43 PM
 

Gloria Giraldo said:

Perfecto! Muchas Gracias Roman.

February 9, 2012 7:57 PM
 

AB said:

Awesome, it works great. I was trying to connect SQL 2008 R2 back to SQL 2000 (SP3) and this wrap Stored procedure works like champ.

Thanks, Roman.

February 17, 2012 1:15 AM
 

roland said:

cool... thanks a lot..

February 21, 2012 1:32 AM
 

Alessandro Bernardi said:

Thankyou much for sharing!!

March 1, 2012 10:14 AM
 

Omar Noboa said:

Maestro de los maestros!!! gracias por tu aporte me fue de mucha utilidad.

Saludos a todos.

Atte.

Omar Noboa

March 6, 2012 3:41 PM
 

Rubens said:

What a great job! It worked like a charm. Thanks a lot!

March 12, 2012 11:52 AM
 

Aamir said:

Wow it worked for me>thanks alot for the post.

March 14, 2012 5:29 AM
 

Deependra said:

Thanks a lot. It solved my problem of Creating linked server from SQL server 2008 (64 BIT) to SQL Server 2000.

March 16, 2012 9:59 AM
 

soph said:

hi, my sql server 2000 is service pack 2. On which of the servers do I run the grant command? On the 2000 or 2008 server?

March 21, 2012 7:46 AM
 

Bill P said:

AWESOME!  WAY WAY better than running instcat.sql on a ridiculously old server.  Way to go and thanks.  

March 21, 2012 12:09 PM
 

Angie said:

Thanks heaps, this resolved my problem too with my linked server from 2008 to 2000. Absolutley Awesome !!!!

April 1, 2012 10:47 PM
 

Ramesh said:

Awesome this works, saved a lot from manual work...

April 5, 2012 6:09 PM
 

John Dunleavy said:

Thanks!  Worked perfect and saved us a lot of time diagnosing the issue.  Just used your proc and we were up and running again.

April 6, 2012 11:17 AM
 

Jay said:

Your Article was of great help!

April 9, 2012 5:28 AM
 

nico said:

thank you it work !!!

April 11, 2012 11:53 AM
 

Sam Ayadi said:

Thanks for the help!

April 17, 2012 11:09 AM
 

bishopofnairobi said:

Thank you. Worked beautifully!

April 17, 2012 5:07 PM
 

jaeger adams said:

Great article!  It works!

April 19, 2012 5:14 PM
 

mahesh said:

Thanks, its worked fine

April 27, 2012 2:33 AM
 

david said:

worked like a charm

May 7, 2012 7:58 AM
 

Rony said:

Hi! in my blog I have posted a guide to include a linked server in your SQL Server 2008:

runakay.blogspot.com/2012/05/using-linked-servers-in-sql-server.html

June 5, 2012 9:32 PM
 

Lorenzo said:

I love you! Thanks a lot!

June 8, 2012 9:06 AM
 

Yannik said:

Many thanks!! Worked directly. It's unbelievable MS advises to run Instcat.sql whereas the solution is so simple

Thanks again

June 11, 2012 8:00 AM
 

Keith Lard said:

Blimey, never came across a post with so much positive feedback!

June 28, 2012 10:51 AM
 

Kpro said:

Perfect!

Tank's a lot

July 12, 2012 1:06 AM
 

Pierky said:

Thanks man, it works fine!

July 13, 2012 5:21 AM
 

Carlos Gonzalez said:

Thanks. It worked on 2000.

August 16, 2012 5:29 PM
 

Dave said:

Just applied to our SS 2000 to allow access from SS 2008 R2.  Perfect!

August 28, 2012 9:25 AM
 

Martyn said:

Great worked for me. Thanks.

August 31, 2012 9:39 AM
 

Marc said:

I love you I love you I love you!

Why doesn't Microsoft ever release simple fixes like this?  It always has to be some gigantic convoluted thing that scares everyone.  This did the trick for me, thank you so much!

September 12, 2012 3:12 PM
 

Fredoid said:

That was exactly my problem, and it worked fine directly !

Many Thanks !

September 24, 2012 9:01 AM
 

allottee said:

This saved me a huge amount of time which I really did not have. Excellent. Thank You!

November 22, 2012 1:02 PM
 

Dana said:

This helped me so much. It's been a while since I've been in SQL 2000 and this saved me a bunch of time.

December 7, 2012 4:26 PM
 

Trish said:

Thank you for this post!  It helped save a lot of headaches.  

December 27, 2012 5:00 PM
 

Bart said:

Thank you ! This worked great ;-)

December 29, 2012 12:49 PM
 

Daniel said:

Fantastic, works like a charm

January 10, 2013 7:39 PM
 

Brian said:

Thank you very much! Worked perfectly!

February 7, 2013 1:00 PM
 

Patel said:

Thanks.. Worked  perfectly

February 13, 2013 3:16 PM
 

Hemanshu said:

Thanks man , worked great .. Solved my 4 days of headache .

February 19, 2013 2:20 PM
 

Vilas Mahalle said:

Thanks Man...you saved me

March 5, 2013 11:52 AM
 

Richard Treweek said:

Thanks for the help. Worked like a charm.

March 15, 2013 3:23 AM
 

David said:

You are a rockstar!!!! This worked for men as well!

April 9, 2013 2:34 PM
 

Jim said:

THANKS!!  this worked for me too!

July 2, 2013 12:45 PM
 

A said:

Thank you very much

July 5, 2013 6:03 AM
 

David said:

Awesome! So simple. Thank you very much!

August 20, 2013 2:48 PM
 

John Mtawala said:

thank you so much,was awesome

September 13, 2013 5:46 AM
 

Bala said:

Much Appreciated. Great Help

October 9, 2013 9:08 PM
 

Stan said:

Awesome worked very well and easily.  Thank you very much.

October 17, 2013 10:38 AM
 

Usha said:

while executing the sp i got error:"object sys doesn't exist or you do not have permissions"

Please give some solutions

October 29, 2013 3:13 AM
 

Bai Lamin said:

Couldn't thank you much mate! Spent a good couple of hours trying to fix this issue

May 8, 2014 9:31 AM
 

Jose Hernandez said:

Works really great, thank you!

I have spent long time looking aroung too solve this issue.

Please add the information about doing this in the "simple" Stored Procedures (not Extended Stored procedures) and having PUBLIC allowed to execute this in permisons of the new stored procedure.

November 19, 2014 8:41 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement