Windows 7 64 bit + 10.0.1 4075 + System DSN Setup Problems

[Former Member]

We did a clean 64-bit install of SQL Anywhere 10.0.1 EBF 4075. We were careful to un-check any option that would allow a 32-bit install. We absolutely need to use the 64-bit options.

The TEST button in the ODBC tab of the Windows ODBC configuration tool, ISQL and our application all work fine if we use the USER DSN.

Our problem is we have to deploy this first to the most shared machines in the company. We've always solved this problem by utilizing the SYSTEM DSN.

If we set up the SYSTEM DSN instead of the USER DSN, the TEST button in the ODBC tab of the Windows ODBC configuration tool works just fine. In ISQL, if we connect directly to the database without using the DSN, it connects fine. But if we try to use the DSN, if we click browse, it doesn't even show the System DSN we've set up, and checking "show all" doesn't help. But if we type the actual name of the DSN we've created, we can we get the error: "The specified DSN contains an architecture mismatch between the Driver and Application". And when we run our application, it just says there's an error but doesn't report the text of the error.

Have we done something to force ISQL and our application to use the 32-bit db administrator? Do we have a permissions problem? We're running out of ideas. How do we make this work?

Thank you for all your help, -carol

[VolkerBarth]

What about your applications? Are they 64 bit, too? (In that case, I would expect you would have to use different DSNs for the database tools - 32 bit, as Karim tells - and your apps.)

[Breck_Carter · Accepted solution]

AFAIK Version 10 ISQL does not actually use ODBC to connect. If you specify a DSN, you are simply telling ISQL to get other connection parameters from the DSN.

Here are a couple of examples of ISQL connecting directly to a database (no DSN):

"%SQLANY10%\\win32\\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"

"%SQLANY10%\\win32\\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql;LINKS=TCPIP(HOST=123.123.123.123:49152;DOBROAD=NONE);"

Save the ODBC troubleshooting for applications that need ODBC 🙂

[Former Member · Accepted solution]

You're right, of course. But ultimately, the ODBC is my problem. My app uses ODBC and it just fails with a completely useless error message ("Problem with of_connect"). We've been (likely incorrectly) assuming that if we could understand what's wrong with the ODBC connection to ISQL, it would be much easier to debug the connection to our app (written in PowerBuilder 10). The database has a different name on every computer, so we've been using ODBC to accommodate that. Otherwise, I'd just hard-code it into the app. I'm sure we're doing something exactly backward.

[Breck_Carter · Accepted solution]

Testing with ISQL may not help you find the problem. Test with your application, or try using dbping with -m. Try turning on the ODBC trace - see the Tracing tab in the ODBC Administrator (but remember to turn it off, it KILLS performance). Start SQL Anywhere with the -z option to trace connection attempts. Get a message that doesn't involve ISQL and ask another question here.

[Former Member · Accepted solution]

Breck,

Keep in mind that DBISQL uses the iAnywhere JDBC Driver which in turn uses ODBC under the covers. So, in reality, DBISQL does use ODBC to make connections. And, since DBISQL in SA 10 explicitly uses a 32-bit JVM, it needs to use DSNs that are compatible with the 32-bit ODBC driver. That is why I suggested using the 32-bit ODBC Administrator when configuring DSNs for use with DBISQL in SA 10.

[Former Member · Accepted solution]

Also, in the case where you can get DBISQL to connect without specifying a DSN, the only reason that works is because DBISQL in that particular case fills in the "driver=..." in the connection string prior to handing the connection string to the iAnywhere JDBC Driver.

[Former Member · Accepted solution]

Turning on the ODBC trace gives me the same error message in my app: "The specified DSN contains an architecture mismatch between the Driver and Application." Thanks to all the advice here, we installed the 32-bit tools and checked our requirement that our application have access to more than 4G memory, which was satisfied when it used 6G. You helped us give up on the impossible and discover we were satisfied anyhow! Thank you so much!

[VolkerBarth · Accepted solution]

@Carol: Could you elaborate more on what you finally did (64 bit server with 32 bit app or 32 bot server with AWE cache or...)? Just curious:)

[Breck_Carter · Accepted solution]

@Kharim: I stand corrected! @Carol: I just ran into a 64-bit anomaly that may or may not have anything to do with anything: A SQL Anywhere 11 database refused to establish an ODBC proxy connection to another V11 database on the same machine via shared memory when run as a service... only turning on TCPIP with HOST=localhost made it work. Either that, or it was the dead chicken I draped over the system unit.

[Former Member · Accepted solution]

@Volker: What we did - there's a checkbox on the install to install the system 32 tools. What checking that "on" did was put a whole bunch of files in the WINDOWSwin32 directory. That directory had been completely empty before. By allowing those tools to be used, everything just worked fine. The reason we carefully did not do that in the first place was a mistaken belief that the 8G memory on the machine would not be used by the 32-bit compatible versions. Fortunately, we were completely wrong on that point!

[Former Member · Accepted solution]

@Breck: I hope I remember your experience in the future. We aren't running anything as a service in this case, but I'm sure I'll trip over this some day!

[Former Member · Accepted solution]

You all rock - if it weren't for you, we never would have gotten this working. But I should share with you the very final solution.

In my question, I stated, "We were careful to un-check any option that would allow a 32-bit install. We absolutely need to use the 64-bit options." Thanks to all of the above explanations, we learned we had to go back and re-install and allow those options so we could get the 32 bit tools.

Then, once we were able to actually make the database run on the x64 machine, I discovered I could script this using dbdsn and it would work properly the first time every time.

So now when I create a database, I create a small batch file for the person who does the install that includes the following command:

"C:\\Program Files\\SQL Anywhere 10\\win32\\dbdsn" -ws "<name>" -pe -c "dbn=<>;uid=<>;pwd=<>;dbf=C:\\Database\\<>.db;dbkey=<>;AutoStop=YES;START='dbeng10 -cl 40p';INT=NO;COMP=NO"

I also have this run automatically on the machine if I extract the database directly onto the target machine (wish I could do that more often!).

Thank you all for your help. I hope this saves someone else from our headache some day!

[VolkerBarth · Accepted solution]

Just a bit of the SA 12 docs w.r.t. ODBC System DSNs:

64-bit versions of Windows maintain two sets of the System Data Source collection; one for 64-bit applications and one for 32-bit applications. To create a System Data Source that is accessible to both 64-bit and 32-bit applications, you must run a copy of the 32-bit ODBC Data Source Administrator (located in the WINDOWS\\SysWOW64 folder). To avoid connection problems, set up your 32-bit System Data Source exactly like your 64-bit System Data Source.

Since I don't use 64 bit so far, I can't tell more; particularly I can't tell what kind of DSN DBISQL will create. But I hope the docs give a starting point:)

[Former Member · Accepted solution]

Ok. Running the ODBC administrator in the WINDOWSSysWOW64 folder returns an error when I try to load the SQL Anywhere 10 driver: "could not load dbcon10.dll." I'll go hunt for that on the machine. Thanks.

[Former Member]

Carol,

In SA 10.0.1, only the server is 64-bit, the tools are still only 32-bit. So I would assume that you would be fine if you used the 32-bit ODBC administrator and created a System DSN with it.

Karim

[Former Member]

That just leaves me wondering why does the User DSN work?

[Breck_Carter]

My question is, why does any DSN fail, since ISQL does not actually use ODBC? Perhaps it is some fault in the ISQL GUI, where it cannot parse the DSN's registry entry to get what it needs.