Sql anywhere -User DSN is not getting updated using windows script

[Privete_member__1042652]

screenshot-after-executing-the-script.jpgscript-snippet-sqlanywhere9.txtWe have a Power builder based product(Appeon Power Builder 21 – Pro – 32 bit). This product has a client DB hosted in SQL Anywhere 9. Both these components are deployed to windows server 2016 R2

Power builder UI makes use of ODBC to get connected to the SQL Anywhere 9. User DSN for this ODBC connection is set up by a windows script.

Recently we have done a POC to upgrade SQL Anywhere 9 to SAP SQL Anywhere 17. After this upgrade, User DSN set up via windows script stopped working.

We are able to set up ODBC manually. But it is not feasible since we have 500+ users which uses this ODC connection.

I have attached old windows odbc creation script(working version) and new odbc creation script(not working).

Only change in these scripts are the ODBC driver used for SAP SQL Anywhere 17.

We suspect that dbodbc17.dll(32 – bit) is not working with windows cmd script. We haven’t raised this ticket with windows since same windows script is able to create User DSN for SQL Anywhere 9 version.

We have attached issue replication screen shots and scripts. Please review and let us know about the fix.

1.

[chris_keating]

I do not think that regedit /s is permitted at this point. Use reg import instead. See

How to add, modify, or delete registry subkeys and values by using a .reg file (microsoft.com)

That said, I prefer to use the dbdsn utility for this purpose. The benefit of DBDSN is that as you move versions, you simply need to use the dbdsn in the SQL Anywhere version that you want to use. It handles the [ODBC Data Sources] and the Driver= entries in the registry and is not affected by changes in the registry tooling on Windows (at least to this date).

set DSN=Qz98
set AStop=Yes
set DBF=D:\\PB_Migration\\QUARTZBASE_PB21\\QUARTZBASE_PB10\\DB\\%USERNAME%\\qz98.db
set DBN=qz98
set DESC=qz98
set UID=dba
set PWD=sql

dbdsn -y -wu "Qz98" -c "UID=%UID%;PWD=%PWD%;DBN=%DBN%;DBF=%DBF%;ASTOP=%ASTOP%;Description=%DESC%"

And it can generated the dbdsn with options based on an existing DSN. For example

dbdsn -cm -gu Qz98

will output

dbdsn -y -wu "Qz98" -c "UID=dba;PWD=sql;DBN=qz98;DBF=D:\PB_Migration\QUARTZBASE_PB21\QUARTZBASE_PB10\DB\<username>\qz98.db;ASTOP=yes;Description=qz98"

That could be used in the Windows script, you would need to deal with <username> in the database file path. If in a batch, you should still be able to use %USERNAME% but I leave that as an exercise for you if you opt to go that path.

[Privete_member__1042652]

We were able to create the DSN entries using this approach. Hence closing this ticket. Thanks a lot for your support