Knowledgebase: Connection
[QODBC-Desktop] Accessing QuickBooks Data remotely via SQL Server
Posted by Jack - QODBC Support on 19 August 2016 08:06 AM

Accessing QuickBooks Data remotely via SQL Server

First, you need to create Linked Server in SQL Server.

For 32-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote.

For 64-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote.

 

I have already created a linked server, "QRemote."

After creating Linked Server, You need to create a new database in SQL Server for creating the stored procedure which accepts SQL queries. The stored procedure will call the linked server and execute the query.

To create a database, You need to right-click on "Database" & click on "New Database."

Enter the database name & click "OK." In this example, I am creating the database "QODBC."

Database created.

Now, We will create a stored procedure that accepts SQL queries. The stored procedure will call the linked server and execute the query.

In Object Explorer, connect to an instance of Database Engine and then expand that instance.

Expand Databases, expand Your database, and then expand Programmability.

Right-click Stored Procedures, and then click New Stored Procedure.

 

We will create two stored procedures first one will give us a list of all customers & second procedure will accept SQL queries through parameters & execute them on the linked server & provide the query result.

We will create the stored procedure named GetCustomer, which will query the Customer table through the linked table in SQL Server & provide a list of the customers.

Stored Procedure Created.

Stored Procedure Code:

================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetCustomer

AS
BEGIN
SET NOCOUNT ON;

SELECT * from QRemote...customer

END
GO
================================================================

Note: The above-stored procedure code is, for example. You can change it as per your requirement.

By executing this stored procedure, we will get a list of the Customers.

We will create a second stored procedure named QODBCQuery which will accept SQL query through parameters & execute on the linked server & provide the query result.

Stored Procedure Created.

Stored Procedure Code:

================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE QODBCQuery

@sqlQuery nvarchar( 4000)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @TSQL varchar(8000)
SELECT @TSQL = 'SELECT * FROM OPENQUERY(QRemote,''' + @sqlQuery + ''')'
EXEC (@TSQL)

END
GO
================================================================

Note: The above-stored procedure code is, for example. You can change it as per your requirement.

We must pass the query as the parameter value to execute this stored procedure.

Query results through the stored procedure.



Now, We will create SQL Server DSN on the non-SQL Server machine to access QuickBooks Data remotely via SQL Server.

Locate the QODBC Driver for the QuickBooks program group on your desktop. Click on the Configure QODBC Data Source icon to create SQL Server DSN.

Click on the System DSN tab to create a DSN that both Windows applications and services can call. Click on the Add button to create a new DSN.

Select SQL Server & click "Finish."

Enter the DSN name and SQL Server machine IP address & click "Next."

Select the authenticity of SQL Server & click "Next."

Select the SQL Server database where we have created the stored procedure & click "Next."

Click "Finish."

Click on "Test Data Source.." to test the connection.

Test connection success.



Note: If you are using the latest version of QODBC, you can use the "QODBC 64-Bit Test tool"

Open the "QODBC 64-Bit Test tool" from the start menu

Click on the "Drop-down Menu" button

Select DSN from the Drop-down list

For example: "QuickBooks Data 64-Bit QRemote."

Click on the "Connect" button.

Write down the query in the marked field. Please refer to the image

Click on the "Run" button

Note: The above Query is, for example. You can change it as per your requirement.

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).