I am using MS SQL 2000, Quick Books Account Edition 2006, QODBC V 7.00.00.207. I have set up QODBC as a Linked Server in MS SQL. I am trying to insert PO and SO records from SQL into QuickBooks. I have some success inserting records into QB using the following insert statement, but when I try to automate it using an SQL trigger with the same statement it does not work and I get an error. I need to finish this project soon and could use any help availiable. I am willing to pay for Custom programming just need an estimate on hourly rate and estimated hours to complete it. Here are the statements I used for Purchase Orders.

WORKED

DECLARE @PoLineNo int
DECLARE @Status varchar(50)
DECLARE @PONo varchar(13)
DECLARE @POID int
DECLARE @QBInsert varchar(3)
DECLARE @SupplierID int
DECLARE @PoLineNoMax int
DECLARE @SupplierListID varchar(36)
DECLARE @Description1 varchar(100)
DECLARE @QtyOrdered int
DECLARE @QtyReceived int
DECLARE @PricePerUnit money
DECLARE @TotalPrice money
DECLARE @PODetailsListID varchar(36)
SELECT @Status = status, @PONo = PONo, @POID = POID, @QBInsert = QBInsert, @SupplierID = SupplierID FROM purchaseOrders WHERE poid = 10040
SELECT @SupplierListID = ListID FROM Supplier WHERE SupplierID = @SupplierID
SELECT @PoLineNoMax = MAX(PoLineNo) FROM PODetails WHERE POID = 10040
SELECT @PODetailsListID = ListID, @Description1 = Description1, @QtyOrdered = QtyOrdered, @PricePerUnit = PricePerUnit, @TotalPrice = TotalPrice, @POLineNo = POLineNo FROM PODetails WHERE POID = 10040
WHILE @PoLineNoMax >= @PoLineNo
BEGIN
 IF @Status = 'Received' AND @QBInsert = 'No'
   SET @PoLineNo = @PoLineNo + 1
   insert openquery(QODBC, 'select "VendorRefListID", "RefNumber",
   "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
   "PurchaseOrderLineQuantity",
    "PurchaseOrderLineRate", "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
   "FQSaveToCache" from PurchaseOrderLine Where 1=0')
   VALUES (@SupplierListID, @PONo, @PODetailsListID,
   @Description1,@QtyOrdered, @PricePerUnit,@TotalPrice, '2F60000-1151552070', 1)
END
BEGIN
 IF @Status = 'Received' AND @QBInsert = 'No'
  INSERT OPENQUERY(QODBC, 'SELECT "VendorRefListID", "RefNumber", "Memo",
 "IsToBePrinted" FROM PurchaseOrder WHERE 1=0')
 VALUES (@SupplierListID, @PONo,'Web based Purchase Order', 0)

DOES NOT WORK:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--Insert PO into Quickbooks thru QODBC
CREATE               TRIGGER InsertPOAndItemsintoQB
ON CPF.dbo.PurchaseOrders
FOR UPDATE
AS
IF UPDATE(status)
DECLARE @PoLineNo int
DECLARE @Status varchar(50)
DECLARE @PONo varchar(13)
DECLARE @POID int
DECLARE @QBInsert varchar(3)
DECLARE @SupplierID int
DECLARE @PoLineNoMax int
DECLARE @SupplierListID varchar(36)
DECLARE @Description1 varchar(100)
DECLARE @QtyOrdered int
DECLARE @QtyReceived int
DECLARE @PricePerUnit money
DECLARE @TotalPrice money
DECLARE @PODetailsListID varchar(36)
SELECT @Status = status, @PONo = PONo, @POID = POID, @QBInsert = QBInsert, @SupplierID = SupplierID FROM purchaseOrders WHERE poid = 10040
SELECT @SupplierListID = ListID FROM Supplier WHERE SupplierID = @SupplierID
SELECT @PoLineNoMax = MAX(PoLineNo) FROM PODetails WHERE POID = 10040
SELECT @PODetailsListID = ListID, @Description1 = Description1, @QtyOrdered = QtyOrdered, @PricePerUnit = PricePerUnit, @TotalPrice = TotalPrice, @POLineNo = POLineNo FROM PODetails WHERE POID = 10040
WHILE @PoLineNoMax >= @PoLineNo
BEGIN
 IF @Status = 'Received' AND @QBInsert = 'No'
   SET @PoLineNo = @PoLineNo + 1
   insert openquery(QODBC, 'select "VendorRefListID", "RefNumber",
   "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
   "PurchaseOrderLineQuantity",
    "PurchaseOrderLineRate", "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
   "FQSaveToCache" from PurchaseOrderLine Where 1=0')
   VALUES (@SupplierListID, @PONo, @PODetailsListID,
   @Description1,@QtyOrdered, @PricePerUnit,@TotalPrice, '2F60000-1151552070', 1)
END
BEGIN
 IF @Status = 'Received' AND @QBInsert = 'No'
  INSERT OPENQUERY(QODBC, 'SELECT "VendorRefListID", "RefNumber", "Memo",
 "IsToBePrinted" FROM PurchaseOrder WHERE 1=0')
 VALUES (@SupplierListID, @PONo,'Web based Purchase Order', 0)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The error I get is: Database error: '[Microsoft][ODBC SQL Driver][SQL Server][OLE/DB provider return message: [QODBC] Driver not Capable]...'MSDASQL' ITransactionJoi Join Transaction returned 0x8004d00a...'MSDASQL' was unable to begin a distributed transaction.'

Any help would be GREATLY APPRECIATED.

Thanks in advance.

James

The QODBC linked tables only appear as a local database and therefore can't be run as a distributed transaction from another location, must be a local transaction on the local database. Are you using the QODBC Server Edition and DCOMs or the QODBC Remote Connector?

Most "Driver not Capable" errors arise from using the QODBC Remote Connector.

I am running the triggers on the same server as Quickbooks is on, so I am not sure why they would not be local db's to each other. I am have looked at the posts talking about DCOM and remote connector. I have set the driver to use DCOM and it basically shut down all of my queries, so I set it back. I have not done anything with the remote connector. I am running QODBC Server Edition 2007 V7.00.00.214. All of my transactions will be on a single server.

Thanks for you help.

My apologies for being unclear. I not using either DCOM or Remote connector at least that I am aware of. I tried changing the setting in the QODBC configuration to use DCOM server and it did not work when I ran simple queries so I set it back. If I understand what you are saying the errors that I am seeing typically are linked to using DCOM or Remote Connector, correct? I will look at my configuration closer to see of I am inadvertantly using them. Have you used SQL triggers to insert data? If so could you send me a sample of your Trigger.

Thanks

James 

Thanks for the info, I went thru this procedure once and had very limited success. I will go thru it again and attempt to set it up correctly. Is there some support from QODBC folks, that I could purchase, who can set this up correctly? I know that I spent a number of hours trying to get the DCOM setup to test out correctly a couple of months ago and could never get it to test correctly.

Thanks