How to run sql scripts for multiple databases

[sramesh]

Hi,

I need to run a sequence of sql scripts in multiple databases and possibly multiple SQL server instances.
How do I achieve this using the advanced Installer. ?

Please help !

Thanks

[Cosmin]

Hi,

This can be done by creating multiple connections in the SQL Scripts page. Each connection will have its own SQL scripts and you can order the connections the way you need (the order of execution is set by the order in the "SQL Scripts" page).

If you encounter problems while configuring the SQL connections and scripts you can check out the SQL Script tutorial or post on this thread.

Regards,
Cosmin

[sramesh]

How do I pass the different database names. I can get the database names from the server. But I will need to save these in different properties to be able to pass them uniquely for each connection

Thanks

[Cosmin]

Hi,

If you are referring to getting the names of the databases from the server and using them in your project then this is not supported by Advanced Installer. However, this can be done through a custom action.

If this is not what you are referring to, please give me more details.

Regards,
Cosmin

[sramesh]

Hi,

If you are referring to getting the names of the databases from the server and using them in your project then this is not supported by Advanced Installer. However, this can be done through a custom action.

Yes. I would like to do exactly that. I want to get the list of databases from the server. Then allow the user to identlify wich database to run the script against. Then run the scripts for those specific databases only.

How would I achive this using custom action ?
do you mean that I would be using vb script or osql script or sqlcmd script to run all my scripts ?

[Cosmin]

Hi,

In order to do this you can create a custom action which connects to the server, gets the name of the databases and places them in a combo box. After the user selects a database you can use the property of the combo box in the connection you create in the "SQL Scripts" page.

There is an example for populating a combo box on the forum.

Regards,
Cosmin

[sramesh]

Thank you Cosmin,

That was very helpful. I incorporated the code to select from multiple instances.

To populate the combo box with database names, I found this script and modified it. It compiled fine. I am new to vbscript and I am unable to figure out why the combo is not getting the values.

Can you please help again ?

following is my script. In the AI run log it is returning value 0.

------------------------------------------------------------------------

Function FillDatabaseList( )

OBJECT oDataBase, oSQLServer
STRING szoDataBase, szSQLServer
STRING szUserName, szPassword, szServerName
NUMBER nCount, nvBufferSize, order
NUMBER hView, hDatabase, nResult
HWND hRec

hDatabase=Session.Database

nCount = 0
nvBufferSize = 256

nResult = MsiDatabaseOpenView(hDatabase, "SELECT * FROM ComboBox WHERE Property='DBCOMBO_PROP'", hView)
nResult = MsiViewExecute(hView,NULL)
while (MsiViewFetch(hView,hRec) = ERROR_SUCCESS)
nResult = MsiViewModify(hView, 6, hRec)
Wend
nResult = MsiViewClose (hView)

' connect to SQL Server
szSQLServer = "SQLDMO.SQLServer"
set oSQLServer = CreateObject(szSQLServer)

' Set the login timeout
oSQLServer.LoginTimeout = 15


' Decision code for login authorization type: WinNT or SQL Server
'oSQLServer.LoginSecure = TRUE
MsiGetProperty ISMSI_HANDLE, "sa", szUserName, nvBufferSize
MsiGetProperty ISMSI_HANDLE, "SAPASSWORD_PROP", szPassword, nvBufferSize
MsiGetProperty ISMSI_HANDLE, "SELSERVER_PROP", szServerName, nvBufferSize
oSQLServer.Connect szServerName, szUserName, szPassword


' Now were are connected, get the list of databases
szoDataBase = "SQLDMO.Database"
set oDataBase = CreateObject(szoDataBase)
nResult = MsiDatabaseOpenView(hDatabase, "SELECT * FROM ComboBox WHERE Property='DBCOMBO_PROP'", hView)
nResult = MsiViewExecute(hView,NULL)

For nCount = 1 to oSQLServer.Databases.Count
set oDataBase = oSQLServer.Databases(nCount)
if ( oDataBase.SystemObject = FALSE ) then
order = order + 1
hRec = MsiCreateRecord(4)
nResult = MsiRecordSetString(hRec, 1, "DBCOMBO_PROP") ' Column1: Property tied to the entry
nResult = MsiRecordSetInteger(hRec, 2, order) ' Column2: Display order of the item
nResult = MsiRecordSetString(hRec, 3, oDataBase.Name) ' Column3: Value to set property to
nResult = MsiRecordSetString(hRec, 4, oDataBase.Name) ' Column4: Display text for item
nResult = MsiViewModify(hView, MSIMODIFY_INSERT_TEMPORARY, hRec)
nResult = MsiCloseHandle(hRec)
end if
Next
nResult = MsiViewClose (hView)


set oSQLServer = NOTHING
set oDataBase = NOTHING


End Function
--------------------------------------------------------

[Cosmin]

Hi,

Please note that this is not VBScript code, it is an InstallScript and it can be used only by an InstallShield project. There are examples on the forum for populating a combo, please take a look:
http://www.advancedinstaller.com/forums ... php?t=1768
http://www.advancedinstaller.com/forums ... php?t=5130

Regards,
Cosmin