Installscript SQL SERVER

[jchristman]

I have an installscript project and I have a sql connection that uses the default dialog box, i would like to be able to use installscript to print out into my log file the SQL Server and the datbase that was selected for that dialog.

[hidenori]

Look into the OnSQLServerInitialize() event. The server and database name selected in the default SQLLogin dialog will be set to the szServer and szDB variables respectively after the SQLServerSelectLogin2() function call.

Hope that helps.

[jchristman]

will I need to add this event to my installscript using the dropdowns so it is visible? Will I need to make szServer and szDatabase global so I can use them in the rul I have added to the project?

[hidenori]

Yes, try that and let me know if you have any issue.

[jchristman]

I have added the function to my project and see the variables, but I cannot access them in my rul, I am not for sure how to make them global so my script can use the variables.

[hidenori]

Try declaring 2 variables with different names from szServer and szDB outside of the main program block, and set them from szServer and szDB after the SQLServerSelectLogin2() function call. Please also see the Global vs. Local Variables help topic for more information about the InstallScript global variables.

[jchristman]

That worked great thank you.

If you would not mind could you explain to me how this works SQL dialog works with mutliple connections to differant servers and databases, is all this information stored in the list Connections. I ask because I run a script to go out and retrive information from a config table that holds other sql server and database information i could actually end up connecting to 3 or more servers and 3 or more databases, one database and server per connection. I am trying to figure out how I can populate these connections so a user is not prompted for each connection and on install the scripts that are assigned to the connections install to the correct server and database.

[jchristman]

Ok on to my next challange now. I am not for sure the best way to accomplish this.

I have setup one connection the dialog appears and I run a vbscript that runs a query and processes I then take the information and populate a custom dialog and a user makes a selection, i then run another vbscript and querry to obtain new SQL connection information. I now need a way to load this information into the installscript process so the connections will run and the script be processesed on install and send the output to file. and somehow have the progress bar show status updates as they scripts are installing.

I use print statements in my SQL Scripts I would like to have this outputed to a logfile and I do not know how this can be done if I setup the connections and let the installer run the scripts for me.

[jchristman]

let me ask this question first - can I have sql scripts out their print statements to files if I load the scripts under the sqlscripts section of the installscript project or do I have to run them from an Installscript to accomplish this?

[hidenori]

The first thing you need to do is to add all connections to the SQL Scripts view, and modify the OnSQLServerInitialize() event so that the SQLLogin dialog will be displayed only for the first connection. Also, you need to write your own InstallScript code to retrieve the connection properties for other connections (establish the first connection and execute the SQL script) because the built-in SQL feature currently does not have the ability that allows you to handle SQL query results outside SQL scripts. The OnSQLServerInitialize() event will look like this:

function number OnSQLServerInitialize( nBtn )
begin
                                       .
                                       .

if szConnection = "NameOfFirstConnection" then
   nResult = SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, FALSE, TRUE );
   RetrieveOtherConnectionsInfo( szServer, szUser, szPassword, bWinLogin, szDB );
else
   GetConnectionInfo( szConnection, szServer, bWinLogin, szUser, szPassword,  szDB );
endif;
                                       .
                                       .

if( nResult = NEXT ) then
   //store data in case we need it again
   SQLRTPutConnectionInfo2( szConnection, szServer, szDB, szUser, szPassword );
   SQLRTPutConnectionAuthentication( szConnection, bWinLogin );

   if szConnection != "NameOfFirstConnection" then
      //try connection
      nResult = SQLRTConnect( szConnection, szServer, bWinLogin, szUser, szPassword );
   endif;
                                       .
                                       .
                                       .

endif;
                                       .
                                       .
endif;

RetrieveOtherConnectionsInfo() is a function that you need to write for retrieving other connections' properties from the main database. This function will establish a connection with the connection parameters passed in, run a query, and save the connection properites for other connections to global variables. Please see this thread for sample InstallScript code that manage databases. I recommend that you choose the ADO object becasue it is available on all Windows platforms except clean Windows 95.

GetConnectionInfo() is another function that you need to write for loading the connection properties for the specified connection from the global variables stored by the RetrieveOtherConnectionsInfo() function. The connection properties will be set to szServer, bWinLogin, szUser, szPassword, and szDB in the above example.

Hope this helps.

[jchristman]

yes, this does help me understand thank you.

after the initial connection how can I call another dialog before getting the rest of the connections to setup.

[hidenori]

You need to place another SQLServerSelectLogin2() function call where appropriate. If you place it immediate after the GetConnectionInfo() function call, the dialog will be displayed for every connection with the connection properties retrieved from the main database. If you do not want to display it for every connection, you need to place an if statement around with appropriate conditions.

[jchristman]

Thank for you help hidenori, that worked great.



Is there a way to log the sql output to a log file?

Is there also a way to have sql script "Print" command that normally output to the screen be sent to a log file.

Are any of these possible from the sqlscripts section or an INSTALLSCRIPT?

[hidenori]

Unfortunately, you need to write your custom InstallScript code to manage connections and execute SQL scripts in order to accomplish it in a pure InstallScript project. In Basic MSI projects and InstallScript MSI projects, the information are written into Windows Installer verbose logs.

Regards.

[jchristman]

Is it possible to still use the connections and load the scripts to accomplish this method or will I need to do it completely from support files and custom code to create the connections and execute the scripts?

[hidenori]

You can use the connections in the SQL Scripts view to populate the SQLLogin dialogs, but you need to write custom code that will establish the connections and execute SQL scripts. It would be probably easy for you to locate SQL script files if you install them to the support folder.

Also, the PRINT message is returned as an informational error to database applications. You can get the messages through the Error object if you are writing an ADO application. Please refer to the Using PRINT and Handling Errors and Messages in ADO help topics for more information.

[jchristman]

Could you give me insight on how to break the automatic install process for the scripts that I load in the SQL Scripts View. Or Should I just use the connection from this View and load the SQL Scripts to the Support Files?

[hidenori]

In order not to execute SQL scripts added in the SQL Scripts view by the built-in SQL feature, you just need to comment out the SQLRTConnect() function call. However, I would recommned that you install SQL scripts as support files because it would be easy to locate them like this; \SqlScriptxxx.sql.

[jchristman]

is there an Installscript command that I should you to call and execute the sql scripts from

[hidenori]

No, you need to write your own code that will executes your SQL scripts.

[jchristman]

Can an ADO object run a script and send output to a text file?