Split string column into multiple rows

 SELECT A.content_id,
     Split.a.value('.', 'VARCHAR(100)') AS String
 FROM  (SELECT content_id,
         CAST ('' + REPLACE(page_path, '
', '') + '' AS XML) AS String
     FROM  ContentTrackerMaster where content_id = 518) AS A CROSS APPLY String.nodes ('/M') AS Split(a); 

Import CSV into sql server with Bulk insert

*****************************************************************************************************************************/
CREATE PROCEDURE [dbo].[DynamicImportCSV]
@FileName VARCHAR(200), @delimiter VARCHAR(2),@schema VARCHAR(20)='dbo',@tableName VARCHAR(100)
AS
BEGIN
--DECLARE @FileName varchar(100)
--SET @FileName = 'C:\3M Domestic.tsv'

DECLARE @importSqlQuery varchar(4000)
DECLARE @oFSO int, @oTSO int, @line varchar(4000)
-- get the first line from the file
IF (@FileName IS NULL)
BEGIN
PRINT 'There is no file name is specified. Process wont be continued.'
RETURN
END
ELSE IF (@delimiter IS NULL)
BEGIN
PRINT 'Delimiter Input is empty . Process wont be continued.'
RETURN
END


--DROP TABLE
--EXEC('drop table ' + @schema + '.' + @tableName)

SET @delimiter = RTRIM(LTRIM(@delimiter))
--PRINT @delimiter
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @oFSO OUTPUT
EXECUTE sp_OAMethod @oFSO, 'OpenTextFile', @oTSO OUTPUT, @FileName
EXECUTE sp_OAMethod @oTSO, 'ReadLine', @line OUTPUT
EXECUTE sp_OAMethod @oTSO, 'Close'
EXECUTE sp_OADestroy @oTSO
EXECUTE sp_OADestroy @oFSO

IF (@delimiter = '\t')
SET @delimiter = CHAR(9)
ELSE IF (@delimiter = '\t\0')
SET @delimiter = '\t\0'

SET @line = REPLACE (@line, @delimiter, '] NVARCHAR(MAX),[')


SET @line = 'CREATE TABLE '+ @schema + '.' + @tableName +' ([' + @line + '] NVARCHAR(MAX))'
--PRINT @line
EXEC (@line)

--Import CSV data to DynamicImport Table
SET @importSqlQuery =
'BULK INSERT '+ @schema + '.' + @tableName +'
FROM ''' + @FileName + '''
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '''+@delimiter +''',
ROWTERMINATOR = ''\n'',
DATAFILETYPE=''widechar'' ,
CODEPAGE=1252
)'
--PRINT @importSqlQuery
EXEC (@importSqlQuery)
END

Rebuild all the indexes

--Description : This script reorganizes and rebuilds the index if the fragmentation level is higher the given threshold
-- INPUTS : @fillfactor - While rebuilding index what would be FILLFACTOR for new index
-- @FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level lower threshold to check for reorganizing the table, if the fragmentation is higher than this level, it will be considered for reorganize
-- @@FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level lower threshold to check for rebuilding the table, if the fragmentation is higher than this level, it will be considered for rebuild
DECLARE @cmd NVARCHAR(1000)
DECLARE @Table VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @AvgFragmentationInPercent DECIMAL
DECLARE @fillfactor INT
DECLARE @FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10)
DECLARE @FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10)
DECLARE @Message VARCHAR(1000)

SET NOCOUNT ON

--You can specify your customized value for reorganize and rebuild indexes, the default values
--of 10 and 30 means index will be reorgnized if the fragmentation level is more than equal to 10
--and less than 30, if the fragmentation level is more than equal to 30 then index will be rebuilt
SET @fillfactor = 90
SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' -- Percent
SET @FragmentationThresholdForRebuildTableLowerLimit = '30.0' -- Percent

BEGIN TRY

-- ensure the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#FramentedTableList')) IS NOT NULL
DROP TABLE #FramentedTableList;

SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieving indexes with high fragmentation from ' + DB_NAME() + ' database.'
RAISERROR(@Message, 0, 1) WITH NOWAIT

SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name [IndexName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed INTO #FramentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, @FragmentationThresholdForReorganizeTableLowerLimit)
ORDER BY avg_fragmentation_in_percent DESC

SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieved indexes with high fragmentation from ' + DB_NAME() + ' database.'

RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT

WHILE EXISTS ( SELECT 1 FROM #FramentedTableList WHERE IsProcessed = 0 )
BEGIN

SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,
@SchemaName = SchemaName, @IndexName = IndexName
FROM #FramentedTableList
WHERE IsProcessed = 0

--Reorganizing the index
IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
BEGIN
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganizing Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'
EXEC (@cmd)
--PRINT @cmd
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganize Index completed successfully for [' + @Table + '].'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END
--Rebuilding the index
ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit )
BEGIN
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuilding Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'
EXEC (@cmd)
--PRINT @cmd
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuild Index completed successfully for [' + @Table + '].'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END

UPDATE #FramentedTableList
SET IsProcessed = 1
WHERE TableName = @Table
AND IndexName = @IndexName
END

DROP TABLE #FramentedTableList

END TRY

BEGIN CATCH
PRINT 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
PRINT 'ERROR CODE : ' + CONVERT(VARCHAR, ERROR_NUMBER())
PRINT 'ERROR MESSAGE : ' + ERROR_MESSAGE()
END CATCH

BMTC Volvo Complaints

I have a horrific experience today with BMTC volvo bus. It almost crushed me.
I decided to teach those guys a lesson, and contact BMTC call center. From there I got the email-id where one can forward his/her complaints. I have already got a response back from Chief Traffic Manager.

BMTC toll free number is 18004251663
BMTC Email-ID is ctmobmtc@gmail.com

Lets hope some action is taken against those B*******ds.

Script to know what is currently running in the system

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Create CSV file from a query using SQLCMD.

ALTER PROC [dbo].[create_csv_from_query]
(
@FullFileName VARCHAR(MAX),
@Query VARCHAR(MAX),
@Server VARCHAR(50) ='DEV',
@Database VARCHAR(50) ='Test'
)
AS
BEGIN
-- Query should have nocount on
DECLARE @cmd VARCHAR(2000)

SET @cmd = 'sqlcmd -S '+@Server+' -d '+@Database+' -E -o "'+@FullFileName +'" -Q "'+@Query+'" -W -w20000 -s","'
EXEC master..xp_cmdshell @cmd

END

Clear Transaction logs

You can clear the transaction log by using the following command

Dump tran < db_name > to < file_name >
OR
Dump tran < db_name > with truncate_only
OR
Dump tran < db_name > with no_log

Please read the sybase documents for more details.

Note : Dump tran will not work, if your database option is set
to "truncate log on checkpoint".

if still log is not cleared then, either you can kill the process
or can abort the transaction log by using lct_admin command.