Excel FIND() function

IF Cell A1 = "cfuccillo003***********00300285569" THEN Cell B2 = LEFT(A1, FIND("*",A1,1)-1)

ORACLE SQL BETWEEN two dates

SELECT * FROM ps_job

WHERE emplid = '00000000000'

  and action_dt between to_date('10/01/2014','mm-dd-yyyy') and to_date('06/30/2015','mm-dd-yyyy')

Display Column Properties

 SELECT b.name as 'schema_name', a.name, c.name as 'Column_Name', c.column_id, a.object_id, a.schema_id, a.parent_object_id, a.type, a.type_desc, a.create_date, a.modify_date
 FROM sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id) ON c.object_ID = a.object_id
 WHERE a.object_id IN (select object_ID from sys.columns where name like 'snapshot%')
  and b.name = 'dbo'
  and a.name = 'Flatfile_employee'
 ORDER BY a.[type], b.name, a.name

Database Stored Procedures list

SELECT o.schema_ID, s.name, o.name, o.create_date, o.modify_date, len(m.[definition]) as 'Definition_length'

FROM sys.schemas s INNER JOIN (sys.sql_modules m inner join sys.objects o on m.object_id=o.object_id) ON s.schema_id = o.schema_id

WHERE type = 'P'

 --and m.[definition] LIKE '%2015%'

 and s.name IN ('[name1]', '[name2]', '[name3]')

 --and s.name NOT IN ('Reporting')

 --and modify_date > '07/01/2015'

order by o.schema_ID, o.name, o.modify_date DESC

SQL Server builds

http://sqlserverbuilds.blogspot.com/

Find all Stored Procedures with specific text included within

In this example, I'm looking for the hard-coded "2015" so it can be replaced with a dynamic variable.

SELECT o.schema_ID, s.name, o.name

   , o.create_date

   , o.modify_date

   , len(m.[definition]) as 'Definition_length'

FROM sys.schemas s INNER JOIN (sys.sql_modules m inner join sys.objects o on m.object_id=o.object_id) ON s.schema_id = o.schema_id

WHERE type = 'P'

 and m.[definition] LIKE '%2015%'

 --and o.name IN ('snapshot', 'snapshotbase', 'snapshotLimit', 'snapshotlimitnonhc', 'snapshot_rel2_model_a','snapshot_rel2_model_b', 'snapshot_rel2_model_c', 'snapshotlimitnonhc')

 and s.name NOT IN ('Reporting')

ORDER BY o.schema_ID, o.name, o.modify_date DESC

Last Run Date of Stored Procedure

SELECT o.name,
       ps.last_execution_time
FROM   sys.dm_exec_procedure_stats ps
INNER JOIN
       sys.objects o
       ON ps.object_id = o.object_id
WHERE  DB_NAME(ps.database_id) = 'HRSSCRPT'
--and o.name like 'sp_gen%'
ORDER BY ps.last_execution_time DESC

T-SQL convert integer to Time

declare @T int

set @T = 10455836
set @T = 4450100  -- add 2 zeroes for milliseconds if your integer doesn't already have them.

select (@T / 1000000) % 100 as hour,
       (@T / 10000) % 100 as minute,
       (@T / 100) % 100 as second,
       (@T % 100) * 10 as millisecond

select dateadd(hour, (@T / 1000000) % 100,
       dateadd(minute, (@T / 10000) % 100,
       dateadd(second, (@T / 100) % 100,
       dateadd(millisecond, (@T % 100) * 10, cast('00:00:00' as time(2))))))

T-SQL Status of SQL Jobs

You could try using the system stored procedure sp_help_job. This returns information on the job, its steps, schedules and servers. For example

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

SQL Books Online should contain lots of information about the records it returns.

For returning information on multiple jobs, you could try querying the following system tables which hold the various bits of information on the job

• msdb.dbo.SysJobs
• msdb.dbo.SysJobSteps
• msdb.dbo.SysJobSchedules
• msdb.dbo.SysJobServers
• msdb.dbo.SysJobHistory

Their names are fairly self-explanatory (apart from SysJobServers which hold information on when the job last run and the outcome).

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

 DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'NAM\wbenson007'

SELECT * FROM @xp_results WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

--EXEC msdb.dbo.sp_help_job @Job_name = 'T2_Sync_Rebuild_PROD'

--select * from  msdb.dbo.sysjobactivity where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
/*
IF EXISTS(SELECT 1 
          FROM msdb.dbo.sysjobs J 
          JOIN msdb.dbo.sysjobactivity A 
              ON A.job_id=J.job_id 
          WHERE J.name=N'T2_Sync_Rebuild_PROD' 
          AND A.run_requested_date IS NOT NULL 
          AND A.stop_execution_date IS NULL
         )
    PRINT 'The job is running!'
ELSE
    PRINT 'The job is not running.'
*/

select step_id, step_name, subsystem, last_run_outcome
, last_run_date
, last_run_time
, cast(cast(last_run_time as varchar(10)) + '00' as integer) as 'last_run_time_2'
, cast(cast(last_run_date as varchar(12)) as date) as 'last_run_date_2'
--, *
INTO #temp
from msdb.dbo.SysJobSteps WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'


SELECT step_id, step_name, subsystem, last_run_outcome, last_run_date, last_run_time, last_run_time_2, last_run_date_2
, dateadd(hour, (last_run_time_2 / 1000000) % 100,
       dateadd(minute, (last_run_time_2 / 10000) % 100,
       dateadd(second, (last_run_time_2 / 100) % 100,
       dateadd(millisecond, (last_run_time_2 % 100) * 10, cast('00:00:00' as time(2)))))) as 'last_run_time_3'
INTO #temp2
FROM #temp


SELECT step_id, step_name, subsystem, last_run_outcome
--, last_run_date, last_run_time, last_run_time_2, last_run_date_2, last_run_time_3
, cast(cast(last_run_date_2 as varchar(10)) + ' ' + cast(last_run_time_3 as varchar(11)) as datetime) as 'last_run_date_time'
FROM #temp2

drop table #temp
drop table #temp2

----------------------------------------------------------------------------------------------------

/*
use hrsscrpt
go

EXEC dbo.AuditLogToday

T2_Sync_Rebuild_PROD

select * from msdb.dbo.SysJobSteps WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
*/

--EXEC msdb.dbo.sp_help_job @Job_name = 'T2_Sync_Rebuild_PROD'

--select * from  msdb.dbo.sysjobactivity where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'

  DECLARE @xp_results TABLE (job_id               UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

--EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=0, @job_owner='NAM\wbenson007', @job_id='8B5B4493-711B-40F2-B447-C74225D78A17'
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1, @job_owner='NAM\wbenson007'

--SELECT * FROM @xp_results WHERE [Job_ID] = '8B5B4493-711B-40F2-B447-C74225D78A17'
--DELETE FROM @xp_results WHERE [Job ID] = '8B5B4493-711B-40F2-B447-C74225D78A17'

  SELECT xpr.job_id
, xpr.last_run_date
, xpr.last_run_time
, xpr.job_state
, IsNull(sjs.step_id,0) as 'Current_Execution_Step'
, ISNULL(sjs.step_name, N'0 ' + FORMATMESSAGE(14205))   as 'Current_Execution_Step_Name'
, xpr.current_retry_attempt
, xpr.next_run_date
, xpr.next_run_time
, xpr.next_run_schedule_id
INTO #tempA
  FROM @xp_results xpr LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
       msdb.dbo.sysjobs_view sjv
  WHERE (sjv.job_id = xpr.job_id)
and xpr.job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'

declare @Job_Execution_Status varchar(50)

IF EXISTS(SELECT 1 
          FROM msdb.dbo.sysjobs j
          JOIN msdb.dbo.sysjobactivity a ON a.job_id=J.job_id 
          WHERE j.name=N'T2_Sync_Rebuild_PROD' 
AND a.run_requested_date IS NOT NULL 
AND a.stop_execution_date IS NULL
         )
    SET @Job_Execution_Status = 'The job is running!'
ELSE
    SET @Job_Execution_Status = 'The job is NOT running.'


select step_id, step_name, subsystem, last_run_outcome
, last_run_date
, last_run_time
, cast(cast(last_run_time as varchar(10)) + '00' as integer) as 'last_run_time_2'
, cast(cast(last_run_date as varchar(12)) as date) as 'last_run_date_2'
, last_run_duration
--, *
INTO #temp
from msdb.dbo.SysJobSteps 
WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'


SELECT step_id, step_name, subsystem, last_run_outcome, last_run_date, last_run_time, last_run_time_2, last_run_date_2
, dateadd(hour, (last_run_time_2 / 1000000) % 100,
       dateadd(minute, (last_run_time_2 / 10000) % 100,
       dateadd(second, (last_run_time_2 / 100) % 100,
       dateadd(millisecond, (last_run_time_2 % 100) * 10, cast('00:00:00' as time(2)))))) as 'last_run_time_3'
, last_run_duration
INTO #temp2
FROM #temp


SELECT step_id, step_name, subsystem, last_run_outcome
, last_run_duration 
, CASE len(last_run_duration)
WHEN 2 THEN cast( '00:00:' + right(cast(last_run_duration as varchar(2)),2) as time(2))
WHEN 3 THEN '00:0' + left(cast(last_run_duration as varchar(3)),1) + ':' + right(cast(last_run_duration as varchar(3)),2)
WHEN 4 THEN '00:' + left(cast(last_run_duration as varchar(4)),2) + ':' + right(cast(last_run_duration as varchar(4)),2)
WHEN 5 THEN left(cast(last_run_duration as varchar(5)),1) + ':' + substring(cast(last_run_duration as varchar(5)),2,2) + ':' + right(cast(last_run_duration as varchar(5)),2)
ELSE cast(last_run_duration as varchar(10))
END as last_run_duration2
, cast(cast(last_run_date_2 as varchar(10)) + ' ' + cast(last_run_time_3 as varchar(11)) as datetime) as 'last_run_date_time'
INTO #temp3
FROM #temp2

declare @BeginTime as datetime
declare @EndTime as datetime
declare @last_step_id as tinyint = (select max(step_id) from #temp3)

SELECT @BeginTime = last_run_date_time from #temp3 where step_id = 1
SELECT @EndTime = last_run_date_time from #temp3 where step_id = @last_step_id

SELECT step_id, step_name, subsystem, last_run_outcome, last_run_duration, last_run_duration2, last_run_date_time
, case 
when a.Current_Execution_Step is not null then 'Executing'
else ''
end as 'current_step'
FROM #temp3  x left join #tempA a ON x.step_id = a.Current_Execution_Step

SELECT distinct cast(@EndTime - @BeginTime as time(0)) as 'Total_Execution_Duration'
, @Job_Execution_Status as 'Job_Execution_Status' FROM #temp3

SELECT * FROM #tempA

drop table #temp
drop table #temp2
drop table #temp3
drop table #tempA

----SELECT * FROM msdb.dbo.SysJobs where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
----SELECT * FROM msdb.dbo.SysJobHistory where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17' order by run_date, run_time, step_id

Excel Formatting Date value

up vote127down voteaccepted

=TEXT(A1,"DD/MM/YYYY hh:mm:ss") (24 hour time) =TEXT(A1,"DD/MM/YYYY hh:mm:ss AM/PM") (standard time)

DEFAULT Date column value

For modifying an existing column in an existing table:

ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn

Changing Credential Password

Requires ALTER ANY CREDENTIAL permissions to be granted.
----------------------------
--changes the password.
ALTER CREDENTIAL WilliamBenson_wbenson007 WITH IDENTITY = 'wbenson007',
    SECRET = '123456789';
GO

--removes the password.
ALTER CREDENTIAL WilliamBenson_wbenson007 WITH IDENTITY = 'wbenson007';
GO

MS-Access TRANSFORM Pivot into SQL Server T-SQL

-- MS-Access code
-----------------------------------------------------------------------------------
TRANSFORM Avg(q_percent) AS 'AvgOfq_percent'
SELECT [GUID], cast(quality_date as datetime) AS 'quality_date'
FROM #temp
GROUP BY [Guid], cast(quality_date as datetime)
PIVOT [Assessment Type]


-- T-SQL conversion
----------------------------------------------------------------------------------
SELECT *
INTO #temp2
FROM
(
SELECT [GUID], cast(quality_date as datetime) AS 'quality_date', q_percent, [Assessment Type]
FROM #temp
) t
PIVOT
(
  AVG(q_percent)
  FOR [Assessment Type] IN ("HR Call Assessment 2015", "HR Quality Assessment", "HR Ticket Assessment")
) p

CREATE VIEW syntax

Important to remember that ORDER BY works below when TOP is used.
--------------------------------------------------------------------

USE [database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].vw_name
as
SELECT TOP 10
[col1], [col2], ...
FROM dbo.[tblName]
GROUP BY [col1], [col2], ...
ORDER BY [col1], [col2], ...

GO

DATEDIF

The syntax for the DATEDIF function is:

= DATEDIF ( start_date , end_date , "interval")

WITH (TABLOCK)

Use TABLOCK to boost your INSERT INTO … SELECT performance

Something that we do frequently in an analytics application is loading data from one table to another. Personally I recommend SSIS for such tasks. But there are chances SSIS is not suitable, then we usually use INSERT INTO … SELECT.

I don’t recommend SELECT … INTO because you can’t control which file group the destination table goes to. And it is very important when I have to use table partition.

So if we need use T-SQL to move data from one place to another one. It is recommended to use TABLOCK option on the destination table. With a few other constraints, the query can be executed with minimal logging.

The few constraints are:

• The database must be in bulk logged recovery model.
• The destination table must be empty or without clustered index.
• There is no non-clustered index on the destination table.

Maybe it is difficult to match the second and third constraint. But we have an alternative which is partition switch. We can partition the destination table. Load the data from source into an empty table with same schema except the index objects (usually I keep the clustered index). After load, we can do a partition switch.

Here is two sample queries:

INSERT INTO dbo.FactDevicePerformance_History
([TimeKey], [DeviceKey], [reads per sec], [read hits per sec],
[seq reads per sec], [seq read hits per sec], [writes per sec],
[write hits per sec], [Kbytes read per sec], [Kbytes written per sec])
SELECT h.* FROM AIR.dbo.FactDevicePerformance_History h
INNER JOIN AIR.dbo.DimTime t ON h.TimeKey = t.TimeKey
WHERE t.[Date] BETWEEN ‘2011-11-1′ AND ‘2011-11-30′

INSERT INTO dbo.FactDevicePerformance_History WITH (TABLOCK)
([TimeKey], [DeviceKey], [reads per sec], [read hits per sec],
[seq reads per sec], [seq read hits per sec], [writes per sec],
[write hits per sec], [Kbytes read per sec], [Kbytes written per sec])
SELECT h.* FROM AIR.dbo.FactDevicePerformance_History h
INNER JOIN AIR.dbo.DimTime t ON h.TimeKey = t.TimeKey
WHERE t.[Date] BETWEEN ‘2011-1-1′ AND ‘2011-1-31′

You can find the only difference is query hint “TABLOCK”. But first query runs 21 seconds, the second one runs only 8 seconds. First query increase the log file from 0.5MB to 672MB. The second one increase the log file from 0.5MB to 3.37MB. There are 2.3 million records moved in the query.

This is what called minimal logging since SQL Server 2005. Anyway you still need to compare these two approaches before you implement. Because with minimal logging SQL Server will force data pages to be flushed to disk before the transaction commits. So if the IO system for data file is not very fast, or the data pages affected by the operation are not sequentially arranged, you might see worse performance with minimal logging.

Time variable

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';
DECLARE @time time(4) = @smalldatetime;

SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';

--Result
--@smalldatetime          time
------------------------- -------------
--1955-12-13 12:43:00     12:43:00.0000
--
--(1 row(s) affected)

Beginning and End of month calculation

DECLARE @MonthBegin as datetime
DECLARE @MonthEnd as datetime

SELECT @MonthBegin = CAST( CAST(Month(GetDate()) as nvarchar(2)) + '/01/'+ CAST(Year(GetDate()) as nvarchar(4)) + ' 00:00:00.000' as datetime)

SET @MonthBegin = DateAdd(m,-1, @MonthBegin)
SET @MonthEnd = DateAdd(ss,-1,DateAdd(m,1, @MonthBegin))

SELECT @MonthBegin as 'MonthBegin'
, @MonthEnd as 'MonthEnd'