What options for a MS SQL Server Linked Server provider does the SA OLEDB provider support?

The document page on Setting up a Microsoft Linked Server using OLE DB does tell that one has to set the "AllowInProcess" option for the SQL Anywehre OLE DB provider in order to make queries run at all (which I stumbled over lately).

There are several other provider-wide options as well.

I'd like to know if there are general recommendations to set some of them. As this relies partly on the facilities of the provider, I hope that a general recommendation can be made here. My aim would be to specify at least those options that could lead to performance improvements over the default settings (which is "Off" in general).

The list for MS SQL Server 2008 R2 includes these options - and I have included a comment whether I guess this could/should be specified for the 12.0.1 OLE DB Provider.

EDIT: I've corrected my wrong suggestions based on Jack's guidance:

AllowInProcess - *("A Boolean value that specifies whether the OLE DB provider is instantiated as an in-process server.") - must be set, see above
DisallowAdHocAccess - ("A Boolean value that specifies whether SQL Server allows ad hoc functions with the OLE DB provider.") - ~~AFAIK this is primarily a security feature, so "it depends", as they say...~~ Should only be set if one wants to disallow OPENROWSET and OPENDATASOURCE functions - and it seems useful to allow these functions as they enable "full passthrough queries" against the SQL Anywhere database.
DynamicParameters - ("Setting this option allows SQL Server to execute parameterized queries against the OLE DB provider. The ability to execute parameterized queries against the OLE DB provider can yield better performance for certain queries.") - ~~is supported, methinks~~is supported by default based on the provider's SQL/ODBC conformance flags and need not be set
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.") - ~~should be set, methinks~~ should not be set, as it would generate wrong syntax
LevelZeroOnly - ("A Boolean value that specifies whether all OLE DB providers are supported or just those that are compliant with the level 0 OLE DB interface.") - ~~???~~ should not be set, as it would restrict capabilities of the provider
NestedQueries - ("Setting this option allows SQL Server to delegate certain queries to the provider that require nesting SELECT statements in the FROM clause.") - ~~can be set, methinks~~ is supported by default based on the provider's SQL/ODBC conformance flags and need not be set (and may have no effect at all for MS SQL)
NonTransactedUpdates - ("A Boolean value that specifies whether data updates with the OLE DB provider are logged and are recoverable.") - ~~???~~ should not be set, as it would restrict capabilities of the provider
SqlServerLike - ("A Boolean value that specifies whether the OLE DB provider supports the LIKE operator as it is implemented in SQL Server.") - ~~is supported, methinks~~ is supported by default based on the provider's SQL/ODBC conformance flags and need not be set (and may have no effect at all for MS SQL)

Any clarification is highly appreciated.

mssql remote-server

asked 17 Feb '12, 07:02

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 04 Apr '12, 03:53

Just to "finalize": Based on Jack's very helpful explanations, I simply have selected the AllowInProcess property and have left all other properties on their default values, so that's exactly as what is listed in the docs... another testimony of that classical "Watcom does the things the way they should be done" motto:)

(03 Apr '12, 15:31) Volker Barth

flat view

3 Answers:

active answers oldest answers newest answers popular answers

Most of these options are for SQL Server backwards compatibility and are ignored by SQL Server when communicating with the SQL Anywhere OLE DB provider. For example:

"Supports LIKE operator" - SQL Anywhere supports LIKE but don’t expect SQL Server to pass a LIKE operator to SQL Anywhere even when this option is selected.

"Nested queries" - SQL Anywhere supports nested queries but don’t expect SQL Server to pass a nested query to SQL Anywhere even when this option is selected.

Check out this page http://msdn.microsoft.com/en-us/library/ms178039.aspx. When you read this, you'll see the correspondence between the SQLPROP properties and many of the options that you can select. Note that the remark "OLE DB providers that support DBPROPVAL_SQL_ANSI92_ENTRY or DBPROPVAL_SQL_ODBC_CORE do not need any one of the SQLPROPSET_OPTHINTS properties" applies to the SQL Anywhere provider.

Do not select the "Index as access path" option. This option is not ignored but it generates invalid SQL Anywhere syntax.

I don't know whether the following have any affect but don't select them since they are meant to downgrade capabilities. - "Level zero only" option. SQL Server queries which interfaces are supported by the SQL Anywhere provider. - "Disallow ad hoc access" option. The provider supports OPENROWSET. - "Non transacted updates" option. The provider supports transactions.

permanent link

answered 22 Feb '12, 10:01

JBSchueler
3.3k●4●15●64
accept rate: 19%

flat view

Pursuant to the above, there are performance implications that you should consider. To illustrate, here are two queries that produce the same result set but with significant performance differences.

When you execute the query below using SQL Server Management Studio, the nested queries are executed on the SQL Server side. This means that all rows in all tables referenced in the query are prefetched by SQL Server before the query can be evaluated.

SELECT ID, LineID
FROM [SATEST12].[demo].[GROUPO].[SalesOrderItems]
WHERE ShipDate = ANY (
   SELECT OrderDate
   FROM [SATEST12].[demo].[GROUPO].[SalesOrders]
   WHERE FinancialCode IN (
      SELECT Code
      FROM [SATEST12].[demo].[GROUPO].[FinancialCodes]
      WHERE ( Description = 'Fees' ) ) )
ORDER BY ID, LineID;

When you execute the query below using SQL Server Management Studio, the nested queries are executed by the SQL Anywhere server and only the result set is returned. This yields a significant performance improvement.

SELECT * FROM OPENQUERY( SATEST12, 'SELECT ID, LineID
FROM [SalesOrderItems]
WHERE ShipDate = ANY (
   SELECT OrderDate
   FROM [SalesOrders]
   WHERE FinancialCode IN (
      SELECT Code
      FROM [FinancialCodes]
      WHERE ( Description = ''Fees'' ) ) )
ORDER BY ID, LineID;' );

Consider using OPENQUERY if your query uses LIKE, nested queries, and other result set reduction clauses. OPENQUERY queries are always executed in whole by the SQL Anywhere server.

permanent link

answered 22 Feb '12, 10:27

JBSchueler
3.3k●4●15●64
accept rate: 19%

edited 22 Feb '12, 10:59

Volker Barth
40.2k●361●550●822

So you are saying nested queries are always executed on SQL Server when using the four-part-syntax even if the property "NestedQueries" is set?

And that the advantage of OPENQUERY over the four-part-syntax is the effect that the whole query is sent to SQL Anywhere - allowing native SQL Anyhwere syntax (like KEY JOIN) that is not permitted on SQL Server?

That would seem similar to the difference between queries in SQL Anywhere against proxy tables vs. the FORWARD TO statement or the sp_forward_to_remote_server system procedure...

(22 Feb '12, 10:56) Volker Barth

Replies hidden

...if my understanding is correct, that is a very important difference and should be added to the docs, methinks...

(22 Feb '12, 11:02) Volker Barth

Yes, even if the property "NestedQueries" is set, it is my observation that SQL Server choses to handle the nested query itself. And yes to your OPENQUERY comment as well. OPENQUERY permits you to use SQL Anywhere syntax since the query is sent over to the SQL Anywhere server for execution.

(22 Feb '12, 11:06) JBSchueler

By the way, the "-zr SQL" option is a good friend when you are interested in seeing what SQL Server is sending to the SQL Anywhere server via the OLE DB provider.

(22 Feb '12, 11:07) JBSchueler

Replies hidden

Thanks for the confirmation - and "-zr SQL" would then be the counterpart to "set cis_option = 7" for proxy tables, right?

BTW: From your deep insight, I could imagine that this invitational FAQ might relate to you - just in case...

(22 Feb '12, 11:14) Volker Barth

OK, I've updated my profile. A bit more revealed. How's that?

(22 Feb '12, 14:46) JBSchueler

If I was a bit more familiar with "set cis_option = 7", I just might agree :-)

(22 Feb '12, 14:47) JBSchueler

I feel very honoured!

FWIW, I noticed your personal greetings on a nice card I once got sent from Laura Nevin and the DCX team - that means a lot to me, so thank you very much:)

(23 Feb '12, 03:25) Volker Barth

showing 3 of 8 show all flat view

By the way, you mentioned the "Allow Inprocess" option but not some others. The options to set are documented. The other two important options to select are RPC and RPC OUT. These two options must be selected each time you create a Linked Server object.

permanent link

answered 22 Feb '12, 10:08

JBSchueler
3.3k●4●15●64
accept rate: 19%

Yes, I'm aware of those options, and that they have to be set, and that this is well documented.

The difference (at least for MS SQL 2005 and above) seems to be that these options can be set per Linked Server with sp_serveroption, whereas the properties mentioned in the question are set per provider with sp_MSset_oledb_prop.

(22 Feb '12, 10:37) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

remote-server ×56
mssql ×41

question asked: 17 Feb '12, 07:02

question was seen: 14,300 times

last updated: 04 Apr '12, 03:53

SAP SQL Anywhere

What options for a MS SQL Server Linked Server provider does the SA OLEDB provider support?

Follow this question

Related questions