首頁 > 開發者 > SQL Server
那麼，你可以參考如下的這封email來解決你的問題。當然你可以參考：msdn介紹
Artek10-23-03, 10:51Hello,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 could 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: ].
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 with
your issue.
First of all, I would 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 could not be
performed as the OLE DB provider 'SQLOLEDB' was unable to begin a
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. Could 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 the
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. As
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:26Hello Billy,
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³ w
wiadomo¶ci news:WyeTtEfmDHA.1548@cpmsftngxa06.phx.gbl...
> Hi Artek,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> First of all, I would 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 could not be
> performed as the OLE DB provider 'SQLOLEDB' was unable to begin a
> 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. Could 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 the
> 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. As
> 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:34Dear 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 the
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 the
OPENQUERY succeeds.
As you could execute the four-part naming distributed query from 32bit
server to 64 bit server on the different machines, I suspect that the
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 could 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 includes the INSCAT.SQL.
Therefore, I strongly 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 installs catalog 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 should 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 of
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:37Hello 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 for
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:38Dear Artek,
I'm glad that the four-part naming distributed query works fine after you manually re-run the
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 assistance, 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.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
