From RiskWiki
Jump to: navigation, search

RMSQLAdminLib - BPC DataBase Desktop Support Library

Language: Delphi 7 - 2007

This library does most of the heavy lifting for the shipped RM DB Manager shipped with BPC RM/SM systems as a database desktop/enterprise manager/sgl management studio replacement.

uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls;
  TbpcRMDBReportProgress = procedure(bDisplay: boolean; DisplayString: string; Step, OfSteps: Integer; Msg: string; var bCancelMe: boolean) of object;
  TbpcRMDBReportError =  function(sMSG : string) : boolean of object; // True if stop required
function bpcRMSQLGetSystemRegistryValue(const AKeyPath, AKey : String) : String;
function bpcRMSQLGetRegistryValue(const AKey : String) : String;
procedure bpcRMSQLSetRegistryValue(const AKey, AValue : String);
function bpcRMSQLGetRegistryValueWithDefault(const AKey, ADefaultValue : String) : String;

procedure GetFileListing(const AFolderName : String; var AStringList : TStringList);
// Replace special characters with spaces
// Remove sql comment lines
function ReadSQLFileContents(const AFileName: String): String;
// Modify the ado connection, and return the OLD catalog name;
function  bpcSQLModifyConnectionCatalogName(AADOConnection: TADOCOnnection; const ANewCatalogName: String) : string;
procedure bpcMSSSQLRunSQLScript(AADOCommand: TADOCommand; const ASQLScriptText: String; ReportProgress : TbpcRMDBReportProgress = nil; bIgnoreErrors: boolean = FALSE; ReportError : TbpcRMDBReportError=nil);
function bpcSQLServerIs2005Up( AADOConnection: TADOConnection ) : boolean;
function bpcMSSQLBackupDatabase( AADOConnection: TADOConnection;   bIs2005 : boolean; sDatabaseName, sBackupFileName, sBackupDeviceName : String; var ErrMsg : string ) : boolean;
function bpcMSSQLRestoreDatabase( AADOConnection: TADOConnection;   bIs2005 : boolean; sDatabaseName, sBackupDeviceName : String; var ErrMsg : string ) : boolean;
function bpcMSSQLCopyDatabase( AADOConnection: TADOConnection; bIs2005 : boolean; sOptionalMSSQLBackupDirectory : string; sNewDBName, sDatabaseToCopy : String; var ErrMsg : string ) : boolean;
// Kill all db processes assigned to a database so database has no locks on it (in order to restore over it)
// zzz Incomplete
procedure bpcMSSQLKillDatabaseProcesses(const ADatabaseName: String);
function bpcMSSQLConnectToDB( AADOConnection: TADOConnection; const FDatabaseName : string ) : TADOConnection;
function bpcMSSQLConnectToMasterDB( AADOConnection: TADOConnection ) : TADOConnection;
function bpcSQLMakeADOCommand( AADOConnection: TADOConnection ) : TADOCommand;
procedure bpcMSSQLGetDBProperties(AADOConnection: TADOConnection; const ADBToCopyName : String; out ASourceDBDataFileName, ASourceDBLogFileName : String);
// Drops the named backup device
function bpcMSSQLDropDevice( AADOConnection: TADOConnection; sBackupDeviceName : String; var ErrMsg : string ) : boolean;

// Create builtin SQL Accounts on server
function bpcMSSQLCreateRMSQLAccounts( AADOConnection: TADOConnection;  sriskmanuserpwd : string; var ErrMsg : string ) : boolean;

// Attempt a repair of builtin RM SQL Accounts on server given a connection string and the sa password:
// Extracts the current riskmanuser password from the connection string,
// Connects as sa and attempts to create SQL accounts with that password on the server
// Adds standard users and standard roles to the database if missing and assigns roles to standard accounts
// Ties the standard users to the standard sql accounts if simply orphaned.
// Works on both 2000 and 2005
function bpcMSSQLRMRepairAccountOnRMConnection( AdoConnectionString,  sapwd : string; var ErrMsg : string ) : boolean;

 rsRegKey         = 'Software\Bishop Phillips Consulting\RiskDBManager';

 rsMSSQL2005SetupKeyOptA='SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\Setup';  //This is not really a key that holds much - here just in case we missed a set up option
 rsMSSQL2005MSSQL1SetupKey='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup';
 rsMSSQL2005MSSQL1Key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer';
 rsMSSQL2000MSSQLKey='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' ;