Knowledgebase
[QODBC-Desktop] How to perform INSERTs, UPDATEs, and DELETEs using OPENQUERY with Linked MS SQL Servers
Posted by Juliet (QODBC Support) on 16 March 2010 08:35 AM

Note: OpenQuery requires a result set to be returned, but UPDATE, DELETE and INSERT statements used with OpenQuery and QODBC do not return a result set.

WORKAROUND

     You can work around this problem in the following two ways:

Use four-part names

     Can use four-part names (linked_server_name.catalog.schema.object_name) to do insert, update, or delete operations, such as:

     SELECT ListID, Name FROM QODBC...Customer

     Note: In this example, I'm only using the linked_server_name "QODBC," so it's followed with three full stops.

With the SQL Server OLE DB provider

     As documented in SQL Server Books Online, reference the OpenQuery function as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. The following queries demonstrate proper usage with the SQL Server OLE DB provider:

UPDATE OEPNQUERY

Update openquery(QODBC, 'select InvoiceLineDesc from InvoiceLine WHERE TxnID = ''6130-1197786604'' And InvoiceLineTxnLineID = ''7133-1197736106'' ') SET InvoiceLineDesc = 'New Desc 10001'

Note: There are two single quotes (') around the value (i.e. 6130-1197786604 & 7133-1197736106) not double quote ("). 

INSERT OPENQUERY

     Insert openquery(QODBC, 'select Name,LastName,FirstName,BillAddressAddr1,
BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalcode from customer where ListID=''0''') values ('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')

Example of creating a new Invoice

Insert openquery(QRemote, 'select CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID from InvoiceLine where TxnID=''0''') values ('670000-1071517519', '7047', '320000-1071525597', 'POWER TRAK-2000', 1, 130,'20000-999022286')

Example of adding InvoiceLine item to an existing Invoice

Insert openquery(QRemote, 'select TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnID=''0''') values ('1B308-1513325705','320000-1071525597', '88880DECA', 5,5)

OR

Insert into QRemote...InvoiceLine (TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('1B308-1513325705','320000-1071525597', '11440DECA', 5,5)

DELETE OPENQUERY

Delete openquery(QODBC, 'select ListID from customer where ListID = ''300000-1081400329'' ')

Note: There are two single quotes (') around the value (i.e., 300000-1081400329), not double quotes (").

Note: In the INSERT statement, a where should be either ListID =''0'' or TxnID=''0'' depending on your query field. The ListID =''0'' or TxnID=''0'' predicate is used to avoid retrieving data from the remote server, resulting in slower performance. Also, UPDATE and DELETE operations have special QODBC requirements.

See also:

How to create a Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote

How to create a Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote 

Problem Description:

I have a QODBC stored procedure that I have proved and tested in VBDemo. Now I need to call it from a SQL command while specifying the linked server. Can you provide an example SQL statement?

Solutions:

You can execute sp_report from SQL Server using the below sample query:

For Example:

select * from openquery (YourLinkedServerName, 'sp_report salesbyrepsummary show label, amount_1 date parameters DateMacro = ''Today''')

select * from openquery (QODBC, 'sp_report salesbyrepsummary show label, amount_1 date parameters DateMacro = ''Today''') 

select * from openquery (QODBC,'sp_report PurchaseByItemDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, SourceName_Title, Quantity_Title, UnitPrice_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, SourceName, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = ''ThisMonthToDate''')

select * from openquery (QODBC,'sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Amount, Amount_1_Title as "As of" parameters DateMacro = ''ThisMonthToDate'', SummarizeColumnsBy = ''TotalOnly'')

In the same way, you can run other reports. You can change parameters & report columns as per your requirements. 

Please refer:

How to use the QuickBooks Reporting Engine with QODBC

Please refer to the mentioned link to get details about the QODBC table & report schema. You will get information on each report column parameters which are supported: 

QODBC Data Layouts

 

Select

Select * from QRemote…Account

or

select * from OpenQuery (QRemote, 'select * from Account')

Note: If your SQL statement contains a where clause, use OpenQuery. When using OpenQuery 'where' clause (filters) will be sent to QODBC, and data retrieval will be faster.

Example:

select * from OpenQuery (QRemote, 'select * from Account where FullName=''MyAccount''')

 

Setup of Optimizer When Using OPENQUERY

     The QODBC optimizer is a single user only, so you need to set up a different optimizer file for the SQL Server to use than the other desktop applications. To do this, step up a separate System DSN (see: How do I create or configure a QODBC DSN?) and specify a fixed location of the optimizer database folder for SQL Server to use instead of the default %AppData% method.

 

Keywords: SQL server update table 

(165 vote(s))
Helpful
Not helpful

Comments (2)
Christine Mick
26 October 2015 04:02 PM
I tried the update as described above and instead of just one record getting updated...every record updated. You must put the where condition like this...

update openquery([QODBC], 'select SalesRepRefListID,SalesRepRefFullName,TxnID from Invoice WHERE TxnID = ''1111'' ') SET SalesRepRefListID='5555',SalesRepRefFullName='REP' WHERE TxnID = '1111'
Jack
27 October 2015 12:48 PM
Hi Christine,

I would like to share you that update query which is showing in above article will update single line records only. If all records are updated at your end, then I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.

1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).