We just purchased QODBC in order to update price level lists through SQL server. I have setup DCOM access and have QODBC as a linked server on my SQL 2005 server.

I first tried inserting from a local table by using

INSERT INTO pricelevelperitem ( ListID, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice )
SELECT '80000003-1226590291' AS ListID, wholesale.ListID, wholesale.Price
FROM wholesale;

and when that failed, I switched to listing out the inserts

INSERT INTO QODBC...PriceLevelPerItem (ListId,PriceLevelPerItemItemRefListId,PriceLevelPerItemCustomPrice) Values ('80000003-1226972251','80000FDD-1226929092',39);
INSERT INTO QODBC...PriceLevelPerItem (ListId,PriceLevelPerItemItemRefListId,PriceLevelPerItemCustomPrice) Values ('80000003-1226972251','80000FDE-1226929093',29);
INSERT INTO QODBC...PriceLevelPerItem (ListId,PriceLevelPerItemItemRefListId,PriceLevelPerItemCustomPrice) Values ('80000003-1226972251','80000FDF-1226929093',19);
INSERT INTO QODBC...PriceLevelPerItem (ListId,PriceLevelPerItemItemRefListId,PriceLevelPerItemCustomPrice) Values ('80000003-1226972251','80000FE0-1226929094',39);

It doesn't take long to get this error

[OLE/DB provider returned message: [QODBC] Incorrectly built XML from Update Start.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005:   ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. 

I need to import over 3000 custom prices and so far I have 16. Please help!

Thanks in advance.

Please see: PriceLevelPerItem Updates & Inserts

See also :PriceLevelPerItem: [QODBC] Incorrectly built XML from Update Start. and Speeding up PriceLevelPerItem INSERTS? 

Tom,

Thank you for your quick response. It seems I am formatting the insert statements correctly.

I tried a set of 10 inserts this morning and it ran fine, in about a minute and 27 seconds. Then I did a set of 20 and it ran for over 8 minutes. I know this is because of how the pricelevelperitem list works per the article on speeding up inserts. We are using MSSQL and cannot access the Stored Procs for batch inserts, what is the reccommended way to do this?

I tried using the VB Demp app but going line by line is not going to work when we have 3000 prices to insert.

I've switched to trying to do this through Access 2007 and I am getting this in the SDK log

20081118.123033 I 13892 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4756), ret = 1.
20081118.123033 E 4756 RequestProcessor Process Request error = 80042500, There was an error when parsing the provided XML file.
20081118.123033 I 13892 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4756).
20081118.123033 E 13892 XercesSAXErrorHandler Element 'CustomPrice' is not valid for content model: '(ItemRef,(CustomPrice|CustomPricePercent|(AdjustPercentage,AdjustRelativeTo)))' -- error at line 7, column 12404 in XML data.
20081118.123033 I 13892 MsgSetHandler Parser error, finishing.
20081118.123033 I 13892 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4756), ret = 1.
20081118.123033 E 4756 RequestProcessor Process Request error = 80042500, There was an error when parsing the provided XML file.
20081118.123033 I 13892 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 4756).
20081118.123034 E 13892 XercesSAXErrorHandler Element 'CustomPrice' is not valid for content model: '(ItemRef,(CustomPrice|CustomPricePercent|(AdjustPercentage,AdjustRelativeTo)))' -- error at line 7, column 12404 in XML data.
20081118.123034 I 13892 MsgSetHandler Parser error, finishing.
20081118.123034 I 13892 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 4756), ret = 1.
20081118.123034 E 4756 RequestProcessor Process Request error = 80042500, There was an error when parsing the provided XML file.

The query I'm trying to run is

INSERT INTO pricelevelperitem ( ListID, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice )
SELECT '80000003-1226972251' AS ListId, wholesale.ListID, wholesale.Price
FROM wholesale;

I'm sorry for posting a few times in a row, but I wanted to add that the XML log looks good up until this last entry which doesn't have an item ref

                <PriceLevelPerItem>
                    <ItemRef>
                        <ListID>8000022F-1210781401</ListID>
                        <FullName>BDPM-C-BLKT</FullName>
                    </ItemRef>
                    <CustomPrice>39</CustomPrice>
                </PriceLevelPerItem>
                <PriceLevelPerItem>
                    <ItemRef>
                        <ListID>80000230-1210792702</ListID>
                        <FullName>BTGR-C-BP-13PAI-B</FullName>
                    </ItemRef>
                    <CustomPrice>69</CustomPrice>
                </PriceLevelPerItem>
                <PriceLevelPerItem>
                    <CustomPrice>7.00000</CustomPrice>
                </PriceLevelPerItem>

Ok, I removed the rows that were invalid, and now the insert is running. It has been running for about 16 hours. If I peek at the driver status panel and then close it, it appears it is still working, so just some more waiting I guess.