SQL Anywhere .NET Data Provider SAConnection error when connecting to a utility_db

[#1]

I am currently investigating migrating from a SQL Anywhere 10 to SQL Anywhere 16 or 17 database. I have been testing my application on a SQL Anywhere 16 database using the new SQL Anywhere .Net Data provider for visual studio (iAnywhere.Data.SQLAnywhere.v3.5.dll v16.0.0.20433). The application uses a connection to the utility database to stop and start the main application database. When attempting to connect to the utility database (via SAConnection.Open()) the following error occurs: “Permission denied: you do not have permission to execute a statement of this type”. I am able to connect to the utility_db via Sybase central and a dbisql command but not via the .Net dll.

After a little investigation I have found that the SA connection class Open() method in the SQL Anywhere .Net Data provider attempts to run the command “SET TEMPORARY OPTION CHAINED = ON”, which is not permitted to be executed on a utility database. I am unable to report the incident as I am only a potential customer until the result of testing the migration. Has anyone else been able to connect to the utility database via the .Net data provider? Is there anyone from SAP to confirm if this is an issue or that I’m completely missing something?

I created a simple test application that threw the same error with the following code:

SAConnection conn = new SAConnection();
conn.ConnectionString = “ENG=data;DBN=utility_db;UID=dba;PWD=pwd”;
conn.Open();

Thanks for any help

[Former Member · Accepted solution]

Hi Adrian,

The fix Mirco mentioned prevents accessing the utility_db from the ADO.NET driver in 17, 16.0.0.1965+, and 12.0.1.4132+.

This has been fixed, and you should now be able to connect to the utility_db again in versions: 17.0.0.1218+, 16.0.0.2162+, 12.0.1.4299+.

This fix will probably be in 17 Service Pack 2

Thanks,

Mikel

[former_member207653]

There is the following note in the readme file:

================(Build #1965  - Engineering Case #766113)================

The ADO.NET provider would not have been able to roll back a transaction

if CHAINED option was OFF. This has ben fixed by setting CHAINED option to

ON after opening a database connection.

That behaviour is expected.

[Former Member]

Thanks for your response. That explains why the SAConnection.Open() sets the CHAINED option to ON after opening the database connection. As I am connecting to the utility database (utility_db), which I do not think supports executing the CHAINED statement (see http://dcx.sap.com/index.html#sqla170/en/html/815404366ce21014b496ac686acbb35b.html), my application throws an exception on connection. Does SQL Anywhere 17 support connecting to the utility database via the .net SAConnection?

Thanks