64 Bit SQL Server try to access 32-Bit

Hi All,

I have a scenario where today we are migrating the existing code to new production server which is 64 Bit server.

The SSIS packages are put on new server and we are trying to run.

I tried to run a simple select query to fetch data from a 32bit remote server from cmd prompt usings a .sql file and it gives me error.

But this is the known issue of

‘a 64-bit SQL server trying to communicate to a 32-bit sql server’ and the error message is

The error message is similar to the following:

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 "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used

I saw the help for this in support.microsoft and it is suggested the resolution is

‘To resolve this problem, install SQL Server 2000 SP3 or SQL Server 2000 SP4 on the 32-bit SQL Server 2000 server. Then, manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server.’

I cannot do this as the remote server which am trying to access is not under my control and other issues.

Please suggest me what is the work around and what modifications do I need to do for my SSIS packages?

Help much appreciated.

Thanks and Regards

Sai

Sai,

You will need to contact the system administrator for the remote server and convince them to run the instcat.sql. I had exactly the same issue at our site, and this resolved the issue. Assuming the remote system is running SQL Server 2000 with at least SP3, there is no installation required, as the instcat.sql script will already exist on the machine. In fact, it isn't even necessary to get a console login to the box as the KB article suggests; the script can be run via Query Analyzer from a remote location. You will find the script in the <installation folder>\MSSQL\Install directory.

The script updates some of the Microsoft-delivered system stored procedures to the SP3 or SP4 version. It should not otherwise impact the normal functioning of any user databases or user stored procedures; at least, it caused no ill effects at our site, and it DID solve the problem. Perhaps you can use this information to help convince the other sysadmin of the benefit of executing the script.

Best regards,

Matt