Length of LOB data to be replicated exceeds configured maximum 65536.

This week I came across the following error :
Length of LOB data to be replicated exceeds configured maximum 65536

When we where uploading pictures to store in a column of data type "Image" into our SQL server 2005 database.
The error is thrown when replication is enabled on the server, so the fix for this is rather easy ... increase the size that can be replicated using the following T-SQL Statement

sp_configure ‘max text repl size’, 2147483647

Or via the Management Studio :

1. Right Click a server in Object Explorer and click Properties
2. Select Advanced
3. Under the Miscellaneous Category, change the Max Text Replication Size to the value you want.

Max Text Replication Size definition (as defined in the SSMS): Specifies the maximum data that can be added to a replicated column in
a single INSERT, UPDATE , WRITETEXT or UPDATETEXT statement

Playing with DateTime in SQL2005

For a project I'm working on I needed to extract semester and quarter from a date.
This brought me to the idea of storing these values in a table.

The following code sample is a table valued function which generates detailed calendar output.

Create Function [dbo].[DateTimeFunctions.ReturnExtendedCalendar]
  (@TableStartDate DateTime , @TableEndDate DateTime , @FYStart DateTime)
Returns @ExtendedResultSet Table
    ( MonthStart DateTime , MonthEnd DateTime , [Year] Int , [Month] Int , 
     [Quarter] Int , Semester Int , [MonthName] Varchar(20) , [FiscalYear] Int , 
     [FiscalMonth] Int , [FiscalQuarter] Int , FiscalSemester Int)
As
Begin
 Declare @CalendarTable Table 
   ( MonthStart DateTime , MonthEnd DateTime , [Year] Int , [Month] Int , 
     [Quarter] Int , Semester Int , [MonthName] Varchar(20) , [FiscalYear] Int , 
     [FiscalMonth] Int , [FiscalQuarter] Int , FiscalSemester Int)
     
 Declare @FirstDayOfCurrentYear DateTime
 Declare @FiscalStartDiff Int

 Select @FirstDayOfCurrentYear = Cast(Cast(Convert(Varchar(4), DatePart(yyyy ,GetDate())) + '-01-01' As Varchar(20)) As DateTime)

 Select @FiscalStartDiff = DateDiff(mm , @FYStart , @FirstDayOfCurrentYear)

 While @TableStartDate <= @TableEndDate
 Begin
  Insert Into @CalendarTable
    (
     MonthStart , MonthEnd , [Year] , [Month] , [Quarter] , Semester , [MonthName] ,
     [FiscalYear] , [FiscalMonth] , [FiscalQuarter] , FiscalSemester
    )
  Values ( @TableStartDate ,
     DateAdd(ms, -3, DateAdd(mm, DateDiff(m, 0, @TableStartDate) + 1, 0)) ,
     DatePart(yyyy , @TableStartDate ) ,
     DatePart(mm , @TableStartDate ) ,
     DateName(qq , @TableStartDate),
     Case When DatePart(mm , @TableStartDate ) Between 1 and 6 Then 1
       Else 2
     End ,
     DateName(mm , @TableStartDate) ,
     DatePart(yyyy , DateAdd(mm  , @FiscalStartDiff , @TableStartDate )) , 
     DatePart(mm , DateAdd(mm  , @FiscalStartDiff , @TableStartDate )),
     DatePart(qq , DateAdd(mm  , @FiscalStartDiff , @TableStartDate )),
     Case When DatePart(qq , DateAdd(mm  , @FiscalStartDiff , @TableStartDate )) 
         In (1 , 2 )Then 1
       Else 2
     End
    )
     
  Set @TableStartDate = DateAdd(mm , 1 , @TableStartDate)
 End

 Insert @ExtendedResultSet (MonthStart , MonthEnd , [Year] , [Month] , [Quarter] , 
     Semester , [MonthName] , [FiscalYear] , [FiscalMonth] , [FiscalQuarter] , FiscalSemester)
 Select MonthStart , MonthEnd , [Year] , [Month] , [Quarter] , 
     Semester , [MonthName] ,[FiscalYear] , [FiscalMonth] , [FiscalQuarter] , FiscalSemester 
 From @CalendarTable

 Return
End

Execution of the function is as follows :

Set DateFirst 1
Set @TableStartDate = DateAdd(yyyy ,-10 , '2009-01-01')
Set @TableEndDate = DateAdd(yyyy , 0 , '2009-12-31')
Set @FYStart = Convert(DateTime, '2009-04-01')

Select * From [dbo].[DateTimeFunctions.ReturnExtendedCalendar]
         (@TableStartDate , @TableEndDate , @FYStart)