April 5, 2006 at 10:58 am
I wrote a procedure that create and drop a database if something wrong occurs.
I need to show the error message when an error occur but, together with the error description, the procedure return these lines:
The CREATE DATABASE process is allocating 126.00 MB on disk 'Test_data'.
The CREATE DATABASE process is allocating 12.00 MB on disk 'Test_log'.
Login failed for user 'sa'.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\Test.ldf'.
Deleting database file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\Test.mdf'.
I would like to know how to suppress these messages and return only the error occurred.
Someone here had a problem like this?
April 5, 2006 at 11:03 am
Can you send your proc
Amit Lohia
April 5, 2006 at 11:19 am
OK, here is!
alter procedure sp_new_database
(
@campaign_name varchar(50),
@campaign_client int,
@database_name nvarchar(30),
@database_path nvarchar(100),
@database_size int = 100, -- MB
@database_growth smallint = 10 -- %
)
as
begin
set nocount on
set statistics io off
declare @sql nvarchar(4000), @error smallint
-- database existence verification
if exists (select name from master.dbo.sysdatabases where name = @database_name)
begin
raiserror( 'A database with same name %s already exists', 16, 1, @database_name )
return 50000
end
set @error = 0
-- database creation
if @error = 0
begin
set @sql = 'create database [' + @database_name + '] on (name = N''' + @database_name + '_data'', ' +
'filename = N''' + @database_path + '\' + @database_name + '.mdf'', size = ' +
cast(@database_size as varchar) + ', ' + 'filegrowth = ' +
cast(@database_growth as varchar) + '%) log on (name = N''' +
@database_name + '_log'', filename = N''' +
@database_path + '\' + @database_name + '.ldf'', size = ' +
cast((@database_size/10) as varchar) + ', filegrowth = ' +
cast(@database_growth as varchar) + '%) ' +
'collate SQL_Latin1_General_CP1_CI_AS'
execute( @sql )
if ( @@ERROR 0 ) set @error = 1
end
-- database objects´ creation (tables, views, etc.)
if @error = 0
begin
declare @errmsg varchar(1000)
create table #tbl ( tx_row varchar(1000) )
set @sql = 'osql.exe -S localhost -H localhost -U sa -P nopwd -d ' + @database_name + ' -i d:\scd_objects.sql -n -b'
insert into #tbl execute master.dbo.xp_cmdshell @sql
select top 1 @errmsg = tx_row from #tbl where (tx_row is not null )
if @errmsg is not null
begin
set @error = 1
raiserror( @errmsg, 16, 1 )
end
drop table #tbl
end
-- campaign register´s addition
if @error = 0
begin
insert into
tb_campanha (strCampanha,intCdCliente,bolFgDeleted,strDatabase,bolFgFinalizada)
values
(@campaign_name,@campaign_client,0,@database_name,0)
if ( @@ERROR 0 ) set @error = 1
end
-- undo all if an error has occurred
if @error = 1
begin
if exists (select name from master.dbo.sysdatabases where name = @database_name) execute( 'drop database [' + @database_name + ']' )
delete from tb_campanha where (strCampanha = @campaign_name) and (intCdCliente = @campaign_client)
end
endb
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply