You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))

My rebuild index job (With ONLINE-Enterprise edition only) failed with error 




An online operation cannot be performed for %S_MSG '%.*ls' because the index contains column '%.*ls' 
of data type text, ntext, image or FILESTREAM. 
For a non-clustered index, the column could be an include column of the index. For a clustered index,
 the column could be any column of the table.
  If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.




select * from sys.messages 
where message_id=2725 and language_id=1033

You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))



Solution to search the Table with LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))
and for that particular db, modify the rebuild indexes job not to online rebuild..


SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id 
WHERE c.system_type_id IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.[type] = 'U'
GO

Will the linked server honor the NOLOCK hint?

Will the linked server honor the NOLOCK hint? -No

Ex:
INSERT INTO  CallHistory
SELECT *  FROM LINKSERVER.DATABASE.DBO.CallHistory WITH(NOLOCK)WHERE callplacedtimeUTC >= DATEADD(hh,-4,GETUTCDATE())  ORDER BY callplacedtimeUTC DESC
linked server do not  honor the NOLOCKso Create a view like below 
 Create view test
as
SELECT *  FROM  DATABASE.DBO.CallHistory WITH(NOLOCK)WHERE callplacedtimeUTC >= DATEADD(hh,-4,GETUTCDATE()) 

and then Finally:INSERT INTO  CallHistory
SELECT *  FROM LINKSERVER.DATABASE.DBO.test  --view  ORDER BY callplacedtimeUTC DESC

converting local time to UTC time With daylight Savings

I have a job to get data from a table with 4 hours data..my column is datetime and UTC default. and i prepared some logic below...
SELECT *  FROM   CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >=  DATEADD(hh,-4,GETUTCDATE())
GO

But it dint work with daylight savings...so, my boss asked me to convert local time to UTC time which will work and gave me below logic...
Just  add below in place of -4

SELECT DATEADD(HOUR, -1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()), GETUTCDAT

SELECT *  FROM  CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >=  DATEADD(hh,-1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),GETUTCDATE())
GO


it works for daylight savings...