System Environment

Window XP Service Pack 2     

QuickBooks Enterpriase Solutions 9.0 (USA Version)

Microsoft SQL Server 2005 Development

(Microsoft SQL Server 2008 Express)

QODBC 9.00.00.261 Version

Instruction

     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.

     To create a link server with MS SQL 2005/2008, there are three main steps:

     1. Create a new QuickBooks Data Source and connect to QuickBooks.

     2. Create a link server with MS SQL 2005/2008

     3. Execute test query using created link server.

[Note: During out test, it shows that the Link Server failed be created successfully with the MS SQL Express Edition of MS SQL Server 2005 but works fine with MS SQL Server 2008 Express Edition. And in MS SQL Server 2005 Development or Enterprise version it works fine. ]

The process with MS SQL 2005 Dev and MS SQL 2008 Express is almost the same. Below we take MS SQL 2005 Dev as an example to show you the steps:

Create a new data source and connect to QuickBooks

Create a new QuickBooks Data Source

1. Run Configure QODBC Data Source

   Start -> All Programs -> QODBC Driver for use with QuickBooks -> Configure QODBC Data source

2. Add a new system DSN with QODBC Driver for QuickBooks

3. Configure the source company file

    Now the QODBC setup screen will become available. First, name this new DSN as 'SLSTest'. Then select 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”. [Note: 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 Browse button will let you choose your QuickBooks company file from the folder it is in.

4. Configure the QODBC basic setup for using DCOM Server

    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.

5. Configure the Optimizer Setup of QODBC

    Select Dirty Reads option and check Optimize data after an Insert or Update at the same time (or select Real Time option and check Optimize data after an Insert or Update at the same time). These setups relate to the execution of UPDATE query using link server and more details will be recorded below in this article.

Test Connection to QuickBooks

1. Test Connection to QuickBooks

    After Apply all settings of the new DSN, press Test Connection to QuickBooks button. [Note: QuickBooks must be run before and start as Administrator.]

2. Application Certificate

    The first time you connect to QuickBooks using the new created DSN, you need to finish the application certificate process like below:

3. 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 Integrated Applications from the left side.

   Select the Company Preferences tab and then highlight one application (usually FLEXquarters QODBC) and press the Properties button. Check Allow this application to read and modify this company file and Allow this application to login automatically. And you will need to repeat this for all the applications on the list that you use.

4. Exit QuickBooks

Create Link Server with MS SQL 2005/2008

Run MS SQL 2005 Dev / 2008 Express and Login in

    Run SQL Server Management Studio, you'd better choose the SQL Server Authentication and then input right name password.

Create Link Server

1. When connect successfully, select Server Objects -> Linked Servers and then right click and select New Linked Server...

2. The following screen shot below shows the general settings of a simple Link Server setup for QODBC. The data source SLSTest has been created before.

3. The basic security settings

4. The basic Server settings. Press OK after applying all settings.

5. Configure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers)

   [Note: MS SQL Express Edition do not have this setup options!!! ]
   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.

   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.

  Now you can see QODBC link server displayed at left side.

Execute Test Query using created link server

Execute Test Query

1. Press New Query in MS SQL Server Management Studio Express, input test query on the right side and then click Execute button.

2. The query results are displayed as follows:

Some examples based on 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')

Different UPDATE queries with Different QODBC Setup Settings

   There are two ways to UPDATE data as below:

   (1) Using OPENQUERY:  

        UPDATE OPENQUERY(QODBC, 'SELECT "Name", "CompanyName" FROM Vendor WHERE "Name" = ''Bayshore Water''') SET "CompanyName" = 'Bayshore Water, LLC'

   (2) Update directly:

        UPDATE QODBC...Vendor SET "CompanyName" = 'Bayshore Water, LLC' WHERE "Name" = 'Bayshore Water'

   With the QODBC Optimizer Setup: Select Dirty Reads option or Real Time option (which performs slower than Dirty Reads) and check Optimize data after an Insert or Update , both kinds of UPDATE query can execute successfully.

   With the QODBC Optimizer Setup: Select Null option and uncheck Optimize data after an Insert or Update, only OPENQUERY Update can execute correctly.

About MS SQL Server Services Run as the Build-in System Account

   The MS SQL Server services running as the build-in system account is strongly suggested when you configure your Properties settings about SQL Server. During our test, the link server sometimes fails to work when log on as a specified user account. To configure the build-in system account settings, please follow the steps as below:

1. Start -> Run -> Input the following command: %SystemRoot%\system32\services.msc /s to start MS SQL Services.

2. Double click SQL Server (MSSQLSERVER)

3. Select the Log On Tab, then select Local System account option and press Apply to finish the configuration.

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" = 'Succes sfully Created'
END

EXEC @iHR = sp_OADestroy @oQBXMLrp
SET @iCnt = @iCnt + 1
END