First & Last Dates of Current, Previous & Next Month

declare @DateZero datetime

declare @MonthsSinceZero int

declare @FirstofCurrentMonth datetime

declare @FirstofNextMonth datetime

declare @FirstofPreviousMonth datetime

declare @LastofCurrentMonth datetime

declare @LastofNextMonth datetime

declare @LastofPreviousMonth datetime

set @DateZero = 0

select @DateZero as DateZero

set @MonthsSinceZero = DATEDIFF(m, @DateZero, GETDATE())

-- CURRENT MONTH

set @FirstofCurrentMonth = DATEADD(mm, @MonthsSinceZero, @DateZero)

select @FirstofCurrentMonth as FirstofCurrentMonth

set @LastofCurrentMonth = DATEADD(s, -1, DATEADD(mm, @MonthsSinceZero + 1, @DateZero))

select @LastofCurrentMonth as LastofCurrentMonth

-- NEXT MONTH

set @FirstofNextMonth = DATEADD(mm, @MonthsSinceZero + 1, @DateZero)

select @FirstofNextMonth as FirstofNextMonth

set @LastofNextMonth = DATEADD(s, -1, DATEADD(mm, @MonthsSinceZero + 2, @DateZero))

select @LastofNextMonth as LastofNextMonth

-- PREVIOUS MONTH

set @FirstofPreviousMonth = DATEADD(mm, @MonthsSinceZero - 1, @DateZero)

select @FirstofPreviousMonth as FirstofPreviousMonth

set @LastofPreviousMonth = DATEADD(s, -1, DATEADD(mm, @MonthsSinceZero, @DateZero))

select @LastofPreviousMonth as LastofPreviousMonth

SQL Instance Cluster Failover Alert

declare @Body nvarchar(128)

select @Body = CONVERT(sysname, SERVERPROPERTY('ServerName')) + ' SQL Agent has failed over to ' + CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

EXEC msdb.dbo.sp_send_dbmail

@recipients='test@test.com',

@Body=@Body,

@subject='SQL Instance Cluster Failover Alert'

Dynamic PIVOT

-- Dynamic PIVOT

DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

DECLARE

@cols AS NVARCHAR(MAX),

@y    AS INT,

@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause

-- e.g., [2002],[2003],[2004]

SET @cols = STUFF(

(SELECT N',' + QUOTENAME(y) AS [text()]

FROM (SELECT DISTINCT cast([DATE] as Date) AS y FROM dbo.BuildReportByMonth) AS Y

ORDER BY y

FOR XML PATH('')),

1, 1, N'');

-- Construct the full T-SQL statement

-- and execute dynamically

SET @sql = N'SELECT *

FROM (SELECT DecimalId, HexId, Date, isnull(HttpBw, 0) + isnull(HttpsBw, 0) as Usage

FROM dbo.BuildReportByMonth b

join DecHex d on b.CustomerId = DecimalId) AS q

PIVOT(SUM(Usage) FOR Date IN(' + @cols + N')) AS P

order by DecimalId;';

EXEC sp_executesql @sql;

GO

Check existence of global temp table

if object_id('tempdb..##deleteme') is not null

begin

    drop table ##deleteme

end

Unpivot

SELECT [Epoch], [CustomerID], [EdgeNodeID], MediaTypes, Bytes

FROM

   (SELECT top 1000 [Epoch], [CustomerID], [EdgeNodeID], [HTTP], [HTTPS], [WMS], [FMS], [WAC], [WACS]

   FROM [NEW_BandwidthByNodeAggs] (nolock)) p

UNPIVOT

   (Bytes FOR MediaTypes IN

      ([HTTP], [HTTPS], [WMS], [FMS], [WAC], [WACS])

)AS unpvt;

GO

Data Types of Columns

SELECT

case when data_type in ('char', 'varchar', 'nvarchar')

then DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH as varchar(5))+ ')'

else DATA_TYPE end as ColumnType,

*

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'wholesales'

View SSIS PackageSource Contents

SELECT [name]
      ,[id]
      ,[description]
      ,[createdate]
      ,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
      ,[packagetype]
      ,[vermajor]
      ,[verminor]
      ,[verbuild]
      ,[vercomments]
      ,[verid]
      ,[isencrypted]    
  FROM [msdb].[dbo].[sysssispackages]

Round datetime to nearest minute or hour

declare @dt datetime
set @dt = '09-22-2007 15:07:38.850'
select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

will return

2007-09-22 15:07:00.000
2007-09-22 15:00:00.000

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

and you'll get:

2007-09-22 15:08:00.000
2007-09-22 15:00:00.000

---

Before the date data type was added in SQL Server 2008, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is to determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days intset @days = datediff(day, 0, @dt)

and then add that number of days to the fixed point in time, which gives you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

or more succinctly:

select dateadd(day, datediff(day, 0, @dt), 0)

Using a different datepart (e.g. hour, mi) will work accordingly.

http://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions

SSIS Flat File skips importing the last row

When copying an SSIS file from one server to another, the text qualifier for flat files gets messed up. It becomes _x003C_none_x003E_ instead of <none>. This causes the last record of the flat file to be skipped when importing.

SET ROWCOUNT

---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.


The data in row 2 was not committed.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).


Correct the errors and retry or press ESC to cancel the change(s). 
---------------------------
OK   Help   
---------------------------

To workaround this error without modifying the table structure by adding an identity column, use the "SET ROWCOUNT" command.

SET ROWCOUNT 1

update b set column1 = 1

FROM         Table b

WHERE   column2 = 'ABC'

NOTE: The ROWCOUNT statement setting is used for the entire duration of the connection!

SQL Server - Default trace

Use the following query to find the path of the default trace

SELECT * FROM ::fn_trace_getinfo(0) ;

SELECT

     loginname,

     loginsid,

     spid,

     hostname,

     applicationname,

     servername,

     databasename,

     objectName,

     e.category_id,

     cat.name as [CategoryName],

     textdata,

     starttime,

     eventclass,

     eventsubclass,--0=begin,1=commit

     e.name as EventName

FROM ::fn_trace_gettable('M:\MSSQL10.SQL1\MSSQL\Log\log_26.trc',0)

     INNER JOIN sys.trace_events e ON eventclass = trace_event_id

     INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id

Aliasing Column Names

Today, I discovered a new way columns can be aliased.

select o.*

from

(

      select id, name, xtype

      from sys.sysobjects

) o

join sys.syscomments c on o.id = c.id

select o.*

from

(

      select id as a, name as b, xtype as c

      from sys.sysobjects

) o

join sys.syscomments c on o.a = c.id

select o.*

from

(

      select id, name, xtype

      from sys.sysobjects

) o (a, b, c)

join sys.syscomments c on o.a = c.id

-- XP_CMDSHELL

-- Tab keyed in

EXEC xp_cmdshell 'sqlcmd -U user -P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"     " -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~-11,2%%time:~-8,2%%time:~-5,2%.xls"'

-- <TAB>

EXEC xp_cmdshell 'sqlcmd -U user -P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"<TAB>" -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~-11,2%%time:~-8,2%%time:~-5,2%.xls"'

-- COMMAND LINE - CMD.EXE

sqlcmd -U user -P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"<TAB>" -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~-11,2%%time:~-8,2%%time:~-5,2%.xls"

-- SQL AGENT - USE OF "SQL AGENT TOKENS" BECAUSE SQL AGENT DOES NOT RECOGNIZE WINDOWS DATE, TIME

sqlcmd -U user-P pwd -S 10.84.92.201\SQL1 -d EdgeCastControlCenter -s"<TAB>" -W -Q "EXEC dbo.Report_PartnerManagement" -o "N:\MSSQL10.SQL1\PartnerReports\PartnerReport_$(ESCAPE_NONE(DATE)) _$(ESCAPE_NONE(TIME)).xls"

-- DATE TIME WINDOWS FORMATS

%date:~x,y%

where x is starting character position of date substring,

and y is ending character position of date substring

"-" determines whether it counts from left-to-right or right-to-left in the date string.

-- DATE TIME SQL AGENT TOKENS

(DATE) Current date (in YYYYMMDD format).

(TIME) Current time (in HHMMSS format).

$(ESCAPE_NONE( token_name )) Replaces token without escaping any characters in the string. This macro is provided to support backward compatibility in environments where token replacement strings are only expected from trusted users.

http://msdn.microsoft.com/en-us/library/ms175575.aspx

Remove compression off a table or index

-- TABLES

SELECT distinct st.name, st.object_id, schema_name(schema_id), 'ALTER TABLE ' + schema_name(schema_id) + '.' + st.name + ' REBUILD WITH (DATA_COMPRESSION =  NONE)'--, sp.partition_id, sp.partition_number, sp.data_compression, sp.data_compression_desc

FROM sys.partitions SP

INNER JOIN sys.tables ST ON st.object_id = sp.object_id

WHERE data_compression <> 0

and index_id = 1

-- INDEXES -- DROP AND CREATE INDEX WITH "DATA_COMPRESSION = NONE"

SELECT distinct st.name, st.object_id, schema_name(schema_id), i.name

FROM sys.partitions SP

INNER JOIN sys.tables ST ON st.object_id = sp.object_id

join sys.sysindexes i on SP.object_id = i.id and SP.index_id = i.indid

WHERE data_compression <> 0

and index_id <> 1

CTE - Generate Sequence

WITH Nbrs ( n ) AS (

        SELECT 1 UNION ALL

        SELECT 1 + n FROM Nbrs WHERE n < 500 )

    SELECT n FROM Nbrs

    OPTION ( MAXRECURSION 500 )

with DateCTE as

(

    select cast('2012-01-01' as datetime) DateValue

    union all

    select DateValue + 1

    from    DateCTE  

    where   DateValue + 1 < GETDATE()

)

select DateValue

 from    DateCTE

 OPTION (MAXRECURSION 0)

with DateCTE as

(

    select cast('2012-01-01 00:00:00' as datetime) DateValue

    union all

    select dateadd(hh, 1, DateValue)

    from    DateCTE  

    where   dateadd(hh, 1, DateValue) < GETDATE() + 1

)

select DateValue

 from    DateCTE

 OPTION (MAXRECURSION 0)

Generate Sequence from a Range

;with cte as

(

      select Id, StartIp, EndIP, StartIp as Ip

      from Subnets

union all

      select Id, StartIp, EndIP, Ip + 1 as Ip

      from cte where Ip + 1 <= EndIP

)

select * from cte

order by id

OPTION ( MAXRECURSION 5000 

SQL Profiler Trace

-- STOP TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 0

-- START TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 1

-- CLOSE & DELETE TRACE

EXEC sp_trace_setstatus @trace_id = 2, @status = 2

-- INFO ABOUT A TRACE

-- ALL TRACES

select *

from fn_trace_getinfo (NULL)

select *

from fn_trace_getinfo (0)

select *

from fn_trace_getinfo (DEFAULT)

-- INFO OF SPECIFIC TRACE_ID

select *

from fn_trace_getinfo (3)

-- IMPORT TRACE FILE INTO A TABLE

SELECT * INTO yourtracetable

FROM ::fn_trace_gettable('C:\MyTrace.trc', default)

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 500

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create

      @TraceID output,

      @options = 2,           -- TRACE_FILE_ROLLOVER

      @tracefile = N'Z:\MSSQL10.MSSQLSERVER\MSSQL\SQLTraces\AdvancedReportsDecommission',

      @maxfilesize = @maxfilesize, -- MB

      @stoptime = NULL, -- date and time the trace will be stopped

      @filecount = 10         -- maximum number or trace files to be maintained with the same base filename

-- FIND THE SOURCE OF A TRACE

select *

from sys.traces