sql script file to execute as part of the installation.

[steve1rm]

Hello

I have a script that is for updating the database schema that won't destroy the data. So most of the script is for dropping and creating new objects.

One thing that is confusing me is how can I make it part of the installation? and how will the program call it so that the script can be executed, and update the SQL Server 2005?

Before I used to update the database by having all my sql in a module. Then I called that module to update the database based on a value that keeps track of the database version.

The module would contain many sql statements like the one below. There is slightly more to this on how it works, but I am sure you get the idea.
Code:

executeSQL("DECLARE @SQL NVARCHAR(1000) IF EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RefComponentsTypes' AND COLUMN_NAME = 'AssemblyID') BEGIN SET @SQL = 'ALTER TABLE RefComponentsTypes DROP COLUMN AssemblyID' EXEC sp_executeSQL @SQL END", True)

However, the database is becoming to complex to update the schema using the above method so I have decided to use a tool called DB synchroComp that compares the 2 database and generates a script file. That can be run on the target database to update the schema without destroying the database.

After using DB SynchroComp, I would recommend this tool for comparing and generating a script, it is a most excellent tool. And is free.

So my main problem is, how can the script file be part of the installation, how can I get my program to run it so it will update the sql server?

Thanks once again,

Steve

 

[AlexCuse]

What kind of script file does this tool generate? You might be able to read the generated file into your app as a string, and then execute it through any one of the available sql tools.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom