cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
DonAIR
Level 6

ADO Still best method to run SQL Scripts?

We are currently working to re-write our clunky database package for our software, its been a long time coming. Needless to say, we get 10-15 scripts from development that need to be run (these scripts get modified all the time so just plopping them in the SQL Scripts section wont cut it for automation purposes.

I am wondering, is ADO Still the best course of action? The current process we do is this:

Creating the Connection

//===========================================================================
// CheckSQL
//===========================================================================

function CheckSQL()

BOOL bLoggedIn;
LONG nAdCmdText, nFileValue, nRows;
NUMBER nResult, nServiceState, nSize, nType, nStringLocation1, nStringLocation2, nCompareFlag, nvRows;
STRING sCSDVersion, sDatabaseName, sDataFile, sDriver, sIntSecurity, sLogFile, sMsg, sQuery, sProductVersion, sResult, sReturnString, sSQLEdition, sSubStr;
STRING svSQLVer, svSQLEd, svSQLBuild, szSQLVersion, szSQLQueryVersion, svRequiredVersion;

begin

LogCreateNode(sInstallLog, "OnFirstUIBefore", "CheckSQL", BEFORE);

// Set ADO objects to be used in this function
try
set oCn = CreateObject("ADODB.Connection");
set oFld = CreateObject("ADODB.Field");
set oRs = CreateObject("ADODB.Recordset");
set oErr = CreateObject("ADODB.Error");
set oCmd = CreateObject ("ADODB.Command");
LogCreateKey(sInstallLog, "CheckSQL", "Message", "ADO objects set properly", BEFORE);
catch
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not create remote ADODB connection object. Please ensure that MDAC is installed", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not create remote ADODB connection object. Please ensure that MDAC is installed", BEFORE);
MODE = NORMALMODE;
MessageBox("Could not create remote ADODB connection object. Please ensure that MDAC is installed. Setup will abort.",SEVERE);
abort;
endcatch;

Dlg_SQLServerSelectLogin:
bSysAdmin = FALSE;
bSQL2008 = FALSE;

try
sDatabaseName = "Master";
oCn.Provider = "sqloledb";
oCn.Properties("Connect Timeout").Value = 10;
oCn.CommandTimeout = 0;
oCn.ConnectionTimeout = 10;
LogCreateKey(sInstallLog, "CheckSQL", "oCn", "Properties: "+oCn, BEFORE);
catch
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not set ADO connection properties", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not set ADO connection properties", BEFORE);
MODE = NORMALMODE;
MessageBox("Could not set ADO connection properties. Setup will abort.",SEVERE);
abort;
endcatch;

try
oCn.Properties ("Prompt").Value = 4;
LogCreateKey(sInstallLog, "CheckSQL", "Message", "oCn property Prompt set to 4", BEFORE);
catch
ADOErrorHandler();
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not set oCn properties", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not set oCn properties", BEFORE);
MODE = NORMALMODE;
MessageBox (sError, SEVERE);
abort;
endcatch;

try
SQLRTInitialize( SUPPORTDIR ^ "SQLRT.ini");
LogCreateKey(sInstallLog, "CheckSQL", "Message", "Initialized SQLRT.ini file", BEFORE);
catch
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Error initializing SQLRT.ini file", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Error initializing SQLRT.ini file", BEFORE);
MODE = NORMALMODE;
MessageBox("Could not initialize SQLRT.ini file. Setup will abort",SEVERE);
abort;
endcatch;

if(!bSilentInstall) then
nResult = SQLServerSelectLogin (sSQLServer, sUserID, sPassword, bWindowsLogin);
endif;

//Change common SQL conventions to computer name = (local) and .
if (sSQLServer % "(local)") then
StrReplace(sSQLServer,"(local)",sComputerName,0);
endif;
if (sSQLServer % ".") then
StrReplace(sSQLServer,".",sComputerName,0);
endif;

if (nResult = BACK) then
return BACK;
endif;

sDatabaseName = "master";
sConnection = "NewSQLConnection1";
SdShowMsg("Please wait while verifying SQL login credentials",TRUE);
nResult = SQLRTConnect2 (sConnection, sSQLServer, bWindowsLogin, sUserID, sPassword, sDatabaseName);
SdShowMsg("Please wait while verifying SQL login credentials",FALSE);

// Check if connection works with credentials provided
if (nResult >=ISERR_SUCCESS ) then
if (bWindowsLogin) then
bIntSecurity = TRUE;
LogCreateKey(sInstallLog, "CheckSQL", "WindowsAuthentication", "Yes", BEFORE);
else
LogCreateKey(sInstallLog, "CheckSQL", "SQLAuthentication", "Yes", BEFORE);
endif;
LogCreateKey(sInstallLog, "CheckSQL", "Message", "Connection using credentials worked", BEFORE);
else
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Failed to validate authentication to SQL, please re-enter your SQL server login information", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Failed to validate authentication to SQL, please re-enter your SQL server login information", BEFORE);
MODE = NORMALMODE;
bSilentInstall = FALSE;
MessageBox("Failed to validate authentication to SQL. Please re-enter your SQL server login information.",INFORMATION);
ResetoCn();
goto Dlg_SQLServerSelectLogin;
endif;

if (sIntSecurity = "SSPI")then
bIntSecurity = TRUE;
endif;

// Check for instance name to modify Analysis Service name
if (sSQLServer % "\\") then
LogCreateKey(sInstallLog, "CheckSQL", "Message", "SQL instance found", BEFORE);
nLocation = StrFindEx(sSQLServer, "\\", 0);
nLocation = nLocation + 1;
StrSub (sSubStr, sSQLServer, nLocation, 100);
sServiceName = "MSOLAP$"+ sSubStr;
else
LogCreateKey(sInstallLog, "CheckSQL", "Message", "No SQL instance found", BEFORE);
sServiceName = "MSSQLServerOLAPService";
endif;
LogCreateKey(sInstallLog, "CheckSQL", "AnalysisServiceName", sServiceName, BEFORE);

// Change SQLServerAnalysisServices.bat
sConfigFile = SUPPORTDIR^"SQLServerAnalysisServices.bat";
sSearchStr = "**SQLANALYSISSERVICE**";
sReplaceStr = sServiceName;
EditConfigs (sConfigFile,sSearchStr,sReplaceStr);
LogCreateKey(sInstallLog, "CheckSQL", "Message", "In "+sConfigFile+", changed "+sSearchStr+" with "+sReplaceStr, BEFORE);

// Check for instance name to modify Analysis Service name
if (sSQLServer % "\\") then
LogCreateKey(sInstallLog, "CheckSQL", "Message", "SQL instance found", BEFORE);
nLocation = StrFindEx(sSQLServer, "\\", 0);
nLocation = nLocation + 1;
StrSub (sSubStr, sSQLServer, nLocation, 100);
sServiceName2 = "SQLAgent$"+ sSubStr;
else
LogCreateKey(sInstallLog, "CheckSQL", "Message", "No SQL instance found", BEFORE);
sServiceName2 = "SQLSERVERAGENT";
endif;
LogCreateKey(sInstallLog, "CheckSQL", "AgentServiceName", sServiceName2, BEFORE);

// Change SQLServerAgentServices.bat
sConfigFile = SUPPORTDIR^"SQLServerAgentServices.bat";
sSearchStr = "**SQLAGENTSERVICE**";
sReplaceStr = sServiceName2;
EditConfigs (sConfigFile,sSearchStr,sReplaceStr);
LogCreateKey(sInstallLog, "CheckSQL", "Message", "In "+sConfigFile+", changed "+sSearchStr+" with "+sReplaceStr, BEFORE);

// Try connecting with Native Client 10.0
sDriver = "Driver={SQL Server Native Client 10.0};";
if(bIntSecurity) then
sConnection = sDriver + "Server="+sSQLServer+";Database=master;Trusted_Connection=yes;";
else
sConnection = sDriver + "Server="+sSQLServer+";Database=master; Uid="+sUserID+";Pwd="+sPassword +";";
endif;

try
oCn.Open(sConnection);
bLoggedIn = TRUE;
catch
LogCreateKey(sInstallLog, "CheckSQL", "ConnectionString", sConnection, BEFORE);
bLoggedIn = FALSE;
endcatch;

// Try connecting with Native Client if Native Client 10.0 failed
if(!bLoggedIn) then
sDriver = "Driver={SQL Native Client};";
if(bIntSecurity) then
sConnection = sDriver + "Server="+sSQLServer+";Database=master;Trusted_Connection=yes;";
else
sConnection = sDriver + "Server="+sSQLServer+";Database=master; Uid="+sUserID+";Pwd="+sPassword +";";
endif;

try
oCn.Open(sConnection);
bLoggedIn = TRUE;
catch
bLoggedIn = FALSE;
LogCreateKey(sInstallLog, "CheckSQL", "ConnectionString", sConnection, BEFORE);
endcatch;
endif;

if(!bLoggedIn) then
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not open MDAC connection", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not open MDAC connection", BEFORE);
MODE = NORMALMODE;
MessageBox ("Could not open MDAC connection. Setup will abort.",INFORMATION);
abort;
endif;


if (bLoggedIn) then
// Check if installer is running remotely, and fail if so
CheckSQLRemote();

nResult = StrFindEx(sRemoteName, "\\", 0);
StrSub ( sRemoteServerName, sRemoteName, 0, nResult);

if (sRemoteServerName != sComputerName) then
bRemoteSQLServer = TRUE;
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Possible server name mismatch found, Machine found: " +sRemoteName+" compared to Local Machine Name: "+sComputerName, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Possible server name mismatch found, Machine found: " +sRemoteName+" compared to Local Machine Name: "+sComputerName, BEFORE);
endif;

if (bRemoteSQLServer) then
nResult = AskYesNo ( "Are you attempting to Install "+PRODUCT_NAME+" databases on a Clustered SQL Server?", YES );

if (nResult = NO) then
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Remote Database Installation is not permitted. Setup will only install "+PRODUCT_NAME+" databases on a local SQL Server. Please re-enter your SQL server login information", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Remote Database Installation is not permitted. Setup will only install "+PRODUCT_NAME+" databases on a local SQL Server. Please re-enter your SQL server login information", BEFORE);
MODE = NORMALMODE;
MessageBox("Remote Database Installation is not permitted. Setup will only install "+PRODUCT_NAME+" databases on a local SQL Server. Please re-enter your SQL server login information.",WARNING);
ResetoCn();
goto Dlg_SQLServerSelectLogin;
else
LogCreateKey(sInstallLog, "UserInput", "SQLClustering", "Yes", BEFORE);
endif;
endif;

// Get SQL Version
set oRs = oCn.Execute ("select @@version");
sSQLVersion = oRs.Fields(0);

nStringLocation1 = StrFindEx (sSQLVersion, "-", 0 );
StrSub ( svSQLVer, sSQLVersion, 0, nStringLocation1 );
StrTrim (svSQLVer);
LogCreateKey(sInstallLog, "SystemInformation", "SQLVersion", svSQLVer, BEFORE);

nStringLocation1 = StrFind (sSQLVersion, "Corporation");
if nStringLocation1 < 0 then
nStringLocation1 = StrFindEx (sSQLVersion, "(c)", 0 );
nStringLocation1 = nStringLocation1 + 3;
else
nStringLocation1 = nStringLocation1 + 11;
endif;
nStringLocation2 = StrFindEx (sSQLVersion, "Edition", 0 );
StrSub (svSQLEd, sSQLVersion, nStringLocation1, nStringLocation2 - nStringLocation1);
StrTrim (svSQLEd);
LogCreateKey(sInstallLog, "SystemInformation", "SQLEdition", svSQLEd + " Edition", BEFORE);
oRs.Close;

//Check if SQL is 2012 SP2 or 2014 SP1
// Compare by Build Number
szSQLQueryVersion = "SELECT SERVERPROPERTY('productversion')";
set oRs = oCn.Execute (szSQLQueryVersion);
szSQLVersion = oRs.Fields(0);
oRs.Close;

//check version
svRequiredVersion = "11.00.5058.0";
//svRequiredVersion2 = "12.00.4100.1";
nCompareFlag = VERSION;

nCompareFlag = VerCompare ( svRequiredVersion , szSQLVersion , nCompareFlag);
if (nCompareFlag = GREATER_THAN) then
LogCreateKey(sInstallLog, "SystemInformation", "ErrorMessage", "This installation requires Microsoft SQL Server 2012 SP2 or 2014 SP1. Setup will abort.", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "This installation requires Microsoft SQL Server 2012 SP2 or 2014 SP1. Setup will abort.", BEFORE);
MessageBox("This installation requires Microsoft SQL Server 2012 SP2 or 2014 SP1. Setup will abort.",SEVERE);
elseif ((nCompareFlag = LESS_THAN) || (nCompareFlag = EQUALS)) then
LogCreateKey(sInstallLog, "SystemInformation", "SQLBuild", szSQLVersion, BEFORE);
endif;

// Check to see of login user has sysadmin rights
if (bIntSecurity) then
sQuery = "SELECT IS_SRVROLEMEMBER ('SYSADMIN')";
else
sQuery = "SELECT IS_SRVROLEMEMBER ('SYSADMIN', '"+sUserID+"')";
endif;

try
set oRs = oCn.Execute (sQuery);
LogCreateKey(sInstallLog, "CheckSQL", "QueryExecuted", sQuery, BEFORE);
catch
ADOErrorHandler();
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
MODE = NORMALMODE;
MessageBox (sError, SEVERE);
abort;
endcatch;

if (!oRs.EOF) then
nFileValue = oRs.Fields(0);
endif;

oRs.Close;

if (nFileValue = 1) then
bSysAdmin = TRUE;
LogCreateKey(sInstallLog, "CheckSQL", "Message", "User is a sysadmin in SQL", BEFORE);
endif;

// Abort if user does not have sys admin rights
if (!bSysAdmin) then
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Setup has determined that the logon account does not have the permission to perform this installation", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Setup has determined that the logon account does not have the permission to perform this installation", BEFORE);
MODE = NORMALMODE;

if (bIntSecurity) then
MessageBox ("Setup has determined that the logon account does not have the permission to perform this installation. Please contact your system adminisator to obtain a logon that has sysadmin permissions.",INFORMATION);
else
MessageBox ("Setup has determined that the logon account " +sUserID+" does not have the permission to perform this installation. Please contact your system adminisator to obtain a logon that has sysadmin permissions.",INFORMATION);
endif;

ResetoCn();
goto Dlg_SQLServerSelectLogin;
endif;

// Check if SQL Server collation is SQL_Latin1_General_CP1_CI_AS
sQuery = "SELECT SERVERPROPERTY('collation') AS 'Collation'";

try
set oRs = oCn.Execute (sQuery);
LogCreateKey(sInstallLog, "CheckSQL", "QueryExecuted", sQuery, BEFORE);
catch
ADOErrorHandler();
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
MODE = NORMALMODE;
MessageBox (sError, SEVERE);
abort;
endcatch;

sCollation = oRs.Fields('Collation');
LogCreateKey(sInstallLog, "CheckSQL", "SQLCollation", sCollation, BEFORE);
oRs.Close;

if (sCollation != "SQL_Latin1_General_CP1_CI_AS") then
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Microsoft SQL Server collation SQL_Latin1_General_CP1_CI_AS not found", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Microsoft SQL Server collation SQL_Latin1_General_CP1_CI_AS not found", BEFORE);
MODE = NORMALMODE;
MessageBox("This installation requires Microsoft SQL Server collation SQL_Latin1_General_CP1_CI_AS. Setup will abort.",SEVERE);
abort;
endif;

// Enable SQL Server Analysis Services
ServiceGetServiceState(sServiceName, nServiceState);
if ( nServiceState != SERVICE_RUNNING ) then
if(!bSilentInstall) then
if (AskYesNo("The installers needs to enable SQL Server Analysis Services to continue, hit yes to enable and continue installation",YES) = NO) then
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "User decided to not enable SQL Server Analysis Services", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "User decided to not enable SQL Server Analysis Services", BEFORE);
MessageBox ("Contact your system administrator for more information to enable SQL Server Analysis Services. Setup will abort.", SEVERE);
abort;
endif;
endif;
// Enable SQL Server Analysis Services
ExecuteFile(SUPPORTDIR, "SQLServerAnalysisServices.bat");
endif;

// Enable SQL Server Agent Service
//sServiceName = "SQLSERVERAGENT";
ServiceGetServiceState(sServiceName2, nServiceState);
if ( nServiceState != SERVICE_RUNNING ) then
if(!bSilentInstall) then
if (AskYesNo("The installers needs to enable SQL Server Agent Services to continue, hit yes to enable and continue installation",YES) = NO) then
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "User decided to not enable SQL Server Agent Services", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "User decided to not enable SQL Server Agent Services", BEFORE);
MessageBox ("Contact your system administrator for more information to enable SQL Server Agent Services. Setup will abort.", SEVERE);
abort;
endif;
endif;
// Enable SQL Server Analysis Services
ExecuteFile(SUPPORTDIR, "SQLServerAgentServices.bat");
endif;

CloseOCN();

// Find the Master Database path. Set it as the default path if default not found in the regisy.
sQuery = "Master..sp_helpfile";
try
set oRs = oCn.Execute (sQuery);
LogCreateKey(sInstallLog, "CheckSQL", "QueryExecuted", sQuery, BEFORE);
catch
ADOErrorHandler();
LogCreateKey(sInstallLog, "CheckSQL", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
MODE = NORMALMODE;
MessageBox (sError, SEVERE);
abort;
endcatch;

while oRs.EOF = FALSE

if oRs("usage") = "data only" then
sDataFile = oRs("filename");
else
sLogFile = oRs("filename");
endif;

oRs.MoveNext;
endwhile;

ParsePath (sReturnString, sDataFile, PATH);
sMasterDataFile = sReturnString;

else
ResetoCn();
goto Dlg_SQLServerSelectLogin;
endif;
end;

//===========================================================================
// ResetoCn
//===========================================================================

function ResetoCn()

begin

try
set oCn = NOTHING;
set oFld = NOTHING;
set oRs = NOTHING;
set oErr = NOTHING;
catch
LogCreateNode(sInstallLog, "OnFirstUIBefore", "ResetoCn", BEFORE);
LogCreateKey(sInstallLog, "ResetoCn", "ErrorMessage", "Setting oCn, oFld, oRs or oErr to NOTHING", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Setting oCn, oFld, oRs or oErr to NOTHING", BEFORE);
endcatch;

try
set oCn = CreateObject("ADODB.Connection"); // Connect to SQL-DMO Restore
set oFld = CreateObject("ADODB.Field"); // Connect to SQL-DMO Restore
set oRs = CreateObject("ADODB.Recordset"); // Connect to SQL-DMO Restore
set oErr = CreateObject("ADODB.Error"); // Connect to SQL-DMO Restore
catch
LogCreateNode(sInstallLog, "OnFirstUIBefore", "ResetoCn", BEFORE);
LogCreateKey(sInstallLog, "ResetoCn", "ErrorMessage", "Could not create local ADO connection object. Please ensure that MDAC is installed", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not create local ADODB connection object. Please ensure that MDAC is installed", BEFORE);
MODE = NORMALMODE;
MessageBox("Could not create local ADO connection object. Please ensure that MDAC is installed. Setup will abort",SEVERE);
abort;
endcatch;
end;

//===========================================================================
// CheckSQLMode
//===========================================================================
function CheckSQLMode()

STRING sQuery, sSQLMode;

begin

LogCreateNode(sInstallLog, "OnFirstUIBefore", "CheckSQLMode", BEFORE);

sQuery = "EXEC xp_loginconfig";
try
set oRs = oCn.Execute (sQuery);
LogCreateKey(sInstallLog, "CheckSQLMode", "QueryExecuted", sQuery, BEFORE);
catch
ADOErrorHandler();
LogCreateKey(sInstallLog, "CheckSQLMode", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not execute query: " + sQuery, BEFORE);
MODE = NORMALMODE;
bSilentInstall = FALSE;
MessageBox (sError, SEVERE);
endcatch;

while oRs.EOF = FALSE
if oRs("name") = "login mode" then
sSQLMode = oRs("config_value");
endif;
oRs.MoveNext;
endwhile;

if (StrCompare ("Mixed",sSQLMode)!=0) then
LogCreateKey(sInstallLog, "CheckSQLMode", "ErrorMessage", "SQL Server Authentication mode is not set to Mixed Mode", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "SQL Server Authentication mode is not set to Mixed Mode", BEFORE);
MODE = NORMALMODE;
MessageBox("SQL Server Authentication mode is not set to Mixed Mode. Please change the Authentication mode to Mixed Mode and re-run setup. Setup will abort", SEVERE);
abort;
else
LogCreateKey(sInstallLog, "CheckSQLMode", "Message", "SQL Server Authentication mode is set to Mixed Mode", BEFORE);
endif;
end;

//===========================================================================
// CloseOCN
//===========================================================================

function CloseOCN()

begin
try
oCn.Close;
oCn.Properties("Connect Timeout")= 3600;
oCn.Open(sConnection);
oCn.CommandTimeout = 0;
catch
LogCreateNode(sInstallLog, "OnFirstUIBefore", "CloseOCN", BEFORE);
LogCreateKey(sInstallLog, "CloseOCN", "ErrorMessage", "CloseOCN failed", BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "CloseOCN failed", BEFORE);
endcatch;
end;


Executing a Script

//===========================================================================
// ExecuteScript
//===========================================================================

function ExecuteScript(sScriptPath)

OBJECT oFileSysObj,oFile,oErr;
STRING szInfo, sResult,sMessages,szQuery1;
STRING strLine,strFileName,strError,svDate,svTime,strTemp;
NUMBER nvResult;
LONG nvRows,adCmdText,adCmdFile,sLong,nLength;
INT n,nErrCount;
BOOL nMode,bGO,bGOSPACE;

begin

//Write to Intall Log
LogCreateNode(sInstallLog, "DetailedLog", "ExecuteScript", BEFORE);
LogCreateKey(sInstallLog, "ExecuteScript", "Script", sScriptPath, BEFORE);

// Get script name
ParsePath(sScriptName,sScriptPath,FILENAME);

//Write To Script Log
LogCreateText(sScriptLog, "============================================================ "+sScriptName+" ============================================================", 0, AFTER);
LogCreateText(sScriptLog, "=================================================================================================================================================", 0, AFTER);

oCn.Close;
oCn = sConnection;
oCn.Properties("Connect Timeout")= 28000;
oCn.CommandTimeout = 0;

oCn.Open;
set oFileSysObj = CreateObject("Scripting.FileSystemObject");

if(!IsObject(oFileSysObj)) then
MODE = NORMALMODE;
MessageBox("File system object not installed on target machine. Install file system Object. Setup will abort", SEVERE);
abort;
endif;

nMode = TRUE;
sSQL = "" ;

try
//set oFile = oFileSysObj.OpenTextFile(sScriptPath, nMode);
set oFile = oFileSysObj.OpenTextFile(sScriptPath, nMode(ForReading), false(create), TristateFalse);
catch
LogCreateKey(sInstallLog, "ExecuteScript", "ErrorMessage", "Could not open "+sScriptName+" from " + sScriptPath, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not open "+sScriptName+" from " + sScriptPath, BEFORE);
MODE = NORMALMODE;
MessageBox ("Could not open "+sScriptName+" from " + sScriptPath, SEVERE);
return 0;
endcatch;

ParsePath(sScriptName,sScriptPath,FILENAME);

while (!oFile.AtEndOfStream)
strLine = oFile.ReadLine;
StrToUpper ( strTemp , strLine);
StrTrim(strTemp);

if (strTemp = "GO" || strTemp = "GO ") then
LogCreateText(sScriptLog, sSQL, 0, AFTER);
try
if sSQL != "" then
oCn.Execute(sSQL);
endif;
catch
if (sScriptPath % "KillSQLConnections") then
//do nothing. known issue.
else
ADOErrorHandler();
LogCreateKey(sInstallLog, "ExecuteScript", "ErrorMessage", "Could not execute script: " + sSQL, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not execute script: " + sSQL, BEFORE);
endif;
endcatch;

sSQL = "";
else

if(strLine = "?") then
strLine = "";
endif;
sSQL = sSQL + " " + strLine + "\n";

endif;
endwhile;

if (sSQL != "") then

try
oCn.Execute(sSQL);
LogCreateText(sScriptLog, sSQL, 0, AFTER);
catch
ADOErrorHandler();
LogCreateKey(sInstallLog, "ExecuteScript", "ErrorMessage", "Could not execute script: " + sSQL, BEFORE);
LogCreateKey(sInstallLog, "Errors", "ErrorMessage", "Could not execute script: " + sSQL, BEFORE);
endcatch;
endif;

// Write To Script Log
LogCreateText(sScriptLog, "=================================================================================================================================================", 0, AFTER);
LogCreateText(sScriptLog, "", 0, AFTER);
LogCreateText(sScriptLog, "", 0, AFTER);

sScriptName = "";
end;


Any help is appreciated, even if we are starting over. I just want to make sure we are using the best methods, the "newest" info I can find here is from 2012.
Labels (1)
0 Kudos
(7) Replies
chad_petersen
Level 9

Do the names of the SQL Scripts change constantly of just the contents of the SQL Scripts? I use the SQL Scripts portion of IS any chance I get unless their is something really weird going on. The contents of the SQL Scripts makes no difference to me as the installer just runs them in the order presented and the developers are free to change the contents as needed. The installer doesn't really know the contents of the SQL Scripts anyway, so it doesn't matter to it if they change internally as long as they are valid SQL.

Running it using the SQL Scripts section is a good idea - it supports rollback and it does it as a transacted install. Running things through ADO you don't get any of that for free. Ask yourself what happens when ADO hits an error in a script - what does it do and does it rollback leaving the database unchanged when something goes wrong? Or does it leave the database in a half-baked state? I'd imagine it leaves it half-baked.

SQL Scripts don't support dynamic linking so it will need to know the names of the SQL Scripts that you intend it to run. Other than that I have not see a lot of drawbacks to it.

Chad
0 Kudos
DonAIR
Level 6

chad.petersen wrote:
Do the names of the SQL Scripts change constantly of just the contents of the SQL Scripts? I use the SQL Scripts portion of IS any chance I get unless their is something really weird going on. The contents of the SQL Scripts makes no difference to me as the installer just runs them in the order presented and the developers are free to change the contents as needed. The installer doesn't really know the contents of the SQL Scripts anyway, so it doesn't matter to it if they change internally as long as they are valid SQL.

Running it using the SQL Scripts section is a good idea - it supports rollback and it does it as a transacted install. Running things through ADO you don't get any of that for free. Ask yourself what happens when ADO hits an error in a script - what does it do and does it rollback leaving the database unchanged when something goes wrong? Or does it leave the database in a half-baked state? I'd imagine it leaves it half-baked.

SQL Scripts don't support dynamic linking so it will need to know the names of the SQL Scripts that you intend it to run. Other than that I have not see a lot of drawbacks to it.

Chad


Its possible I am not using it correctly, but my understanding was once you add a script the content is added now, so when development updates a script I would then need to re-add it. Is this not the case? Maybe I am doing it wrong, but all I can figure out is how to import it then its "in the system" so to speak without a way to update it.
0 Kudos
DonAIR
Level 6

DonAIR wrote:
Its possible I am not using it correctly, but my understanding was once you add a script the content is added now, so when development updates a script I would then need to re-add it. Is this not the case? Maybe I am doing it wrong, but all I can figure out is how to import it then its "in the system" so to speak without a way to update it.


disregard my stupidity, I figured that out. I thought i could only import, didn't realize i can link directly to files.

now I just need to figure out the best way to use schema versioning on it.
0 Kudos
Cary_R
Level 11

DonAIR wrote:
disregard my stupidity, I figured that out. I thought i could only import, didn't realize i can link directly to files.

now I just need to figure out the best way to use schema versioning on it.


https://flexeracommunity.force.com/customer/articles/en_US/INFO/Q111714

Pretty much the definitive article on it.

I might suggest looking into Data Tier Applications for your database maintenance. AFAIK, no installer tool supports *.dacpac's out of the box (you have to use executable custom actions for them), but they take a lot of the rigor out of database installations. I believe they also can produce *.sql scripts which are usable by SQLCMD (which frustratingly still require a lot of massaging to use in an installer tool...).
0 Kudos
chad_petersen
Level 9

Just a side note: I've been working on some related issues running SQL Scripts through InstallShield and in watching the install log it appears that Flexera is using ADO behind the scenes. I see messages logged that look similar to this

1: Executing SQL at Line 17257 >>>
1: ADO Error: Number: -2147217900 Description: The query uses non-ANSI outer join

There is some interesting behavior, too. If I have the "Create Catalog If Absent" checkbox set then it will create the Database called for during the Next button click on the SQLLogin dialog. But, if I then Cancel the install the newly created database is not cleaned back up again.

Likewise if I set a script to "Run Script During Login" on the runtime tab if will create the Database automatically on the Next button click and not remove it on a Cancel.

I can see why they are doing it, but it is interesting to consider since I generally try not to make configuration changes to a system during the UISequence myself.

Chad
0 Kudos
DonAIR
Level 6

Cary R wrote:
https://flexeracommunity.force.com/customer/articles/en_US/INFO/Q111714

Pretty much the definitive article on it.

I might suggest looking into Data Tier Applications for your database maintenance. AFAIK, no installer tool supports *.dacpac's out of the box (you have to use executable custom actions for them), but they take a lot of the rigor out of database installations. I believe they also can produce *.sql scripts which are usable by SQLCMD (which frustratingly still require a lot of massaging to use in an installer tool...).


We are actually looking into dacpac's. Are you using them currently? It hasn't hit the installshield process at our office yet so we haven't started looking into it fully, I just know they eventually want to provide us with dacpacs. Anything I should look into?
0 Kudos
Cary_R
Level 11

DonAIR wrote:
We are actually looking into dacpac's. Are you using them currently? It hasn't hit the installshield process at our office yet so we haven't started looking into it fully, I just know they eventually want to provide us with dacpacs. Anything I should look into?


I am not personally yet in my installs, but I know a few people that are.

Basically, those folks have implemented logic to locate the command line executable that processes the *.dacpac, not unlike how you'd do with SQLCMD. Which is to say you have to install as a prereq, create a search to locate the *.exe, and then pass through all the params on the command line.
0 Kudos