"Delphi通过SQLDMO來控制SQLSERVER"：
关键词：delphi 通过 sqldmo 來 控制 sqlserver
作者： 陆岛工作室
2008-05-30 我在网上找到一个网址， 俄国人的，里面关于 sqldmo的说明有很大的帮助。
地址如下：http://www.delphiturkiye.com/forum/viewtopic.php?f=19&t=20072 希望得空的时候，再来好好研究。
首先， 需要把 sqldmo.dll 文件导入到delphi 中。sqldmo.dll 是一个com, 安装sqlserver 后位于 program files\microsoft sql server\80\tools\binn
打开delphi,project菜单->import type library...,在列表框中找到"microsoft sqldmo objectlibrary(version 8.0)"
导入完后，当我们compile的时候却发现这个单元无法编译, 提示大意为id重复定义, 这个简单, 找到不能编辑的地方，把id 改成 xid就行了，这样就可以编译通过。
在使用过程中,我发现在很多情况下,sqldmo_tlb.pas中的类是不能用的, 出现av错误。比如restore，必须这样使用: createoleobject('sqldmo.restore');
定义相同的类，如ttable等，这个需要处理一下，在ttable前加单元名，如dbe.ttable 或 sqldmo_tlb.ttable 就ok啦。
下需介绍几个常用的功能。
取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
rtn.append(sname.item(i));
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
ojobserver.stop;
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
ojobserver.start;
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; 
