January 3, 2008 at 11:21 pm
I'm having the same problem trying to use a linked server from a 64bit SQL2005 Server SP2 to a 32bit SQL2000 server SP3.
I've tried running the instcat.sql file which ran fine and the 64bit SPs have been created on the SQL2000 server but it's still failing.
The new message I'm getting is ...
OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.
The code I'm using when I'm getting this message is just a simple update which joins from a table of the 64bit SQL2005 box to a table on the 32Bit SQL2000 box. The code is fine and it had been working without any problems up until we migrated to the 64bit version of SQL2005
Anyone got any ideas?
January 30, 2008 at 4:30 pm
I'm not sure what the issue could be, we go from 32bit 2000 to 64bit 2005 based servers (and vice versa ) and from 32bit 2005 to 64bit 2000 servers all the time. I know when dealing with XML packages we had to install SQLXML 4.0 on our 64bit 2000 servers. Also, I know we had to make some changes to handle cursors while using DTC by using the XACTABORT ON (or was that OFF) anyway - when we didn't have that set properly it threw an error that read something like this: ...unable to begin a distributed transaction...
April 7, 2008 at 6:29 pm
Situation:
Attempting to Use linked server from 64 bit SQL2005 to 32 BIT SQL2000 sp4
SELECT * FROM aussql1.aisdata.dbo.tblActiveDirectoryExtract
returns
OLE DB provider "SQLNCLI" for linked server "aussql1" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "aussql1" 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 "aussql1". The provider supports the interface, but returns a failure code when it is used.
The Forums suggest that a script called INSTCAT.sql be run to correct the problem -- I'm very hesitant to run this as it rewrites system objects in Master.mdb.
April 7, 2008 at 6:31 pm
Further to above post the issue is also manifest going from 64BIT SQL2005 SP2 to a 32 Bit SQL2005 SP2.
Same error and description.
CodeOn 😛
April 11, 2008 at 9:51 am
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1
Excerpt from the article:
"When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
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
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk"
November 7, 2008 at 1:00 pm
The reason you receive errors when you parse the query is because you have not yet run the initial piece of the script which set the options etc and RECONFIGURE's so that you can modify system procedures...if you run the first several lines of the instcat.sql on you SQL 2000 server (everything before the "** If old versions of tables exist, drop them.") and then parse the query it comes back clean...the parser isn't smart enough to realize that you will run the setup steps and *then* try to modify the system procedures - it just says "sorry can't do that"
AndyG
December 17, 2008 at 2:03 pm
We have a real mixed bag of SQL servers and I've run into this issue a number of time when linking various servers. Running the instcat script as described in the KB has always fixed it. I've run into one today, however, where it doesn't seem to solve it, although the instcat script claims it ran successfully.
The 32-bit machine is at SP4. The instcat file has a date/time stamp of 5/23/2005 10:35m, 935kb.
Any thoughts?
December 17, 2008 at 4:04 pm
This reply has been reported for inappropriate content.
READ THIS RUN THIS It works !!!
Thanks Marek(Legend) Adamczuk
CodeOn:P
Glen (4/11/2008)
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1Excerpt from the article:
"When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
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
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
Regards,
Marek Adamczuk"
December 18, 2008 at 9:39 am
Thanks for chiming in, Malcolm. I'd seen that post too, and planned to manually add the SP if need be, BUT... since I've run the instcat script many times and its solved the problem successfully every time, and this time it didn't... couldn't help but wonder why.
December 18, 2008 at 3:18 pm
BTW I have actually rolled all our 2K server versions of instcat back to the original (SP4) and implemented this wrapper. This hasn't failed yet. But a word of caution, make sure you document this and place it in your Server build configuration documentation. Sorry for the Egg Sucking lesson 🙂 just thought I'd add it for completeness.
CodeOn
😛
June 1, 2009 at 10:04 am
When you issued statment to query 32 bit linked server from 64 bits server, the query is looking for stored procedure sp_tables_info_rowset_64 in 32 bit server and it doesn't exist.
What I have done is script the procedure from 64 bit and create it in 32 bit server
we have the linked server the other way. We have SQL Server 2005 64 bit as linked server in SQL Server 2000 32 bit? In this case do we have any issues?what are the changes do I need to do?
June 1, 2009 at 10:16 am
'C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql'.
I do not have the instcat.sql in the above path in sql sevrer 2005 64-bit.
what all I have is in attachent
July 20, 2009 at 1:03 am
Hi to All,
Its a very generous problem, which may have many solutions. But as we have witnessed so many user saying that they have tried everything.
What solved my problem is..
I upgraded sql server 2000 from sp2 to SP4 and if you already have sp4 on sql server 2000 then run Instcat.sql. As per my experience I can assure you this will work for sure, if you are exhausted with all the other workarounds.
Thanks,
Mithalesh
Mithalesh Gupta
mithaleshgupta@aol.com
8802955929
July 20, 2009 at 11:57 am
If you're running SQL Server 2000, you should be running SP4. (I apologize for leaving that out of my original post.) It never occurred to me that someone wouldn’t be running SP4. Live and learn...
September 21, 2009 at 9:12 am
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply