SQL Server 64 bit linked server error with SQL Server 32 bit

If you establish a connection server with a 32-bit database server on a 64-bit computer and use the connection server for Distributed queries on 64-bit computers, the following error message is returned: Server: Message 7399 , Level 16 , Status 1 , Line 1
Ole db Provider ' Sqloledb ' Reported an error.
[ OLE/DB Provider returned message: unspecified error ]
[ OLE/DB Provider returned message: the stored procedure required to complete this operation cocould not be found on the server (they were supplied with the ODBC setup disk for the SQL Server Driver ). please contact your system administrator. ]
Ole db error trace [ OLE/DB Provider 'sqloledb' idbschemarowset: getrowset returned 0x80004005: ] .

Then, you can refer to the following email to solve your problem. Of course, you can refer to: msdn Introduction

Artek10-23-03, 10: 51 hello,
I 've just setup new HP Server with 4 itanium processors and installed a SQL
Server 200 64 bit developer version on it. Operating System is Windows 2003
Enterprice 64 bit. Ram 16 GB
I created a link server configuration to existing SQL Server 2000 32 bit
Running on Windows 2000 Advanced Server.

When I try to execute Distributed Query
Select * From server32.dbname. DBO. Table
I get Following Error

Server: MSG 7399, level 16, state 1, line 1
Ole db Provider 'sqloledb' reported an error.
[OLE/DB Provider returned message: unspecified error]
[OLE/DB Provider returned message: the stored procedure required to complete
This operation cocould not be found on the server (they were supplied with
ODBC setup disk for the SQL Server Driver). Please contact your system
Administrator.]
Ole db error trace [OLE/DB Provider 'sqloledb' idbschemarowset: getrowset
Returned 0x80004005:].

Funny thing is that when I use select * From openquery (...) It works
Can anyone help
If necessary, I can provide a SQL dump file (I run SQL with-y7399 option ).
Thanks in advance.
Artek

 

Billy Yao10-24-03, 01: 39hi Artek,

Thank you for using msdn newsgroup! It's my pleasure to assist you
Your issue.

First of all, I wowould like to confirm my understanding of your issue.

From your description, I understand that you executed the simple
Distributed Query from the 64-bit SQL Server to the 32-bit linked server,
But the Distributed Query failed and gave you the error message you posted
In newsgroup. Thank you for your detailed information! If there is
Anything I misunderstand you, please feel free to let me know.

From the error message, we can see that the Distributed Query cocould not be
Saved med as the ole db Provider 'sqloledb' was unable to begin
Distributed Transaction. This symptom may be caused as the 32 bit Server
Was unable to carry on with the stored procedure due to incomplete
Application of instcat. SQL, which is updated currently in the latest SQL
Server Service Pack 3 (SP3 ).

Looking through your post, however, I'm unsure of your service pack's (or
MDAC's) versions on both your SQL servers. cocould you check them on the two
Servers so that we can narrow down the problem?

Before providing you with a workaround Artek, I recommend you test
Distributed Query between the same bit servers if convenient. For example:
Link server between 32 bit with 32 bit or 64 bit with 64 bit. Then execute
The distributed query in this environment to see if it can be successful.
If it can, we can narrow down the problem accurately to different bit
Server issues, and not to other configuration and query issues etc.

Therefore, to workaround the Distributed Query between the different bit
Servers, you can re-run inscat. SQL manually or just by applying inscat. SQL
From SP3. however, I strongly recommend you apply sp3/sp3a because this
Latest version will automatically help you apply inscat. SQL and also update
Your MDAC which can support your ole db Provider gracefully. Please apply
SP3 on both servers (32 bit and 64 bit ).

Artek, please confirm my understanding to help narrow down the problem.
Per our effort, I hope the information will make things clear and help us
Move closer to finding the cause and resolution.

Please apply my suggestions and workaround above, and let me know if it
Helps you resolve your problem. If there is anything more I can assist you
With, please feel free to post it in the group with any updates and
Feedback regarding your experience on this specific issue.

Best regards,

Billy Yao
Microsoft online partner support
----------------------------------------------------
Get secure! -Www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

 

Artek10-24-03, 05: 26 Hello, Billy,
Thank you for your reply
With regards to my problem, yes you unerstood well.
This means that distributed query from sql64 does't work with sql32 (in
Oposite direction it works ).
I checked Distributed Query between the same bit servers it also works,
However on 64 bit I checked between two instances installed on the same
Computer (I don't have two separate itanium servers ).
I tested following deployments:
Sql32 with installed sp3a -- does't work
Sql32 with installed sp3a and MDAC 2.8 -- does't work

As far as sql64 bit is concerned, it is a developer edition. I din't find
Any information about sp3a for sql64 bit version.

Result from select @ version on sql64:

Microsoft SQL Server 2000-8.00.760 (Intel IA-64)
Feb 6 2003 16:07:24
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 (build 3790 :)

Result from select @ version on sql32:
Microsoft SQL Server 2000-8.00.760 (Intel x86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (build 2195: Service Pack 4)

Regards Artek

U ¿ytkownik "Billy Yao [MSFT]" <v-binyao@online.microsoft.com> napisa sans W
Wiadomo marketing CI news: WyeTtEfmDHA.1548@cpmsftngxa06.phx.gbl...
> Hi Artek,
> Thank you for using msdn newsgroup! It's my pleasure to assist you
> Your issue.
> First of all, I wowould like to confirm my understanding of your issue.
> From your description, I understand that you executed the simple
> Distributed query from the 64-bit SQL Server to the 32-bit linked server,
> But the Distributed Query failed and gave you the error message you posted
> In newsgroup. Thank you for your detailed information! If there is
> Anything I misunderstand you, please feel free to let me know.
> From the error message, we can see that the Distributed Query cocould not be
> Saved med as the ole db Provider 'sqloledb' was unable to begin
> Distributed transaction. This symptom may be caused as the 32-bit Server
> Was unable to carry on with the stored procedure due to incomplete
> Application of instcat. SQL, which is updated currently in the latest SQL
> Server Service Pack 3 (SP3 ).
> Looking through your post, however, I'm unsure of your service pack's (or
> MDAC's) versions on both your SQL servers. cocould you check them on the two
> Servers so that we can narrow down the problem?
> Before providing you with a workaround Artek, I recommend you test
> Distributed Query between the same bit servers if convenient.
Example:
> Link server between 32 bit with 32 bit or 64 bit with 64 bit. Then execute
> The distributed query in this environment to see if it can be successful.
> If it can, we can narrow down the problem accurately to different bit
> Server issues, and not to other configuration and query issues etc.
> Therefore, to workaround the Distributed Query between the different bit
> Servers, you can re-run inscat. SQL manually or just by applying inscat. SQL
> From SP3. however, I stronugly recommend you apply sp3/sp3a because this
> Latest version will automatically help you apply inscat. SQL and also
Update
> Your MDAC which can support your ole db Provider gracefully. Please apply
> SP3 on both servers (32 bit and 64 bit ).
> Artek, please confirm my understanding to help narrow down the problem.
> Per our effort, I hope the information will make things clear and help us
> Move closer to finding the cause and resolution.
> Please apply my suggestions and workaround above, and let me know if it
> Helps you resolve your problem. If there is anything more I can assist you
> With, please feel free to post it in the group with any updates and
> Feedback regarding your experience on this specific issue.
> Best regards,
> Billy Yao
> Microsoft online partner support
> ----------------------------------------------------
> Get secure! -Www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.

 

Billy Yao10-25-03, 03: 34 dear Artek,

Thank you for your updates and help confirm the problem!

I 'd greatly appreciated your cooperation with your further information and
Detailed testings. Thanks a lot!

Now it's clear to me that the Distributed Query with FOUT-PART naming
Convention on your side:
1. Works fine on 32 bit to32 bit instances on the different machines
2. Works fine on 32 bit to 64 bit instances on the different machines
3. Works fine on 64 bit to 64 bit different instances on the same machine.
4. fails on 64 bit to 32 bit instances on the different machines
Additionally, the distributed query with openquery works fine on all
Scenarios. Am I right Artek?

Looking through your information getting from "select @ version", I can see
Both servers have applied SP3 (8.00.760) and SP3 is OK for 64 bit server.

It seems all your environments are definitely current and the Service Packs
Are also the latest ones. This symptom makes me puzzled and there are few
Articles describing this four-part naming Distributed Query fails while
Openquery succeeds.

As you cocould execute the four-part naming distributed query from 32bit
Server to 64 bit server on the different machines, I suspect that
Problem is located on the side of the 32 bit server.

I noticed that the error message also reported: "The Stored Procedure
Required to complete this operation cocould not be found on the server ".
Based on my knowledge, this message is mostly related to inscat. SQL.

The only way I can explain is that the inscat. SQL is not applied properly
On the 32 bit server even though the server is updated to Service Pack 3,
Which indeed into des the inscat. SQL.

therefore, I stronugly recommend you manually execute the inscat. SQL in
query analyzer (QA) to see if the problem can be resolved. here I provide
you the detailed steps to apply the inscat. SQL:
Find the inscat. SQL in c: \ windows \ system32 on your 32 bit server's computer.
execute the inscat. SQL in QA to installcatalog stored procedures on the
32 bit server.
3. stop and restart the SQL server services to see if the four-part naming
Distributed Query works fine.
4. if it still doesn' t work, please apply the inscat. SQL on the 64 bit
server with the similar steps 1, 2, 3 mentioned above. (Note: The
inscat. SQL shocould be on your 64 bit server's computer)

Last but not the least, I noticed that your SQL Server's version is
8.00.760. I recommend you to apply MS03-031 to update the SQL server's
Version from 8.00.760 to 8.00.0818 for remote queries and security issues.
This can be helpful for you if you are using Named Pipes service or are
Exposed to the Internet.

MS03-031 (SQL Server 32 bit)

Http://www.microsoft.com/downloads/details.aspx? Displaylang = en & familyid = 9814

AE9D-BD44-40C5-ADD3-B8C99618E68D

MS03-031 (SQL Server 64 bit)

Http://www.microsoft.com/downloads/details.aspx? Familyid = 72336508-057a-4e86-

8f2e-cb1bd3a6a44b & displaylang = en

Artek, I wonder if you have any concerns about using openquery instead
The four-part naming distributed query in case the latter one will still
Fail. If you do have, please feel free to let me know.

Please apply my suggestions above and let me know if it help you resolve
The problem. If there is anything more I can assist you with, please post
It in the group.

Best regards,

Billy Yao
Microsoft online partner support
----------------------------------------------------
Get secure! -Www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

 

Artek10-27-03, 07: 37 Hello Billy,

Thank you very much Billy for your help. Once I installed inscat. SQL on
32bit server, four-part naming started to work.
When I run inscat. SQL from QA everything was OK beside a few messages which
Appeared:

Creating sp_ddopen
Cannot Add rows to sysdepends for the current stored procedure because it
Depends on the missing object 'SP _ ddopen '. The stored procedure will still
Be created.
Cannot Add rows to sysdepends for the current stored procedure because it
Depends on the missing object 'SP _ ddopen '. The stored procedure will still
Be created.
...

I saw a comment in the script and there was written that sp_ddopen was
Pre 6.5 SQL. Because I don't use ealier version of SQL than 2000, I think
That these messages were not danger for my server. Am I right?

Regards Artek.

 

Billy Yao10-27-03, 09: 38 dear Artek,

I'm gglad that the four-part naming Distributed Query works fine after you manually re-run
Instcat. SQL and re-created all the needed stored procedure used by distributed queries.

You are right. The Stored Procedure sp_ddopen is for pre 6.5 SQL version. SQL Server 7.0 or
2000 will not use this stored procedure even though it was created. So it will not do harms
To your server. Don't worry!

Artek, please reply directly to the thread with any updates and your feedback regarding your
Experience on this specific issue. If you 'd like further balance ance, please feel free to let me
Know.

Best regards,

Billy Yao
Microsoft online partner support
----------------------------------------------------
Get secure! -Www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.