Having trouble logging in or locating tickets ?


 

How to create Link Server with MS SQL 2005/2008
Posted by Juliet on 27 November 2009 07:57 AM

System Environment

Window XP Service Pack 2     

Window 7 -32 Bit  

Window 7 -64 Bit

QuickBooks Enterprise Solutions 9.0 (USA Version)

Microsoft SQL Server 2005 Development -32 Bit

(Microsoft SQL Server 2008 Express)-32 Bit

QODBC 9.00.00.261 Version

 

Refer : DCom Option / Server Edition Tab missing in QODBC Setup Tab

We recommend to use QRemote

For SQL Server 64 Bit Click Here "How to create Link Server with MS SQL 2005/2008/2012 64-Bit Using QODBC/QRemote"

For SQL Server 32 Bit Click Here "How to create Link Server with MS SQL 2005/2008/2012 32-Bit Using QODBC/QRemote"

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 our 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.

   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: IIS users download: TestDCOM.ASP    PHP users download: TestDCOM.PHP    Cold Fusion users download: TestDCOM.CFM

 

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

(617 vote(s))
This article was helpful
This article was not helpful

Comments (6)
Karla
20 September 2012 07:55 AM
The SQL standard names are LOWER and UPPER, not LCASE and UCASE. Some prdctous like MySQL alias LCASE and UCASE to the LOWER and UPPER functions for increased compatibility with other non-standard prdctous and some prdctous that are not databases. MS Access uses LCASE and UCASE as does the non-database prdctous Excel and OOCalc. There are some programming languages which use LCASE and UCASE. There may be other DB prdctous that do not use the SQL standard LOWER/UPPER names for these functions. Oracle does use LOWER/UPPER. DB2 supports both. PostgreSQL uses LOWER/UPPER.
Raja
20 September 2012 11:58 AM
You teach sql and in simple mnrneas. I thought that sql is very diffiult language but after reading this post i have understand the functions of NOW() and getdate() easily. thanks for sharing.Alizya recently posted..
Thanarut
20 September 2012 03:28 PM
Great cheat sheet you have put together here. Sometimes it is more hflpeul to new SQL users to see an explanation in this format than reading the books online pages. Well done.
Manuel
20 September 2012 03:32 PM
looks like it has plenty of room for afolirw too thanks to a big case and mini motherboard. makes me wish I was at a point in things to build one myself.
Mehul
27 October 2014 09:40 AM
How can I take a backup of existing quickbook database which is attached as linked server in sql server?
Jack
28 October 2014 01:02 PM
Hi Mehul,

I would like to inform you that there is no physical database attached in SQL Linked Server. There is only link between QuickBooks Data & SQL Server via QODBC. So you cannot take backup of Linked Server.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below. This is required to prevent automated registrations and form submissions.

Help Desk Software by Kayako Resolve