SQL Server and QODBC
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'
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
SELECT "Object" = @sObjectName, "Function" = 'CreateObject', "HR" = convert(varbinary(4),0), "Source" = '', "Description" = 'Successfully Created'
EXEC @iHR = sp_OADestroy @oQBXMLrp
SET @iCnt = @iCnt + 1
So isn't it time you unleashed the full power of QODBC?
Our web version of QODBC will allow you to use any web tool you wish to use (ASP, ADO, Cold Fusion) to access data live from QuickBooks without conversion.
Download and Activate QODBC Web Server Edition
Did you find the answer you were looking for on this page? If not, please Contact Us. We are happy to help you find the solution you are looking for. Thanks!
FLEXquarters North America Sales
All rights reserved
QuickBooks and the Gold and Silver Developer Logos are trademarks and/or registered trademarks of Intuit Inc., displayed with permission. What is RSS?