ODBC - Missing Columns

Hi All,

I am installing Dynac for a client and I am experiencing problems with connecting to the data, including the sample data.

First thing to note is I have installed Dynac and used the ODBC at a number of other clients with minimal issues.

The details:
Ledger 9.1A
ODBC 9.1A (140922) - Latest off the website

In Excel using the Microsoft Query tool I can select AL90DGLO but I am unable to expand to see the columns.

Interesting note: The user gave us all the GL.* files and I have no problem connecting to their data on my computer.

Do you get an error?
Can you expand other files?
What version of Excel, 32 bit or 64 bit?

It is 32-bit excel and I cannot expand any tables ( random testing about 15 tables ).

No error in Excel but when I run a select * from AL90DGLO from the Dynac DB tool I am getting the error of "(8) Error attempting to open file: AL90DGLO". I would have posted that early but I swear the error was data file not found as in bad table name.

What are the usual suspects in this case?

Look at the files in ..\Softrak\ODBCv2 on your machine and see what is installed at the site.

What does "(8) Error attempting to open file: AL90DGLO" mean in your application?

Is it possible you are not looking at the correct data folder?

How is the "connection" being made in MSQuery from Excel. If the client has not purchased/registered Adagio ODBC then they will not be able to create a DSN or connect to the data.

(8) Error attempting to open file: AL90DGLO" is the error returned by ODBC.

If I copy the folder to my local environment I can access the data to problem.

Is there any kind of logging or debugging that can be turned on or looked at?

You may turn on Tracing for ODBC using the ODBC Data Source Administrator and set the destination folder for the log file.

What folder are you copying to your local environment? The data folder or ..\Softrak\ODBCv2?

Are the files in your local ..\Softrak\ODBCv2 the same as at the client site?

I copied the folder containing the GL.* files extracted them to C:\Temp and created a new DSN and pulled the data into Excel.

I'll try turning on the log file

Doesn't seem to give anything useful. Is there any Adagio debugging or logs to help figure out what went wrong?

here is the ODBC tracing:

RESS;Init 12e8-7c4 ENTER SQLExecDirectW
HSTMT 0x028707C8
WCHAR * 0x0273C8F8 [ 24] "select * from AL90DMST\ d\ a"
SDWORD 24

SQLEXPRESS;Init 12e8-7c4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0x028707C8
WCHAR * 0x0273C8F8 [ 24] "select * from AL90DMST\ d\ a"
SDWORD 24

DIAG [S1000] [Softrak Systems][Adagio] (8) Error attempting to open file: AL90DMST. (8)

SQLEXPRESS;Init 12e8-7c4 ENTER SQLGetDiagRecW
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x028707C8
SQLSMALLINT 1
SQLWCHAR * 0x0018EBE8
SQLINTEGER * 0x0018E7D0
SQLWCHAR * 0x0018E7E8
SQLSMALLINT 512
SQLSMALLINT * 0x0018E7D4

SQLEXPRESS;Init 12e8-7c4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x028707C8
SQLSMALLINT 1
SQLWCHAR * 0x0018EBE8 [ 5] "S1000"
SQLINTEGER * 0x0018E7D0 (8)
SQLWCHAR * 0x0018E7E8 [ 70] "[Softrak Systems][Adagio] (8) Error attempting to open file: AL90DMST."
SQLSMALLINT 512
SQLSMALLINT * 0x0018E7D4 (70)

I believe Adagio ODBC is registered and licensed on your machine but that will not be the case at your client's site unless they purchased the product.

Further, the files in your ..\Softrak\System folder are probably not the same as the ones at the client site.

You need to use your 3rd Party OEM string when connection at the client site.

I believe I am but I will verify.

I am using the 3rdParty parameter. I believe the error I get is the the license has expired if I am not licensed.

I used the Client NFR code to install the ODBC.

There is no additional Adagio logs.

Your log shows SQLEXPRESS not Excel?

That last log was through our DB Tool because it generates the error message. Excel just displays the table names without the columns.

So what is different between your setup and the setup at the client site?

I managed to get the sample working at the clients. I had installed into a new directory away from their C:\Softrak\ and I deleted and re-created the DSN. It occured to me that if I didn't delete and re-create the DSN it could be pointing to a different AdagioODBC driver.

So I can load the Sample data now. I am still having problems with the company data but I think I'm in the realm of talking with the client's Adagio Var and it might simple be a setup or pathing issue.

Ok, I got a company working.
Here are my steps.

Current setup: (not exactly but should work )

1. C:\Softrak\data\{ Companies }
2. Install ODBC in D:\Softrak\ODBCV2 ( this creates a system folder in D:\Softrak )
3. Copy C:\Softrak\data\Company1 to D:\Softrak\data\Company1
4. Create DSN to D:\Softrak\data\Company1
5. Try DSN - Failure
6. Modify ADSPROF for company1. Invalidate the Currency / Tasks directories
7. Try DSN
8. Prompted for Currency / Tasks
9. Point them to D:\Softrak\system
10. Success!

I determine that success was due to changing the tasks folder to the D:\Softrak\system folder. This folder was created during the installation of the ODBCv2.

If I left the tasks pointed at C:\Softrak\system as defined in ADSPROF then the failure is [DSI]: Error message not found: Connect Failed.

This success is not useful as the ADSPROF file cannot be modified. Have to figure out why the ODBC doesn't like the tasks in the original system folder.

Adagio ODBC should be installed with the rest of the Adagio software.
Is this a local install of Adagio to drive C: or is C: the main drive on a Server that you are RDP'd into?

Well the setup is different.

The server I am on is not the server Adagio is used from. Each user uses Adagio from their own computer and the adagio files all sit on a single server.

At one point Adagio was installed on the server but is not being used. I did try to install ODBC in the folder that is shared by everyone but since the files were being used it would not let me install it.

That is the last thing I would need to do is to get ODBC installed in the same location that is being shared.

In the meantime for testing you can modify your copy of ADSPROF in D:\Softrak\data\Company1

Don't copy ADSPROF when you refresh the data.

You need to discuss with the site's Adagio consultant, I would be very surprised to hear that they installed Adagio on each workstation and not a single install on the Server.

Workstation installs would be needed on the workstations.

Ya, they were having performance issues on the server so they went with workstation installs.

Oh, I meant to ask what the EZTasks file is all about?

It seems that the tasks are causing the issue. of course the current location cannot be changed and somehow I need to get ODBC to play nicely with the eztasks file.

As mentioned before it doesn't seem to care about the currency file it only freaks out when using the proper eztasks file.

What role does eztasks play in the ODBC? There might be some special config with the client that is causing problems. I was warned that there was some oddities. The eztasks might be one of them.

Adagio is licensed to be installed on one computer, that computer may be a Server when multiple users are running the applications.

The setup you describe is not stable in a multiuser environment.

As I said, I am very surprised that an Adagio consultant would have taken this approach.

Understood. At some point that will need to be addressed. What is the reason to believe that my problem will go away if I install the ODBC in the same directory as the other modules when I am able to connect to the company by pointing to a different tasks folder.

I would like to understand the how the tasks folder is affecting the ODBC connection so I can make the appropriate changes to my import / setup or suggest an appropriate fix to the client.

The EZTASKS entry in ADSPROF is really a pointer to the system files ( not to a single file ) that control data access, multi user access, and record and file locking. The files in the folder pointed to by this entry ( and in the subfolders) are codependent on each other.

In your incorrect setup you load system files from D:\Softrak\System and then point to a different set of files.

Are all the files in C:\Softrak\System identical to D:\Softrak\System?

My guess would be that they are not.

So the module will need to be installed in the same folder to ensure proper data access / record and file locking. That makes sense. The error indicated a failure to connect which was probably a failure to correctly establish a lock.