Feeds:
Posts
Comments

Archive for the ‘T SQL’ Category

Split Decimal Values

Select Floor(568.05)

Output : 568

Select Floor(-568.05)

Output : -569

Select Floor(Abs(-568.05))*-1

Output : 568

Select ParseName(-568.05,2)

Output : -568

Select ParseName(-568.05,1)

Output : 05

Advertisements

Read Full Post »

Declare @Month as Int

Set @Month=2

SELECT VisitID,PatientID,ImageId,Trans_No,DATEADD(DAY, DATEDIFF(DAY, VisitDate,
CONVERT(DATETIME, CAST(YEAR(VisitDate) AS VARCHAR(4))
+ RIGHT(‘0’+CAST(DAY(VisitDate) AS VARCHAR(2)),2) +
+ RIGHT(‘0’+CAST(MONTH(VisitDate) AS VARCHAR(2)),2), 112)), VisitDate) As VDate
From SN_Visits_Image
Where Year(VisitDate)=2013 And Day(VisitDate)=1 And Month(VisitDate)=@Month
Update SN_Visits_Image Set VisitDate = VT.VDate
From
SN_Visits_Image SVI Inner join
(
SELECT VisitID,PatientID,ImageId,ImageFileName,Trans_No,DATEADD(DAY, DATEDIFF(DAY, VisitDate,
CONVERT(DATETIME, CAST(YEAR(VisitDate) AS VARCHAR(4))
+ RIGHT(‘0’+CAST(DAY(VisitDate) AS VARCHAR(2)),2) +
+ RIGHT(‘0’+CAST(MONTH(VisitDate) AS VARCHAR(2)),2), 112)), VisitDate) As VDate
From SN_Visits_Image
Where Year(VisitDate)=2013 And Day(VisitDate)=1 And Month(VisitDate)=@Month
) VT on
SVI.VisitID = VT.VisitID And SVI.PatientID = VT.PatientID And SVI.ImageId = VT.ImageId And SVI.ImageFileName = VT.ImageFileName
And SVI.Trans_No = VT.Trans_No

 

Read Full Post »

Create Function Dbo.Part
(@Value Varchar(8000)
,@Part Int
,@Sep Char(1)=’-‘
)Returns Varchar(8000)
As Begin
Declare @Start Int
Declare @Finish Int
Set @Start=1
Set @Finish=CharIndex(@Sep,@Value,@Start)
While (@Part>1 And @Finish>0)Begin
Set @Start=@Finish+1
Set @Finish=CharIndex(@Sep,@Value,@Start)
Set @Part=@Part-1
End
If @Part>1 Set @Start=Len(@Value)+1 — Not found
If @Finish=0 Set @Finish=Len(@Value)+1 — Last token on line
Return SubString(@Value,@Start,@Finish-@Start)
End

 

DECLARE @MRDNUMBER VARCHAR(10)
DECLARE @STRQUERY VARCHAR(MAX),@STROD VARCHAR(100),@STROS VARCHAR(100)
DECLARE @RECVALUE VARCHAR(50)
DECLARE @RECCNT INT,@CNT VARCHAR(2)

SET @MRDNUMBER=’1665773′

SET @RECVALUE = (SELECT TOP 1 FN_OD_FMETHOD FROM MR_FUNDUS WHERE FN_MRD_NUMBER=@MRDNUMBER)

SET @RECCNT = (Select Count(Value) FROM DBO.FN_SPLIT(@RECVALUE,’#’))

SET @CNT=1
SET @STRQUERY = ”
SET @STROD = ”
SET @STROS = ”

WHILE (@RECCNT > 0)

BEGIN
SET @STROD = @STROD + ‘ODMETHOD’ + @CNT + ‘+’ + CHAR(39) + ‘,’ + CHAR(39) + ‘+’
SET @STROS = @STROS + ‘OSMETHOD’ + @CNT + ‘+’ + CHAR(39) + ‘,’ + CHAR(39) + ‘+’

SET @STRQUERY = @STRQUERY + ‘,(SELECT MPM_PARAMETER_VALUE FROM MR_PARAMETER_MASTER1 WHERE MPM_PARAMETER_CD=DBO.PART(FN_OD_FMETHOD,’ + @CNT +’ ,’ + CHAR(39) + ‘#’ + CHAR(39) + ‘)) As ODMETHOD’ + @CNT
SET @STRQUERY = @STRQUERY + ‘,(SELECT MPM_PARAMETER_VALUE FROM MR_PARAMETER_MASTER1 WHERE MPM_PARAMETER_CD=DBO.PART(FN_OS_FMETHOD,’ + @CNT +’ ,’ + CHAR(39) + ‘#’ + CHAR(39) + ‘)) As OSMETHOD’ + @CNT

SET @RECCNT = @RECCNT – 1
SET @CNT = @CNT + 1
END
SET @STROD = (SELECT SUBSTRING(@STROD,1,LEN(@STROD)-5))
SET @STROS = (SELECT SUBSTRING(@STROS,1,LEN(@STROS)-5))

SET @STRQUERY = ‘SELECT FN_MRD_NUMBER,
FN_OD_MCLARITY,
FN_OD_TYPE as ODTYPE,
FN_OD_SHAPE,FN_OD_COLOR,FN_OD_CUP,FN_OD_CD_RATIO,FN_OD_MARGIN,FN_OD_PRETINA,FN_OD_NRR_RIM,
FN_OD_N_VESSELS,FN_OD_VITREOUS,FN_OD_V_ABNORMAL,FN_OD_DISC,
FN_OD_DISC_ABNORMALITY,FN_OD_MACULA,FN_OD_MACULA_ABNORMAL,
FN_OD_RVESSELS,FN_OD_RVESSELS_ABNORMAL,FN_OD_POSTERIOR_POLE,FN_OD_POSTERIOR_POLE_ABNORMALITY,
FN_OS_FMETHOD as OSMETHOD,
FN_OS_MCLARITY,
FN_OS_TYPE,FN_OS_SHAPE,FN_OS_COLOR,FN_OS_CUP,
FN_OS_CD_RATIO,FN_OS_MARGIN,FN_OS_PRETINA,
FN_OS_NRR_RIM,FN_OS_N_VESSELS,FN_OS_VITREOUS,
FN_OS_V_ABNORMAL,FN_OS_DISC,FN_OS_DISC_ABNORMALITY,FN_OS_MACULA,FN_OS_MACULA_ABNORMAL,FN_OS_RVESSELS,FN_OS_RVESSELS_ABNORMAL,FN_OS_POSTERIOR_POLE,
FN_OS_POSTERIOR_POLE_ABNORMALITY,FN_CRT_DATE’ + @STRQUERY + ‘ FROM DBO.MR_FUNDUS WHERE FN_MRD_NUMBER=’ + CHAR(39) + @MRDNUMBER + CHAR(39)
SET @STRQUERY = ‘SELECT TOP 1 FN_MRD_NUMBER,ISNULL(‘ + @STROD + ‘,’+ CHAR(39) + ‘–‘ + CHAR(39) + ‘) AS ODMETHOD,
FN_OD_MCLARITY,
ODTYPE,
FN_OD_SHAPE,FN_OD_COLOR,FN_OD_CUP,FN_OD_CD_RATIO,FN_OD_MARGIN,FN_OD_PRETINA,FN_OD_NRR_RIM,
FN_OD_N_VESSELS,FN_OD_VITREOUS,FN_OD_V_ABNORMAL,FN_OD_DISC,
FN_OD_DISC_ABNORMALITY,FN_OD_MACULA,FN_OD_MACULA_ABNORMAL,
FN_OD_RVESSELS,FN_OD_RVESSELS_ABNORMAL,FN_OD_POSTERIOR_POLE,FN_OD_POSTERIOR_POLE_ABNORMALITY,
ISNULL(‘ + @STROS + ‘,’+ CHAR(39) + ‘–‘ + CHAR(39) + ‘) AS OSMETHOD,
FN_OS_MCLARITY,
FN_OS_TYPE,FN_OS_SHAPE,FN_OS_COLOR,FN_OS_CUP,
FN_OS_CD_RATIO,FN_OS_MARGIN,FN_OS_PRETINA,
FN_OS_NRR_RIM,FN_OS_N_VESSELS,FN_OS_VITREOUS,
FN_OS_V_ABNORMAL,FN_OS_DISC,FN_OS_DISC_ABNORMALITY,FN_OS_MACULA,FN_OS_MACULA_ABNORMAL,FN_OS_RVESSELS,FN_OS_RVESSELS_ABNORMAL,FN_OS_POSTERIOR_POLE,
FN_OS_POSTERIOR_POLE_ABNORMALITY,FN_CRT_DATE FROM (‘ + @STRQUERY + ‘)V ORDER BY FN_CRT_DATE DESC’

EXECUTE(@STRQUERY)

 

Read Full Post »

Select (SYSCONS.Refer_Table_Name) As PK_Table_Name,(SYSCONS.Refer_Column_Name) As PK_Column_Name,(SYSO.Name) As FK_Table_Name,(SYSC.Name) As FK_Column_Name From SysObjects SYSO,SysColumns SYSC,
(Select SFK.Constid [ConstraintID],SFK.fkeyid [TableID], SFK.fkey [ColumnID],SC.Name As Refer_Column_Name,SO.Name As Refer_Table_Name,SFK.Rkeyid [TableID refered], SFK.Rkey[ColumnID refered] From dbo.SysForeignKeys SFK
Inner Join dbo.SysColumns SC on SFK.Rkeyid = SC.id and SFK.Rkey = SC.colid
Inner Join dbo.SysObjects SO on SC.id = SO.id)SYSCONS
Where SYSC.ColId = SYSCONS.ColumnID And SYSC.id = SYSCONS.TableId And SYSO.Id=SYSC.Id

Read Full Post »

—Table Size Information Periodically in SQL Server 2005

USE [master]

GO

CREATE DATABASE [DBA_Activities]

GO

—===========================================

USE [DBA_Activities]

GO

CREATE TABLE [dbo].[DATABASE_TABLE_SIZE_LOG](
[Database_Name] [sysname] NOT NULL DEFAULT (”),
[Table_Name] [sysname] NOT NULL,
[Row_Count] [int] NULL,
[Reserved] [varchar](50) NULL,
[Data] [varchar](50) NULL,
[Index_Size] [varchar](50) NULL,
[Unused] [varchar](50) NULL,
[Get_Date] [datetime] NULL
) ON [PRIMARY]

–====================================================

USE [msdb]

GO

/****** Object:  Job [DATABASE_TABLE_SIZE_LOG]    Script Date: 01/21/2012 13:04:28 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 01/21/2012 13:04:28 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DATABASE_TABLE_SIZE_LOG’,

@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’Database Maintenance’,

@owner_login_name=N’sa’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Step1]    Script Date: 01/21/2012 13:04:28 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step1′,

@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’DECLARE @EachDBCmd VARCHAR(8000)

SET @EachDBCmd = ”USE [?];

IF ””?”” NOT IN (””tempdb””,””master””, ””model””, ””msdb””)

BEGIN

DECLARE @EachTableCmd VARCHAR(8000)

SET @EachTableCmd = ””EXEC sp_spaceused ”””””” + CHAR(63) + ””””””””

INSERT INTO DBA_Activities.dbo.DATABASE_TABLE_SIZE_LOG(Table_Name,Row_count,Reserved,data,index_size,unused)

EXEC sp_MSforeachtable @EachTableCmd

UPDATE DBA_Activities.dbo.DATABASE_TABLE_SIZE_LOG SET Database_Name = ””?””,Get_Date=(SELECT CONVERT(CHAR(10), GETDATE(), 101)) WHERE Database_Name = ””””

END

EXEC sp_MSforeachdb @EachDBCmd’,

@database_name=N’DBA_Activities’,

@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’Sche1′,

@enabled=1,

@freq_type=8,

@freq_interval=17,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_date=20111003,

@active_end_date=99991231,

@active_start_time=220000,

@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:

—==========================================================

Select * From dbo.DATABASE_TABLE_SIZE_LOG

Read Full Post »

--===========================Monitor DB Size Growth Report In SQL Server 2005=======================

USE [master]
GO

CREATE DATABASE [DBA_Activities]
GO

--====================================================================================================

USE [DBA_Activities]
GO

CREATE TABLE [dbo].[Get_DB_Details_DBA](
	[Server_Name] [varchar](100) NULL,
	[Database_Name] [varchar](100) NULL,
	[Logical_File_Name] [sysname] NOT NULL,
	[Physical_File_Name] [nvarchar](520) NULL,
	[File_Size_MB] [int] NULL,
	[Status] [sysname] NOT NULL,
	[Recovery_Mode] [sysname] NOT NULL,
	[Free_Space_MB] [int] NULL,
	[Free_Space_Per] [varchar](7) NULL,
	[Get_Date] [datetime] NULL
) ON [PRIMARY]

--====================================================================================================

USE [DBA_Activities]
GO

CREATE  PROC   [dbo].[Get_Database_Size_Growth]
(
     @dtFromDate DateTime=Null,
     @dtToDate DateTime=Null,
     @Option Varchar(10)='DAY_WISE' --DAY_WISE,MONTH_WISE,YEAR_WISE

)
AS

BEGIN

Declare @SQL Varchar(Max)

Declare @intFromMonth int
Declare @intToMonth int

Declare @intFromYear int
Declare @intToYear int

Declare @intI int
Declare @intDiff int

Declare @dtDate datetime

If @dtFromDate Is Null And @dtToDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
   End

If @dtToDate Is Null
   Begin
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate > @dtToDate
   Begin
      RAISERROR ('From date should be less than To date.',10,1)
      Return
   End

If @intFromMonth < 1 Or @intFromMonth > 12
   Begin
      RAISERROR ('From Date Month between 1 to 12.',10,1)
      Return
   End

If @intToMonth < 1 Or @intToMonth > 12
   Begin
      RAISERROR ('To Date Month between 1 to 12.',10,1)
      Return
   End

If @intFromYear < 1753 Or @intFromYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

If @intToYear < 1753 Or @intToYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

Set @SQL = 'Select Database_Name'

If @Option = 'DAY_WISE'

   Begin

     Set @dtDate = @dtFromDate

     While (@dtDate <= @dtToDate)

       Begin
         Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
         Set @dtDate = @dtDate + 1
       End

   End

If @Option = 'MONTH_WISE'

   Begin

      Set @intFromMonth = Month(@dtFromDate)
      Set @intToMonth = Month(@dtToDate)

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + CONVERT(Varchar(2),@intFromMonth) +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + CONVERT(Varchar(2),@intToMonth) +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(mm,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(mm,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

If @Option = 'YEAR_WISE'

   Begin

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + 'Jan' +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + 'Jan' +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(yy,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(yy,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

Set @SQL = @SQL + 'From Get_DB_Details_DBA Group By Database_Name Order by Database_Name'
EXECUTE(@SQL)

END

GO

--====================================================================================================

USE [msdb]
GO

/****** Object:  Job [Get_DB_Details_DBA]    Script Date: 10/04/2011 14:40:30 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 10/04/2011 14:40:30 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get_DB_Details_DBA',
		@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'Database Maintenance',
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 10/04/2011 14:40:30 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
		@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'DECLARE @DBSizeDetails TABLE
(Server_Name VARCHAR(100),
Database_Name VARCHAR(100),
Logical_File_Name sysname,
Physical_File_Name NVARCHAR(520),
File_Size_MB INT,
Status sysname,
Recovery_Mode sysname,
Free_Space_MB INT,
Free_Space_Per VARCHAR(7),
Get_Date datetime)

DECLARE @command VARCHAR(5000)
SELECT @command = ''Use ['' + ''?'' + ''] SELECT
@@servername As Server_Name,
'' + '''''''' + ''?'' + ''''''''+ '' As Database_Name,
sysfiles.name As Logical_File_Name,
sysfiles.filename AS Physical_File_Name,
CAST(sysfiles.size/128.0 AS int) As File_Size_MB,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Status'''')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Recovery'''')) AS Recovery_Mode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, '' + '''''''' +
''SpaceUsed'' + '''''''' + '' ) AS int)/128.0 AS int) AS Free_Space_MB,
CAST(100 * (CAST (((sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
'' + '''''''' + ''SpaceUsed'' + '''''''' + '' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + '' + '''''''' + ''%'' + '''''''' + '' AS Free_Space_Per,
GETDATE() as Get_Date FROM dbo.sysfiles''
INSERT INTO @DBSizeDetails
(Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per, Get_Date)
EXEC sp_MSForEachDB @command

INSERT INTO Get_DB_Details_DBA (Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,Recovery_Mode, Free_Space_MB, Free_Space_Per,Get_Date)
SELECT
Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per,CONVERT(CHAR(10), Get_Date, 101) FROM @DBSizeDetails Where Database_Name Not In (''master'',''msdb'',''tempdb'',''model'')
',
		@database_name=N'DBA_Activities',
		@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'Sche1',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20101029,
		@active_end_date=99991231,
		@active_start_time=200000,
		@active_end_time=235959,
		@schedule_uid=N'3b2022cd-96b6-42df-8cab-d3be37d711b4'
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

--====================================================================================================

USE [DBA_Activities]
GO

Exec Get_Database_Size_Growth
Exec Get_Database_Size_Growth '05-May-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Jun-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Oct-2011','MONTH_WISE'
Exec Get_Database_Size_Growth NULL,NULL,'MONTH_WISE'
Exec Get_Database_Size_Growth '05-May-2011','05-May-2012','YEAR_WISE'

--====================================================================================================

Read Full Post »

While Loop

CREATE TABLE [dbo].[ID_Table](
	[Id] [bigint] NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]

GO

DECLARE @INTX BIGINT
DECLARE @STRSQL NVARCHAR(400)

SET @INTX=1

WHILE (@INTX <= 100)
   BEGIN
      SET @STRSQL='INSERT INTO ID_Table VALUES(' + CONVERT(VARCHAR(50),@INTX) + ',' + CHAR(39) + 'NAME' + CHAR(39) + ')'
      EXEC(@STRSQL)
      SET @INTX=@INTX+1
  END

Read Full Post »

Older Posts »