Execute SQL on SQL Server

[ned_mcevoy]

Hi, this should be simple but im kinda new to this.

I'm trying to create a server side install that will create a database on a SQL server engine.

I have created a connection and a SQL Script to run on the database. But im not quite sure how to execute it on the database and for some reason im struggling to find it in the help.

Any suggestions would be great - im sure its not that difficult.

[Nick_Umanski]

I've just recently been struggling with the same thing, there is no useful documentation on the subject that I could find, but I've got it working so I'm sure I can help, only I'm not too sure what you are struggling with.

When you say you have created a SQL Script, do you mean you have added an entry in the SQL Scripts view under System Configuration? Or just created the script itself?

You need to do the former, right click and add a new SQL Connection (which I named "SQLServer"), then right click and add a new Script under that. This will add an entry called sqlscript.sql and create a blank file of the same name in some default directory, but then you can go to the general tab and browse to your created script and replace the default entry with that.

I see I've also got an ODBC entry - under Drivers & DSNs, the SQL Server checkbox is ticked - can't remember if I did that manually or it came about as a result of adding the SQLLogin dialog to the project, or whether this is completely unnecessary.

Repost any other questions if this doesn't help - I know this is frustrating, but it does all work beautifully once you have sorted the problems out.

Nick.

[TheTraveler]

I use ADO COM Objects in Install Shield Script to do anything I need with SQL Server. Here is a list of what I'm able to do.

1) Creating the database to any SQL Server on the network.
2) Create Database users on the SQL Server.
3) I can query the database for information.

And a lot more. Here is a link for an ADO Example.

[ned_mcevoy]

Thanks guys, I managed to get scripts running and youre right it does all work pretty well.

The only problem im finding now is that all the scripts run on installation and it is difficult to query the database and manage error messages properly during runtime

i.e checking if a database name already exists before creating it - assuming I dont want to go deleting databases willy nilly. This COM object thing looks like it might be a good solution but do you know if it is Version Independent - I want to run on SQL Server 2000, 2005 and 2008

And can these objects return results at runtime before executing the script on installation?

[TheTraveler]

Hello,

I'm currently running the the ADO COM objects versus SQL Server 2000 and 2005. I have not tried running it versus 2008 yet. That will definitely be on my to do list.

You Said, "And can these objects return results at runtime before executing the script on installation?"...

If you use ADO Objects, you will have to parse the SQL Script file into single SQL Statements. I use the "GO" statement as the SQL delimiter and store them in a String List structure. That way, I can use a loop to execute all the SQL commands. I even show a progress bar during the execution. Furthermore, if there is an error, I know what SQL line statement errors and I can get the SQL Server error message as well.

As for checking the database before you run your script, you can do that in the OnFirstUIBefore event. That way, you can prompt the user for information about the database before the installation modifies the system. All you would have to do is connect to the SQL Server and make sure you have in the connection string the database name you want to make sure exists or not. If it errors, you know that it doesn't exist.

If you need more information, please let me know.

[ned_mcevoy]

Yeah works a treat, thanks Traveller! I just did a

SELECT COUNT (*) FROM sysdatabases WHERE name = DB_NAME

to determine if it exists.

I'm interested in this progress bar you use, if I can get that figured out I will run all my scripts with these objects. At the moment it uses the default bar and just sits on Custom Actions while it does everything - doesnt look to great.

My only concern would be the timing as im restoring from a backup that first has to be installed on the target machine, guess i could use OnMoved Event???

One more question - I use Find and Replace to change things within the script at runtime - i.e. database name, is this possible with the objects. I guess it could be possible to check each string as you iterate through the list?

[TheTraveler]

Hello,

Yes, it is possible. I actually have 12 SQL files that I have to run during our installation. Before I run the files, I wrote a function that goes through each of the files and it does a search and replace of key phrases I put into the files. Here is a link to a post I made a while ago. Take a look. This might be what you are looking for.

Also, When I run my SQL Scripts, I usually do it in the OnFirstUIAfter event. That way, all the files are in place and it will only run all of the files on a first time installation. I then use the same code in the OnUpdateUIAfter event for patching the database.

Enjoy...

[jchristman]

What about getting them to process during an upgrade. I have my sql scripts written so they can be ran over and over. So I just append the scripts for each upgrade. So I need them to go ahead and run again during an upgrade. Is this possible?

[TheTraveler]

Yes... All you have to do is run your ADO functions in the OnUpdateUIAfter event.

The way I have our SQL scripts doing upgrades, I have placed what I call "Break" points in the SQL file. These break points are in commented areas in SQL script. So you can run the script file in query analyzer and you don't have to worry about the file having problems running due to these break points. Anyway, these break points contain an identifying string like a database version number or a product version. During the upgrade, I can look up the current database version on the machine or the product version and look for that break point in the SQL file. This gives me my starting point in the file so that I can upgrade the database from that point in the file.

Do you like the concept?

[jchristman]

I do like this concept of using these types of break points.

In the initial install I prompt the user for a sql server and database, then I query them and setup all the other connections with no user input then I run My_InstallSQLScripts() inside the MY_DBFeature_Installed(). The My_InstallSQLScripts() loads all sql scripts from SUPPORTDIR and based upon the script name I call SQLRTGetConnectionInfo( svSQLConnection,svSQLServer, svDB, svUser, svPassword ); usig the correct connection name from what I built earlier to get the settings. Is all of this stored somewhere that during the upgrade I do as You mentioned and run My_InstallSQLScripts() inside OnUpdateUIAfter()

[TheTraveler]

Unfortunately, the installation doesn't store your variables automatically. Would be nice though. However, you can store them yourself in any way you want. For us, the registry is perfect. So I store all the necessary information is in the registry and any registry entries you create within install shield will be automatically removed during the uninstall. So I use the information stored in the registry to rebuild my connection string during the upgrade in the OnUpdateUIBefore event. I would also keep in mind that testing these settings should be done in this event before you modify the system. Servers get renamed and moved. So if the configuration has changed, you can query the user for that information before anything is done.

[jchristman]

Is it possible to have the upgrade call Dlg_SQLServer used in the OnFirstUIBefore() to prompt the user asking for the sql server and the db.

[ned_mcevoy]

HI there, thanks for all your help Traveller i've got it running my scripts doing Find and Replace and all seems good. The only little thing that worries me is that progress bar thing you mentioned, at the minute im doing a SdShowMsg() to display activity to the end user, this does the job but doesn't really look brilliant - could you give me any pointers on a good UI to display activity

Cheers

[jchristman]

during the installation process you can use SetStatusWindow(0-100%,"DISPLAY TEXT")

[TheTraveler]

That is exactly how I'm doing it. I'm using SetStatusWindow... Here is some snipits of code.

    ///////////////////////////////////////////////////////////////////////////
    //   I disabled the Cancel Button on the SetStatusWindow, because I      //
    // didn't have the time to program that option in.  It would make a      //
    // create option though.                                                 //
    ///////////////////////////////////////////////////////////////////////////
    Disable(CANCELBUTTON);
    Enable (STATUS);    

    ///////////////////////////////////////////////////////////////////////////
    //   The function that I got this from runs a list of SQL files stored   //
    // in a string list.  So I put in the status "Running 1 of 12 files."    //
    // so the user knows where we are.                                       //
    ///////////////////////////////////////////////////////////////////////////
    NumToStr(strStatus, nFileCount);
    NumToStr(strTemp, nFileIndex + 1);
    strStatus = "Running "+ strTemp +" of "+ strStatus +" files.";

    ///////////////////////////////////////////////////////////////////////////
    //   m_listSQL contains the SQL Statements.  One SQL Statement Stored in //
    // a String in the String List.                                          //
    //                                                                       //
    // PLEASE NOTE:  There are NO float, double, or real data types in       //
    // install shield.  So when you do calculations that will result in real //
    // numbers and store them into an integer type, install shield will      //
    // automatically chop off everything to the right of the decimal point.  //
    // To get the percentage, multiply the index first and then devide.      //
    ///////////////////////////////////////////////////////////////////////////
    nCount = ListCount(m_listSQL);

    nStatus = ((nIndex * 100) / nCount);
    SetStatusWindow (nStatus, strStatus);

    Disable (STATUS);    
    Enable(CANCELBUTTON);