private void ExecuteUpgradeScripts(EPMData epmData, Guid siteId) { using (SqlConnection sqlConnection = epmData.GetSpecificReportingDbConnection(siteId)) { sqlConnection.FireInfoMessageEventOnUserErrors = true; sqlConnection.InfoMessage += SqlConnectionInfoMessage; FileVersionInfo fileVersionInfo = FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location); foreach ( string command in new[] { EPMLiveCore.Properties.Resources.ReportingInitDatabaseCreateTables, EPMLiveCore.Properties.Resources.ReportingInitDatabaseCreateProcedures } .Select(s => s.Replace("@version", "''" + fileVersionInfo.FileVersion + "''"))) { try { using (var sqlCommand = new SqlCommand(command, sqlConnection)) { sqlCommand.CommandType = CommandType.Text; sqlCommand.ExecuteNonQuery(); } } catch (SqlException exception) { foreach (SqlError sqlError in exception.Errors) { LogError(string.Format("Msg {0}, Procedure {1}, Line {2}", sqlError.Number, sqlError.Procedure, sqlError.LineNumber)); LogError(exception.Message); } } } object username = null; try { using ( var sqlCommand = new SqlCommand("SELECT ClientUsername FROM dbo.RPTDATABASES WHERE (SiteId = @SiteId)", epmData.GetEPMLiveConnection)) { sqlCommand.Parameters.AddWithValue("@SiteId", siteId); sqlCommand.CommandType = CommandType.Text; username = sqlCommand.ExecuteScalar(); } } catch (SqlException exception) { //foreach (SqlError sqlError in exception.Errors) //{ // LogError(string.Format("Msg {0}, Procedure {1}, Line {2}", sqlError.Number, // sqlError.Procedure, sqlError.LineNumber)); // LogError(exception.Message); //} } if (username != null && username != DBNull.Value) { const string query = @"EXEC sp_executesql N'IF (ISNULL(OBJECT_ID(''@SP''),'''')) <> '''' BEGIN PRINT ''Granting EXECUTE permission to @Username on @SP'' GRANT EXECUTE ON @SP TO @Username END'"; foreach (string sp in new[] { "spRPTWork", "spRPTGetCapacity", "spRPTGetTotalCapacity" }) { try { username = username.ToString().Replace("'", "''"); using ( var sqlCommand = new SqlCommand(query.Replace("@SP", sp).Replace("@Username", username.ToString()), sqlConnection)) { sqlCommand.CommandType = CommandType.Text; sqlCommand.ExecuteNonQuery(); } } catch (SqlException exception) { foreach (SqlError sqlError in exception.Errors) { LogError(string.Format("Msg {0}, Procedure {1}, Line {2}", sqlError.Number, sqlError.Procedure, sqlError.LineNumber)); LogError(exception.Message); } } } } } }