July 17, 2003 at 12:19 pm
I need to develop a solution for automating our upgrade scripts for customer databases. The problem I am running into is that we have a hosting company host our SQL Server databases and they do not allow us access to the command line or xp_cmdshell.
Not all of the databases are at the same version.
Normally I would write a script that would check the database version and if it was the correct version run the appropriate upgrade script. Since I don't have access to xp_cmdshell to launch osql with the upgrade script is there another way to do this?
Shane Delmore
Clinicient
Shane
July 17, 2003 at 12:52 pm
Are you looking for SQL Server version or database version?
select @@version will give server information.
SELECT DATABASEPROPERTY('yourdbname', 'Version') will show the database version.
quote:
Since I don't have access to xp_cmdshell to launch osql with the upgrade script is there another way to do this?
Why do you need xp_cmdshell to launch osql.exe? Create a batch file to run osql that executes your upgrade script.
July 17, 2003 at 1:12 pm
I'm actually looking for a version number we keep in a user table that tracks the version of the database, we update it when we have to make schema changes so that our front end application can verify it is connecting to a compatible version of the database.
I had considered a batch file but it would need to connect to each database first and check the current schema version to determine which upgrade script to run.
Shane Delmore
Clinicient
Shane
July 17, 2003 at 1:27 pm
quote:
I had considered a batch file but it would need to connect to each database first and check the current schema version to determine which upgrade script to run.
I don't understand why you can not check it in your sql script.
July 17, 2003 at 2:33 pm
I have a pretty good idea what you're after.
I guess you make one big, giant T-SQL script to upgrade your database from one version to the other. (We do that too!)
And I guess you're problem is not getting the version from your User table (that should be trivial in your T-SQL statement), but the fact that you don't have the possibility to launch a T-SQL script file.
Do you think a DTS job might do the trick? You could write some queries to check your version. Then, use a ActiveX Script task to update the Execute T-SQL task with the correct version of the script from file (you have uploaded before, of course).
Haven't tried or thought about this, but I think you might be able to pull it off.
July 17, 2003 at 3:14 pm
You've hit the nail on the head. We collect all of the upgrade scripts and merge them into one large upgrade script when we actually deploy. Is there a better way of doing this?
The main reason I want to automate it is to reduce the chance of errors and also to save time as currently I am trying to run 50 scripts as quickly as possible at midnight during our maintenance window. I hadn't even thought to try DTS packages, I'll check and see if we have access to them.
Shane Delmore
Clinicient
Shane
July 18, 2003 at 2:13 am
Another way might be to have an 'UpdateScripts' table that stores your script.
You can upload your script using a webinterface and store it in SQL Server.
Then, you run a job that checks the table for new updates or checks the user databases for old versions. If you find one, you load the update script and execute it with EXEC @UpdateScript.
Keep in mind that a string variable is limited to 8000 bytes (so 8000 or 4000 characters, depending if you're using multibyte). You can concatenate them like 'EXEC @string1 + @string2 + ...'. So you might need to do some processing and stuff.
July 18, 2003 at 6:51 am
quote:
We collect all of the upgrade scripts and merge them into one large upgrade script when we actually deploy.
How large (MB) your upgrade script is? I am not aware there is a limitation in srcipt size for OSQL to execute.
July 18, 2003 at 10:01 am
The update script is large but the issue has nothing to do with it being too large for OSQL, the issue was that I wanted to write a script that would automatically launch osql and the proper additional scripts or do something similar to that so I could write a script, press run and be done with it.
What I ended up doing seems to work allright, although it is a two step process. I wrote a script that loops through my databases and creates the appropriate osql command lines for the upgrades, then I copy the output into a batch file and run it. I actually have access to osql, I just don't have access to xp_cmdshell so I can't automate the process of launching the additional scripts.
Shane Delmore
Clinicient
Shane
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply