some will work. Can anyone explain why the ones that don't work work on some
servers and not others? Can anyone give assurances that some constructs that
happen to work will continue to work, or will work on other servers? Is
there a standard way for SQL Server to query another SQL Server?
Most of these work on our development servers, only one works on the clients
test and live servers.
NOTE: Not using linked servers.
SELECT *
FROM OPENROWSET('sqloledb',
'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
CMSArchiveTraining.dbo.DisciplineVariances) dv
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'sqloledb' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
SELECT *
FROM OPENROWSET('sqloledb',
'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
dbo.DisciplineVariances) dv
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'sqloledb' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
SELECT *
FROM OPENROWSET('sqloledb',
'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
DisciplineVariances) dv
Server: Msg 7359, Level 16, State 1, Line 1
The OLE DB provider 'sqloledb' reported a schema version for table
'DisciplineVariances' that changed between compilation and execution.
OLE DB error trace [Non-interface error: Schema version number changed from
compile time (18446744073709551615) to run time (46155385833296703) for
ProviderName='sqloledb' TableName='DisciplineVariances'.].
SELECT *
FROM OPENROWSET('sqloledb',
'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
'SELECT * FROM CMSArchiveTraining.dbo.DisciplineVariances') dv
(806 row(s) affected)
SELECT *
FROM OPENDATASOURCE('sqloledb',
'Data Source=servertest;User
ID=FinanceArchiveTraining;Password=these
cretpassword'
).CMSArchiveTraining.dbo.DisciplineVariances dv
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'sqloledb' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
returned 0x80004005: The provider did not give any information about the
error.].
SELECT *
FROM OPENDATASOURCE('sqloledb',
'Data Source=servertest;User
ID=FinanceArchiveTraining;Password=these
cretpassword'
).dbo.DisciplineVariances dv
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'dbo.DisciplineVariances'.
SELECT *
FROM OPENDATASOURCE('sqloledb',
'Data Source=servertest;User
ID=FinanceArchiveTraining;Password=these
cretpassword'
).DisciplineVariances dv
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'DisciplineVariances'.
Note that not all fail the same way.You can get detail error if you turn on trace 7300.
e.g.
dbcc traceon(7300,3604,-1)
--dbcc traceoff(7300,3604,-1) --to turn off
-oj
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:Ov%23yCHVPGHA.2624@.TK2MSFTNGP12.phx.gbl...
> i'm trying to query across SQL Servers. i try random T-SQL constructs
> hoping some will work. Can anyone explain why the ones that don't work
> work on some servers and not others? Can anyone give assurances that some
> constructs that happen to work will continue to work, or will work on
> other servers? Is there a standard way for SQL Server to query another SQL
> Server?
> Most of these work on our development servers, only one works on the
> clients test and live servers.
> NOTE: Not using linked servers.
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> CMSArchiveTraining.dbo.DisciplineVariances) dv
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'sqloledb' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> dbo.DisciplineVariances) dv
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'sqloledb' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> DisciplineVariances) dv
> Server: Msg 7359, Level 16, State 1, Line 1
> The OLE DB provider 'sqloledb' reported a schema version for table
> 'DisciplineVariances' that changed between compilation and execution.
> OLE DB error trace [Non-interface error: Schema version number changed
> from compile time (18446744073709551615) to run time (46155385833296703)
> for ProviderName='sqloledb' TableName='DisciplineVariances'.].
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> 'SELECT * FROM CMSArchiveTraining.dbo.DisciplineVariances') dv
> (806 row(s) affected)
>
> SELECT *
> FROM OPENDATASOURCE('sqloledb',
> 'Data Source=servertest;User
> ID=FinanceArchiveTraining;Password=these
cretpassword'
> ).CMSArchiveTraining.dbo.DisciplineVariances dv
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'sqloledb' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>
> SELECT *
> FROM OPENDATASOURCE('sqloledb',
> 'Data Source=servertest;User
> ID=FinanceArchiveTraining;Password=these
cretpassword'
> ).dbo.DisciplineVariances dv
> Server: Msg 208, Level 16, State 1, Line 4
> Invalid object name 'dbo.DisciplineVariances'.
>
> SELECT *
> FROM OPENDATASOURCE('sqloledb',
> 'Data Source=servertest;User
> ID=FinanceArchiveTraining;Password=these
cretpassword'
> ).DisciplineVariances dv
> Server: Msg 208, Level 16, State 1, Line 4
> Invalid object name 'DisciplineVariances'.
>
> Note that not all fail the same way.
>|||i'm not sure what trace flags 7300 and 3604 are, since they are not
documented in the BOL.
But enabling these trace flags does not change any error messages.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23DFhTmXPGHA.3888@.TK2MSFTNGP12.phx.gbl...
> You can get detail error if you turn on trace 7300.
> e.g.
> dbcc traceon(7300,3604,-1)
> --dbcc traceoff(7300,3604,-1) --to turn off
>|||Ian,
Not all trace flags are documented in bol. Here are the kb that touch on
these 2 trace flags.
http://support.microsoft.com/kb/306212
http://support.microsoft.com/?id=280102
-oj
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:uU7fHjiPGHA.1132@.TK2MSFTNGP10.phx.gbl...
> i'm not sure what trace flags 7300 and 3604 are, since they are not
> documented in the BOL.
> But enabling these trace flags does not change any error messages.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23DFhTmXPGHA.3888@.TK2MSFTNGP12.phx.gbl...
>|||This seems to be a very common problem, that randomly begins happening, with
no solution:
http://sqlforums.windowsitpro.com/w...5&enterthread=y
2006: starlightpegeon had it
http://www.mcse.ms/archive85-2004-10-890567.html
2004: Andrew J. Kelly tried to help
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+not+give+
any+information
+about+the+error.&rnum=2&hl=en#e134528b9a47c7c8
2004: Poor Gabriele
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provide
r+did+not+give+
any+information+about+the+error.&rnum=4&hl=en#41642d09ca1d0ba0
2004: Yes MSDTC is running on both machines
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+di
d+not+give+any+
information+about+the+error.&rnum=5&hl=en#b0a3e8537195b166
2005: Set
HKLM/Software/Microsoft/MSSQLServer/Providers/SQLOLEDB/AllowInProcess to
Zero? Well, whatever.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did
+not+give+any+i
nformation+about+the+error.&rnum=6&hl=en#f63ad0126fe3b0ea
2002: Again with the allow in process to zero/unchecked. Well, i'm not
changing it until i get some documention on it.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+
not+give+any+in
formation+about+the+error.&rnum=7&hl=en#4f94912a08808686
2003: Poor Sachi
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+di
d+not+give+any+
information+about+the+error.&rnum=10&hl=en#7efc36e00261e514
2000: Michers was hoping it was bad logins. Yeah right.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+d
id+not+give+any
+information+about+the+error.&rnum=11&hl=en#eb751f9a00af6347
2003: Shawn Aebi from Microsoft admits is not one of their better error
message. He thinks it is most likely security releated.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+di
d+not+give+any+
information+about+the+error.&rnum=12&hl=en#30c009b4fcef204c
2003: Cindy Gross from Microsoft says turn off AllowInProcess, and reinstall
MDAC.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+di
d+not+give+any+
information+about+the+error.&rnum=13&hl=en#9f2be247391c2263
2002: Mischa still having problems.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provide
r+did+not+give+
any+information+about+the+error.&rnum=14&hl=en#a8ad503340f1a468
2003: Greg says make sure you spelled everything right.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+not+g
ive+any+informa
tion+about+the+error.&rnum=15&hl=en#8eeca281640d298c
2001: Narahari never got any responses
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provide
r+did+not+give+
any+information+about+the+error.&rnum=16&hl=en#2a1c33de0eda8aaf
2004: Either did Sam
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+di
d+not+give+any+
information+about+the+error.&rnum=17&hl=en#af60727738abb0c0
2004: Grabrile still having problems, who's last name is Pozzi
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+
not+give+any+in
formation+about+the+error.&rnum=18&hl=en#b587a7750400578b
2005: Rustom never got any responses
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provide
r+did+not+give+
any+information+about+the+error.&rnum=19&hl=en#682ddc6ecbc07986
2003: Neither did xj
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+di
d+not+give+any+
information+about+the+error.&rnum=20&hl=en#fce8c5d47989e828
2001: Mo Lin from Microsoft suggests hitting "Test" and turning on the trace
flags
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did
+not+give+any+i
nformation+about+the+error.&rnum=21&hl=en#7fdd79fadea413a1
2000: Poor Steve, never even got a response. i feel for ya, man.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+not+g
ive+any+informa
tion+about+the+error.&rnum=22&hl=en#1d755e7f1d7f7e68
2000: Nor did William.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did
+not+give+any+i
nformation+about+the+error.&rnum=23&hl=en#b5aa4ab178b4c352
2002: It was working for ayu, but suddenly stopped working. Nice.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did
+not+give+any+i
nformation+about+the+error.&rnum=24&hl=en#ea89dd3030a34827
2000: William trying again.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did
+not+give+any+i
nformation+about+the+error.&rnum=25&hl=en#aa4323787db5a391
2001: Hey,
. Guy named oj trying to help Narahari. Doesn't sound like itever worked.
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+not
+give+any+infor
mation+about+the+error.&rnum=27&hl=en#8e88a29e8fe052b1
2003: Anyone read spanish?
http://groups.google.com/group/micr...LE+DB+provider+'sqloledb'+reported+an+error.+The+provider+did+not+give+
any+information
+about+the+error.&rnum=28&hl=en#4d555012c84e2320
2000: i definetly don't speak this language.
So, the ideas so far are:
reinstall MDAC
make sure both sql servers are the same service pack level
make sure you have no typos
turn on trace flags to get more info on the error
and turn off the undocumented "AllowInProcess" flag.
make sure MSDTC is running on both machines|||SOLUTION
Tturn off "LevelZeroOnly" and, in my case, "IndexAsAccessPath"
BACKGROUND
i was investigating the meaning of "AllowInProcess" option for the SQLOLEDB
provider, that is supposedly maintained under
HKLM\Software\Microsoft\MSSQLServer\Prov
iders\SQLOLEDB\AllowInProcess
i finally found some Microsoft documentation on the option in:
SQL Server 7.0 Administrator's Companion
Chapter 4- Managing Servers
Configuring OLE DB Providers for Distributed Queries
http://www.microsoft.com/technet/pr...04.msp
x
The content of which i will reproduce here (as i am sure eventually
Microsoft will hide it's documentation):
<quote>
Provider Options
The OLE DB provider options for managing distributed queries are set using
SQL Server Enterprise Manager. In the left pane of SQL Server Enterprise
Manager, right-click a linked server definition that uses the OLE DB
provider for which you want to set the properties. On the General tab, click
Options..., and then set the properties.
DynamicParameters
If nonzero, indicates that the provider allows '?' parameter marker syntax
for parameterized queries. This option should be set only if the provider
supports the ICommandWithParameters interface, and supports a '?' as the
parameter marker. Setting this option allows SQL Server to execute
parameterized queries against the provider. The ability to execute
parameterized queries against the provider can result in better performance
for certain queries.
NestedQueries
If nonzero, indicates that the provider allows nested SELECT statements in
the FROM clause. Setting this option would allow SQL Server to delegate
certain queries to the provider that require nesting SELECT statements in
the FROM clause.
LevelZeroOnly
If nonzero, only level 0 OLE DB interfaces are invoked against the provider.
AllowInProcess
If nonzero, SQL Server allows the provider to be instantiated as an
in-process server. When this option is not set in the registry, the default
behavior is to instantiate the provider outside the SQL Server process.
Instantiating the provider outside the SQL Server process protects the SQL
Server process from errors in the provider. When the provider is
instantiated outside the SQL Server process, updates or inserts referencing
long columns (text, ntext, or image) are not allowed.
NonTransactedUpdates
If nonzero, SQL Server allows updates, even if ITransactionLocal is not
available. Note that if this option is turned on, updates against the
provider are not recoverable, since the provider does not support
transactions.
IndexAsAccessPath
If nonzero, SQL Server attempts to use indexes of the provider to fetch data
(by default, indexes are used only for metadata and are never opened).
DisallowAdhocAccess
If a nonzero value is set, SQL Server does not allow ad hoc access through
the OpenRowset() function against the OLE DB provider. When this option is
not set, the default behavior is to allow OpenRowset().
</quote>
Note: The way to access these options from Enterprise Manager is to go to
Security->Linked Server. Right-click to create a "New Linked Server." In
the Provider Name combo box, select "Microsoft OLE DB Provider for SQL
Server", and now you are allowed to click "Provider Options". These provider
options apply to all linked servers that use this provider and is
server-wide, and they don't apply for just the linked server you are
creating. You don't have to actually ever save this newly created linked
server, you only need to get here so you can edit the SQLOLEDB provider
options.
The option everyone talks about "AllowInProcess" determines whether or not,
as a security feature, to create the object "in process" or "out of
process." Out of process is safer, but you apparently cannot access any
blob fields (text, ntext or image). So people suggest that you allow in
process, which means that if you remote table contains blobs you can access
it. This was not my problem, but could be for you.
The option that helped me was "LevelZeroOnly". i do not know what "Level 0
OLE DB" interfaces are, and why i would only want to access them, but
turning off the option gave me a new error:
SELECT *
FROM OPENROWSET('sqloledb',
'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
CMSArchiveTraining.dbo.DisciplineVariances) dv
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'sqloledb' returned a 'NON-CLUSTERED and NOT INTEGRATED'
index 'IX_DisciplineVariances_DisplayCode' with incorrect bookmark ordinal
0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an
invalid bookmark ordinal from the index rowset.].
Now there is some error about indexes. i recognized an option from the above
table that talked about indexes: "IndexAsAccessPath." i guess the option
is whether or not SQL Server will try to use indexes on the linked server to
cover a query, rather than just using the indexes for information. The
default setting is to use indexes as a data access path. i turn the option
off and get:
SELECT *
FROM OPENROWSET('sqloledb',
'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
CMSArchiveTraining.dbo.DisciplineVariances) dv
(806 row(s) affected)
Excellent.
SOLUTION
Turn off the SQLOLEDB provider options
LevelZeroOnly
IndexAsAccessPath
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> CMSArchiveTraining.dbo.DisciplineVariances) dv
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'sqloledb' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> dbo.DisciplineVariances) dv
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'sqloledb' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> DisciplineVariances) dv
> Server: Msg 7359, Level 16, State 1, Line 1
> The OLE DB provider 'sqloledb' reported a schema version for table
> 'DisciplineVariances' that changed between compilation and execution.
> OLE DB error trace [Non-interface error: Schema version number changed
> from compile time (18446744073709551615) to run time (46155385833296703)
> for ProviderName='sqloledb' TableName='DisciplineVariances'.].
>
> SELECT *
> FROM OPENROWSET('sqloledb',
> 'servertest';'FinanceArchiveTraining';'t
hesecretpassword',
> 'SELECT * FROM CMSArchiveTraining.dbo.DisciplineVariances') dv
> (806 row(s) affected)
>
> SELECT *
> FROM OPENDATASOURCE('sqloledb',
> 'Data Source=servertest;User
> ID=FinanceArchiveTraining;Password=these
cretpassword'
> ).CMSArchiveTraining.dbo.DisciplineVariances dv
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'sqloledb' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'sqloledb' IUnknown::QueryInterface
> returned 0x80004005: The provider did not give any information about the
> error.].
>
> SELECT *
> FROM OPENDATASOURCE('sqloledb',
> 'Data Source=servertest;User
> ID=FinanceArchiveTraining;Password=these
cretpassword'
> ).dbo.DisciplineVariances dv
> Server: Msg 208, Level 16, State 1, Line 4
> Invalid object name 'dbo.DisciplineVariances'.
>
> SELECT *
> FROM OPENDATASOURCE('sqloledb',
> 'Data Source=servertest;User
> ID=FinanceArchiveTraining;Password=these
cretpassword'
> ).DisciplineVariances dv
> Server: Msg 208, Level 16, State 1, Line 4
> Invalid object name 'DisciplineVariances'.
>
> Note that not all fail the same way.
>|||Some information on "Index as Access Path".
From:
http://www.developmentnow.com/g/118...ion.htm
Rand Boyd (rboyd@.onlinemicrosoft.com) says:
"The Index as Access Path is used for providers that support index s
s.Most providers do not."
From:
http://msdn.microsoft.com/library/d...r />
_8rcj.asp
"option should be set only if the data source is on the same computer as SQL
Server"
SQL Server 2000
\SDK Documentation
\Access and Changing Relational Data
\Distributed Queries
\Keyset-Driven Cursors Requirements for OLE DB Providers
Keyset-Driven Cursors Requirements for OLE DB Providers
Transact-SQL keyset-driven cursors can reference remote tables only if the
following conditions are met:
The distributed query must meet the requirements for SELECT statements used
in a DECLARE CURSOR statement that declares the keyset-driven cursor. For
more information about the Transact-SQL conditions for keyset-driven cursor
support, see DECLARE CURSOR.
All local tables in the query must have a unique index. The index of the
remote table should be exposed through the INDEXES rowset of the
IDBSchemaRowset interface.
Index Requirements on OLE DB Providers
SQL Server can use indexes on tables from an OLE DB provider to evaluate
certain queries. For this, the provider should expose OLE DB interfaces that
allow scanning an index rowset and s
into the base table rowset usingbookmarks obtained from the index rowset.
Using the OLE DB provider's indexes has performance benefits only when the
index and table rowsets are on the same computer as the instance of
Microsoft SQL ServerT. Thus, the Index AS Access Path option should be set
only if the data source is on the same computer as SQL Server.
SQL Server can use an OLE DB provider's indexes only if the following
conditions are met:
The provider must support the IDBSchemaRowset interface with the TABLES,
COLUMNS, and INDEXES schema rowsets.
The provider must support opening a rowset on an index through IOpenRowset
by specifying the index name and the corresponding base table name.
The Index object must support all its mandatory interfaces: IRowset,
IRowsetIndex, IAccessor, IColumnsInfo, IRowsetInfo, and IConvertTypes.
Rowsets opened against the indexed base table (through IOpenRowset) must
support the IRowsetLocate interface for positioning on a row based off a
bookmark.
If the OLE DB provider meets these requirements, the SQL Server
administrator can set the Index As Access Path provider option to enable SQL
Server to use the provider's indexes to evaluate the queries. By default,
SQL Server does not attempt to use the provider's indexes unless this option
is set.
Updatable Keyset Cursor Requirements
A remote table can be updated or deleted through a keyset cursor defined on
a distributed query. For example:
UPDATE | DELETE remote_table WHERE CURRENT OF cursor_name.
Here are the conditions under which updatable cursors against distributed
queries are allowed:
The provider should meet the conditions for updates and deletes on the
remote table. For more information, see UPDATE and DELETE Requirements for
OLE DB Providers.
All the cursor operations must be in an explicit user transaction (or
multi-statement transaction) with read-repeatable isolation level or
serializable isolation level.
The provider must support distributed transactions with the ITransactionJoin
interface.|||About "Level Zero"...
From http://msdn2.microsoft.com/en-us/library/h63swas7.aspx
MSDN
Development Tools and Languages
Visual Studio
Visual C++
Programming Guide
General Concepts
Data Access
OLE DB Programming
OLE DB Provider Templates
<quote>
OLE DB Specification Level Support
The OLE DB provider templates support the OLE DB version 2.7 specification.
Using the OLE DB provider templates, you can implement a level 0 compliant
provider. (For an explanation of level 0, see "Minimum Provider
Functionality" in "OLE DB Leveling: Choosing the Right Interfaces" at
http://www.microsoft.com/data/oledb...bleveling2.htm.) The
Provider sample, for example, uses the templates to implement a non-SQL
(MSDOS) command server that executes the DOS DIR command to query the file
system. The Provider sample returns the directory information in a rowset,
which is the standard OLE DB mechanism for returning tabular data.
</quote>
Of course the URL specified no longer exists. But here is an archived copy
of from the wayback machine:
http://web.archive.org/web/20021203...dbleveling2.htm
i realize all the formatting of tables is broken, but i think having the
information (even garbled) is better than not having it at all.
i know google will keep this information archived, it would be nice if
Microsoft would do the same.
<quote>
Last Updated: May 4, 1998
OLE DB Leveling: Choosing the Right Interfaces
Download the printable compressed document file: oledbleveling2.zip (13K)
http://www.microsoft.com/data/oledb...dbleveling2.zip
Abstract
OLE DB defines a comprehensive set of interfaces for accessing a diverse
range of data types, located in a variety of data stores. The OLE DB
Programmer's Reference can be intimidating in size, but no one data store is
expected to support all of the functionality defined in the reference. OLE
DB data providers only expose the interfaces that reflect the natural
functionality of their data store. Providers all support a base level of
functionality. Above that base level, common service components provide
interoperability through generic implementations of extensions such as
scrolling, or predicate-based positioning if the provider doesn't support
them.
Defining a minimal level of support exposed by all data stores, along with a
common set of extended services, means:
- OLE DB data providers are easier to write. They only have to expose the
subset of interfaces that reflect native functionality, resulting in the
availability of more data available through OLE DB.
- Applications that consume OLE DB, directly or through ADO, can assume a
rich level of functionality because service components provide extended
functionality where not supported by the data store.
Introduction: OLE DB as a Component Technology
To meet its goal of providing data access to all types of data in a COM
(Component Object Model) environment, OLE DB is designed as a component
technology. In OLE DB, data stores expose the interfaces that reflect their
native functionality. Common components can be built on top of those
interfaces to expose more robust data models. To define a component
architecture, OLE DB identifies common characteristics between different
data providers and services, and defines common interfaces to expose those
characteristics. So, for example, while a rowset may be obtained through a
number of very different mechanisms, the end result is still a rowset, with
well-defined interfaces, methods and characteristics. With OLE DB,
navigating the result of a complex multitable join is no different than
navigating the result of a text file containing tabular data. Defining
common interfaces in this manner allows components to more efficiently
augment the individual data provider's native functionality.
Once the base functionality is defined, the next step is to view the
additional functionality as incremental additions to this base
functionality. Thus, the more sophisticated providers can expose these
advanced features in addition to the base level interfaces. Furthermore,
individual service components can be built to implement these features on
top of the simpler providers.
Consumers
Developers writing OLE DB consumers can choose their level of
interoperability with OLE DB providers. Consumers may be written to consume
a specific provider, in which case they are designed to be aware of the
functionality of the provider. Or they may be written to consume generic
providers. In order to consume generic providers, the consumer may do one of
the following:
- Consume a minimum set of functionality and work with all OLE DB providers.
- Consume a higher level of functionality and query the provider for support
of extended functionality.
- Consume a higher level of functionality and invoke service components to
implement missing functionality, where such service components are
available.
Base Consumer Functionality
Table 1 describes the minimum level of functionality that a consumer can
expect to be supported when talking to any OLE DB provider. The table is
broken into three columns:
- Object Lists the OLE DB object that supports the interface or behavior.
- Base Interfaces Lists the functionality available over any OLE DB
provider.
- Updatable Lists the minimum functionality available over any OLE DB
provider that supports updating data. Providers not supporting this complete
set of functionality are considered read-only providers.
Table 1 Base Consumer Interfaces
Object Base interfaces Updatable
Root Enumerator
IDBInitialize (1)
IParseDisplayName (1)
ISourcesRowset (1)
Data Links
IDataInitialize (1)
IPromptInitialize (1)
RowPosition
IRowPosition (1)
DataSource
IDBCreateSession
IDBInitialize
IDBProperties
IPersist
Session
IGetDataSource
IOpenRowset
ISessionProperties
Rowset
IAccessor
IRowsetChange
IColumnsInfo
IConvertType
IRowset
IRowsetFind
IRowsetIdentity
IRowsetInfo
IRowsetLocate
IRowsetScroll
Rowset Behavior
DBPROP_ CANHOLDROWS
DBPROP_ REMOVEDELETED
DBPROP_ CANFETCHBACKWARDS
DBPROP_ OWNUPDATEDELETE
DBPROP_ CANSCROLLBACKWARDS
DBPROP_ OWNINSERT
(1)These interfaces are always supported by common components in the SDK.
They are never implemented directly by providers.
Consumers are guaranteed to get the above functionality in one of three
ways:
- Data providers written in Visual Basic, Visual J++, C, or C++ by using the
OLE DB Simple Provider (OSP) Toolkit automatically support all of the above
functionality and more.
- Data providers natively written in C or C++ can expose the full set of
interfaces listed in Table 1.
- Data providers natively written in C or C++ can implement the minimal
provider functionality described in the section "Minimum Provider
Functionality" in this paper. They rely upon service components to implement
the additional functionality required for the base consumer functionality.
Providers
An OLE DB provider exposes OLE DB interfaces over some type of data. OLE DB
providers include everything from a full SQL DBMS to a text file or data
stream. Obviously these data providers have different functionality, and
it's important not to limit that functionality. But at the same time it's
not reasonable to expect all providers that expose simple tabular data to
implement a full-blown query engine as well.
Providers support the native functionality of the data that they expose.
This includes at least the functionality described in the section "Minimum
Provider Functionality" in this paper. Additional interfaces should be
implemented as appropriate. If the provider natively supports all of the
functionality listed in the section "Base Consumer Functionality," then
service components won't be needed to provide the minimal consumer
functionality. In addition, providers may expose interfaces for the extended
functionality described in the section "Extended Interfaces."
All providers must be either apartment, rental, or free threaded, and must
support aggregation of the DataSource, Session, and Rowset objects. If
applicable, providers must support the aggregation of the Command and View
objects.
Providers must support data conversions to the types returned in
IColumnsInfo::GetColumnsInfo or IColumnsRowset::GetColumnsRowset for rowset
columns, and ICommandWithParameters::GetParameterInfo
for parameters, as
well as DBTYPE_WSTR for all column or parameter values other than those
described as objects. Providers that support binding to objects as IStream
or ILockBytes must also support binding to those columns as
ISequentialStream.
Minimum Provider Functionality
It is important for provider writers to implement the full set of interfaces
that apply to their particular type of data. At a minimum, the provider must
implement the interfaces and behavior listed in Table 2 to be considered a
generic OLE DB provider. Providers implementing the minimum provider
functionality can rely on common service components available in the SDK to
implement the base consumer functionality.
Table 2 Minimum Provider Interfaces
Object
Any OLE DB provider
Updatable providers
DataSource
IDBCreateSession
IDBInitialize
IDBProperties
IPersist
Session
IGetDataSource
IOpenRowset
ISessionProperties
Rowset
IAccessor
IConnectionPointContainer for IRowsetNotify (1)
IConvertType
IRowsetChange
IColumnsInfo
IRowset
IRowsetIdentity
IRowsetInfo
Rowset Behavior
DBPROP_CANHOLDROWS
DBPROP_ OWNUPDATEDELETE
DBPROP_OWNINSERT
DBPROP_ REMOVEDELETED
(1) Supporting IConnectionPointContainer for IRowsetNotify is not strictly
required for OLE DB 2.0 providers to satisfy the minimum requirements for an
updatable provider. However, updatable providers are strongly encouraged to
expose this connection point as some consumers will be forced to treat
providers as read-only if they do not support rowset notifications by
exposing the connection point for IRowsetNotify.
Supporting an interface means supporting all methods within that interface.
No methods return E_NOTIMPL.
Supporting a property means supporting the setting of and associated
behavior of all possible values for a property.
Supporting the required rowset properties means that setting any combination
of the required properties must yield a rowset that reflects at least those
properties. It does not mean that those properties are always true for any
rowset if the property has not been requested by the user.
Base Providers
Providers that implement the full set of base interfaces can be consumed by
general consumers without the support of additional service components.
Providers implementing the functionality described in Table 3 in addition to
the minimum provider functionality are consumed as base providers.
Table 3 Full Interfaces for Base Providers
Object
Any base provider
Updatable providers
Rowset
IRowsetFind
IRowsetLocate
IRowsetScroll
Rowset Behavior
DBPROP_ CANSCROLLBACKWARDS (1)
DBPROP_ CANFETCHBACKWARDS (1)
(1) Providers that support the property DBPROP_CANSCROLLBACKWARDS must also
support the property DBPROP_CANFETCHBACKWARDS.
Extended Interfaces
General purpose providers may support additional functionality. It is
advantageous that providers support as many extended interfaces as apply to
their particular type of data.
In addition to common interface extensions, providers may expose specialized
interfaces for the following sets of extended functionality:
- Rowset processing Exposed by providers that support native filter or
sort capabilities.
- Commands Exposed by providers that can execute more sophisticated
queries or statements.
- Transactions Exposed by providers that can support transactional
capabilities.
- Index navigation Exposed by providers built on indexed data to expose
index functionality to service components such as query processors. These
interfaces are generally not directly consumed by OLE DB consumers other
than service components.
Table 4 Extended Interfaces
Object
Any provider
Updatable providers
Transacted providers
DataSource
IConnectionPointContainer for IDBAsynchNotify
IDBAsynchStatus
IDataSourceAdmin
IDBInfo2
IPersistFile
ISupportErrorInfo
Session
IAlterIndex
ITransactionLocal
IAlterTable
ITransactionJoin
IDBSchemaRowset
ITransactionObject
IDBView (1)
IDBCreateCommand (2)
IIndexDefinition
ITableDefinition
ISupportErrorInfo
View (1)
IViewFilter
IViewSort
IColumnsInfo
IAccessor
ISupportErrorInfo
IViewRowset
IViewChapter
Command (2)
IAccessor
ICommand
ICommandPersist
ICommandText
IColumnsInfo
ICommandPrepare
IColumnsRowset
ICommandProperties
ICommandWithParameters
IConvertType
ISupportErrorInfo
Command Behavior
MAXTABLESINSELECT>1
Custom Error
ISQLErrorInfo
Multiple Results
IMultipleResults
Rowset
IConnectionPointContainer for IRowsetNotify,
IDBAsynchNotifyIRowsetResynch
IColumnsRowset
IChapteredRowset (1)
IDBAsynchStatus
IRowsetUpdate
IRowsetChapterMember
IRowsetIdentity
IRowsetLocate
IRowsetView (1)
IRowsetIndex (3)
IRowsetIndex (3)
ISupportErrorInfo
Rowset Behavior
DBPROP_ LITERALBOOKMARKS
DBPROP_CHANGE INSERTEDROWS
DBPROP_ COMMITPRESERVE
DBPROP_ ORDEREDBOOKMARKS
DBPROP_RETURN PENDINGROWS
DBPROP_ ABORTPRESERVE
DBPROP_ LITERALIDENTITY
DBPROP_ IMMOBILEROWS
DBPROP_ BOOKMARKSKIPPED
DBPROP_OTHERINSERT
DBPROP_ OTHERUPDATEDELETE
DBPROP_ STRONGIDENTITY
BLOB Support
DBPROP_OLEOBJECTS
DBPROP_MULTIPLE STORAGEOBJECTS
DBPROP_STRUCTURED STORAGE
ISequentialStream
IStream
IStorage
ILockBytes
DBPROP_BLOCKING STORAGEOBJECTS=FALSE
(1) Extended functionality that may be exposed by providers which support
rowset processing.
(2) Extended functionality that may be exposed by providers which support
commands.
(3) Extended functionality that may be exposed by providers which support
index navigation.
Generic consumers must be prepared for providers that don't support the
extended interfaces. Consumers can handle such providers in several
different ways:
- The consumer can invoke common services to implement extended
functionality, where such service components are available.
- The consumer can implement extra code within the application to compensate
for missing functionality.
- The consumer can reduce the functionality available to the user based on
missing functionality in the provider.
- The consumer can return an error indicating that the provider is not
capable of supporting required functionality.
2002 Microsoft Corporation. All rights reserved. Terms of use.
</quote>
So, to answer the question, level 0 functionality is a basic set of
interfaces that the OLE DB provider COM object must implement. Asking for
more than level zero is guess is trusting that the provider writers are good
programmers. In my case, i seem to require some extended interfaces of the
SQLOLEDB provider. Which is scary, since i can't know that Microsoft fully
supports all the optional interfaces on their OLE DB provider for SQL
Server. i don't know what interface SQL Server is requiring from the
SQLOLEDB provider, but whatever it is, it is making my distributed query
work.
No comments:
Post a Comment