January 3, 2007 at 9:03 am
I haven't come up with a solution, so I installed SQL 2005 on the same machine as SQL 2000 (32-bit) and linking works fine. This is not an adequate solution to the problem, but at least it is a workaround that I will use until our entire network is upgraded to SQL 2005.
Therefore, I am of the opinion that the problem has to do with the 64-bit version of SQL 2005.
January 3, 2007 at 9:54 am
Agreed. I have a very similar problem and am working with MS Product Support but no solution yet.
I wonder if your have same behavior. I can create the linked server (to 32-bit machine) and when I FIRST execute a query from the 64-bit server's console, then I can run queries (using linked server) from a client machine for about 30 minutes. If I do not run query from 64-bit console first, then query from client never works.
So far, MS says this looks like a Kerberos problem but no solution yet.
I am anxious to see if anyone else has worked through this problem or if you have similar behavior.
Charles
October 17, 2007 at 10:22 am
I am having the same problem. Has anyone found a solution yet?
October 24, 2007 at 3:03 am
I ran this on the 32-bit SQL Server 2000, and it appears to have resolved the issue.
Full credit must go to uber1024 (http://ncane.com/awh).
USE master
go
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int
select @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
go
GRANT EXECUTE ON dbo.sp_tables_info_rowset_64 TO [public]
GO
October 24, 2007 at 10:57 am
You were running the instcat.sql from the wrong place. You should have run it from SQL Server 2005 x64 server. we had the same problem initially, and once I ran instcat.sql from SQL Server 2005 x64, our linked servers worked fine.
😎
November 12, 2007 at 11:00 am
Did you run the stored procedure before or after running instcat.sql?
Thanks.
November 14, 2007 at 10:44 pm
I didn't have to run instcat.sql
January 17, 2008 at 4:36 am
Hi All
I came accross a similar problem on our systems this was corrected by manually adding in the sp_tables_info_rowset_64 stored procedure into the master database of the 32 bit SQL Server box
this allows the 64 bit 2005 box to the 32 bit 2000 Box
Hope this helps ...
Kind Regards
Brian
January 17, 2008 at 4:39 am
Hi All forgot to add text for creating procedure on 2000 box is as follows
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
Regards
Brian
January 17, 2008 at 4:59 am
Brian
I posted that as a solution in October already.
Randolph
January 17, 2008 at 6:12 am
Hi Randolph
So you did sorry about that happy ...
Regards
Brian
April 24, 2009 at 12:46 pm
I'm having exactly the same issue linking 2005 to 2000 only difference is that my 2005 is 32bit and the error I get when running an OpenQuery is:
OLE DB provider "SQLNCLI" for linked server "myserver" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "myserver" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
I've been over all the settings numerous times and nothing works. ODBC works fine, but LinkedServer does not.
April 28, 2009 at 4:27 am
Hi Terry
Consider playing around with the Security page on the Linked Server properties, with Impersonation and security contexts.
Regards
Randolph
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply