//取SQLSERVER的版本
function getSQLVersion(ServerName,LoginName,LoginPassword:String):TSqlVersion;
var
osqlserver :_SqlServer;
temp :SQLDMO_SQL_VER;
begin
try
oSqlserver :=coSqlserver.Create;
oSqlserver._AddRef;
oSqlserver.Name :=ServerName;
temp :=oSqlserver.PingSQLServerVersion(ServerName,LoginName,LoginPassword);
if temp=SQLDMOSQLVer_Unknown then
result :=UnknownVer
else if temp=SQLDMOSQLVer_Pre_60 then
result :=SQL60
else if temp=SQLDMOSQLVer_60 then
result :=SQL60
else if temp=SQLDMOSQLVer_65 then
result :=SQL65
else if temp=SQLDMOSQLVer_70 then
result :=SQL70
else
result :=SQL2000;
oSqlserver._Release;
oSqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘取SQLSERVER版本資訊出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//取安裝SQLSERVER操作系統的版本
function getOSType(ServerName:String):ToSTypes;
var
oSqlserver :_SqlServer;
begin
try
oSqlServer :=coSqlServer.Create;
oSqlserver._AddRef;
oSqlserver.Name :=ServerName;
if oSqlserver.IsOS(SQLDMO_WIN95) then
result :=Win98ORWin95
else if oSqlserver.IsOS(SQLDMO_WINNT) then
result :=WinNTOR2000
else
result :=SysUnknown;
oSqlserver._Release;
oSqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘取SQLSERVER操作系統出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//停止SQLServer
function stopSQLServer(serverName:String):Boolean;
var
oSqlserver :_SqlServer;
begin
try
oSqlServer :=coSqlserver.Create;
oSqlserver._AddRef;
oSqlserver.Name :=ServerName;
oSqlServer.Stop;
oSqlserver._Release;
oSqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘停止SQLSERVER服務出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//暫停SQLServer
function PauseSQLServer(ServerName:String):Boolean;
var
oSqlserver :_SqlServer;
begin
try
oSqlServer :=coSqlserver.Create;
oSqlserver._AddRef;
oSqlserver.Name :=ServerName;
oSqlServer.Pause;
oSqlserver._Release;
oSqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘暫停SQLSERVER服務出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//取SQLSERVER的狀態
function getSQLServerStatus(ServerName:String):TSQlServerStatus;
var
oSqlServer :_SqlServer;
begin
try
osqlserver :=coSqlserver.Create;
osqlServer._AddRef;
osqlserver.Name :=serverName;
if oSqlserver.Status=SQLDMOSvc_Unknown then
result :=Unknown
else if oSqlserver.Status=SQLDMOSvc_Running then
result :=Running
else if oSqlserver.Status=SQLDMOSvc_Paused then
result :=Paused
else if oSqlserver.Status=SQLDMOSvc_Stopped then
result :=Stopped
else if oSqlserver.Status=SQLDMOSvc_Starting then
result :=Starting
else if oSqlserver.Status=SQLDMOSvc_Stopping then
result :=Stopping
else if oSqlserver.Status=SQLDMOSvc_Continuing then
result :=Continuing
else if oSqlserver.Status=SQLDMOSvc_Pausing then
result :=Pausing;
oSqlserver._Release;
oSqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘取SQLSERVER狀態出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//啟動SQLServer服務
function StartSQLServer(ServerName,LoginName,LoginPassword:String):Boolean;
var
oSqlServer :_SqlServer;
begin
try
osqlserver :=coSqlserver.Create;
oSqlServer._AddRef;
osqlserver.Name :=serverName;
if oSqlServer.Status =SQLDMOSvc_Stopped then //服務停止
oSqlServer.Start(False,ServerName,LoginName,LoginPassword);
if oSqlServer.Status=SQLDMOSvc_Paused then //服務暫停
oSqlServer.Continue;
if (oSqlServer.Status=SQLDMOSvc_Paused) or (oSqlServer.Status=SQLDMOSvc_Stopped) then
result :=False
else
result :=true;
oSqlServer._Release;
oSqlServer :=nil;
except
on E :Exception do
begin
result:=False;
application.MessageBox(Pchar(‘啟動SQLSERVER服務出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//創建備份設備
procedure createBackUpDevice(serverName,LoginName,LoginPassword,DeviceName,FileName:String);
var
osqlServer :_SqlServer;
oBackupDevice :_BackupDevice;
begin
try
osqlServer :=coSqlServer.Create;
osqlserver._AddRef;
osqlServer.Connect(servername,LoginName,LoginPassword);
oBackupDevice :=coBackupDevice.Create;
oBackupDevice.Name :=DeviceName;
oBackupDevice.PhysicalLocation :=FileName;
oBackupDevice.Type_ :=SQLDMODevice_DiskDump;
osqlServer.BackupDevices.Add(oBackupDevice);
osqlServer.DisConnect;
osqlserver._Release;
osqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘創建SQLSERVER備份設備出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//刪除備份設備
procedure dropBackUpDevice(serverName,Loginname,LoginPassword,deviceName:String);
var
osqlServer :_SqlServer;
count,i:Integer;
oBackupDevice :_BackupDevice;
begin
try
osqlServer :=coSqlServer.Create;
osqlServer._AddRef;
osqlServer.Connect(servername,LoginName,LoginPassword);
count :=osqlServer.BackupDevices.Count;
For i :=1 to count do
begin
oBackupDevice:=osqlServer.BackupDevices.Item(i);
if UpperCase(Trim(oBackupDevice.Name))=UpperCase(trim(deviceName)) then
begin
oSqlServer.BackupDevices.Remove(i);
break;
end;
end;
oSqlserver.DisConnect;
oSqlServer._Release;
oSqlServer :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘刪除SQLSERVER備份設備出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//取所有的備份設備
function GetBackupDeviceInfo(serverName,LoginName,LoginPassword:String):TStringList;
var
osqlServer :_SqlServer;
count,i:Integer;
oBackupDevice :_BackupDevice;
rtn :TStringList;
begin
try
osqlserver :=cosqlServer.Create;
osqlServer._AddRef;
osqlserver.Connect(serverName,LoginName,LoginPassword);
count :=osqlServer.BackupDevices.Count;
rtn :=TStringList.Create;
For i :=1 to count do
begin
oBackupDevice :=oSqlserver.BackupDevices.Item(i);
rtn.Append(oBackupDevice.Name);
end;
result :=rtn;
osqlserver.DisConnect;
osqlserver._Release;
osqlserver :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘取SQLSERVER備份設備資訊出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//殺所有連接SqlServer的線程
procedure killprocess(serverName,dataBaseName,LoginName,LoginPassword:String);
var
oqr :QueryResults;
osqlServer :_SqlServer;
coli,colcount,count,i :Integer;
rs : _RecordSet;
iColPIDNum :Integer;
iColDbName :Integer;
strName,strDBName:String;
lPID :Integer;
begin
try
osqlserver :=coSqlserver.Create;
osqlserver._AddRef;
osqlServer.Connect(serverName,Loginname,LoginPassword);
oqr :=osqlserver.EnumProcesses(-1);
iColPIDNum :=-1;
iColDbName :=-1;
colcount :=oqr.Columns;
for i :=1 to colcount do
begin
strName :=oqr.ColumnName[i];
if Uppercase(strName)=‘SPID‘ then
iColPIDNum :=i
else if Uppercase(strName)=‘DBNAME‘ then
iColDbName :=I;
IF (iColPIDNum<>-1) and (iColDbName<>-1) then
break;
end;
count :=oqr.Rows;
for i :=1 to count do
begin
lPID :=oqr.GetColumnLong(i,iColPIDNum);
strDBName:=oqr.GetColumnString(i,iColDbName);
if Uppercase(Trim(strDBName))=Uppercase(trim(dataBaseName)) then
oSqlserver.KillProcess(lPID);
end;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘刪除Sqlserver線程出錯：‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//取服務器所有的資料庫
function getAllDataBases(serverName,LoginName,LoginPassword:String):TStringList;
var
oSqlServer :_SqlServer;
rtn :TStringList;
odataBase :_DataBase;
count,i :Integer;
begin
try
osqlServer :=coSqlServer.Create;
osqlServer._AddRef;
osqlServer.Connect(ServerName,LoginName,LoginPassword);
count :=osqlServer.Databases.Count;
rtn :=TStringList.Create;
for i :=1 to count do
begin
odataBase :=osqlserver.Databases.Item(i,‘owner‘);
rtn.Append(odataBase.Name);
end;
result :=rtn;
osqlServer.DisConnect;
oSqlServer._Release;
osqlServer :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘取SQLSERVER資料庫列表出錯了‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//取網絡中所有SQLServer服務器列表
Function GetAllServers:TStringList;
var
sApp : _Application ;
sName : NameList;
rtn :TStringList;
count,i :Integer;
begin
try
sApp :=coApplication.Create;
sName :=sApp.ListAvailableSQLServers;
rtn :=TStringList.Create;
count :=SName.Count;
for i :=0 to count -1 do
begin
rtn.Append(sName.Item(i));
end;
result :=rtn;
except
on E :Exception do
begin
application.MessageBox(Pchar(‘取網絡中的SQLSERVER列表出錯了‘+E.message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//刪除資料庫
procedure dropDatabase(serverName,LoginName,LoginPassword,dataBaseName:String);
var
osqlServer:_SqlServer;
begin
try
osqlServer :=coSqlserver.Create;
osqlServer._AddRef;
osqlServer.Connect(serverName,LoginName,LoginPassword);
osqlServer.Databases.Remove(dataBaseName,‘owner‘);
oSqlServer.DisConnect;
osqlServer._Release;
oSqlServer :=nil;
except
on E:Exception do
begin
application.MessageBox(Pchar(‘刪除資料庫錯誤：‘+E.Message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//創建資料庫
procedure createDatabase(serverName,LoginName,LoginPassword,dataBaseName:String);
var
odataBase :_Database;
oDBFileData :_DBFile;
oLogFile :_LogFile;
osqlServer :_SqlServer;
begin
try
osqlServer :=cosqlServer.Create;
osqlServer._AddRef;
osqlServer.Connect(serverName,LoginName,LoginPassword);
odataBase :=coDataBase.Create;
odataBase._AddRef;
oDBFileData :=coDBFile.Create;
oDBFileData._AddRef;
oLogFile :=coLogFile.Create;
oLogFile._AddRef;
oDataBase.Name :=dataBaseName;
oDBFileData.Name :=dataBaseName;
oDBFileData.PhysicalName :=oSqlServer.Registry.SQLDataRoot +‘\data\‘+dataBaseName+‘.mdf‘;
oDBFileData.PrimaryFile :=true;
oDBFileData.Size :=2;
oDBFileData.FileGrowthType := SQLDMOGrowth_MB;
oDBFileData.FileGrowth := 1;
oDatabase.FileGroups.Item(‘PRIMARY‘).DBFiles.Add(oDBFileData);
oLogFile.Name :=dataBaseName+‘Log‘;
oLogFile.PhysicalName :=oSqlServer.Registry.SQLDataRoot +‘\data\‘+dataBaseName+‘.ldf‘;
oLogFile.Size :=2;
oDatabase.TransactionLog.LogFiles.Add(oLogFile);
oSqlServer.Databases.Add(oDatabase);
osqlServer.DisConnect;
oSqlServer._Release;
oSqlServer :=nil;
oDataBase._Release;
oDataBase :=nil;
oDBFileData._Release;
oDBFileData :=nil;
oLogFile._Release;
oLogFile :=nil;
except
on E:Exception do
begin
application.MessageBox(Pchar(‘創建資料庫錯誤：‘+E.Message),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//停止SQLSERVER代理
procedure stopJobServer(serverName,LoginName,LoginPassword:String);
var
oSqlServer :_SqlServer;
oJobServer :JobServer;
begin
try
oSqlServer :=coSqlServer.Create;
oSqlServer._AddRef;
osqlServer.Connect(ServerName,LoginName,LoginPassword);
oJobServer :=osqlServer.JobServer;
if (oJobServer.Status=SQLDMOSvc_Running) then
begin
OjobServer.Stop;
end;
oSqlServer.DisConnect;
oSqlServer._Release;
oSqlServer :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(E.Message+‘停止SQLSERVER代理錯誤！‘),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//啟動SQLServer代理
procedure startJobserver(serverName,LoginName,LoginPassword:String);
var
oSqlServer :_SqlServer;
oJobServer :JobServer;
begin
try
oSqlServer :=coSqlServer.Create;
oSqlServer._AddRef;
osqlServer.Connect(ServerName,LoginName,LoginPassword);
oJobServer :=osqlServer.JobServer;
if (oJobServer.Status<>SQLDMOSvc_Starting) and (oJobServer.Status<>SQLDMOSvc_Running) then
begin
OjobServer.Start;
end;
osqlServer.DisConnect;
oSqlServer._Release;
oSqlServer :=nil;
except
on E :Exception do
begin
application.MessageBox(Pchar(E.Message+‘啟動SQLSERVER代理錯誤！‘),‘系統提示‘,MB_OK+MB_ICONSTOP);
end;
end;
end;
//取SQL的安裝路徑
function getSqlRootPath(serverName,LoginUserName,LoginPassword:String):String;
var
osqlServer :_SqlServer;
begin
try
osqlServer :=coSqlServer.Create;
oSqlServer._AddRef;
oSqlServer.Connect(ServerName,LoginUserName,LoginPassword);
result :=oSqlServer.Registry.SQLRootPath;
osqlServer.DisConnect;
osqlServer._Release;
osqlServer :=nil;
except
Raise;
end;
end;
end.
