| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2008-06-25 : 12:49:14
|
| Hi,I'm trying to create a new View in SQL Server 2005, which is very simple: just list some fields from a View in another server-ABC2000 (SQL Server2000). After I created the View, if I click Verify SQL Syntax, I got this error message:The OLE DB provider "SQLNCLI" for linked server "ABC2000" reported an error. The provider did not give any information about the error.Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "ABC2000". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.If I use Query to read the View in the 2000 Server:Select * from ABC2000.testDB.dbo.Sampleview_vwI got the error too:Msg 7399, Level 16, State 1, Line 1The OLE DB provider "SQLNCLI" for linked server "ABC2000" reported an error. The provider did not give any information about the error.Msg 7312, Level 16, State 1, Line 1Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "ABC2000". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.But if I read a table from same server same database, Select * from ABC2000.testDB.dbo.SampleTable1There is no problem.Please give me any idea why I can't read/access Views in SQL Server2000 from SQL Server2005.Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-25 : 12:51:41
|
| Do you permission for the Views in SQL server 2000? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2008-06-25 : 14:42:24
|
| Thanks for the response,Yes, I set the permission for the View in SQL Server 2000 to me and the Administrator.For the Linked Server, under the Provider, I set SQLNCLI and SQLOLEDB as "Enable" to all Provider options in their properties, except "Disallow adhoc access". And the "Linked servers using this provider:" listed as "ABC2000".Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2008-06-25 : 15:38:54
|
| Thanks tkizer,Where to set the server type as "SQL Server"? in SQL Server 2005 or SQL server 2000? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2008-06-25 : 16:46:35
|
| Thanks Tara,Yes, I did select the SQL Server as Server type as I remember. But, I deleted the Linked Server and added back again with correct selection, and the result is the same. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-25 : 16:48:51
|
| Try removing the object owner/schema name and instead use ..:Select * from ABC2000.testDB..Sampleview_vwAlso try doing it within the view too.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2008-06-26 : 13:04:43
|
| Yes, I got the problem, it is SQLNCLI in Linked Servers under Provider. In the properties of SQLNCLI, the "Allow inprocess" must be checked to "Enable". Also the other Providers need to be set correctly, I don't know where I can find the document or links from Microsoft SQL Server 2005 Help.Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-28 : 00:45:32
|
| Not in books online? |
|
|
|
|
Can't read Views in SQL server 2000