VB.NET project not running SQL script through resources on first attempt.

LIBRALEX
LIBRALEX used Ask the Experts™
on
Using visual studio 2017, and a VB.net project, we use the resources.resx to execute SQL queries to update the live databases of the clients using our project (We add .txt files with queries in, and then run and update the database). The database is MS SQL2017.

I have added a query that will not run the first time I execute it (for example when I first install the program and all queries have to run), but if I go and manually update the "database version" in the project to not include the latest failed script but rather shows the previous one, it then runs fine. Meaning anyone updating to the newest version does not miss the script, but any new installations does and needs to then be run manually.

The script is simple :  

USE [Vetmaster]
GO

      UPDATE [dbo].[Role]
      SET [Admin] = 0
      GO
      
      INSERT INTO [dbo].[ScriptVersion]
           ([Version])
VALUES ('DB2.0.1.96')
GO

The ScriptVersion is just a table we created to make sure the scripts complete, and if for any reason it does not completely run, that version does not get written into the table.

Why would the code not run on the first attempt but run fine during a second attempt or update?
Please let me know if any more information is needed.

regards,
Louis.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
How are you running the script? What event triggers it?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
The GO statements are for SSMS only. I would remove the USE statement and use full path:

      UPDATE [Vetmaster].[dbo].[Role]
      SET [Admin] = 0
      GO
      
      INSERT INTO [Vetmaster].[dbo].[ScriptVersion]
           ([Version])
VALUES ('DB2.0.1.96')

Open in new window

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
and also you might run for trouble if 2 persons start the app at the very same time.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
All users must be logged out of the program, so that there is no other connection to the database. This initializes the update of the first computer (normally the server). once the update has run, the first time the client logs in, the script is triggered. During the login process, the project checks the database for the latest script that was run, and if there is a newer one (the resource DB2.0.1.96.txt), then the query within the DB2.0.1.96.txt is run before they can login. Because of this system, we will not run into 2 computers initializing the script at the same time, as other computers can only update once the first computer has completely updated.

As for the suggested change, I implemented it, and it still failed on the first login. Once I went back and changed the database version manually to DB2.0.1.95 (so that 96 will run again) and log in again, the script runs without any problems. Please see the attached error message when loggin in for the first time.

I cannot seem to find anything in any previous scripts that would have an effect on the last one. I still dont understand why it will run fine the second time but not the first.
Image.png
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you will need to output the InnerException as well in your message box because currently we just cannot guess what the error is.
ste5anSenior Developer

Commented:
In addition to Eric's point:

You need to decide, which changes must be run as a batch, covered by a single transaction and which must be run independently.

Then you should create one file (resource) per batch. And you execute on file (resource) after another.
Also USE should/must be avoided here. Connect explicitly to the correct database to run those scripts.

Also you should show us your updater code. Remember it must be bulletproof, thus have complete error handling in your application as well as the correct settings for the connection. Furthermore you should log everything during the update process. So that there is more information than your simple dialog.
Thank you very much for all the help and suggestions.  After using the inner exception, I realized that it was a trigger that was causing the error. We have triggers in place to capture all insert, update and deletes that occur on the database. This table was changed a few scripts back (one of the recourse texts) and I believe that the update_trigger had not yet updated itself before my new script was running. meaning I was inserting into a table, data that no longer had the same name.

After dropping and recreating this trigger to make sure it is up to date, the scripts ran without issues. This is also why the script ran the second time, but never on the first install.

I do not know if there is a way to refresh the trigger before I run my script (a quick google search found nothing helpful), so I opted for the drop and recreate as I was on a deadline.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial