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

Note: OpenQuery requires a result set to be returned, but UPDATE, DELETE and INSERT statements that are 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 fullstops.

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 1=0') values ('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')

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 quote (").

    Note: In the INSERT statement, a where 1=0 predicate is used to avoid retrieving data from the remote server, which can result in slower performance. Also, UPDATE and DELETE operations have special QODBC requirements.

See also:

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

How to create 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 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'')



Same way you can run other reports. You can change parameter & report columns as per your requirements. 

Please refer:

How to use the QuickBooks Reporting Engine with QODBC

I kindly request you to please refer below mentioned link to get details about the QODBC table & report schema. You will get details about each report column, parameters which are supported:

 
QODBC Data Layouts

Setup of Optimizer When Using OPENQUERY

     The QODBC optimizer is single user only so you need to setup a different optimizer file for the SQL Server to use than the other desktops 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 %UserProfile% 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 enter the text you see in the image into the textbox below (we use this to prevent automated submissions).