QODBC Tutorial for SQL Server

Creating a linked table in SQL Server

Running QODBC as a link server requires the Web Server Edition of QODBC and assumes QuickBooks running on the system console. The Web 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 must create a system DSN, by default the DSN "QuickBooks Data" is created for you, 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 screen shot below shows a simple Link Server setup:

Run a query. Below are a few examples based a Link Server named "QODBC":

    SELECT * FROM OPENQUERY(QODBC, 'SELECT * FROM Host')
    SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Employee')
    SELECT * FROM OPENQUERY(QODBC, 'SELECT ListID, Name FROM Customer')

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 the web site for the Web Server Edition if you have difficulty with DCOM configuration.

Test DCOM Script:

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

SQL Server and QODBC