Connect to the remote server in SQL Server and create and delete a new database

Connect to the remote server in SQL Server and create and delete a new database

 

1. Create a new database

/*
Connect to SQL2000 through the query analyzer in sql2005 and create the tmpdb Database: succeeded

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

*/

----------------------- The storage creation process starts --------------------
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. If this parameter is not specified, it indicates logging on as a Windows user.
@ PWD sysname = n'' -- Password
As
Set nocount on
Declare @ srvid int, -- Server Object
@ Dbsid int,
@ Dbid int, -- create a database object
@ Dbfile int, -- create a database file
@ Logfile int, -- create a database log file
@ Brief STR nvarchar (4000)
Declare @ err int, @ SRC varchar (255), @ DESC varchar (255) -- error handling variable

If isnull (@ servername, n'') = n'' set @ servername = @ servername -- default: Local Database

-- Create an sqldmo object ·
Exec @ err = sp_oacreate 'sqldmo. sqlserver ', @ srvid out
If @ err <> 0 goto lb_err

-- Connect to the server
If isnull (@ username, n'') = n'' -- Log On As a Windows User
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
-- Create a new database object
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 attributes of data files and log files. If this parameter is left blank, It is the default attribute of SQL Server.
-- Create a new database file object 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, 'filegrouwth', 1
If @ err <> 0 goto lb_err
-- Add new database objects to DB files
Exec @ err = sp_oamethod @ dbid, 'filegroups. Item ("primary"). dbfiles. add', null, @ dbfile
If @ err <> 0 goto lb_err

-- Create a new database log file object 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'
-- Add new database objects to DB files
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 @ brief STR = 'add'
Exec @ err = sp_oamethod @ dbsid, @ brief STR, null, @ dbid
*/

-- End
Set @ err = 0
Goto lb_exit

-- Handle errors
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. delete a 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. If this parameter is not specified, it indicates logging on as a Windows user.
@ PWD sysname = n'' -- Password
As
Set nocount on
Declare @ srvid int, -- Server Object
@ Dbsid int,
@ Dbid int, -- database object
@ Brief STR nvarchar (4000)
Declare @ err int, @ SRC varchar (255), @ DESC varchar (255) -- error handling variable

If isnull (@ servername, n'') = n'' set @ servername = @ servername -- default: Local Database

-- Create an sqldmo object ·
Exec @ err = sp_oacreate 'sqldmo. sqlserver ', @ srvid out
If @ err <> 0 goto lb_err

-- Connect to the server
If isnull (@ username, n'') = n'' -- Log On As a Windows User
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 a 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. delete', null, @ des_db
If @ err <> 0 goto lb_err
*/

-- End
Set @ err = 0
Goto lb_exit

-- Handle errors
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' 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 sp_oadestroy @ srvid
Exec @ err = sp_oamethod @ srvid, 'disconnect'
Return @ err
Go

-- For more functions, refer to books online