在sql server裡串連遠程伺服器，並進行建立和刪除新資料庫

在sql server裡串連遠程伺服器，並進行建立和刪除新資料庫

 

一、建立新庫

/*
測試 在sql2005中通過查詢分析器，串連到sql2000，並建立了 tmpdb 庫：成功

p_CreateDB 'tmpdb1','data','sa','sa'

*/

-----------------------建立預存程序開始--------------------
if object_id('p_CreateDB') is not null
drop procedure p_CreateDB
go
CREATE PROCEDURE p_CreateDB  
@Des_DB sysname, --目標資料庫
@ServerName sysname=N'', --伺服器名
@UserName sysname=N'', --使用者名稱,不指定則表示使用 Windows 身份登入
@pwd sysname=N'' --密碼 
AS
SET NOCOUNT ON
DECLARE @srvid int,--伺服器的對象
 @dbsid int,
 @Dbid int,--建立資料庫物件
 @DBFile int,--建立資料庫DB檔案
 @LogFile int,--建立資料庫Log檔案
 @CmdStr nvarchar(4000)
declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變數

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@ServerName --預設為本機資料庫

--建立sqldmo對象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err

--串連伺服器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登入
BEGIN
  EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
  IF @err<>0 GOTO lb_Err

  EXEC @err=sp_oamethod @srvid,'connect',NULL,@ServerName
END
ELSE
  EXEC @err=sp_oamethod @srvid,'connect',NULL,@ServerName,@UserName,@pwd

IF @err<>0 GOTO lb_Err
--新資料庫物件建立
EXEC @err=sp_oacreate 'SQLDMO.Database',@Dbid OUT
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @Dbid, 'Name',@Des_DB
IF @err<>0 GOTO lb_Err
/*
---這裡可以設定資料檔案與記錄檔的屬性，不寫就由sql server預設
--新資料庫DB檔案對象建立,並設定屬性
EXEC @err=sp_oacreate 'SQLDMO.DBFile',@DBFile OUT
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @DBFile, 'Name','tmpfile'
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @DBFile, 'PhysicalName','c:/tmp.mdf'
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @DBFile, 'PrimaryFile','true'
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @DBFile, 'FileGrowthType',0
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @DBFile, 'FileGrowth',1
IF @err<>0 GOTO lb_Err
--新資料庫物件加入DB檔案
EXEC @err = sp_OAMethod @Dbid,'FileGroups.Item("primary").DBFiles.Add',null,@DBFile
IF @err<>0 GOTO lb_Err

--新資料庫LOG檔案對象建立,並設定屬性
EXEC @err=sp_oacreate 'SQLDMO.LogFile',@LogFile OUT
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @LogFile, 'Name','tmplg'
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @LogFile, 'PhysicalName','c:/tmp.ldf'
--新資料庫物件加入DB檔案
EXEC @err = sp_OAMethod @Dbid,'TransactionLog.LogFiles.Add',null,@LogFile
IF @err<>0 GOTO lb_Err
*/
--在伺服器上建立 新資料庫
EXEC @err = sp_OAMethod @srvid,'Databases.Add',null,@dbid
IF @err<>0 GOTO lb_Err

/*
EXEC @err= sp_OAGetProperty @srvid, 'Databases', @dbsid OUT
IF @err<>0 GOTO lb_Err

SET @CmdStr = 'Add'
EXEC @err = sp_OAMethod @dbsid,@CmdStr,null,@dbid
*/

--結束
SET @err=0
GOTO lb_Exit

--錯誤處理
lb_Err:
  EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT 
  EXEC sp_OADestroy @LogFile
  EXEC sp_OADestroy @DBFile
  EXEC sp_OADestroy @Dbsid
  EXEC sp_OADestroy @Dbid 
  EXEC sp_OADestroy @srvid
  EXEC @err=sp_oamethod @srvid,'DisConnect'
  RAISERROR(N'錯誤編號 %#x, 錯誤源 "%s", 錯誤描述 "%s"',16,1,@err,@src,@desc)
  RETURN -1

lb_Exit:
  EXEC sp_OADestroy @LogFile
  EXEC sp_OADestroy @DBFile
  EXEC sp_OADestroy @Dbsid
  EXEC sp_OADestroy @Dbid 
  EXEC sp_OADestroy @srvid 
  EXEC @err=sp_oamethod @srvid,'DisConnect'
  RETURN @err
GO

 

 

二、刪除資料庫

/*
測試

p_DropDB 'tmpdb1','data','sa','sa'
*/

if object_id('p_DropDB') is not null
drop procedure p_DropDB
go
CREATE PROCEDURE p_DropDB  
@Des_DB sysname, --目標資料庫
@ServerName sysname=N'', --伺服器名
@UserName sysname=N'', --使用者名稱,不指定則表示使用 Windows 身份登入
@pwd sysname=N'' --密碼 
AS
SET NOCOUNT ON
DECLARE @srvid int,--伺服器的對象
 @dbsid int,
 @Dbid int,--資料庫物件
 @CmdStr nvarchar(4000)
declare @err int,@src varchar(255), @desc varchar(255) --錯誤處理變數

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@ServerName --預設為本機資料庫

--建立sqldmo對象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err

--串連伺服器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登入
BEGIN
  EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
  IF @err<>0 GOTO lb_Err

  EXEC @err=sp_oamethod @srvid,'connect',NULL,@ServerName
END
ELSE
  EXEC @err=sp_oamethod @srvid,'connect',NULL,@ServerName,@UserName,@pwd

IF @err<>0 GOTO lb_Err

--刪除資料庫
--這兩個都可以刪除
EXEC @err = sp_OAMethod @srvid,'KillDatabase',null,@Des_DB
IF @err<>0 GOTO lb_Err
/*
--
EXEC @err = sp_OAMethod @srvid,'Databases.Remove',null,@Des_DB
IF @err<>0 GOTO lb_Err
*/

--結束
SET @err=0
GOTO lb_Exit

--錯誤處理
lb_Err:
  EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT 
  EXEC sp_OADestroy @Dbsid
  EXEC sp_OADestroy @Dbid 
  EXEC sp_OADestroy @srvid
  EXEC @err=sp_oamethod @srvid,'DisConnect'
  RAISERROR(N'錯誤編號 %#x, 錯誤源 "%s", 錯誤描述 "%s"',16,1,@err,@src,@desc)
 
  RETURN -1
 
lb_Exit:
  EXEC sp_OADestroy @Dbsid
  EXEC sp_OADestroy @Dbid 
  EXEC sp_OADestroy @srvid 
  EXEC @err=sp_oamethod @srvid,'DisConnect'
  RETURN @err
GO

--更多功能 可以 參考聯機叢書