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

Hello,

I’ve just had the same problem. I was trying to access a SQL2K from a SQL2K5 64bits server. Although I had it a as a linked server, I had the same error.

Reading information, I found the patch to solve this problem here

http://support.microsoft.com/kb/906954

But, maybe if you don’t have the SQL2K server under your control, you won’t be able to execute the patch.

There is another way to make this, using OPENQUERY

select * from openquery([RemoteServer],'select * from DB.dbo.table')

And you won’t have the error. I tried it before install the patch and it worked for me.

Krgrds

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

5 clients takes 5 times more

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Rui Dias VD wrote:

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Sorry, forgot to tell:

I'm using SQL Server 2005 and Visual Studio 2005

|||

You should never write this type of loop. If you want to map one Mesas element to one row, please use the nodes() method as in:

SELECT

d.value('(CodMesa)[1]','integer') Chave,

d.value('(Designacao)[1]','varchar(255)') Designacao,

d.query('.') RegistoXML

FROM @.Documento.nodes('/ROOT/Mesas') as N(d)

(also please try to avoid // if you know the path).

Best regards

Michael