To connect to a remote server in SQL Server and create and delete a new database

To connect to a remote server in SQL Server and to create and delete a new database tutorial

First, create a new library

/*
Test in sql2005 through Query Analyzer, connect to sql2000, and create tmpdb library: Successful

P_createdb ' tmpdb1 ', ' data ', ' sa ', ' sa '

*/

-----------------------create a stored procedure to start--------------------
If object_id (' P_createdb ') is not null
drop procedure P_createdb
Go
CREATE PROCEDURE P_createdb
@Des_DB sysname,--target database
@ServerName sysname=n ',--server name
@UserName sysname=n ',--user name, unspecified, means log on using Windows identity
@pwd sysname=n '--Password
As
SET NOCOUNT on
DECLARE an object @srvid int,--Server
@dbsid int,
@Dbid int,--Create a new database object
@DBFile int,--Create a new database db file
@LogFile int,--New database log file
@CmdStr nvarchar (4000)
declare @err int, @src varchar (255), @desc varchar (255)--Error handling variable

IF ISNULL (@ServerName, N ') =n ' SET @ServerName =@ @ServerName--Default is local database

--Create SQLDMO Objects ·
EXEC @err =sp_oacreate ' Sqldmo.sqlserver ', @srvid out
IF @err <>0 GOTO lb_err

--Connecting to the server
IF ISNULL (@UserName, N ') =n '-Log on using Windows identity
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
-New Database object creates
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
/*
---Here you can set the properties of the data file and the log file, not write it by SQL Server default
-new database DB file object creation, and set properties
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
--New Database object joins DB file
EXEC @err = sp_OAMethod @Dbid, ' Filegroups tutorial. Item (" Primary "). Dbfiles.add ', NULL, @DBFile
IF @err <>0 GOTO lb_err

--New database log file object creation, and set properties
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 '
--New Database object joins DB file
EXEC @err = sp_OAMethod @Dbid, ' TransactionLog.LogFiles.Add ', NULL, @LogFile
IF @err <>0 GOTO lb_err
*/
--Create a new database on the server
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
*/

--End
SET @err =0
GOTO Lb_exit

--Error handling
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 ' Error number% #x, error source '%s ', error description '%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

Ii. deletion of the database

/*
Test

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,--target database
@ServerName sysname=n ',--server name
@UserName sysname=n ',--user name, unspecified, means log on using Windows identity
@pwd sysname=n '--Password
As
SET NOCOUNT on
DECLARE an object @srvid int,--Server
@dbsid int,
@Dbid int,--Database object
@CmdStr nvarchar (4000)
declare @err int, @src varchar (255), @desc varchar (255)--Error handling variable

IF ISNULL (@ServerName, N ') =n ' SET @ServerName =@ @ServerName--Default is local database

--Create SQLDMO Objects ·
EXEC @err =sp_oacreate ' Sqldmo.sqlserver ', @srvid out
IF @err <>0 GOTO lb_err

--Connecting to the server
IF ISNULL (@UserName, N ') =n '-Log on using Windows identity
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

--Delete Database
--both can be deleted
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
*/

--End
SET @err =0
GOTO Lb_exit

--Error handling
Lb_err:
  EXEC sp_OAGetErrorInfo NULL, @src out, @desc out 
  EXEC sp_OADestroy @Dbs ID
  EXEC sp_OADestroy @Dbid  
  exec sp_OADestroy @srvid
  EXEC @err =sp_oamethod @srvid , ' DisConnect '
  RAISERROR (N ' Error number% #x, error source '%s ', error description '%s ', 16,1, @err, @src, @desc)
 
  RETURN-1
 
Lb_exit:
  exec sp_OADestroy @Dbsid
  exec sp_OADestroy @Dbid  
  EXEC s P_oadestroy @srvid  
  EXEC @err =sp_oamethod @srvid, ' DisConnect '
  return @err
Go