今天写了一个压缩日志的存储过程遇到了点问题,执行结果是提示找不到存储过程dbcc SHRINKDATABASE ( xxx )请问那位知道是怎么回事 ?
create procedure xxx
(
@DatabaseName varchar( 50 )
)
as
begin if exists( select groupid from master.dbo.sysaltfiles where [name] = @DatabaseName )
begin --1
declare @error int , @sql varchar( 100 )
set @error = 0
begin transaction
set @sql = 'backup log '+@DatabaseName+' with no_log'
set @error = @@error
if( @error = 0 )
begin --2
set @sql = 'dbcc SHRINKDATABASE ( '+@DatabaseName+' )'
execute @sql
set @error = @@error
if( @error = 0 )
begin
commit transaction
end
else
begin
rollback transaction
end
end --2
else
begin --3
rollback transaction
return @@error
end --3
end --1end
go
create procedure xxx
(
@DatabaseName varchar( 50 )
)
as
begin if exists( select groupid from master.dbo.sysaltfiles where [name] = @DatabaseName )
begin --1
declare @error int , @sql varchar( 100 )
set @error = 0
begin transaction
set @sql = 'backup log '+@DatabaseName+' with no_log'
set @error = @@error
if( @error = 0 )
begin --2
set @sql = 'dbcc SHRINKDATABASE ( '+@DatabaseName+' )'
execute @sql
set @error = @@error
if( @error = 0 )
begin
commit transaction
end
else
begin
rollback transaction
end
end --2
else
begin --3
rollback transaction
return @@error
end --3
end --1end
go
exec xxx 'DatabaseName'
set @sql = 'exec sp_msforeachtable ''dbcc SHRINKDATABASE (csdn_test)'''
execute @sql
set @sql = 'exec sp_msforeachtable ''dbcc SHRINKDATABASE (csdn_test)'''
execute @sql
DBCC SHRINKDATABASE 是一种数据库控制台命令,不能通过
set @sql = 'dbcc SHRINKDATABASE ( '+@DatabaseName+' )'
execute @sql
这样调用
其次:DBCC SHRINKDATABASE不用于自己定义的事务内部使用
set @sql = 'exec master..sp_msforeachtable ''dbcc SHRINKDATABASE (csdn_test)'''
execute (@sql)
---1
set @sql = 'exec master..sp_msforeachtable ''dbcc SHRINKDATABASE (csdn_test)'''
execute (@sql)
/*
命令已成功完成。
*/
----2set @sql = 'dbcc SHRINKDATABASE (csdn_test)'
execute (@sql)
/*
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。*/