Hola a tod@s..!
Como hoy he estado un poco inspirado en las publicaciones en los blog.. pues me he atrevido a poneros un script de auto-ayuda
Básicamente la necesidad era restaurar unas cuantas BBDD's que estaban en una SAN y hacerlo lo mas rápido en un SQL Server.. pues ahi va.. y espero os sea de ayuda.. en esos momentos de googleo.. :)
--**************************************************************+
--// Procedimiento que restura "n" desde una SAN indicada hacia un SQL Server.
--**************************************************************+
--
CREATE PROC [dbo].[PR_RestoreControl] @dbMediaBCK sysname, @RutaData sysname, @RutaLog sysname
AS
DECLARE @QueryRestore varchar(999)
declare @sql4 varchar(200),@sql3 varchar(200),@sql2 varchar(200),@sql1 varchar(200),@NroFR int, @dbname sysname
declare @logicalname nvarchar(128), @PhyName nvarchar(260),@Type char(1),@FGroup nvarchar(128), @Fid bigint
declare @infoRestore TABLE
(
xLogicalName nvarchar(128),
xPhysicalName nvarchar(260),
xType char(1),
xFileGroupName nvarchar(128),
xSize numeric(20,0),
xMaxSize numeric(20,0),
xFileID bigint,
xCreateLSN numeric(25,0),
xDropLSN numeric(25,0),
xUniqueID uniqueidentifier,
xReadOnlyLSN numeric(25,0),
xReadWriteLSN numeric(25,0),
xBackupSizeInBytes bigint,
xSourceBlockSize int,
xFileGroupID int,
xLogGroupGUID uniqueidentifier,
xDifferentialBaseLSN numeric(25,0),
xDifferentialBaseGUID uniqueidentifier,
xIsReadOnly bit,
xIsPresent bit,
xTDEThumbprint varbinary(32)
)
declare @infoDBRestore TABLE
(
xBackupName nvarchar(128),
xBackupDescription nvarchar(255),
xBackupType smallint,
xExpirationDate datetime,
xCompressed smallint,
xPosition smallint,
xDeviceType tinyint,
xUserName nvarchar(128),
xServerName nvarchar(128),
xDatabaseName nvarchar(128),
xDatabaseVersion int,
xDatabaseCreationDate datetime,
xBackupSize numeric(20,0),
xFirstLSN numeric(25,0),
xLastLSN numeric(25,0),
xCheckpointLSN numeric(25,0),
xDatabaseBackupLSN numeric(25,0),
xBackupStartDate datetime,
xBackupFinishDate datetime,
xSortOrder smallint,
xCodePage smallint,
xUnicodeLocaleId int,
xUnicodeComparisonStyle int,
xCompatibilityLevel tinyint,
xSoftwareVendorId int,
xSoftwareVersionMajor int,
xSoftwareVersionMinor int,
xSoftwareVersionBuild int,
xMachineName nvarchar(128),
xFlags int,
xBindingID uniqueidentifier,
xRecoveryForkID uniqueidentifier,
xCollation nvarchar(128),
xFamilyGUID uniqueidentifier,
xHasBulkLoggedData bit,
xIsSnapshot bit,
xIsReadOnly bit,
xIsSingleUser bit,
xHasBackupChecksums bit,
xIsDamaged bit,
xBeginsLogChain bit,
xHasIncompleteMetaData bit,
xIsForceOffline bit,
xIsCopyOnly bit,
xFirstRecoveryForkID uniqueidentifier,
xForkPointLSN numeric(25,0),
xRecoveryModel nvarchar(60),
xDifferentialBaseLSN numeric(25,0),
xDifferentialBaseGUID uniqueidentifier,
xBackupTypeDescription nvarchar(60),
xBackupSetGUID uniqueidentifier,
xCompressedBackupSize bigint NULL
)
/*Carga informacion de Medio de Backup */
BEGIN TRY
IF @RutaData is null
BEGIN
declare @vdefdata sysname
create table #vregistrod
(valor sysname,data sysname )
insert into #vregistrod
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData'
Select @vDefData = data from #vregistrod
Set @RutaData = @vdefdata
END
IF @RutaLog is null
BEGIN
declare @vdefLog sysname
create table #vregistrol
(valor sysname,data sysname )
insert into #vregistrol
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog'
Select @vdefLog = data from #vregistrol
Set @RutaLog = @vdefLog
END
END TRY
BEGIN CATCH
RAISERROR('No se puede acceder a los parametros por defecto ',16,1);
END CATCH
IF EXISTS (select name from master.sys.backup_devices where name = ''+@dbMediaBCK+'')
BEGIN
BEGIN TRY
INSERT INTO @infoRestore
EXEC ('RESTORE FILELISTONLY FROM ['+@dbMediaBCK+']')
INSERT INTO @infoDBRestore
EXEC ('RESTORE HEADERONLY FROM ['+@dbMediaBCK+']')
END TRY
BEGIN CATCH
RAISERROR ('No se puede acceder Medio de BACKUP',16,1);
END CATCH
SELECT @dbname = xDatabaseName from @infoDBRestore
SET @sql1 ='RESTORE DATABASE ['+@dbname+'] FROM ['+@dbMediaBCK+'] WITH FILE = 1, '
SET @sql2 = ''
SET @sql3 = ''
SET @sql4 ='RECOVERY, NOUNLOAD, STATS = 10'
-- Construccion de Query para informacion de Datos que resturaremos...
DECLARE Cur_read CURSOR FOR
Select xLogicalName,xPhysicalName,xType,xFileGroupName,xFileID from @infoRestore
OPEN Cur_read
FETCH NEXT FROM Cur_read INTO @logicalname, @PhyName,@Type,@FGroup,@Fid;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Type = 'D'
BEGIN
IF @Fid = 1
BEGIN
SET @PhyName = @RutaData+'\'+@dbname+convert(char(1),@Fid)+'.mdf'
END
ELSE BEGIN
SET @PhyName = @RutaData+'\'+@dbname+convert(char(1),@Fid)+'.ndf'
END
SET @sql2 = @sql2 + 'MOVE N'''+@logicalname+''' TO N'''+@PhyName+''', '
END
IF @Type = 'L'
BEGIN
IF @Fid <> 1
BEGIN
SET @PhyName = @RutaLog+'\'+@dbname+convert(char(1),@Fid)+'.ldf'
END
SET @sql3 = @sql3 + 'MOVE N'''+@logicalname+''' TO N'''+@PhyName+''', '
END
FETCH NEXT FROM Cur_read INTO @logicalname, @PhyName,@Type,@FGroup,@Fid;
END
CLOSE Cur_read
DEALLOCATE Cur_read
SET @QueryRestore = @sql1+''+
@sql2+''+
@sql3+''+
@sql4
EXEC (@QueryRestore)
END
ELSE BEGIN
RAISERROR ('No Existe el Medio de Restore. ',16, 1)
END
lunes, 10 de diciembre de 2012
viernes, 9 de marzo de 2012
Empezamos Chequeando las Bases de Datos en nuestros SQL
Aqui va el primer remember..
Pues esto empezo con la necesidad de buscar una alternativa a ejecutar un mantenimiento de BBDD sin utilizar DTSx SSIS y poder personalizar y automatizar dichos procesos guardando lo que hace el comando de CHECKDB, y ademas gurdar desde cuando se ha realizado el ultimo checkdb de la BBDD.
Bueno pues se me ocurrió hacerlo así..
Paso 1: Creamos las tablas donde guardaremos la información del checkdb.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQL Server", ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 1: Creacion Tablas para LOG de Ejecuciones
-- @ByTriki
--####################################################################################
use MSDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_Mantenance_InfoCheckdb')
DROP TABLE [dbo].[tbl_Mantenance_InfoCheckdb]
GO
CREATE TABLE [dbo].[tbl_Mantenance_InfoCheckdb](
[NomInstancia] [sysname] NOT NULL,
[NomBaseDatos] [sysname] NOT NULL,
[Ultimo_Check] [datetime] NULL
) ON [PRIMARY]
GO
IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_Mantenance_HistoryCheckdb')
DROP TABLE [dbo].[tbl_Mantenance_HistoryCheckdb]
GO
CREATE TABLE [dbo].[tbl_Mantenance_HistoryCheckdb](
[ServerName] [varchar](100) NULL CONSTRAINT [DF_tbl_Mantenance_HistoryCheckdb_ServerName] DEFAULT (@@servername),
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [varchar](100) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [bigint] NULL,
[IndId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[Fecha_Ejecucion] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQLServer" , ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 2: Creacion Procedimiento Almacenado - ejecucion sentencias T-SQL
-- @ByTriki
--####################################################################################
USE [msdb]
GO
GO
CREATE PROC [dbo].[pr_Mantenance_CheckbdBBDD](@dbmore20Gb char(2))
AS
SET @dbmore20Gb = UPPER(@dbmore20Gb)
DECLARE @dbname sysname, @SQL nvarchar(1000), @sub varchar(200)
DECLARE @HistoryCheckdb TABLE
( [Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] varchar(100) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [bigint] NULL,
[IndId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL
)
DECLARE @InfoChequeoBD TABLE
( ParObject nvarchar(1000) NULL,
NomObject nvarchar(1000) NULL,
Registro nvarchar(1000) NULL,
Valor nvarchar(1000) NULL,
dbname nvarchar(1000) NULL
)
DECLARE @TBLdbname TABLE
(
dbname1 sysname,
tamBBDD int
)
INSERT INTO @TBLdbname
SELECT sd.name ,(sum(sm.size)*8)/1024 as [TamMB]FROM sys.databases as sd
INNER JOIN SYS.MASTER_FILES as sm
ON sd.database_id = sm.database_id
WHERE sd.name NOT IN ('master','model','msdb','tempdb')
AND sd.state_desc = 'ONLINE'
AND sd.source_database_id IS NULL
AND sd.is_read_only = 0
group by sd.name
order by [TamMB] desc
IF @dbmore20Gb = 'SI'
BEGIN
DECLARE Puntero CURSOR READ_ONLY FOR
select dbname1 from @TBLdbname
where tamBBDD > 20000
OPEN Puntero
FETCH NEXT FROM Puntero INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
-- Se guarda informacion del proceso CHECKDB en una tabla de la BBDD.
SET @SQL = 'DBCC CHECKDB ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @HistoryCheckdb
EXEC (@SQL)
PRINT @@ERROR
IF @@ERROR <> 0
BEGIN
Select @@ROWCOUNT
Print ('Fallo en la ejecucion "DBCC CHECKDB --> '+@dbname + '')
END
ELSE Begin
SET @SQL = 'DBCC DBINFO ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @InfoChequeoBD(ParObject, NomObject, Registro, Valor)
EXEC(@SQL)
---- ******************************************************************
---- VOLCADO DE INFORMACION a TABLAS ***************
---- ******************************************************************
INSERT INTO dbo.tbl_Mantenance_HistoryCheckdb
SELECT @@SERVERNAME,[Error],[Level]
,[State],[MessageText],[RepairLevel],[Status]
,[DbId],[Id],[IndId],[PartitionId],[AllocUnitId]
,[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot]
,[Allocation],CAST(getdate() AS datetime)
FROM @HistoryCheckdb
INSERT INTO dbo.tbl_Mantenance_InfoCheckdb(NomInstancia, NomBaseDatos, Ultimo_Check)
SELECT DISTINCT @@servername,@dbname , CAST(Valor AS smalldatetime) AS Ultimo_chequeo
FROM @InfoChequeoBD
WHERE Registro = 'dbi_dbccLastKnownGood'
End
waitfor delay '00:00:05'
DELETE FROM @InfoChequeoBD
DELETE FROM @HistoryCheckdb
FETCH NEXT FROM Puntero INTO @dbname
END
CLOSE Puntero
DEALLOCATE Puntero
END
ELSE BEGIN
IF @dbmore20Gb = 'NO'
BEGIN
DECLARE Puntero CURSOR READ_ONLY FOR
select dbname1 from @TBLdbname
where tamBBDD < 20000
OPEN Puntero
FETCH NEXT FROM Puntero INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
-- Se guarda informacion del proceso CHECKDB en una tabla de la BBDD.
SET @SQL = 'DBCC CHECKDB ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @HistoryCheckdb
EXEC (@SQL)
PRINT @@ERROR
IF @@ERROR <> 0
BEGIN
Select @@ROWCOUNT
Print ('Fallo en la ejecucion "DBCC CHECKDB --> '+@dbname + '')
END
ELSE Begin
SET @SQL = 'DBCC DBINFO ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @InfoChequeoBD(ParObject, NomObject, Registro, Valor)
EXEC(@SQL)
---- ******************************************************************
---- VOLCADO DE INFORMACION a TABLAS ***************
---- ******************************************************************
INSERT INTO dbo.tbl_Mantenance_HistoryCheckdb
SELECT @@SERVERNAME,[Error],[Level]
,[State],[MessageText],[RepairLevel],[Status]
,[DbId],[Id],[IndId],[PartitionId],[AllocUnitId]
,[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot]
,[Allocation],CAST(getdate() AS datetime)
FROM @HistoryCheckdb
INSERT INTO dbo.tbl_Mantenance_InfoCheckdb(NomInstancia, NomBaseDatos, Ultimo_Check)
SELECT DISTINCT @@servername,@dbname , CAST(Valor AS smalldatetime) AS Ultimo_chequeo
FROM @InfoChequeoBD
WHERE Registro = 'dbi_dbccLastKnownGood'
End
waitfor delay '00:00:05'
DELETE FROM @InfoChequeoBD
DELETE FROM @HistoryCheckdb
FETCH NEXT FROM Puntero INTO @dbname
END
CLOSE Puntero
DEALLOCATE Puntero
END
END
Pues esto empezo con la necesidad de buscar una alternativa a ejecutar un mantenimiento de BBDD sin utilizar DTSx SSIS y poder personalizar y automatizar dichos procesos guardando lo que hace el comando de CHECKDB, y ademas gurdar desde cuando se ha realizado el ultimo checkdb de la BBDD.
Bueno pues se me ocurrió hacerlo así..
Paso 1: Creamos las tablas donde guardaremos la información del checkdb.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQL Server", ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 1: Creacion Tablas para LOG de Ejecuciones
-- @ByTriki
--####################################################################################
use MSDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_Mantenance_InfoCheckdb')
DROP TABLE [dbo].[tbl_Mantenance_InfoCheckdb]
GO
CREATE TABLE [dbo].[tbl_Mantenance_InfoCheckdb](
[NomInstancia] [sysname] NOT NULL,
[NomBaseDatos] [sysname] NOT NULL,
[Ultimo_Check] [datetime] NULL
) ON [PRIMARY]
GO
IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl_Mantenance_HistoryCheckdb')
DROP TABLE [dbo].[tbl_Mantenance_HistoryCheckdb]
GO
CREATE TABLE [dbo].[tbl_Mantenance_HistoryCheckdb](
[ServerName] [varchar](100) NULL CONSTRAINT [DF_tbl_Mantenance_HistoryCheckdb_ServerName] DEFAULT (@@servername),
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [varchar](100) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [bigint] NULL,
[IndId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[Fecha_Ejecucion] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]
Paso 2: Creacion del procedimiento almacenado para su despliegue.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQLServer" , ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 2: Creacion Procedimiento Almacenado - ejecucion sentencias T-SQL
-- @ByTriki
--####################################################################################
USE [msdb]
GO
GO
CREATE PROC [dbo].[pr_Mantenance_CheckbdBBDD](@dbmore20Gb char(2))
AS
SET @dbmore20Gb = UPPER(@dbmore20Gb)
DECLARE @dbname sysname, @SQL nvarchar(1000), @sub varchar(200)
DECLARE @HistoryCheckdb TABLE
( [Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] varchar(100) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [bigint] NULL,
[IndId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL
)
DECLARE @InfoChequeoBD TABLE
( ParObject nvarchar(1000) NULL,
NomObject nvarchar(1000) NULL,
Registro nvarchar(1000) NULL,
Valor nvarchar(1000) NULL,
dbname nvarchar(1000) NULL
)
DECLARE @TBLdbname TABLE
(
dbname1 sysname,
tamBBDD int
)
INSERT INTO @TBLdbname
SELECT sd.name ,(sum(sm.size)*8)/1024 as [TamMB]FROM sys.databases as sd
INNER JOIN SYS.MASTER_FILES as sm
ON sd.database_id = sm.database_id
WHERE sd.name NOT IN ('master','model','msdb','tempdb')
AND sd.state_desc = 'ONLINE'
AND sd.source_database_id IS NULL
AND sd.is_read_only = 0
group by sd.name
order by [TamMB] desc
IF @dbmore20Gb = 'SI'
BEGIN
DECLARE Puntero CURSOR READ_ONLY FOR
select dbname1 from @TBLdbname
where tamBBDD > 20000
OPEN Puntero
FETCH NEXT FROM Puntero INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
-- Se guarda informacion del proceso CHECKDB en una tabla de la BBDD.
SET @SQL = 'DBCC CHECKDB ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @HistoryCheckdb
EXEC (@SQL)
PRINT @@ERROR
IF @@ERROR <> 0
BEGIN
Select @@ROWCOUNT
Print ('Fallo en la ejecucion "DBCC CHECKDB --> '+@dbname + '')
END
ELSE Begin
SET @SQL = 'DBCC DBINFO ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @InfoChequeoBD(ParObject, NomObject, Registro, Valor)
EXEC(@SQL)
---- ******************************************************************
---- VOLCADO DE INFORMACION a TABLAS ***************
---- ******************************************************************
INSERT INTO dbo.tbl_Mantenance_HistoryCheckdb
SELECT @@SERVERNAME,[Error],[Level]
,[State],[MessageText],[RepairLevel],[Status]
,[DbId],[Id],[IndId],[PartitionId],[AllocUnitId]
,[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot]
,[Allocation],CAST(getdate() AS datetime)
FROM @HistoryCheckdb
INSERT INTO dbo.tbl_Mantenance_InfoCheckdb(NomInstancia, NomBaseDatos, Ultimo_Check)
SELECT DISTINCT @@servername,@dbname , CAST(Valor AS smalldatetime) AS Ultimo_chequeo
FROM @InfoChequeoBD
WHERE Registro = 'dbi_dbccLastKnownGood'
End
waitfor delay '00:00:05'
DELETE FROM @InfoChequeoBD
DELETE FROM @HistoryCheckdb
FETCH NEXT FROM Puntero INTO @dbname
END
CLOSE Puntero
DEALLOCATE Puntero
END
ELSE BEGIN
IF @dbmore20Gb = 'NO'
BEGIN
DECLARE Puntero CURSOR READ_ONLY FOR
select dbname1 from @TBLdbname
where tamBBDD < 20000
OPEN Puntero
FETCH NEXT FROM Puntero INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbname
-- Se guarda informacion del proceso CHECKDB en una tabla de la BBDD.
SET @SQL = 'DBCC CHECKDB ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @HistoryCheckdb
EXEC (@SQL)
PRINT @@ERROR
IF @@ERROR <> 0
BEGIN
Select @@ROWCOUNT
Print ('Fallo en la ejecucion "DBCC CHECKDB --> '+@dbname + '')
END
ELSE Begin
SET @SQL = 'DBCC DBINFO ('''+@dbname+''') WITH TABLERESULTS'
PRINT (@SQL)
INSERT INTO @InfoChequeoBD(ParObject, NomObject, Registro, Valor)
EXEC(@SQL)
---- ******************************************************************
---- VOLCADO DE INFORMACION a TABLAS ***************
---- ******************************************************************
INSERT INTO dbo.tbl_Mantenance_HistoryCheckdb
SELECT @@SERVERNAME,[Error],[Level]
,[State],[MessageText],[RepairLevel],[Status]
,[DbId],[Id],[IndId],[PartitionId],[AllocUnitId]
,[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot]
,[Allocation],CAST(getdate() AS datetime)
FROM @HistoryCheckdb
INSERT INTO dbo.tbl_Mantenance_InfoCheckdb(NomInstancia, NomBaseDatos, Ultimo_Check)
SELECT DISTINCT @@servername,@dbname , CAST(Valor AS smalldatetime) AS Ultimo_chequeo
FROM @InfoChequeoBD
WHERE Registro = 'dbi_dbccLastKnownGood'
End
waitfor delay '00:00:05'
DELETE FROM @InfoChequeoBD
DELETE FROM @HistoryCheckdb
FETCH NEXT FROM Puntero INTO @dbname
END
CLOSE Puntero
DEALLOCATE Puntero
END
END
Paso 3: Y por último como lo ejecutamos.
--####################################################################################
-- Scripts desarrollados para "Un Blog + de SQLServer" , ejecuta en las instancias con
-- SQL 2005, 2008 y R2
-- realiza un check de todas las bbdd de la instancia donde se ejecuta.
-------------------------------------------------------------------------------------
-- Paso 3: Creacion Job Programado - Schedule ** Sunday 14:30 horas.
-- @ByTriki
--####################################################################################
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ByTriki_Mant_Instancia_db20Gb')
EXEC msdb.dbo.sp_delete_job @job_name='ByTriki_Mant_Instancia_db20Gb', @delete_unused_schedule=1
GO
USE [msdb]
GO
BEGIN TRANSACTION
declare @patherrlog varchar(max)
set @patherrlog = convert(sysname,serverproperty('errorlogfilename'))
select @patherrlog = replace(@patherrlog,'\ERRORLOG','\')
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Procesos ByTriki' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Procesos ByTriki'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
set @patherrlog = ''+@patherrlog+'Log_ByTriki_Mant_Instancia_db20Gb.log'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ByTriki_Mant_Instancia_db20Gb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Procesos ByTriki',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Integridad de BBDD',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[pr_Mantenance_CheckbdBBDD] ''SI''',
@database_name=N'msdb',
@output_file_name=@patherrlog,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch-Mantenimiento',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110519,
@active_end_date=99991231,
@active_start_time=143000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-- ******************************************************+
-- Job Para BBDD menor de 20Gb
-- ******************************************************+
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ByTriki_Mant_Instancia_db-20Gb')
EXEC msdb.dbo.sp_delete_job @job_name = N'ByTriki_Mant_Instancia_db-20Gb', @delete_unused_schedule=1
GO
USE [msdb]
GO
BEGIN TRANSACTION
declare @patherrlog2 varchar(max)
set @patherrlog2 = convert(sysname,serverproperty('errorlogfilename'))
select @patherrlog2 = replace(@patherrlog2,'\ERRORLOG','\')
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Procesos ByTriki' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Procesos ByTriki'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ByTriki_Mant_Instancia_db-20Gb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Procesos ByTriki',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Integridad de BBDD] Script Date: 01/20/2012 14:13:57 ******/
set @patherrlog2 = ''+@patherrlog2+'Log_ByTriki_Mant_Instancia_db-20Gb.log'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Integridad de BBDD',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[pr_Mantenance_CheckbdBBDD] ''NO''',
@database_name=N'msdb',
@output_file_name=@patherrlog2,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch-Mantenimiento',
@enabled=1,
@freq_type=8,
@freq_interval=64,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110519,
@active_end_date=99991231,
@active_start_time=143000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ByTriki_MantenimientoInstancia')
EXEC msdb.dbo.sp_delete_job @job_name='ByTriki_MantenimientoInstancia', @delete_unused_schedule=1
GO
Pues lo que os quedara al final de todo esto es una "tablitas" creada en el paso 1 donde almacena siempre todo el resultado del checkdb donde podeis explotarlas para Reportes y demás.. solo esta contemplado para BBDD de Usuario no las de Sistema..
Personalmente lo mas importante de todo esto es chequear el campo que os indico con la flecha para comprobar que las bbdd estan bien de salud :D
Buenos pues nadad chic@s que lo disfruteis.. espero ir subiendo mas cositas...
hasta la próxima...!!!
ByTriki Return!! a Un Blog + de SQL Server !
Hola a tod@s.. despues de años que no actualizo este blog he decidido ir metiendo cosillas del día a día mas seguido ademas de ir subiendo cosas que he ido desarrollando por necesidad o investigación (adaptaciones de otros script de la red a mis necesidades..) :)
Pues nada empezamos..!!!!
Pues nada empezamos..!!!!
viernes, 19 de noviembre de 2010
Informacion de BBDD por linked Server
buff...la ardua labor de poder investigar.... me dijeron tio me puedes decir los tamaños ocupados y disponibles de las bbdd, dije osti... si claro.. exec sp_spaceused'mibbdd'...
bueno era facil intuirlo.. Pero claro se necesitaba la informacion de unas 30 instancias de SQL Server y como siempre pasa hay diferentes versiones.. SQL 2000, 2005 y 2008..
Buenos pues analizamos de donde salen los datos del sp_spaceused.. bufff (os dejo la duda para que investigueis...) pues a ver me sale este "truño" que eureka da la info que necesitamos de una lista de Linked server guardados en una tablita auxiliar..
--=======================================================
--== trabajito me ha costao...
--== :)
--=======================================================
BEGIN TRY
DECLARE @SRV varchar(16),@BUSCAsrv sysname,@ShBUSCAsrv sysname, @error int, @Sem varchar(6), @retval int, @dbname sysname
DECLARE @Maquina nvarchar(128),@Inst nvarchar(128),@vtver nvarchar(150),@vtVerBBDD tinyint,@tableHTML NVARCHAR(MAX)
DECLARE @Loop int, @Counter int, @sql NVARCHAR(900),@VSQL varchar(15)
DECLARE @Tbldbsize TABLE(
instancia varchar(100)
,nombbdd varchar(100)
,dbsize int
,logsize in)
DECLARE @TblUsado TABLE(
instancia varchar(100)
,nombbdd varchar(100)
,TotalPages int)
CREATE TABLE #TmpBBDD
(idtbl int identity(1,1),
dbname sysname)
--Calcula la Semana en Curso.
set @Sem = CONVERT(varchar(2),datepart(wk,getdate()))
If LEN(@sem) = 1
Begin
set @sem = CONVERT(varchar(4),datepart(yyyy,getdate())) + '0'+ @sem
End
Else Begin
set @sem = CONVERT(varchar(4),datepart(yyyy,getdate())) + @sem
End
declare @versionsql table
(servername sysname,ver varchar(10))
DECLARE BUSCAsrv CURSOR FOR
Select micadenaconexion from mitabladondeestaloslinkedserver
OPEN BUSCAsrv
FETCH NEXT FROM BUSCAsrv INTO @BUSCAsrv
WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.pr_TestLinkedServer @ShBuscasrv,@retval OUTPUT -- Este solo comprueba que la conexion con el linked es correcta
IF @retval = 0
BEGIN
insert into @versionSQL --// pa ver la version y diferencia las tablas de sistema
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,substring(@@version,21,6)'')')
select @VSQL = ver from @versionsql
where servername = @BUSCAsrv
set @VSQL = LTRIM(RTRIM(@VSQL))
-- ******************************
---PARA VERSIONES CON SQL 2000
-- ******************************
IF @VSQL = '2000'
BEGIN
IF exists (select count(*)from #TmpBBDD)
BEGIN
truncate table #TmpBBDD
If @@Error = 0
Print ('Limpieza de TempBBDD Correcta')
insert into #TmpBBDD
exec ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
print ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
select @Loop = count(*) from #TmpBBDD
SET @Counter = 1
WHILE @Loop >0 and @Counter < @Loop
BEGIN
Select @dbname=dbname from #TmpBBDD
Where @Counter =idtbl
Print ('sql 2000 ' )
Insert into @Tbldbsize (instancia,nombbdd,dbsize,logsize)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''',sum(case when status & 64 = 0 then size else 0 end)
,sum(case when status & 64 <> 0 then size else 0 end)
from ['+@dbname+'].dbo.sysfiles'')')
-- Insertando paginas usadas..
insert into @TblUsado (instancia,nombbdd,TotalPages)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''', sum(reserved)
from ['+@dbname+'].dbo.sysindexes where indid in (0, 1, 255)'')')
Set @Counter = @Counter + 1
END END END
-- ******************************************************
---PARA VERSIONES CON SQL 2005 y SQL 2008 y SQL 2008R2
-- ******************************************************
If @VSQL = '2005' or @VSQL = '2008'
BEGIN
IF exists (select count(*)from #TmpBBDD)
BEGIN
truncate table #TmpBBDD
If @@Error = 0
Print ('Limpieza de TempBBDD Correcta')
insert into #TmpBBDD
exec ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
print ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
select @Loop = count(*) from #TmpBBDD
SET @Counter = 1
WHILE @Loop > 0 and @Counter <= @Loop
BEGIN
Select @dbname=dbname from #TmpBBDD
Where @Counter =idtbl
Insert into @Tbldbsize (instancia,nombbdd,dbsize,logsize)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''',sum(case when status & 64 = 0 then size else 0 end)
,sum(case when status & 64 <> 0 then size else 0 end)
from ['+@dbname+'].dbo.sysfiles'')')
-- Insertando paginas usadas..
insert into @TblUsado (instancia,nombbdd,TotalPages)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''', sum(a.total_pages)
from ['+@dbname+'].sys.partitions p join ['+@dbname+'].sys.allocation_units a on p.partition_id =a.container_id left join ['+@dbname+'].sys.internal_tables it on p.object_id = it.object_id'')')
Set @Counter = @Counter + 1
END END END END
ELSE BEGIN
Print ('Fallo Conexion Servidor '+@BUSCAsrv+ ':comprobar comunicaciones..')
Print @@ROWCOUNT
END
FETCH NEXT FROM BUSCAsrv INTO @BUSCAsrv
END
CLOSE BUSCAsrv
DEALLOCATE BUSCAsrv
--******************************
--*** Resultados de consulta
--*** Aqui pa que salga bonito.... :)
--******************************
declare @pagesperMB dec(15,2)
select @pagesperMB = 8.0/1024.0
Select a.instancia
,b.nombbdd
,convert(dec(15,2),(a.dbsize + a.logsize))/128 as [Tamaño]
,convert(dec(15,2),a.logsize)/128 as LogSize
,convert(dec(15,2),a.dbsize - b.totalpages)/128 as Disponible
from @Tbldbsize as a
JOIN @TblUsado as b
on a.nombbdd = b.nombbdd
and a.instancia = b.instancia
drop table #TmpBBDD
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
drop table #TmpBBDD
END CATCH
Ahi queda esoo.... espero os ayude..
ByTriki
bueno era facil intuirlo.. Pero claro se necesitaba la informacion de unas 30 instancias de SQL Server y como siempre pasa hay diferentes versiones.. SQL 2000, 2005 y 2008..
Buenos pues analizamos de donde salen los datos del sp_spaceused.. bufff (os dejo la duda para que investigueis...) pues a ver me sale este "truño" que eureka da la info que necesitamos de una lista de Linked server guardados en una tablita auxiliar..
--=======================================================
--== trabajito me ha costao...
--== :)
--=======================================================
BEGIN TRY
DECLARE @SRV varchar(16),@BUSCAsrv sysname,@ShBUSCAsrv sysname, @error int, @Sem varchar(6), @retval int, @dbname sysname
DECLARE @Maquina nvarchar(128),@Inst nvarchar(128),@vtver nvarchar(150),@vtVerBBDD tinyint,@tableHTML NVARCHAR(MAX)
DECLARE @Loop int, @Counter int, @sql NVARCHAR(900),@VSQL varchar(15)
DECLARE @Tbldbsize TABLE(
instancia varchar(100)
,nombbdd varchar(100)
,dbsize int
,logsize in)
DECLARE @TblUsado TABLE(
instancia varchar(100)
,nombbdd varchar(100)
,TotalPages int)
CREATE TABLE #TmpBBDD
(idtbl int identity(1,1),
dbname sysname)
--Calcula la Semana en Curso.
set @Sem = CONVERT(varchar(2),datepart(wk,getdate()))
If LEN(@sem) = 1
Begin
set @sem = CONVERT(varchar(4),datepart(yyyy,getdate())) + '0'+ @sem
End
Else Begin
set @sem = CONVERT(varchar(4),datepart(yyyy,getdate())) + @sem
End
declare @versionsql table
(servername sysname,ver varchar(10))
DECLARE BUSCAsrv CURSOR FOR
Select micadenaconexion from mitabladondeestaloslinkedserver
OPEN BUSCAsrv
FETCH NEXT FROM BUSCAsrv INTO @BUSCAsrv
WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.pr_TestLinkedServer @ShBuscasrv,@retval OUTPUT -- Este solo comprueba que la conexion con el linked es correcta
IF @retval = 0
BEGIN
insert into @versionSQL --// pa ver la version y diferencia las tablas de sistema
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,substring(@@version,21,6)'')')
select @VSQL = ver from @versionsql
where servername = @BUSCAsrv
set @VSQL = LTRIM(RTRIM(@VSQL))
-- ******************************
---PARA VERSIONES CON SQL 2000
-- ******************************
IF @VSQL = '2000'
BEGIN
IF exists (select count(*)from #TmpBBDD)
BEGIN
truncate table #TmpBBDD
If @@Error = 0
Print ('Limpieza de TempBBDD Correcta')
insert into #TmpBBDD
exec ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
print ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
select @Loop = count(*) from #TmpBBDD
SET @Counter = 1
WHILE @Loop >0 and @Counter < @Loop
BEGIN
Select @dbname=dbname from #TmpBBDD
Where @Counter =idtbl
Print ('sql 2000 ' )
Insert into @Tbldbsize (instancia,nombbdd,dbsize,logsize)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''',sum(case when status & 64 = 0 then size else 0 end)
,sum(case when status & 64 <> 0 then size else 0 end)
from ['+@dbname+'].dbo.sysfiles'')')
-- Insertando paginas usadas..
insert into @TblUsado (instancia,nombbdd,TotalPages)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''', sum(reserved)
from ['+@dbname+'].dbo.sysindexes where indid in (0, 1, 255)'')')
Set @Counter = @Counter + 1
END END END
-- ******************************************************
---PARA VERSIONES CON SQL 2005 y SQL 2008 y SQL 2008R2
-- ******************************************************
If @VSQL = '2005' or @VSQL = '2008'
BEGIN
IF exists (select count(*)from #TmpBBDD)
BEGIN
truncate table #TmpBBDD
If @@Error = 0
Print ('Limpieza de TempBBDD Correcta')
insert into #TmpBBDD
exec ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
print ('select * from openquery (['+@ShBUSCAsrv+'],''select name from master..sysdatabases where databaseproperty(name,''''isoffline'''')= 0'')')
select @Loop = count(*) from #TmpBBDD
SET @Counter = 1
WHILE @Loop > 0 and @Counter <= @Loop
BEGIN
Select @dbname=dbname from #TmpBBDD
Where @Counter =idtbl
Insert into @Tbldbsize (instancia,nombbdd,dbsize,logsize)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''',sum(case when status & 64 = 0 then size else 0 end)
,sum(case when status & 64 <> 0 then size else 0 end)
from ['+@dbname+'].dbo.sysfiles'')')
-- Insertando paginas usadas..
insert into @TblUsado (instancia,nombbdd,TotalPages)
EXEC ('select * from openquery (['+@ShBUSCAsrv+'],''select @@servername,'''''+@dbname+''''', sum(a.total_pages)
from ['+@dbname+'].sys.partitions p join ['+@dbname+'].sys.allocation_units a on p.partition_id =a.container_id left join ['+@dbname+'].sys.internal_tables it on p.object_id = it.object_id'')')
Set @Counter = @Counter + 1
END END END END
ELSE BEGIN
Print ('Fallo Conexion Servidor '+@BUSCAsrv+ ':comprobar comunicaciones..')
Print @@ROWCOUNT
END
FETCH NEXT FROM BUSCAsrv INTO @BUSCAsrv
END
CLOSE BUSCAsrv
DEALLOCATE BUSCAsrv
--******************************
--*** Resultados de consulta
--*** Aqui pa que salga bonito.... :)
--******************************
declare @pagesperMB dec(15,2)
select @pagesperMB = 8.0/1024.0
Select a.instancia
,b.nombbdd
,convert(dec(15,2),(a.dbsize + a.logsize))/128 as [Tamaño]
,convert(dec(15,2),a.logsize)/128 as LogSize
,convert(dec(15,2),a.dbsize - b.totalpages)/128 as Disponible
from @Tbldbsize as a
JOIN @TblUsado as b
on a.nombbdd = b.nombbdd
and a.instancia = b.instancia
drop table #TmpBBDD
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
drop table #TmpBBDD
END CATCH
Ahi queda esoo.... espero os ayude..
ByTriki
Suscribirse a:
Entradas (Atom)