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