Power Query that executes insert records scripts into Pervasive table insert multiple records for each insert script @SQL @Pervasive

Question

Power Query that executes insert records scripts into Pervasive table insert multiple records for each insert script @SQL @Pervasive

Anonymous

I have an Excel "application" which is designed to maintain a table in a Pervasive database. It is supposed to use Power Query to insert records and it does perform that function. The problem is that it executes a single insert script multiple times and so I end up with duplicate records in my table. If I create a key field which would prevent that then the Power Query will execute the insert and then generate a SQL error stating there is a duplicate key; so the initial insert execution worked fine and the duplicates got blocked. If I wanted to insert 10 new records then all 10 would get inserted before the SQL error get generated. So, at the end of the day if my table has a key which prevents duplicate records then the process works but I do not like that I getting an error and want to prevent that.

Additional information:

In order to execute an SQL update script then the code has to have two components:

The SQL scripts separated by a semicolon.
The last piece of script must generate a return results, like a date.

example:

Insert into CDI_Cust_Attributes_text Values ('HON004','000000','','','','','','','','2021-07-12','SUPERMM','','000000');

Insert into CDI_Cust_Attributes_text Values ('HON004','000001','','','','','','','','2021-07-12','SUPERMM','','000001');

Select Now() as Last_Update ;

"Now()" will return the current date & time. and that's the result you will see in the Power Query once it has executed all the scripts.

If you do no include the Now() or some other select script then you will get an error:

ERROR No 1: [Expression.Error] This native day=database query isn't currently supported.

The above example worked perfectly on a table that allows duplicate records but you will end up with 2 to 4 times the number of records you intended to insert, and you get an error indicating that something went wrong.

If your table does not allow duplicated then you will get this error:

ERROR no 2: [DataSource.Error] ODBC: ERROR [HY000] [PSQL][OBDC Client Interface][LNA][PSQL][SQL Engine][Data Record Manager]The record has a key field containing a duplicate value(Btrieve Error 5)

But you will get the correct number of records inserted. The "Select Now() as Last_Updated" script does not get executed and you get an error indicating something went wrong.

Possible solutions:

Stop Power Query from executing scripts multiple times, thus preventing ERROR No 2 or getting duplicate records inserted.
Turn off the reporting of errors in Power Query. This is certainly not the best option but it is better then getting erroneous errors.
Come up with another solution that can execute SQL Scripts from Excel over an ODBC connection into a Pervasive database.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

Hi Mike

This is the kind of request that would be better raised on the MS Q&A forum (where almost nobody contributes to PQ cases for weeks now...). Alternatively you could post on the PowerBi PQ Community

You'll probably be disappointed reading Can You Use Power Query In Power BI Or Excel To Write Data To A Data Source? but that's the official position and this is consistent with what you experience (that was also mentioned by ImkeF in this thread)

No idea how your Excel "application" works. Is VBA involved/can be involved? If so, you might have an opportunity using the legacy approach***** (so no going through Power Query). I don't have my SQL Server next to me these days so can't test this I'm afraid

* If you see what I mean otherwise let me know

Hope this helps, a bit...

0 comments

2 additional answers

Answer 1

Hi Mike

I'm not a SQL expert but usually find my way :) Given the "limitation" imposed by PQ with regard to what you need/want to do the workaround - with the Stored Proc. - you put in place looks to me as a pretty good solution as it doesn't require too much change to your "application"

Glad I could help & Thanks for posting back

Take care...

Answer 2

You were correct, Lz, I am disappointed. I've been using it PQ to update and insert records for quote a while with two other 'applications' and it has worked perfectly - no dups and no errors. just this one has the problems. This is really odd. Wonder why MS deliberately causes issues; clearly they know how to make it work correctly.

Yes, VB is involved, but all it does is refresh the PQ.

This 'application' is simple. It allows the user to maintain a table in a Pervasive database. There are two sheets which query the same table. The 2nd sheet has formulas which compares the two. When the user makes changes to the data then the formula generates an update script, which are then texjoined together and then executed in the PQ. It works perfectly so long as the user does exceed the number of characters that a cell can hold (textjoined scripts), which is about 250 updates. These work perfectly because it doesn't matter if the engine executed an update 10 times; it may take a little longer but the result is exactly the same.

As for the Inserts, the 2nd query uses another table as a primary so that when it have more records than the table we are maintaining then they will appear in the 2nd query results. The same formulas that detects changes can detect new records as well and constructs an insert script , textjoined and then executed in the PQ. I just figured out a work-around. I will insert these new records into a duplicate table, except this one allows duplicate records. Then it will execute a SQL stored procedure which then inserts those records into the tabble we are maintaining,

Like this: Insert into CDI_Cust_Attributes_text

                 *select distinct \* from CDI\_Cust\_Attributes\_dups ;* 

             *delete CDI\_Cust\_Attributes\_dups where Customer\_ID is not null ;*

The tables are identical, except CDI_Cust_Attributes_dups allows dups and CDI_Cust_Attributes_text does not.

NOTE: This did not prevent the error so I added into the VB "ON ERROR RESUME NEXT". I know, this is not recommended but it does accomplish the object.

Another 'application' I have which works perfectly uses PQ to query a website containing currently exchange rates; the results appear in a sheet then a formula generates insert scripts for each currency into a temp table; they are texjoined and then executed with PQ; next the PQ execute a stored procedure which executes an update script for the matching currencies we have identified in our ERP which are in another table; then the temp table records are deleted. It works perfectly.

I have two more which writes records to tables but they do not bring the preliminary results excel, only the final results of all the work.

Your reply was helpful; it means I have not done something wrong and that there is no solution other than the work around I stated above. I couldn't quite tell from the threads if there are available add-ins which allow me to accomplish my objectives a little easier or not.

Share via

Power Query that executes insert records scripts into Pervasive table insert multiple records for each insert script @SQL @Pervasive

2 additional answers