Error: Application can’t be connected via SQL linked server

The linked server stops working after up gradation of platform to 64 bit SQL Server 2005\SQL2008 which is firing queries on a 32-Bit SQL Server 2000 (applicable on SQL Server 7.0 as well)

ApplicationName can’t be connected via SQL linked server: [xxx.xxx.xxx.xx].[xxxxx].dbo

Resolution:

If your application is having some constraint and you think that it is risky to execute the complete instcat.sql script which wills actually upgrading the system stored procedure in master databases. Hence instead of running the complete script you can execute below mentioned stored procedure and the same procedure is called by 64-bit servers when running remote queries.

Make sure your account to connect is setup in the Linked Server and has appropriate permission

Solution 1

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

After executing the above script on the SQL 2000 server in master database the linked server would start working.

Solution 2

Moreover the issue can also be resolved by applying SP4 on SQL 2000 server, this solution is also recommended because it will also patch SQL 2000 server to the latest release, however a downtime is required for this solution (but is the safest solution).