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
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
create procedure sp_tables_info_rowset_64
@table_schema sysname = null,
@table_type nvarchar(255) = null
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
After executing the above script on the SQL 2000 server in master database the linked server would start working.
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).