Tom, I've just upgraded to the server edition of QODBC. I've followed countless links on this forum and I was able to setup the linked server with relative ease but I'm totally stuck when it comes to executing a query without the 'unoptimized' tag. I've got SQL Server running on an XP machine. I've gone to the extreme of giving 'IUSR_JIMSDELL', 'Authenticated Users' and 'INTERACTIVE' full control of the C drive. I also added the same three users to the Administrators group. I've reloaded all data to a non user profile directory (c:\program files\qodbc ...\optimizer). All of your test scripts ran properly except for testDCOM which produced the following results:

QODBC ASP DCOM Access Test Page QB2002 QB2003 QB2004 DCOM COM Object Name Status • •   •   QBXMLRPEQODBCInteractive.exe Success • •     • QBXMLRP.dll Success     • •   QBXMLRP2EQODBCInteractive.exe Success     •   • QBXMLRP2.dll Success • • •   • XERCES-COM.dll Error: ActiveX component can't create object Current Logged on User is: JIMSDELL\JimK Authenticated user is: JIMSDELL\JimK

I'm truly at a loss as to why I can only run queries with the 'unoptimized' tag. The good news is that even with the unoptimized tag it's pretty fast (despite a large file size) and it does reflect changes immediately. I know you are going to give me a series of things to try. When you do this please include whether or not I need to stop and restart SQL Server (which I've been doing along with reboots).

HELP!

Thanks, Jim.

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.

See: XERCES-COM.dll Error: ActiveX component cant create object for the fix to the test script.

Tom,

Once I logged in to Windows as Administrator everything was fine. The test server (my laptop) has really been setup for my username (JimK) as opposed to Administrator. It seems that there has got to be some properties that can be set on the JimK user (in SQL Server 2005) to allow me to replicate the Administrator logon. Do you have any ideas?

As an alternative, if I setup a new user for SQL Authentication do you know what properties would need to be set so I could use it instead of my Windows account (therefore being able to login as JimK but achieve the same results as logging in as Administrator?

You had said '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 bottom line is, how can I set it up to be able to login as a user who has administrative capabilities but just isn't THE Administrator? I'd love to have a better idea of what authentication properties SQL Server 2005 requires to be able to use the optimizer properly. I'm more than happy to work with you on this as you've ALWAYS resolved my issues.

Thanks as always!

Tom, I have followed all of your very explicit instructions on permissions but I know other people are running very similar situations and I know I'm close to being able to run the queries against a linked server as a user who is merely a member of the administrators group as opposed to the administrator. I've been conversing with someone who does it but he isn't able to provide me the insight I need.

Again, queries will return data (not just a header) if I'm logged in as Administrator. They will work if I'm logged into the SQL Server using Windows Authentication or SQL Authentication (logging in as 'sa'). The queries only give headers (even when authenticating as 'sa') when logged in as a user who is part of the Administrators group. I was trying to dig deeper and noticed this slight difference between the query with the 'unoptimized' tag versus the regular query:

20070731.143845 I 4844 RequestProcessor ========= Started Connection =========
20070731.143845 I 4844 RequestProcessor Request Processor, QBXMLRP2 v6.0
20070731.143845 I 4844 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'
20070731.143845 I 4844 CertVerifier The Authenticode signature validated OK.
20070731.143846 I 4844 RequestProcessor Opening the file in the DoNotCare mode.
20070731.143846 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4844).
20070731.143846 I 4224 SpecVersion Current version of qbXML in use: 6.0
20070731.143846 I 4224 QBSDKMsgSetHandler QUERY: Host
20070731.143846 I 4224 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.143846 I 4224 QBSDKMsgSetHandler QUERY: Company
20070731.143846 I 4224 QBSDKMsgSetHandler Request 2 completed successfully.
20070731.143846 I 4224 QBSDKMsgSetHandler QUERY: DataExtension Definition
20070731.143846 I 4224 QBSDKMsgSetHandler Request 3 completed successfully.
20070731.143846 I 4224 MsgSetHandler Finished.
20070731.143846 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4844), ret = 0.
20070731.143846 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4844).
20070731.143846 I 4224 SpecVersion Current version of qbXML in use: 6.0
20070731.143846 I 4224 QBSDKMsgSetHandler QUERY: Employee
20070731.143847 I 4224 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.143847 I 4224 MsgSetHandler Finished.
20070731.143847 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4844), ret = 0.
20070731.143950 I 4844 RequestProcessor "EndSession" has not been called.
20070731.143950 I 4844 RequestProcessor "CloseConnection" has not been called.

20070731.144044 I 4716 RequestProcessor ========= Started Connection =========
20070731.144044 I 4716 RequestProcessor Request Processor, QBXMLRP2 v6.0
20070731.144044 I 4716 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'
20070731.144044 I 4716 CertVerifier The Authenticode signature validated OK.
20070731.144044 I 4716 RequestProcessor Opening the file in the DoNotCare mode.
20070731.144044 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4716).
20070731.144044 I 4224 SpecVersion Current version of qbXML in use: 6.0
20070731.144044 I 4224 QBSDKMsgSetHandler QUERY: Host
20070731.144044 I 4224 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.144044 I 4224 QBSDKMsgSetHandler QUERY: Company
20070731.144044 I 4224 QBSDKMsgSetHandler Request 2 completed successfully.
20070731.144044 I 4224 QBSDKMsgSetHandler QUERY: DataExtension Definition
20070731.144044 I 4224 QBSDKMsgSetHandler Request 3 completed successfully.
20070731.144044 I 4224 MsgSetHandler Finished.
20070731.144044 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4716), ret = 0.
20070731.144044 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4716).
20070731.144045 I 4224 SpecVersion Current version of qbXML in use: 6.0
20070731.144045 I 4224 QBSDKMsgSetHandler QUERY: Employee
20070731.144045 I 4224 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.144045 I 4224 MsgSetHandler Finished.
20070731.144045 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4716), ret = 0.
20070731.144045 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4716).
20070731.144045 I 4224 SpecVersion Current version of qbXML in use: 6.0
20070731.144045 I 4224 QBSDKMsgSetHandler QUERY: List Delete
20070731.144045 I 4224 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.144045 I 4224 MsgSetHandler Finished.
20070731.144045 I 4224 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4716), ret = 0.

Any ideas why the 'List Delete' query is given to the SDK?

When I login as Administrator I get the following SDK trace for the same 2 queries:

20070731.151917 I 5960 RequestProcessor ========= Started Connection =========
20070731.151917 I 5960 RequestProcessor Request Processor, QBXMLRP2 v6.0
20070731.151917 I 5960 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'
20070731.151917 I 5960 CertVerifier The Authenticode signature validated OK.
20070731.151917 I 5960 RequestProcessor Opening the file in the DoNotCare mode.
20070731.151917 I 3468 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5960).
20070731.151917 I 3468 SpecVersion Current version of qbXML in use: 6.0
20070731.151917 I 3468 QBSDKMsgSetHandler QUERY: Host
20070731.151917 I 3468 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.151917 I 3468 QBSDKMsgSetHandler QUERY: Company
20070731.151917 I 3468 QBSDKMsgSetHandler Request 2 completed successfully.
20070731.151917 I 3468 QBSDKMsgSetHandler QUERY: DataExtension Definition
20070731.151917 I 3468 QBSDKMsgSetHandler Request 3 completed successfully.
20070731.151917 I 3468 MsgSetHandler Finished.
20070731.151917 I 3468 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5960), ret = 0.
20070731.151917 I 3468 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5960).
20070731.151918 I 3468 SpecVersion Current version of qbXML in use: 6.0
20070731.151918 I 3468 QBSDKMsgSetHandler QUERY: Employee
20070731.151918 I 3468 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.151918 I 3468 MsgSetHandler Finished.
20070731.151918 I 3468 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5960), ret = 0.
20070731.151943 I 5960 RequestProcessor "EndSession" has not been called.
20070731.151943 I 5960 RequestProcessor "CloseConnection" has not been called.

20070731.151943 I 5960 RequestProcessor ========= Started Connection =========
20070731.151943 I 5960 RequestProcessor Request Processor, QBXMLRP2 v6.0
20070731.151943 I 5960 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'
20070731.151943 I 5960 CertVerifier The Authenticode signature validated OK.
20070731.151943 I 5960 RequestProcessor Opening the file in the DoNotCare mode.
20070731.151943 I 3468 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 5960).
20070731.151943 I 3468 SpecVersion Current version of qbXML in use: 6.0
20070731.151943 I 3468 QBSDKMsgSetHandler QUERY: Employee
20070731.151944 I 3468 QBSDKMsgSetHandler Request 1 completed successfully.
20070731.151944 I 3468 MsgSetHandler Finished.
20070731.151944 I 3468 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 5960), ret = 0.

Quite a bit different on the select * from openquery(qodbc2,'select * from employee') query which is the second to run.

I have detail tracing checked on and I always get the following message when I try to review it 'file QODBCTRA.TXT is empty'.