Is there way I can run certain SQL Scripts based on a results from a SQL Query?
My situation: I need to retrieve the current version of the database. Currently this is stored in a table I have created. Based on that result, I need to decide whether or not to run the remaining SQL scripts in the SQL Scripts view. This is kind of a upgrade process for the database. Is this possible or do I just need to create 1 big script that does everything at once - basically a script with several conditional statements?
I've looked briefly into specifying the version field for each SQL Script which creates a table in the database that stores that version. The problem is that, this install is a new installation package and that table will not be present and I must use the existing tables.
Any suggestions for this would be greatly appreciated.
I solved it by making a dll that checked our database version and stores the value in a property.
The dll does the following.
1. Check whatever the database exists. 2. If it exists, Check whatever it is empty 3. If it is not empty, Check version in version table.
We have made it that way to solve the following problem.
1. We want to run the create db scripts, if it dosn't exists. 2. If a dba already have set up the database in a special way we only want to run the create table etc. scripts. 3. If the tables exists it must be a update and we run only the update scripts that have a higher version number than the database we are look at.
Whatever this is the common way of doing it, I don't know, but it worked for us.
Hope this gives you some inspiration on how you could solve your problem.
What did you right this dll in - .NET? and how did you call the dll and where did you put it? I'm guessing Custom Actions somewhere? Le tme know and I think that might be the answer.
