Error: Application can’t be connected via SQL linked server (linked server from SQL 2008\2005 to SQL2000)

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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s