[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.
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 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 qoute (").
insert openquery(QODBC, 'select Name,LastName,FirstName,BillAddressAddr1,
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 qoute (").
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.
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?
You can execute sp_report from SQL Server using below sample query:
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'')
How to use the QuickBooks Reporting Engine with QODBC
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.