Thanks for your quick reply Tom.

Within the MS SQL Query tool - exactly the same error. Knowing that native environment of MS SQL is throwing the same error I can skip the JSP check at this point.

I'll try to explain why I'm not using the direct connection from JSP to QODBC. As far as I know I need to setup a DSN to make a direct connection. The Java server is running on a separate Linux box and I'm not sure if I can setup a DSN from there to the Windows Server, hosting the QB data file and QODBC. Also, my JDBC driver doesn't support DSN calls(at least to my knowlledge), but can easily handle SQL connections.

Using the optimizer with MS SQL Server can be trickly depending on how MS SQL Server is setup in regards to using authenticated users. The actual connection to QuickBooks normally uses the Windows admin account, but when logged into a Windows user account this gets to be the account logged in. Therefore you should always use the Windows primary "Administrator" admin account to log into the server and use QODBC.

A linked server runs queries as muliple treads, and the QODBC optimizer is single user only. So you need to setup a different optimizer file for the SQL Server to use than the other disktops applications. To do this step up a seperate System DSN (see: How do I create or configure a QODBC DSN? ) and specify a fixed location of the optimizer database folder for SQL Server to use instead of the default %UserProfile% method.

Does:

SELECT * FROM OPENQUERY(DAMS, 'SELECT * FROM Accounts')

work?

The DSN needs to state the full path to your company file.

Ok, you need to repair the optimized file for your MS SQL Server linked table. Try running this to repair the optimized table first:

SELECT * FROM OPENQUERY(DAMS, 'SELECT * FROM Account VERIFY where ListID="x"')

As you can override your QODBC Optimizer configuration screen settings to suit what you are actually doing by using the following tags:

VERIFIED | VERIFY - Forces Full Resync with QuickBooks on the optimized table before Query starts
CALLDIRECT | UNOPTIMIZED - Passthru query directly to QuickBooks - use no optimizations
OPTIMIZE | OPTIMIZED | NOSYNC - Passthru query to optimized table directly for maximum speed

The query itself is:

SELECT * FROM Account VERIFY where ListID='x'

with single quotes. When using MS SQL Server the single quote is replaced by double quotes within a single quote for the whole select statement:

SELECT * FROM OPENQUERY(DAMS, ' SELECT * FROM Account VERIFY where ListID= "x" ' )

so it depends how and from where you are executing the query.