[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 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 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 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(QODBC, 'select Name,LastName,FirstName,BillAddressAddr1,
Example of creating new Invoice
Insert openquery(QRemote, 'select CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID from InvoiceLine where TxnID=''0''') values ('670000-1071517519', '7047', '320000-1071525597', 'POWERTRAK-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)
Insert into QRemote...InvoiceLine (TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('1B308-1513325705','320000-1071525597', '11440DECA', 5,5)
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 should be either ListID =''0'' or TxnID=''0'' depending on the field the query you have used. 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.
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 (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'')
The same way you can run other reports. You can change parameters & report columns as per your requirements.
How to use the QuickBooks Reporting Engine with QODBC
Select * from QRemote…Account
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.
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 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 %AppData% method.
Keywords: SQL server update table