Does QODBC work with MS SQL Server 2005 Linked Tables? |
Author |
Message |
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-06-13 11:02:15 |
Running QODBC as a Linked server requires the Server Edition of QODBC. QuickBooks must be running with the company file opened on the system console. The QODBC Server Edition includes DCOM servers required to allow QODBC running as a system service to find the instance of QuickBooks running on the server console. You have two choices in setting up QODBC Linked Tables with Microsoft SQL Server. The first is using the QODBC DCOM Servers. The second is using the Remote Connector Option.
The DCOM Servers require QuickBooks to be running on the Server Console (Local session), not a Terminal Services session. They are more secure and require no extra overhead but are more rigid on how it is setup and it can be difficult to configure the security of the DCOM servers.
The Remote Connector option allows the window that is running QuickBooks to be anywhere that is accessible via TCP/IP. That means QuickBooks can be run in a Terminal Sessions window, on another computer on your LAN or anywhere on the WEB. If your data will be transmitted over the internet we recommend you upgrading the Remote Connector to the SSL secure version. The instructions on setting up the Remote Connector version are found in Using QODBC Remote.
QODBC Basic Setup For Using the DCOM Servers Method You must turn on use of the DCOM servers. You do this by running the QODBC Setup Screen and selecting the "Server Edition" tab page. Check the checkbox for "Use DCOM Servers".
If you're working with the QuickBooks sample company file, this option is available to check even if you do not have a Server Edition license and will be ignored if you open a standard company file and do not have a QODBC Server Edition or evaluation. Note: If you cannot update using QODBC as an SQL Linked Server you can also try checking the "Simulate Transaction Support for SQL Server" in the QODBC Setup Screen Advanced Tab Page - The option is not to support Transactions. This is a global driver setting. Checking this box will turn transaction support on so that Microsoft SQL Server will allow updates when using QODBC as a link server. QODBC doesn't support transactions, it simply simulates the turning on and off of transactions.
Automatic DCOM Configuration Click here: qodbcSetDCOM.zip to download the following automated tools:-
- Dcpinst.exe is an installation setup for the automated DCOM configuration object. This needs to be run first and only once.
- QODBCSetDCOM.VBS is a VB script that uses the installed configuration object to setup the DCOM Servers. It will reset and automatically add all necessary security each time it is run. The script can be edited if you need to change the Accounts it is configuring.
- The new settings will not take affect until the DCOM servers (qbXMLRPeQODBCInteractive.exe and qbXMLRP2eQODBCInteractive.exe) are removed from memory. Rebooting is the simplest way to accomplish this, but running the Windows Task Manager and Ending Process on any running copies will also work.
- Test your DCOM setup using one of the following:-
Setting up "Auto-Login" Unattended Mode Locate the "QODBC Driver for QuickBooks" program group on your desktop. Click on the "QODBC Setup Screen" icon to launch the setup program supplied with QODBC.
The following general setup screen will become available. Check the Locate a company file: option. This allows you to setup the full path to where your QuickBooks data file resides. For example: “C:\Program Files\Intuit\QuickBooks Pro\Myapp.qbw”.
The [Browse] button will let you choose your QuickBooks company file from the folder it is in.
To use the QODBC auto-login unattended mode you must specifiy the full path of the QuickBooks company file you wish to connect to.
The Advanced tab in QODBC Setup v6 Build 148 and later now has new defaults for Integrated Application Defaults Authorization popup settings. These settings are only available to USA QuickBooks 2005 and USA QuickBooks 2006 users, otherwise they are greyed out. Use these settings to allow "auto-login" with or without a visible QuickBooks User Interface.
You will also have to authorize QODBC to login to QuickBooks automatically. To do this you must start QuickBooks as the Administrator. Go to the "Edit-Preferences" menu item in QuickBooks. Then select the "Integrated Applications" icon from the left side.
Then highlight one application (usually FLEXquarters QODBC) and press the "Properties" button and click on the Company tab. Check the "Allow this application to access this company file".
And check the "Allow this application to login automatically" and select a user from the "Login As" drop down. You will need to repeat this for all the applications on the list that you use.
Creating a Linked Table in Microsoft SQL Server 2005 You must create a system DSN, by default the DSN "QuickBooks Data" is created for you when QODBC is installed, but you can use any system DSN created for QODBC. Using Enterprise Manager, select your SQL server->Security->Linked Servers then right click (or Action menu) and select "New Linked Server...". The following screen shot below shows a simple Link Server setup for QODBC:
The OLE DB provider options for managing linked queries can be set in SQL Server Management Studio. In Object Explorer, right-click the provider name and select Properties for MSDASQL (Microsoft OLE DB Provider for ODBC Drivers).
"Dynamic parameter" should be checked to allow parameter queries. "Level zero only" should be checked to allow four part naming to work. "Allow InProcess" should be checked to help performance. "Non transacted updates" should be checked if you want to be able to update QuickBooks data. "Index as access path" should be checked to help performance. "Disallow adhoc accesses" should be unchecked to allow OPENQUERY to function.
Run a query. Below are a few examples based a Link Server named "QODBC":-
-
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Host')
-
SELECT * FROM QODBC...HOST
-
SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM HostSupportedVersions')
-
SELECT * FROM QODBC...HostSupportedVersions
-
SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Employee')
-
SELECT ListID, Name FROM QODBC...Employee
-
SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Customer')
-
SELECT ListID, Name FROM QODBC...Customer
-
SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name, CompanyName FROM Vendor')
-
SELECT ListID, Name, CompanyName FROM QODBC...Vendor
-
SELECT * FROM OPENQUERY(QODBC, 'SELECT Name, CompanyName FROM Vendor Where "Name" =''Bayshore Water''')
-
SELECT * FROM OPENQUERY(QODBC, 'sp_report CheckDetail show TxnType_Title, RefNumber_Title,Date_Title, Name_Title, Item_Title, Account_Title, PaidAmount_Title, OriginalAmount_Title, TxnType, RefNumber, Date, Name, Item, Account, PaidAmount, OriginalAmount parameters DateMacro = ''ThisMonthToDate''')
-
UPDATE OPENQUERY(QODBC, 'SELECT "Name", "CompanyName" FROM Vendor WHERE "Name" = ''Bayshore Water''') SET "CompanyName" = 'Bayshore Water, LLC'
-
UPDATE QODBC...Vendor SET "CompanyName" = 'Bayshore Water, LLC' WHERE "Name" = 'Bayshore Water'
-
INSERT INTO QODBC...Customer (Name) Values ('Test Customer 201')
-
sp_linkedservers
Trouble Shooting QODBC as a SQL Linked server Below is an SQL script that will test the DCOM Server setup. All components should have a description of "Successfully Created" when everything is properly configured. Follow the instructions on this web site for the Web Server Edition if you have difficulty with DCOM configuration.
Test DCOM Script (.vbs):
DECLARE @oQBXMLrp int DECLARE @iHR int DECLARE @iCnt int DECLARE @sSource varchar(255) DECLARE @sDesc varchar(255) DECLARE @sObjectName varchar(255)
SET @iCnt = 1 SELECT "Current User: " = CURRENT_USER, " Time: " = CONVERT(char(30), CURRENT_TIMESTAMP) WHILE @iCnt < 6 BEGIN SET @sObjectName = CASE @iCnt WHEN 1 THEN 'QBXMLRPEQODBCInteractive.RequestProcessor' WHEN 2 THEN 'QBXMLRP.RequestProcessor' WHEN 3 THEN 'QBXMLRP2EQODBCInteractive.RequestProcessor' WHEN 4 THEN 'QBXMLRP2.RequestProcessor' ELSE 'Xerces.DOMDocument' END EXEC @iHR = sp_OACreate @sObjectName, @oQBXMLrp OUT IF @iHR <> 0 BEGIN EXEC sp_OAGetErrorInfo @oQBXMLrp, @sSource OUT, @sDesc OUT SELECT "Object" = @sObjectName, "Function" = 'CreateObject', "HR" = convert(varbinary(4),@iHR), "Source" = @sSource, "Description" = @sDesc END ELSE BEGIN SELECT "Object" = @sObjectName, "Function" = 'CreateObject', "HR" = convert(varbinary(4),0), "Source" = '', "Description" = 'Successfully Created' END
EXEC @iHR = sp_OADestroy @oQBXMLrp SET @iCnt = @iCnt + 1 END
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-12-30 02:15:48 |
My understanding is that SQL Server 2005 does not have the property menu item on the Link Server providers like SQL Server 2000. The OLE DB provider options for managing linked queries can be set in SQL Server Management Studio. In Object Explorer, right-click the provider name and select Properties. If you can't configure them through the UI as indicated in the directions you can script this action like this:
USE [master] GO EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DynamicParameters', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'IndexAsAccessPath', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'LevelZeroOnly', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NestedQueries', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NonTransactedUpdates', 1 GO
But you can also run the query directly without OPENQUERY like this:
SELECT ListID, Name, CompanyName FROM QODBC...Vendor |
|
|
|
|
Posted : 2007-01-13 13:42:00 |
When this didn't work, I also found that it was because my Ole Automation Procedures Option was turned off in my SQL Server Surface Area Configuration.
To turn it on with a query, see: http://msdn2.microsoft.com/en-gb/library/ms191188.aspx
which contains: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-02-09 21:33:11 |
|
|
|
Mike |
|
Group | : Members |
Posts | : 13 |
Joined | : 2007-05-31 |
|
Profile |
|
Posted : 2007-06-22 02:05:56 |
Hi again,
I have followed these steps to set up my DCOM server and Linked Server in Management Studio. However when I perform the first test query [SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Host') ], I get the column headers but no data. If I do the query SELECT * FROM OPENQUERY(QODBC, 'SELECT count(*) as HostCount FROM Host'), I get the HostCount header but no data.
Management Studio reports the query as successful. Doing these same queries from VB Demo works fine.
Thanks for any help!
_Mike |
|
|
|
Mike |
|
Group | : Members |
Posts | : 13 |
Joined | : 2007-05-31 |
|
Profile |
|
Posted : 2007-06-22 02:14:30 |
Some more details about the problem reported above: I *AM* running from the console, logged into QB as Admin in Multi-user mode and Management Studio responds to the query with "(0 row(s) affected)"
Thanks. |
|
|
|
Mike |
|
Group | : Members |
Posts | : 13 |
Joined | : 2007-05-31 |
|
Profile |
|
Posted : 2007-06-22 03:41:31 |
I find my problem getting stranger. After taking a lunch break a complex query worked perfectly and returned 18 rows. I then tried a simple query and got nothing and retried the complex query and then got nothing from that too.
Any ideas?
_Mike |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-06-22 04:26:24 |
Most likely you are using VB Demo and Management Studio at the same time. 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 desktop applications. To do this, set 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 (like: C:\Program Files\QODBC Driver for QuickBooks) instead of the default %UserProfile% method.
To repair a optimized table (for example the Invoice table) you can run:
SELECT * FROM OPENQUERY(QUICKBOOKS, 'SELECT * FROM Invoice VERIFY WHERE TxnID = "x"')
Another way of doing this is to simply log in as the account that the SQL Server Agent runs under. Bring up the QODBC Setup Screen and under the OPTIMIZER tab click "reload all data" - at this point you will have <companyname>.opt file in the folder. |
|
|
|
|
Posted : 2007-07-02 13:35:08 |
I am having exactly the same problem as Mike. I am NOT running the VB demo simultaneously. I set up a separate DSN and optimizer folder. This does not help. I am able to use the DTS wizard to populate tables. However, when I query the Quickbooks linked server I get a successful execution but 0 rows returned. I have followed all of the outlined steps given on the forums.
In my "ODBC Data Source Administrator" driver list I have the two drivers " QODBC Driver for Quickbooks" and "QODBC OEM Driver N"
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-02 14:04:52 |
Try checking "Run 2.1 ODBC Compatibility" if it's unchecked, or unchecking it if it's already checked.
Delete the "QODBC OEM Driver N" from your QuickBooks Integrated Applications list as it doesn't work with Windows Services like MS SQL Server 2005. |
|
|
|
|
Posted : 2007-07-03 00:28:38 |
The "QODBC OEM Driver N" does not appear in Quickbooks Integrated Applications. However, I removed it from the Windows Registry. I unchecked "Run 2.1 ODBC Compatibility". I get the same results for
SELECT * FROM QBLS...Host
or
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Host')
Query executed successfully."
Query executed successfully."
(0 row(s) affected)
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-03 08:25:58 |
Check the QODBC Message Log in the QODBC Setup Screen for any errors and try checking the "Display Driver Status Panel" to see what's happening. See: How do I turn on the trace log? |
|
|
|
|
Posted : 2007-07-03 09:58:52 |
The ODBC Message log is blank. When I use SQL server no messages are generated. The trace log also remains empty. If I use the VB Demo to run a query, the trace log is not empty.
Also, when I do an insert from Management Studio, it says that it is successful and that 1 row is affected, but nothing appears in Quickbooks. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-03 12:42:56 |
Please paste the start section of your VB Demo trace file here so I can see what version of QODBC you are using. |
|
|
|
|
Posted : 2007-07-03 13:30:15 |
The Quickbooks company file, Dummy.QBW, is new. The only thing that I have added is one customer.
ISAMOpen Database: C:\QBcompany\Dummy.QBW DSN: QBDSN UserName: Password: DeveloperCode: ApplicationCode: OpenMode: M UseDCOM: Y IBizRemoteConnection: N IBizRemoteURL: http://localhost:2080 IBizRemoteUserName: admin IBizRemotePassword: IBizRemoteAskPassword: Y IBizRemoteSSLAcceptServerCert: IBizRemoteConnectionTimeOut: 120 IBizRemoteForce2002_2003: N IBizOEConnection: N IBizOETicketGUID: IBizOELoginGUID: IBizOELoginAsk: Y IBizReconnectionDelay: 0 ColumnNameLen: 50 UseCaseSensitiveRefNumber: Y OptimizerOn: Y OptimizerDBFolder: C:\QBcompany\QBDSNOptimizer OptimizerCurrency: C OptimizerDirtyReads: D OptimizerUpdateSync: Y OptimizerEncrytionOn: Y UseIterators: Y IteratorChunkSize: 1000 IAppLaunchQBUI: Y IAppAutomaticLogin: Y IAppReadOnly: N IAppAllowPersonalData: Y ConvertToLongVARCHAR: FALSE MaxLONGVARCHAR: 4096 Convert To LONGVARCHAR: N Max LONGVARCHAR: 4096 OleInitialize - Returned OK Logged In User: John Is a Service: No Registration Name: Expressions Unlimited, Inc. RegSerial: S07U10016977 QODBC Version: 7.00.00.214 DLL: C:\WINDOWS\system32\fqqb32.dll Driver Name: FQQB32.DLL Process Count: 1 Thread Count: 1 OpenMode: Multi-User Mode Include Raw: Yes Version Type: QODBC Server Edition 2007 Access Method: QBXMLRP2EQODBCInteractive.exe Thread ID: 4904 SDK Module: QBXMLRP2EQODBCInteractive.exe QB Company Name: C:\QBcompany\Dummy.QBW QB Product Name: QuickBooks Enterprise Solutions: Manufacturing and Wholesale Edition 7.0 QB Major Version: 17 QB Minor Version: 0 QB File Mode: MultiUser QB Automatic Login:Yes QB Country: US QB Supported Ver: 1.0 QB Supported Ver: 1.1 QB Supported Ver: 2.0 QB Supported Ver: 2.1 QB Supported Ver: 3.0 QB Supported Ver: 4.0 QB Supported Ver: 4.1 QB Supported Ver: 5.0 QB Supported Ver: 6.0 Optimize SQLITE3 Library Version: 3003007 Optimize File Contents: C:\QBcompany\QBDSNOptimizer\Dummy.OPT |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-03 14:56:29 |
Please turn off the "Include Raw Files" setting in QODBC Setup Advanced Tab screen and make sure you are logged in as the administrator on the server (not John).
|
|
|
|
Mike |
|
Group | : Members |
Posts | : 13 |
Joined | : 2007-05-31 |
|
Profile |
|
Posted : 2007-07-03 22:36:29 |
Tom was right that I was running VB Demo and SQL Server at the same time through the same connection. That's what started the problem. However closing VB Demo didn't fix it. I found that my .OPT file had become corrupted by opening it in SQL Server and VB Demo at the same time. I suggest that you close all QODBC connections, delete your .OPT file and rebuild it.
Cheers,
Mike |
|
|
|
|
Posted : 2007-07-05 08:24:48 |
I deleted the .OPT file and did "rebuild all data". I also tried starting with no .OPT file and letting it be built as I ran queries from Managemment Studio. "John" is the Windows Administrator and I am using Windows authentication in SQL Server 2005 Express.
I am now getting a trace file from using SQL Server.
I increased the iterator chunk size. Why isn't there a line that says "QB Supported Ver: 7.0"? Should I completely uninstall Quickbooks 7.0 and SQL Server 2005 Express and reinstall them? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-05 14:28:20 |
|
|
|
|
Posted : 2007-07-05 16:21:42 |
My DCOM test results are:
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 |
Success |
Current Logged on User is: Authenticated user is:
When I run a query, I can see SQL Server writing to the .OPT file. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-05 23:25:44 |
Ok, do you get a row if you run:
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Host')
If not run:
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Host unoptimized') |
|
|
|
|
Posted : 2007-07-06 00:27:13 |
It was a wonderful feeling! I entered the second query, and a row was returned.
However, the queries only work when I use OPENQUERY and the UNOPTIMIZED flag.
For instance, this query does not work:
SELECT * FROM QBLS...Host unoptimized
but this one does:
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Host unoptimized ')
Why does this happen? Can I make it so that the optimizer can be used and I do not have to use OPENQUERY?
Why does this happen? Can I make it so that the optimizer can be used and I do not have to use OPENQUERY? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-06 08:15:02 |
The UNOPTIMIZED tag tells QODBC to call QuickBooks directly bypassing the QODBC optimizer. So let's try this:
SELECT * FROM OPENQUERY(QBLS,'sp_optimizefullsync Customer')
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer') |
|
|
|
|
Posted : 2007-07-06 10:29:32 |
When I run this query:
SELECT * FROM OPENQUERY(QBLS,'sp_optimizefullsync Customer')
I get this error message
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "sp_optimizefullsync Host". The OLE DB provider "MSDASQL" for linked server "QBLS" indicates that either the object has no columns or the current user does not have permissions on that object.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-06 10:57:34 |
|
|
|
|
Posted : 2007-07-06 12:21:39 |
I get the same error message after I reload all data. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-06 14:57:20 |
Do:
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer')
and if it doesn't return any rows:
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer unoptimized') |
|
|
|
|
Posted : 2007-07-06 23:02:15 |
Only the second query returns rows. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-07 14:14:19 |
This indicates that the Windows System user doens't have access to the optimizer file. You need to set the optimizer folder to a non %UserProfile% area and rebuild it. |
|
|
|
|
Posted : 2007-07-08 14:03:45 |
I move the optimizer file to C: which the SYSTEM user has access to and rebuilt the file. The result is the same. |
|
|
|
|
Posted : 2007-07-09 14:42:23 |
When I run the DCOM TEST SQL script in Management Studio, I get the following result:
dbo Jul 8 2007 11:34PM
QBXMLRPEQODBCInteractive.RequestProcessor CreateObject 0x80070005 ODSOLE Extended Procedure Access is denied.
QBXMLRP.RequestProcessor CreateObject 0x00000000 Successfully Created
QBXMLRP2EQODBCInteractive.RequestProcessor CreateObject 0x80070005 ODSOLE Extended Procedure Access is denied.
QBXMLRP2.RequestProcessor CreateObject 0x00000000 Successfully Created
Xerces.DOMDocument CreateObject 0x00000000 Successfully Created
But when I run the TestDCOM.asp, I get "success" for all. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-09 15:50:16 |
|
|
|
|
Posted : 2007-07-10 01:03:19 |
That worked for the SQL sqript DCOM test. I get "successfully created" for all. I have given full control to the C: drive and all of its subfolders to: INTERACTIVE, ASPNET, IUSR_<computer_name>, AUTHENTICATED USERS.
I am now able to get rows without the 'unoptimized' tag. When I run this query,
SELECT * FROM OPENQUERY(QBLS,'SELECT * FROM Customer')
OR
SELECT * FROM QBLS...Customer
I get the rows, however. If I run this query, I get this error.
SELECT * FROM OPENQUERY(QBLS,'sp_optimizefullsync Customer')
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "sp_optimizefullsync Customer". The OLE DB provider "MSDASQL" for linked server "QBLS" indicates that either the object has no columns or the current user does not have permissions on that object.
|
|
|
|
|
Posted : 2007-07-10 03:49:02 |
I wanted to mention that I started to get the rows returned after I logged onto SQL Server as 'sa'.
When i Run this update query
INSERT INTO QBLS...Customer ("Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode")
values('Zfhblaisd, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')
I get this error
OLE DB provider "MSDASQL" for linked server "QBLS" returned message "[QODBC] Optimizer is turned off".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "QBLS" could not INSERT INTO table "[QBLS]...[Customer]".
But the update works and the customers get inserted into quickbooks.
I have run queries from Management studio logged on as 'sa' (sql authentication) and 'John' (windows authentication) perhaps this is why the optimizer is seen as off. I look at the DSN and it says that the optimizer is on.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-07-10 09:12:35 |
See: How do I do INSERTs, UPDATEs and DELETEs using OPENQUERY with Linked MS SQL Servers?
'sp_optimizefullsync Customer' doesn't return any rows and cannot be run directly through a linked server. A workaround would be:
SELECT * FROM OPENQUERY(QBLS, 'SELECT FullName FROM Customer VERIFY where FullName = ''x''')
For Update Sync: SELECT * FROM OPENQUERY(QBLS, 'SELECT TxnID FROM InvoiceLine OPTIMIZE WHERE TxnID = ''x''')
And for Full Sync: SELECT * FROM OPENQUERY(QBLS, 'SELECT TxnID FROM InvoiceLine VERIFY WHERE TxnID = ''x''')
|
|
|
|
|
Posted : 2007-08-02 04:53:29 |
When I run this query, there is no change in the .OPT file (i.e. modified timestamp or the file size)
SELECT * FROM OPENQUERY(DDQBLS, 'SELECT FullName FROM Customer VERIFY where FullName = ''x''')
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-08-02 08:36:08 |
Often timestamps on files aren't updated until the connection or file is closed. You might need to stop the service to see the timestamp update. |
|
|
|
|
Posted : 2007-08-02 11:33:22 |
shouldn't the optimizer status window open when the query is run? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-08-02 14:15:45 |
That depends on your "Display Optimizer Status Panel" setting, however calls through a service don't get the focus and therefore are often not seen on the desktop, are minimized or appear underneath other elements.
|
|
|
|
|
Posted : 2007-08-03 00:27:35 |
I checked. It does not show up. It is not minimized or hiding. |
|
|
|
|
Posted : 2007-08-03 00:46:29 |
The timestamp does not change on the .OPT file. It is still the timestamp of when the "rebuild all data" completed. I have closed the service and restarted my computer. |
|
|
|
|
Posted : 2007-08-03 01:09:13 |
I created a second linked server with a new DSN. This time I did not create the .OPT file. I ran the same queries on both linked servers and got the same performance. I also tried to use VERIFY on the linked server that does not have a .OPT file and neither the status panel nor a new .OPT file appears. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-06 14:26:25 |
Note: QuickBooks only allows for one Company file to be open at the one time. To link mulitple company files you will need to link and unlink seperate company files by specifing different paths in the DFQ=fullpathname connection parameter with a delay between executing sp_addlinkedserver and sp_dropserver.
For example:
EXEC sp_addlinkedserver @server = N'QODBC', @srvproduct=N'QODBC', @provider=N'MSDASQL', @datasrc=N'QuickBooks Data', @provstr = 'ODBC;DSN=QuickBooks Data;DFQ=C:\Program Files\QODBC Driver for QuickBooks\sample04.qbw;SERVER=QODBC;UseDCOM=Y; OptimizerDBFolder=C:\Program Files\QODBC Driver for QuickBooks\Optimizer; OptimizerAllowDirtyReads=Y'
EXEC sp_dropserver @server = N'QODBC'
These stored procedures also allow you to specify the location of the OptimizerDBFolder.
|
|
|
|
|
Posted : 2008-07-11 02:20:51 |
I have created my linked table using the instructions here and not using the Remote Connector option. My Linked server works great when I connect to my SQL Server 2005 using Windows Authentication from the domain Administrator account. from any machine on the network. Any of the queries listed here works. However, if I connect to that SQL server from another domain account, even though the other account has Domain Admin and Administrator permissions the query hangs indefinately without returning an error. What configuration changes would allow me to perform queries to my linked server from accounts? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-07-11 09:43:37 |
Try looking at the last post in: Permissions Issue with IIS using AUTHENTICATED USERS for further clues. Let me know what works. You may need to create a new System DSN that includes an optimizer file off the root of your C: drive to successfully use the QODBC Optimizer with MS SQL Server 2005. |
|
|
|