<jco-blog/>

Software Development Project Notes

Author’s Archive: ЈЦЅГЇП €ΘΘΚ

Home / ЈЦЅГЇП €ΘΘΚ

48 Posts

originally posted 2013-4-4

1	originally posted 2013-4-4

Using uniqueidentifiers (Guids) as primary/foreign keys in SQL Server has its advantages and disadvantages. There are a lot of good discussions of uniqueidentifiers vs. integers as keys out there. We use uniqueidentifiers and the purpose of this example is to address a problem that some encounter using them with large and active databases.

One problem is that primary keys are typically clustered indexes (they are stored in sort order), so if the key for a new record isn’t sort-wise at the end of the index, then the server must move some number of records down to free a slot for the new key. In an extreme case, the server performance can degrade because it’s spending too much time churning the indexes.

Uniqueidentifiers are longer keys (than integers), requiring a bit more work from the server to sort, but the real issue is that uniqueidentifiers generated with newid() are random and therefore not guaranteed to fall at the end of the index. This can be a problem for large tables that experience frequent inserts.

SQL Server exposes the newsequentialid() function that guarantees that 1) a uniqueidentifier is sort-wise larger than the previously generated uniqueidentifier for that SQL instance, so the server can append to the end of the index and avoid the churn, and 2) it retains the uniqueness characteristics that make uniqueidentifiers desirable. The problem with newsequentialid() is that it’s not entirely convenient to use. SQL Server 2005 thru 2008 (and likely 2012) require that the function only be used as a default value for a column.

So while you can do this (SQL 2008 example)…

[OK]
declare @id uniqueidentifier = newid();

This won’t work…
[NO!]
declare @id uniqueidentifier = newsequentialid();

So, if newsequentialid() can only be used as a default for a column and therefore you can only set it when inserting a new record and *not* including that field in the insert statement, then how can you get the value for that column in the newly inserted record?! Sometimes you need it and sometimes you don’t. The example shows how to get the value for those times when you need it. Here’s the table definition, look at the use of newsequentialguid() in the default constraint in the last statement.

CREATE TABLE [dbo].[MyTable](
 [Id] [uniqueidentifier] NOT NULL,
 [Namespace] [nvarchar](100) NOT NULL,
 [CreateDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
 [Id] ASC,
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_CreateDateTime]  DEFAULT (getdate()) FOR [CreateDateTime]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

CREATE TABLE [dbo].[MyTable](

[Id] [uniqueidentifier] NOT NULL,

[Namespace] [nvarchar](100) NOT NULL,

[CreateDateTime] [datetime] NOT NULL,

CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED

(

[Id] ASC,

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_Id] DEFAULT (newsequentialid()) FOR [Id]

Insert a record like the statement below, and SQL Server sets the default values for the [Id] and [CreateDateTime] fields. The [CreateDateTime] receives a default value of the current SQL Server time. The [Id] receives a default value of a new sequential uniqueidentifier. This works!

insert [MyTable]([Namespace]) values ('MyNamespace');

Insert a record like the statement below, and SQL Server sets the default value for the [CreateDateTime] field and you’re supplying the value for the [Id] field.
This solves the first problem (you know the value of [Id]), but creates another problem… the value for [Id] isn’t a sequential uniqueidentifier.

declare @id uniqueidentifier;
set @id = newid();
insert [MyTable]([Namespace],[Id]) values ('MyNamespace', @id);

declare @id uniqueidentifier;

set @id = newid();

insert [MyTable]([Namespace],[Id]) values ('MyNamespace', @id);

Insert a record like the statement below, and SQL Server sets the default values for the [Id] and [CreateDateTime] fields (good so far) and the new record’s [Id] can be retrieved by using a table variable

--declare the table variable
declare @tab table (id uniqueidentifier)

--insert the new record
insert [MyTable]([Namespace])
output inserted.id 
into @tab
select N'MyNamespace'

--declare the variable to hold the new [Id] value
declare @id uniqueidentifier;

--fetch the value of [Id] for the newly inserted record into the @id variable
select @id = id from @tab

--declare the table variable

declare @tab table (id uniqueidentifier)

--insert the new record

insert [MyTable]([Namespace])

output inserted.id

into @tab

select N'MyNamespace'

--declare the variable to hold the new [Id] value

declare @id uniqueidentifier;

--fetch the value of [Id] for the newly inserted record into the @id variable

select @id = id from @tab

Now you’ve got the best of both worlds! Here’s a stored procedure that sort of side-steps the issue with newsequentialid().
Even though a stored procedure isn’t always convenient, it works.

create procedure [dbo].[spNewSequentialId](@id uniqueidentifier out) 
as
begin
 declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));
 insert @tab (dummy) values (0);
 select @id=id from @tab
end

create procedure [dbo].[spNewSequentialId](@id uniqueidentifier out)

begin

declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));

insert @tab (dummy) values (0);

select @id=id from @tab

end

Wouldn’t it be great to turn this stored procedure into a function?! Yes it would, but it won’t work because of SQL’s side-effecting rules.
If you try something like this…
[NO!]

--[Won't work!]
create function [dbo].[spNewSequentialId]() 
returns uniqueidentifier
as
begin
 declare @id uniqueidentifier;
 declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));
 insert @tab (dummy) values (0);
 select @id=id from @tab;
 return @id;
end

--[Won't work!]

create function [dbo].[spNewSequentialId]()

returns uniqueidentifier

begin

declare @id uniqueidentifier;

declare @tab table(dummy bit, id uniqueidentifier default (newsequentialid()));

insert @tab (dummy) values (0);

select @id=id from @tab;

return @id;

end

… you’re rewarded for you efforts with the following message

<span style="color: #ff0000;">Msg 443, Level 16, State 1, Procedure spNewSequentialId, Line 6
Invalid use of a side-effecting operator 'newsequentialid' within a function.</span>

1 2	<span style="color: #ff0000;">Msg 443, Level 16, State 1, Procedure spNewSequentialId, Line 6 Invalid use of a side-effecting operator 'newsequentialid' within a function.</span>

A C# SQL CLR Solution
Here’s an alternate method, using a C# CLR assembly. This is a “Visual C# SQL CLR Database Project For SQL Server 2005-2008″, these project types have some limitations in terms of what libraries they can reference, they’re restricted to using .Net Framework 3.5 SP1, and they induce registration and versioning headaches outside of those with regular T-SQL schema statements.

I put some info at the end regarding registration of the DLL with SQL Server. It’s a little different with 2005 and 2008, and depending on what you’re doing, you may have to deal with assembly trust issues. If you have the energy and patience to get over the initial hump, then a CLR assembly solution just might be the thing for you.

The C#
The SeqGuid class implements the behavior and is called by the Common class, which is called by a SQL database object. The Common class is the one to which SQL objects are pointed (more on that below).

[assembly: AllowPartiallyTrustedCallers]
namespace MyStuff
{
    public static class Common
    {
        public static Guid NewSequentialGuid()
        {
            return SeqGuid.NewSequentialGuid();
        }
   }
} 

...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace MyStuff
{
    public class SeqGuid
    {
        [DllImport("rpcrt4.dll", SetLastError = true)]
        static extern int UuidCreateSequential(out Guid guid);

        public static Guid NewSequentialGuid()  
        {
            Guid g;
            if (UuidCreateSequential(out g) != 0) throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());
            else
            {
                byte[] bytes = g.ToByteArray();
                Array.Reverse(bytes, 0, 4);
                Array.Reverse(bytes, 4, 2);
                Array.Reverse(bytes, 6, 2);
                return new Guid(bytes);
            }
        }
    }
}

[assembly: AllowPartiallyTrustedCallers]

namespace MyStuff

{

public static class Common

{

public static Guid NewSequentialGuid()

{

return SeqGuid.NewSequentialGuid();

}

...

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

namespace MyStuff

{

public class SeqGuid

{

[DllImport("rpcrt4.dll", SetLastError = true)]

static extern int UuidCreateSequential(out Guid guid);

public static Guid NewSequentialGuid()

{

Guid g;

if (UuidCreateSequential(out g) != 0) throw new System.ComponentModel.Win32Exception(System.Runtime.InteropServices.Marshal.GetLastWin32Error());

else

{

byte[] bytes = g.ToByteArray();

Array.Reverse(bytes, 0, 4);

Array.Reverse(bytes, 4, 2);

Array.Reverse(bytes, 6, 2);

return new Guid(bytes);

}

T-SQL CLR Assembly Registration (arcane incantations)

--Note: To use ":setvar" command variables from the SQL Server Management Studio, 
--you'll have to check the "By default, open new queries in SQLCMD mode" box from 
--Tools-&gt;Options-&gt;Query Execution and you may have to open a new query window.

:setvar User "database user name goes here"
:setvar Db "database name goes here"
:setvar AssemblyName "assembly name goes here (the full logical name, not the file name)"
:setvar AssemblyPath "path to the assembly file, e.g. c:\projects\mystuff.dll"

use [master]
go

alter authorization on database::$(Db) TO $(User);
go

GRANT UNSAFE ASSEMBLY TO $(User)
go

use root_properties
go

exec sp_changedbowner '$(User)'
go

sp_configure 'show advanced options', 1;
go

reconfigure;
go

sp_configure 'clr enabled', 1;
go

reconfigure;
go

alter database $(Db) set trustworthy on;
go

create assembly [$(Assembly)] from '$(AssemblyPath)' with permission_set = unsafe;
go

--Note: To use ":setvar" command variables from the SQL Server Management Studio,

--you'll have to check the "By default, open new queries in SQLCMD mode" box from

--Tools->Options->Query Execution and you may have to open a new query window.

:setvar User "database user name goes here"

:setvar Db "database name goes here"

:setvar AssemblyName "assembly name goes here (the full logical name, not the file name)"

:setvar AssemblyPath "path to the assembly file, e.g. c:\projects\mystuff.dll"

use [master]

alter authorization on database::$(Db) TO $(User);

GRANT UNSAFE ASSEMBLY TO $(User)

use root_properties

exec sp_changedbowner '$(User)'

sp_configure 'show advanced options', 1;

reconfigure;

sp_configure 'clr enabled', 1;

reconfigure;

alter database $(Db) set trustworthy on;

create assembly [$(Assembly)] from '$(AssemblyPath)' with permission_set = unsafe;

If that worked, then create database objects that call the CLR function. You should see the assembly in the Programmability->Assemblies under your database in SQL Server Management Studio. Keep in mind that your case may require more or less work for registration. There’s usually at least one wrench in the gears to remove

--a scalar function to get a new sequential guid. This calls the registered C# assembly
create function [dbo].[fNewSequentialGuid]()
returns uniqueidentifier
as external name [&lt;AssemblyName&gt;].[MySqlStuff.Common].[NewSequentialGuid]
GO

--a table-valued function to get a new sequential guid
create function [dbo].[tfGetNewSequentialGuid]()
returns @tab table([Id] uniqueidentifier)
as
begin
 insert @tab
 select [dbo].[fNewSequentialGuid]();
 return;
end
GO

--a scalar function to get a new sequential guid. This calls the registered C# assembly

create function [dbo].[fNewSequentialGuid]()

returns uniqueidentifier

as external name [<AssemblyName>].[MySqlStuff.Common].[NewSequentialGuid]

--a table-valued function to get a new sequential guid

create function [dbo].[tfGetNewSequentialGuid]()

returns @tab table([Id] uniqueidentifier)

begin

insert @tab

select [dbo].[fNewSequentialGuid]();

return;

end

If all went to plan, the new C# function should be callable from T-SQL
select [dbo].[fNewSequentialGuid]() --as a regular function that returns a scalar
or
select [Id] from [dbo].[tfGetNewSequentialGuid]() --as a table-valued function that returns a single-column, single-row table

Note: In cases where a CLR function is to be called between databases, or even instances (requires a linked server to be defined), you may have to wrap the SQL object
for it to be usable. I don’t know why, and there may be another really easy configuration solution for this, but I never got down to the brass tacks of the issue.

--a scalar function that wraps the [dbo].[fNewSequentialGuid](). It *should* be visible to other databases.
create function [dbo].[fNewSequentialGuidX]()
returns uniqueidentifier
begin
 return [dbo].[fNewSequentialGuid]();
end
GO

--a scalar function that wraps the [dbo].[fNewSequentialGuid](). It *should* be visible to other databases.

create function [dbo].[fNewSequentialGuidX]()

returns uniqueidentifier

begin

return [dbo].[fNewSequentialGuid]();

end

originally posted 2013-6-27

1	originally posted 2013-6-27

--Delete SQL object using its schema-qualified name. For tables, the constraints are dropped first.
create procedure [dbo].[spDropObject] (@fullname nvarchar(520))
as
begin
 begin try
  declare @type nvarchar(5)
  declare @resolvedFullname nvarchar(520) 
  declare @resolvedName nvarchar(255) 
  set @type = null
  set @resolvedFullname = null
  set @resolvedName = null
  
  --find the object
  select 
  @type = o.[type] 
  ,@resolvedFullname = '[' + object_schema_name(o.id) +  '].[' + o.[name] + ']'
  ,@resolvedName = '[' + o.[name] + ']'
  from dbo.sysobjects o
  where id = object_id(@fullname)
  
  --PROCEDURE
  if(@type = 'P') 
  begin
   exec('drop procedure ' + @resolvedFullname);
   return;
  end
    
  --VIEW
  if(@type = 'V') 
  begin
   exec('drop view ' + @resolvedFullname);
   return;
  end

  --FUNCTION
  if(@type = 'FN' or @type = 'TF') 
  begin
   exec('drop function ' + @resolvedFullname);
   return;
  end

  --TRIGGER
  if(@type = 'TR') 
  begin
   exec('drop trigger ' + @resolvedFullname);
   return;
  end

  --CONSTRAINT
  if(@type = 'C' or @type = 'UQ' or @type = 'D' or @type = 'F' or @type = 'PK' or @type = 'K') 
  begin
   declare @fullTablename nvarchar(520);
   set @fullTablename = null

   --find the contraint's table
   select @fullTablename ='[' + object_schema_name(t.[object_id]) + '].[' + t.[Name] + ']'
   from sys.tables t
   join sys.schemas s on t.schema_id = s.schema_id
   where t.object_id = (select parent_obj from dbo.sysobjects where id = object_id(@resolvedFullname))

   exec('alter table ' + @fullTablename + ' drop constraint ' + @resolvedName);
   return; 
  end
  
  --TABLE (drop all constraints then drop the table)
  if(@type = 'U') 
  begin
   --find FK references to the table
   declare @fktab table([Name] nvarchar(255))
   insert @fktab 
   select 
   [Name] = '[' + object_name(fkc.[constraint_object_id]) + ']'
   /*
   ,[Parent] = '[' + object_schema_name(fkc.[parent_object_id]) +  '].[' + object_name(fkc.[parent_object_id]) + ']'
   ,[Ref] = '[' + object_schema_name(fkc.[referenced_object_id]) +  '].[' + object_name(fkc.[referenced_object_id]) + ']'
   */
   from sys.foreign_key_columns as fkc
   where referenced_object_id = object_id(@resolvedFullname)
   order by [Name]

   --iterate FKs
   while(1=1)
   begin
    declare @constraint nvarchar(255)
    set @constraint = null
    select top 1
    @constraint = [Name]
    from @fktab

    if(@constraint is not null)
    begin
     --drop FK constraint
     exec [dbo].[spDropObject] @constraint;
     delete from @fktab where [Name] = @constraint --remove current record from working table
    end
    else break;
   end   

   --find constraints for table
   declare @constraintTab table ([Name] nvarchar(255));

   insert @constraintTab
   select [name] 
   from sys.objects 
   where parent_object_id = object_id(@resolvedFullname)
   order by [name]

   --iterate constraints
   while(1=1)
   begin
    set @constraint = null;
    select top 1 @constraint = [Name] from @constraintTab
   
    if(@constraint is not null)
    begin
     --drop constraint
     exec [dbo].[spDropObject] @constraint;
     delete from @constraintTab where [Name] = @constraint --remove current record from working table
    end
    else break;
   end

   --drop table
   exec('drop table ' + @resolvedFullname);
   return;
  end
 end try
 begin catch
  declare @message nvarchar(max)
  set @message = error_message( ) ;
  print @message
 end catch
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

--Delete SQL object using its schema-qualified name. For tables, the constraints are dropped first.

create procedure [dbo].[spDropObject] (@fullname nvarchar(520))

begin

begin try

declare @type nvarchar(5)

declare @resolvedFullname nvarchar(520)

declare @resolvedName nvarchar(255)

set @type = null

set @resolvedFullname = null

set @resolvedName = null

--find the object

select

@type = o.[type]

,@resolvedFullname = '[' + object_schema_name(o.id) + '].[' + o.[name] + ']'

,@resolvedName = '[' + o.[name] + ']'

from dbo.sysobjects o

where id = object_id(@fullname)

--PROCEDURE

if(@type = 'P')

begin

exec('drop procedure ' + @resolvedFullname);

return;

end

--VIEW

if(@type = 'V')

begin

exec('drop view ' + @resolvedFullname);

return;

end

--FUNCTION

if(@type = 'FN' or @type = 'TF')

begin

exec('drop function ' + @resolvedFullname);

return;

end

--TRIGGER

if(@type = 'TR')

begin

exec('drop trigger ' + @resolvedFullname);

return;

end

--CONSTRAINT

if(@type = 'C' or @type = 'UQ' or @type = 'D' or @type = 'F' or @type = 'PK' or @type = 'K')

begin

declare @fullTablename nvarchar(520);

set @fullTablename = null

--find the contraint's table

select @fullTablename ='[' + object_schema_name(t.[object_id]) + '].[' + t.[Name] + ']'

from sys.tables t

join sys.schemas s on t.schema_id = s.schema_id

where t.object_id = (select parent_obj from dbo.sysobjects where id = object_id(@resolvedFullname))

exec('alter table ' + @fullTablename + ' drop constraint ' + @resolvedName);

return;

end

--TABLE (drop all constraints then drop the table)

if(@type = 'U')

begin

--find FK references to the table

declare @fktab table([Name] nvarchar(255))

insert @fktab

select

[Name] = '[' + object_name(fkc.[constraint_object_id]) + ']'

,[Parent] = '[' + object_schema_name(fkc.[parent_object_id]) + '].[' + object_name(fkc.[parent_object_id]) + ']'

,[Ref] = '[' + object_schema_name(fkc.[referenced_object_id]) + '].[' + object_name(fkc.[referenced_object_id]) + ']'

from sys.foreign_key_columns as fkc

where referenced_object_id = object_id(@resolvedFullname)

order by [Name]

--iterate FKs

while(1=1)

begin

declare @constraint nvarchar(255)

set @constraint = null

select top 1

@constraint = [Name]

from @fktab

if(@constraint is not null)

begin

--drop FK constraint

exec [dbo].[spDropObject] @constraint;

delete from @fktab where [Name] = @constraint --remove current record from working table

end

else break;

end

--find constraints for table

declare @constraintTab table ([Name] nvarchar(255));

insert @constraintTab

select [name]

from sys.objects

where parent_object_id = object_id(@resolvedFullname)

order by [name]

--iterate constraints

while(1=1)

begin

set @constraint = null;

select top 1 @constraint = [Name] from @constraintTab

if(@constraint is not null)

begin

--drop constraint

exec [dbo].[spDropObject] @constraint;

delete from @constraintTab where [Name] = @constraint --remove current record from working table

end

else break;

end

--drop table

exec('drop table ' + @resolvedFullname);

return;

end

end try

begin catch

declare @message nvarchar(max)

set @message = error_message( ) ;

print @message

end catch

end

originally posted 2013-7-25

1	originally posted 2013-7-25

A Unix timestamp is defined as the number of seconds elapsed between January 1, 1970 00:00:00 and a given date/time expressed in Universal Coordinated Time.
A timestamp can be generated up to the date 2038-1-19 3:14:7 before overflowing a 32-bit integer.
http://en.wikipedia.org/wiki/Unix_time

--create a Unix timestamp from current server UTC time
create function [dbo].[fGetUnixTimestamp]()
returns int
as 
begin
   return datediff(s,'1970-1-1',sysutcdatetime());
end
go

--convert a Unix timestamp to a datetime in UTC
create function [dbo].[fConvertUnixTimestampToDateTime] (@timestamp int)
returns datetime
as
begin
   return dateadd(s,@timestamp,'1970-1-1')
end
go

--convert a Unix timestamp to a datetime adjusted to the server's time zone
create function [dbo].[fConvertUnixTimestampToLocalDateTime] (@timestamp int)
returns datetime
as
begin
   return dateadd(hh,datediff(hh,sysutcdatetime(),getdate()),dateadd(s,@timestamp,'1970-1-1'))
end
go

--[TEST]

declare @ts int
select @ts = [dbo].[fGetUnixTimestamp]()

select [Timestamp] = @ts
,[UTC Time] = [dbo].[fConvertUnixTimestampToDateTime](@ts)
,[Local Time] = [dbo].[fConvertUnixTimestampToLocalDateTime](@ts)
,[Local Time Zone Offset from UTC] = datediff(hh,sysutcdatetime(),getdate())

/*
[EXAMPLE RESULTS]
Timestamp Local Time = 1374776005   
UTC Time  = Jul 25 2013  6:13PM
Local Time = Jul 25 2013 11:13AM
Local Time Zone Offset from UTC = -7
*/

--create a Unix timestamp from current server UTC time

create function [dbo].[fGetUnixTimestamp]()

returns int

begin

return datediff(s,'1970-1-1',sysutcdatetime());

end

--convert a Unix timestamp to a datetime in UTC

create function [dbo].[fConvertUnixTimestampToDateTime] (@timestamp int)

returns datetime

begin

return dateadd(s,@timestamp,'1970-1-1')

end

--convert a Unix timestamp to a datetime adjusted to the server's time zone

create function [dbo].[fConvertUnixTimestampToLocalDateTime] (@timestamp int)

returns datetime

begin

return dateadd(hh,datediff(hh,sysutcdatetime(),getdate()),dateadd(s,@timestamp,'1970-1-1'))

end

--[TEST]

declare @ts int

select @ts = [dbo].[fGetUnixTimestamp]()

select [Timestamp] = @ts

,[UTC Time] = [dbo].[fConvertUnixTimestampToDateTime](@ts)

,[Local Time] = [dbo].[fConvertUnixTimestampToLocalDateTime](@ts)

,[Local Time Zone Offset from UTC] = datediff(hh,sysutcdatetime(),getdate())

[EXAMPLE RESULTS]

Timestamp Local Time = 1374776005

UTC Time = Jul 25 2013 6:13PM

Local Time = Jul 25 2013 11:13AM

Local Time Zone Offset from UTC = -7

An alternative, encoding a DateTime into a BigInt and back again.

create function [dbo].[fEncodeDateTimeToBigInt](@dt datetime)
returns bigint
as
begin
 --note: resolution is to the second
 if(@dt is not null)
  return convert(bigint,
   DATEPART(second, @dt) +
   DATEPART(minute, @dt) * 100 +
   DATEPART(hour, @dt) * 10000 +
   DATEPART(day, @dt) * 1000000 +
   DATEPART(month, @dt) * 100000000 +
   DATEPART(year, @dt) * 10000000000)
 return null;
end
go

create function [dbo].[fDecodeBigIntToDateTime](@bi bigint)
returns datetime
as
begin
 if(@bi is not null)
 begin
  declare @str nvarchar(30)
  set @str = convert(nvarchar(30),@bi)
     if(len(@str)=14) return convert(datetime,
  substring(@str,1,4) + '-' 
  + substring(@str,5,2) + '-' 
  + substring(@str,7,2) + ' ' 
  + substring(@str,9,2) + ':' 
  + substring(@str,11,2) + ':' 
  + substring(@str,13,2));
 end
 return null;
end

--TEST
declare @nowUtc datetime
set @nowUtc = sysutcdatetime();

declare @bi bigint
set @bi = [dbo].[fEncodeDateTimeToBigInt](@nowUtc);

declare @dt datetime 
set @dt = [dbo].[fDecodeBigIntToDateTime](@bi);

select [Input DateTime] = @nowUtc, [Encoded BigInt] = @bi, [Decoded DateTime] = @dt

/*
[Example Results]
Input DateTime = 2013-07-25 20:55:30.293
Encoded BigInt = 20130725205530 
Decoded DateTime = 2013-07-25 20:55:30.000
*/

create function [dbo].[fEncodeDateTimeToBigInt](@dt datetime)

returns bigint

begin

--note: resolution is to the second

if(@dt is not null)

return convert(bigint,

DATEPART(second, @dt) +

DATEPART(minute, @dt) * 100 +

DATEPART(hour, @dt) * 10000 +

DATEPART(day, @dt) * 1000000 +

DATEPART(month, @dt) * 100000000 +

DATEPART(year, @dt) * 10000000000)

return null;

end

create function [dbo].[fDecodeBigIntToDateTime](@bi bigint)

returns datetime

begin

if(@bi is not null)

begin

declare @str nvarchar(30)

set @str = convert(nvarchar(30),@bi)

if(len(@str)=14) return convert(datetime,

substring(@str,1,4) + '-'

+ substring(@str,5,2) + '-'

+ substring(@str,7,2) + ' '

+ substring(@str,9,2) + ':'

+ substring(@str,11,2) + ':'

+ substring(@str,13,2));

end

return null;

end

--TEST

declare @nowUtc datetime

set @nowUtc = sysutcdatetime();

declare @bi bigint

set @bi = [dbo].[fEncodeDateTimeToBigInt](@nowUtc);

declare @dt datetime

set @dt = [dbo].[fDecodeBigIntToDateTime](@bi);

select [Input DateTime] = @nowUtc, [Encoded BigInt] = @bi, [Decoded DateTime] = @dt

[Example Results]

Input DateTime = 2013-07-25 20:55:30.293

Encoded BigInt = 20130725205530

Decoded DateTime = 2013-07-25 20:55:30.000

originally posted 2013-8-29

1	originally posted 2013-8-29

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace NPT.Common
{
    /// &amp;lt;summary&amp;gt;
    /// Polls the system periodically and raises the IsIdle event if the
    /// user interface has been idle for at least the specfied number of seconds.
    /// Stops polling once the IsIdle is fired, use Start() to resume polling.
    /// &amp;lt;/summary&amp;gt;
    public class IdleTimer : IDisposable
    {
        [DllImport("user32.dll")]
        static extern bool GetLastInputInfo(ref LASTINPUTINFO plii);

        internal struct LASTINPUTINFO
        {
            public Int32 cbSize;
            public Int32 dwTime;
        }

        private int timeoutSeconds = 0;
        private int pollSeconds = 0;
        private System.Timers.Timer timer = null;
        private System.ComponentModel.ISynchronizeInvoke sychronizingObject = null;

        //ctor
        public IdleTimer(int idleTimeoutSeconds, System.ComponentModel.ISynchronizeInvoke sychronizingObject = null)
        {
            if (System.Diagnostics.Debugger.IsAttached) System.Diagnostics.Debug.WriteLine("IdleTimer: Created");
            pollSeconds = 5;
            if (idleTimeoutSeconds &amp;lt; 5) 
            {
                idleTimeoutSeconds = 5;
                pollSeconds = 4;
            }
            this.timeoutSeconds = idleTimeoutSeconds;
            this.sychronizingObject = sychronizingObject;

        public void Start()
        {
            System.Diagnostics.Debug.WriteLine("IdleTimer: Started");
            StartPolling();
        }

        public void Stop()
        {
            System.Diagnostics.Debug.WriteLine("IdleTimer: Stopped");
            StopPolling();
        }

        //event
        public delegate void IsIdleEventHandler(object sender, IdleTimerEventArgs e);
        public event IsIdleEventHandler IsIdle;
        protected virtual void OnIsIdle(IdleTimerEventArgs e)
        {
            //raise event
            StopPolling();
            if (IsIdle != null) IsIdle(this, e);
        }

        //start polling idle time
        private void StartPolling()
        {
            if (timer == null)
            {
                timer = new System.Timers.Timer(pollSeconds * 1000);
                timer.Elapsed += new System.Timers.ElapsedEventHandler(Poll);
                if (sychronizingObject != null) timer.SynchronizingObject = sychronizingObject;
            }
            timer.Start();
        }

        //stop polling idle time
        private void StopPolling()
        {
            if (timer != null) timer.Stop();
        }

        //poll idle time
        private void Poll(object sender, System.Timers.ElapsedEventArgs e)
        {
            StopPolling();
            int idleSeconds = GetIdleTime();
            if (idleSeconds &amp;gt;= timeoutSeconds)
            {
                if (System.Diagnostics.Debugger.IsAttached)
                {
                    System.Diagnostics.Debug.WriteLine("IdleTimer: Idle - Timeout = {1} seconds, Idle time = {0} seconds", idleSeconds, timeoutSeconds);
                }

                //system has been idle for at least timeoutSeconds seconds
                OnIsIdle(new IdleTimerEventArgs(idleSeconds:idleSeconds,timeoutSeconds:timeoutSeconds)); 
            }
            StartPolling();
        }

        /// &amp;lt;summary&amp;gt;
        /// Returns system idle time in seconds
        /// &amp;lt;/summary&amp;gt;
        /// &amp;lt;returns&amp;gt;&amp;lt;/returns&amp;gt;
        public int GetIdleTime()
        {
            int systemUptime = Environment.TickCount;
            int lastInputTicks = 0;
            int idleTicks = 0;

            LASTINPUTINFO lastInputInfo = new LASTINPUTINFO();
            lastInputInfo.cbSize = (Int32)Marshal.SizeOf(lastInputInfo);
            lastInputInfo.dwTime = 0;

            if (GetLastInputInfo(ref lastInputInfo))
            {
                lastInputTicks = (int)lastInputInfo.dwTime;
                idleTicks = systemUptime - lastInputTicks;
            }
            Int32 seconds = idleTicks / 1000;
            return seconds;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                StopPolling();
                timer = null;
                sychronizingObject = null;
                if (System.Diagnostics.Debugger.IsAttached)
                {
                    System.Diagnostics.Debug.WriteLine("IdleTimer: Disposed");
                }
            }
        }
    }

    public class IdleTimerEventArgs : EventArgs
    {
        public IdleTimerEventArgs(int idleSeconds, int timeoutSeconds)
        {
            IdleSeconds = idleSeconds;
            TimeoutSeconds = timeoutSeconds;
        }

        public int IdleSeconds { get; set; }
        public int TimeoutSeconds {get; set;}
    }
}

using System;
using System.Text;
using System.Runtime.InteropServices;
using System.Security.Permissions;
using System.Threading;

//obsolete: [assembly: SecurityPermission(SecurityAction.RequestMinimum, UnmanagedCode = true)]
namespace System.Windows.Forms
{
    public class MessageBoxEx
    {
        public static DialogResult Show(string text, uint timeoutSeconds)
        {
            Setup("", timeoutSeconds);
            return MessageBox.Show(text);
        }

        public static DialogResult Show(string text, string caption, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(text, caption);
        }

        public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(text, caption, buttons);
        }

        public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(text, caption, buttons, icon);
        }

        public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(text, caption, buttons, icon, defButton);
        }

        public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, MessageBoxOptions options, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(text, caption, buttons, icon, defButton, options);
        }

        public static DialogResult Show(IWin32Window owner, string text, uint timeoutSeconds)
        {
            Setup("", timeoutSeconds);
            return MessageBox.Show(owner, text);
        }

        public static DialogResult Show(IWin32Window owner, string text, string caption, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(owner, text, caption);
        }

        public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(owner, text, caption, buttons);
        }

        public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(owner, text, caption, buttons, icon);
        }

        public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(owner, text, caption, buttons, icon, defButton);
        }

        public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, MessageBoxOptions options, uint timeoutSeconds)
        {
            Setup(caption, timeoutSeconds);
            return MessageBox.Show(owner, text, caption, buttons, icon, defButton, options);
        }

        public delegate IntPtr HookProc(int nCode, IntPtr wParam, IntPtr lParam);
        public delegate void TimerProc(IntPtr hWnd, uint uMsg, UIntPtr nIDEvent, uint dwTime);

        public const int WH_CALLWNDPROCRET = 12;
        public const int WM_DESTROY = 0x0002;
        public const int WM_INITDIALOG = 0x0110;
        public const int WM_TIMER = 0x0113;
        public const int WM_USER = 0x400;
        public const int DM_GETDEFID = WM_USER + 0;

        [DllImport("User32.dll")]
        public static extern UIntPtr SetTimer(IntPtr hWnd, UIntPtr nIDEvent, uint uElapse, TimerProc lpTimerFunc);

        [DllImport("User32.dll")]
        public static extern IntPtr SendMessage(IntPtr hWnd, int Msg, IntPtr wParam, IntPtr lParam);

        [DllImport("user32.dll")]
        public static extern IntPtr SetWindowsHookEx(int idHook, HookProc lpfn, IntPtr hInstance, int threadId);

        [DllImport("user32.dll")]
        public static extern int UnhookWindowsHookEx(IntPtr idHook);

        [DllImport("user32.dll")]
        public static extern IntPtr CallNextHookEx(IntPtr idHook, int nCode, IntPtr wParam, IntPtr lParam);

        [DllImport("user32.dll")]
        public static extern int GetWindowTextLength(IntPtr hWnd);

        [DllImport("user32.dll")]
        public static extern int GetWindowText(IntPtr hWnd, StringBuilder text, int maxLength);

        [DllImport("user32.dll")]
        public static extern int EndDialog(IntPtr hDlg, IntPtr nResult);

        [StructLayout(LayoutKind.Sequential)]
        public struct CWPRETSTRUCT
        {
            public IntPtr lResult;
            public IntPtr lParam;
            public IntPtr wParam;
            public uint message;
            public IntPtr hwnd;
        };

        private const int TimerID = 42;
        private static HookProc hookProc;
        private static TimerProc hookTimer;
        private static uint hooktimeoutSeconds;
        private static string hookCaption;
        private static IntPtr hHook;

        static MessageBoxEx()
        {
            hookProc = new HookProc(MessageBoxHookProc);
            hookTimer = new TimerProc(MessageBoxTimerProc);
            hooktimeoutSeconds = 0;
            hookCaption = null;
            hHook = IntPtr.Zero;
        }

        private static void Setup(string caption, uint timeoutSeconds)
        {
            if (hHook != IntPtr.Zero)
                throw new NotSupportedException("multiple calls are not supported");

            hooktimeoutSeconds = timeoutSeconds*1000;
            hookCaption = caption != null ? caption : "";

            //"AppDomain.GetCurrentThreadId()" is obsolete, however, its replacement "Thread.CurrentThread.ManagedThreadId" isn't correct this context.
            //See: http://stackoverflow.com/questions/772354/appdomain-getcurrentthreadid-vs-thread-managedthreadid-for-windows-api-calls
            #pragma warning disable 618 //disable the obsolete warning
            hHook = SetWindowsHookEx(WH_CALLWNDPROCRET, hookProc, IntPtr.Zero, AppDomain.GetCurrentThreadId()); //obsolete: 
            #pragma warning restore 618 //enable the obsolete warning
        }

        private static IntPtr MessageBoxHookProc(int nCode, IntPtr wParam, IntPtr lParam)
        {
            if (nCode &amp;lt; 0)
                return CallNextHookEx(hHook, nCode, wParam, lParam);

            CWPRETSTRUCT msg = (CWPRETSTRUCT)Marshal.PtrToStructure(lParam, typeof(CWPRETSTRUCT));
            IntPtr hook = hHook;

            if (hookCaption != null &amp;amp;&amp;amp; msg.message == WM_INITDIALOG)
            {
                int nLength = GetWindowTextLength(msg.hwnd);
                StringBuilder text = new StringBuilder(nLength + 1);

                GetWindowText(msg.hwnd, text, text.Capacity);

                if (hookCaption == text.ToString())
                {
                    hookCaption = null;
                    SetTimer(msg.hwnd, (UIntPtr)TimerID, hooktimeoutSeconds, hookTimer);
                    UnhookWindowsHookEx(hHook);
                    hHook = IntPtr.Zero;
                }
            }

            return CallNextHookEx(hook, nCode, wParam, lParam);
        }

        private static void MessageBoxTimerProc(IntPtr hWnd, uint uMsg, UIntPtr nIDEvent, uint dwTime)
        {
            if (nIDEvent == (UIntPtr)TimerID)
            {
                short dw = (short)SendMessage(hWnd, DM_GETDEFID, IntPtr.Zero, IntPtr.Zero);

                EndDialog(hWnd, (IntPtr)dw);
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPT.Common;

namespace WinFormsIdleTimerTest
{
    public partial class BaseForm : Form
    {
        private IdleTimer idleTimer = null;

        public BaseForm()
        {
            InitializeComponent();
        }

        protected int IdleTimeoutSeconds { get; set; }
        
        protected virtual void StartIdleTimer()
        {
            if (idleTimer == null)
            {
                StopIdleTimer();
                idleTimer = new IdleTimer(idleTimeoutSeconds: IdleTimeoutSeconds, sychronizingObject: this);
                idleTimer.IsIdle += new IdleTimer.IsIdleEventHandler(IdleTimer_IsIdle);
            }
            idleTimer.Start();
        }

        protected virtual void StopIdleTimer()
        {
            if (idleTimer != null) idleTimer.Stop();
        }

        protected virtual void IdleTimer_IsIdle(object sender, IdleTimerEventArgs e)
        {
            //handle IsIdle event, close the form (override in inherting classes to do something different)
            Close();
        }

        public new void Close()
        {
            StopIdleTimer();
            base.Close();
        }

        public new void Hide()
        {
            StopIdleTimer();
            base.Hide();
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPT.Common;

namespace WinFormsIdleTimerTest
{
    public partial class MainForm : BaseForm
    {
        public MainForm()
        {
            InitializeComponent();
            IdleTimeoutSeconds = 5;
            StartIdleTimer();
        }

        private void CancelButton_Click(object sender, EventArgs e)
        {
            Close();
        }

        protected override void IdleTimer_IsIdle(object sender, IdleTimerEventArgs e)
        {
            //an optional override of the BaseForm implementation to do something more than just closing the form
            //MessageBoxEx is an implementation of MessageBox that dismisses itself after a specified number of seconds
            var response = MessageBoxEx.Show(
                    "Is there anybody out there?",
                    string.Format("System Idle Detected ({0} seconds)", e.IdleSeconds),
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question,
                    MessageBoxDefaultButton.Button2,
                    10);
            if (response == System.Windows.Forms.DialogResult.No)
            {

                MessageBoxEx.Show(
                    "\tGoodbye blue sky.",
                    string.Empty,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.None,
                    MessageBoxDefaultButton.Button1,
                    3);

                Close();
                return;
            }
            StartIdleTimer();
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

namespace NPT.Common

{

/// &lt;summary&gt;

/// Polls the system periodically and raises the IsIdle event if the

/// user interface has been idle for at least the specfied number of seconds.

/// Stops polling once the IsIdle is fired, use Start() to resume polling.

/// &lt;/summary&gt;

public class IdleTimer : IDisposable

{

[DllImport("user32.dll")]

static extern bool GetLastInputInfo(ref LASTINPUTINFO plii);

internal struct LASTINPUTINFO

{

public Int32 cbSize;

public Int32 dwTime;

}

private int timeoutSeconds = 0;

private int pollSeconds = 0;

private System.Timers.Timer timer = null;

private System.ComponentModel.ISynchronizeInvoke sychronizingObject = null;

//ctor

public IdleTimer(int idleTimeoutSeconds, System.ComponentModel.ISynchronizeInvoke sychronizingObject = null)

{

if (System.Diagnostics.Debugger.IsAttached) System.Diagnostics.Debug.WriteLine("IdleTimer: Created");

pollSeconds = 5;

if (idleTimeoutSeconds &lt; 5)

{

idleTimeoutSeconds = 5;

pollSeconds = 4;

}

this.timeoutSeconds = idleTimeoutSeconds;

this.sychronizingObject = sychronizingObject;

public void Start()

{

System.Diagnostics.Debug.WriteLine("IdleTimer: Started");

StartPolling();

}

public void Stop()

{

System.Diagnostics.Debug.WriteLine("IdleTimer: Stopped");

StopPolling();

}

//event

public delegate void IsIdleEventHandler(object sender, IdleTimerEventArgs e);

public event IsIdleEventHandler IsIdle;

protected virtual void OnIsIdle(IdleTimerEventArgs e)

{

//raise event

StopPolling();

if (IsIdle != null) IsIdle(this, e);

}

//start polling idle time

private void StartPolling()

{

if (timer == null)

{

timer = new System.Timers.Timer(pollSeconds * 1000);

timer.Elapsed += new System.Timers.ElapsedEventHandler(Poll);

if (sychronizingObject != null) timer.SynchronizingObject = sychronizingObject;

}

timer.Start();

}

//stop polling idle time

private void StopPolling()

{

if (timer != null) timer.Stop();

}

//poll idle time

private void Poll(object sender, System.Timers.ElapsedEventArgs e)

{

StopPolling();

int idleSeconds = GetIdleTime();

if (idleSeconds &gt;= timeoutSeconds)

{

if (System.Diagnostics.Debugger.IsAttached)

{

System.Diagnostics.Debug.WriteLine("IdleTimer: Idle - Timeout = {1} seconds, Idle time = {0} seconds", idleSeconds, timeoutSeconds);

}

//system has been idle for at least timeoutSeconds seconds

OnIsIdle(new IdleTimerEventArgs(idleSeconds:idleSeconds,timeoutSeconds:timeoutSeconds));

}

StartPolling();

}

/// &lt;summary&gt;

/// Returns system idle time in seconds

/// &lt;/summary&gt;

/// &lt;returns&gt;&lt;/returns&gt;

public int GetIdleTime()

{

int systemUptime = Environment.TickCount;

int lastInputTicks = 0;

int idleTicks = 0;

LASTINPUTINFO lastInputInfo = new LASTINPUTINFO();

lastInputInfo.cbSize = (Int32)Marshal.SizeOf(lastInputInfo);

lastInputInfo.dwTime = 0;

if (GetLastInputInfo(ref lastInputInfo))

{

lastInputTicks = (int)lastInputInfo.dwTime;

idleTicks = systemUptime - lastInputTicks;

}

Int32 seconds = idleTicks / 1000;

return seconds;

}

public void Dispose()

{

Dispose(true);

GC.SuppressFinalize(this);

}

protected virtual void Dispose(bool disposing)

{

if (disposing)

{

StopPolling();

timer = null;

sychronizingObject = null;

if (System.Diagnostics.Debugger.IsAttached)

{

System.Diagnostics.Debug.WriteLine("IdleTimer: Disposed");

}

public class IdleTimerEventArgs : EventArgs

{

public IdleTimerEventArgs(int idleSeconds, int timeoutSeconds)

{

IdleSeconds = idleSeconds;

TimeoutSeconds = timeoutSeconds;

}

public int IdleSeconds { get; set; }

public int TimeoutSeconds {get; set;}

}

using System;

using System.Text;

using System.Runtime.InteropServices;

using System.Security.Permissions;

using System.Threading;

//obsolete: [assembly: SecurityPermission(SecurityAction.RequestMinimum, UnmanagedCode = true)]

namespace System.Windows.Forms

{

public class MessageBoxEx

{

public static DialogResult Show(string text, uint timeoutSeconds)

{

Setup("", timeoutSeconds);

return MessageBox.Show(text);

}

public static DialogResult Show(string text, string caption, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(text, caption);

}

public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(text, caption, buttons);

}

public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(text, caption, buttons, icon);

}

public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(text, caption, buttons, icon, defButton);

}

public static DialogResult Show(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, MessageBoxOptions options, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(text, caption, buttons, icon, defButton, options);

}

public static DialogResult Show(IWin32Window owner, string text, uint timeoutSeconds)

{

Setup("", timeoutSeconds);

return MessageBox.Show(owner, text);

}

public static DialogResult Show(IWin32Window owner, string text, string caption, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(owner, text, caption);

}

public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(owner, text, caption, buttons);

}

public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(owner, text, caption, buttons, icon);

}

public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(owner, text, caption, buttons, icon, defButton);

}

public static DialogResult Show(IWin32Window owner, string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon, MessageBoxDefaultButton defButton, MessageBoxOptions options, uint timeoutSeconds)

{

Setup(caption, timeoutSeconds);

return MessageBox.Show(owner, text, caption, buttons, icon, defButton, options);

}

public delegate IntPtr HookProc(int nCode, IntPtr wParam, IntPtr lParam);

public delegate void TimerProc(IntPtr hWnd, uint uMsg, UIntPtr nIDEvent, uint dwTime);

public const int WH_CALLWNDPROCRET = 12;

public const int WM_DESTROY = 0x0002;

public const int WM_INITDIALOG = 0x0110;

public const int WM_TIMER = 0x0113;

public const int WM_USER = 0x400;

public const int DM_GETDEFID = WM_USER + 0;

[DllImport("User32.dll")]

public static extern UIntPtr SetTimer(IntPtr hWnd, UIntPtr nIDEvent, uint uElapse, TimerProc lpTimerFunc);

[DllImport("User32.dll")]

public static extern IntPtr SendMessage(IntPtr hWnd, int Msg, IntPtr wParam, IntPtr lParam);

[DllImport("user32.dll")]

public static extern IntPtr SetWindowsHookEx(int idHook, HookProc lpfn, IntPtr hInstance, int threadId);

[DllImport("user32.dll")]

public static extern int UnhookWindowsHookEx(IntPtr idHook);

[DllImport("user32.dll")]

public static extern IntPtr CallNextHookEx(IntPtr idHook, int nCode, IntPtr wParam, IntPtr lParam);

[DllImport("user32.dll")]

public static extern int GetWindowTextLength(IntPtr hWnd);

[DllImport("user32.dll")]

public static extern int GetWindowText(IntPtr hWnd, StringBuilder text, int maxLength);

[DllImport("user32.dll")]

public static extern int EndDialog(IntPtr hDlg, IntPtr nResult);

[StructLayout(LayoutKind.Sequential)]

public struct CWPRETSTRUCT

{

public IntPtr lResult;

public IntPtr lParam;

public IntPtr wParam;

public uint message;

public IntPtr hwnd;

};

private const int TimerID = 42;

private static HookProc hookProc;

private static TimerProc hookTimer;

private static uint hooktimeoutSeconds;

private static string hookCaption;

private static IntPtr hHook;

static MessageBoxEx()

{

hookProc = new HookProc(MessageBoxHookProc);

hookTimer = new TimerProc(MessageBoxTimerProc);

hooktimeoutSeconds = 0;

hookCaption = null;

hHook = IntPtr.Zero;

}

private static void Setup(string caption, uint timeoutSeconds)

{

if (hHook != IntPtr.Zero)

throw new NotSupportedException("multiple calls are not supported");

hooktimeoutSeconds = timeoutSeconds*1000;

hookCaption = caption != null ? caption : "";

//"AppDomain.GetCurrentThreadId()" is obsolete, however, its replacement "Thread.CurrentThread.ManagedThreadId" isn't correct this context.

//See: http://stackoverflow.com/questions/772354/appdomain-getcurrentthreadid-vs-thread-managedthreadid-for-windows-api-calls

#pragma warning disable 618 //disable the obsolete warning

hHook = SetWindowsHookEx(WH_CALLWNDPROCRET, hookProc, IntPtr.Zero, AppDomain.GetCurrentThreadId()); //obsolete:

#pragma warning restore 618 //enable the obsolete warning

}

private static IntPtr MessageBoxHookProc(int nCode, IntPtr wParam, IntPtr lParam)

{

if (nCode &lt; 0)

return CallNextHookEx(hHook, nCode, wParam, lParam);

CWPRETSTRUCT msg = (CWPRETSTRUCT)Marshal.PtrToStructure(lParam, typeof(CWPRETSTRUCT));

IntPtr hook = hHook;

if (hookCaption != null &amp;&amp; msg.message == WM_INITDIALOG)

{

int nLength = GetWindowTextLength(msg.hwnd);

StringBuilder text = new StringBuilder(nLength + 1);

GetWindowText(msg.hwnd, text, text.Capacity);

if (hookCaption == text.ToString())

{

hookCaption = null;

SetTimer(msg.hwnd, (UIntPtr)TimerID, hooktimeoutSeconds, hookTimer);

UnhookWindowsHookEx(hHook);

hHook = IntPtr.Zero;

}

return CallNextHookEx(hook, nCode, wParam, lParam);

}

private static void MessageBoxTimerProc(IntPtr hWnd, uint uMsg, UIntPtr nIDEvent, uint dwTime)

{

if (nIDEvent == (UIntPtr)TimerID)

{

short dw = (short)SendMessage(hWnd, DM_GETDEFID, IntPtr.Zero, IntPtr.Zero);

EndDialog(hWnd, (IntPtr)dw);

}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using NPT.Common;

namespace WinFormsIdleTimerTest

{

public partial class BaseForm : Form

{

private IdleTimer idleTimer = null;

public BaseForm()

{

InitializeComponent();

}

protected int IdleTimeoutSeconds { get; set; }

protected virtual void StartIdleTimer()

{

if (idleTimer == null)

{

StopIdleTimer();

idleTimer = new IdleTimer(idleTimeoutSeconds: IdleTimeoutSeconds, sychronizingObject: this);

idleTimer.IsIdle += new IdleTimer.IsIdleEventHandler(IdleTimer_IsIdle);

}

idleTimer.Start();

}

protected virtual void StopIdleTimer()

{

if (idleTimer != null) idleTimer.Stop();

}

protected virtual void IdleTimer_IsIdle(object sender, IdleTimerEventArgs e)

{

//handle IsIdle event, close the form (override in inherting classes to do something different)

Close();

}

public new void Close()

{

StopIdleTimer();

base.Close();

}

public new void Hide()

{

StopIdleTimer();

base.Hide();

}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using NPT.Common;

namespace WinFormsIdleTimerTest

{

public partial class MainForm : BaseForm

{

public MainForm()

{

InitializeComponent();

IdleTimeoutSeconds = 5;

StartIdleTimer();

}

private void CancelButton_Click(object sender, EventArgs e)

{

Close();

}

protected override void IdleTimer_IsIdle(object sender, IdleTimerEventArgs e)

{

//an optional override of the BaseForm implementation to do something more than just closing the form

//MessageBoxEx is an implementation of MessageBox that dismisses itself after a specified number of seconds

var response = MessageBoxEx.Show(

"Is there anybody out there?",

string.Format("System Idle Detected ({0} seconds)", e.IdleSeconds),

MessageBoxButtons.YesNo,

MessageBoxIcon.Question,

MessageBoxDefaultButton.Button2,

10);

if (response == System.Windows.Forms.DialogResult.No)

{

MessageBoxEx.Show(

"\tGoodbye blue sky.",

string.Empty,

MessageBoxButtons.OK,

MessageBoxIcon.None,

MessageBoxDefaultButton.Button1,

3);

Close();

return;

}

StartIdleTimer();

}

originally posted 2013-9-10

1	originally posted 2013-9-10

...commit tran
END TRY
BEGIN CATCH
  IF @@trancount > 0 ROLLBACK TRANSACTION
  print @@error
  print 'oh crap.'
END CATCH

...commit tran

END TRY

BEGIN CATCH

IF @@trancount > 0 ROLLBACK TRANSACTION

print @@error

print 'oh crap.'

END CATCH

originally posted 2013-9-11

1	originally posted 2013-9-11

Using setvar in T-SQL

:setvar db "mydb"
alter database $(db)
set restricted_user with rollback after 60 seconds
alter database $(db)
set single_user with rollback immediate
alter database $(db) set multi_user
drop database $(db)

:setvar db "mydb"

alter database $(db)

set restricted_user with rollback after 60 seconds

alter database $(db)

set single_user with rollback immediate

alter database $(db) set multi_user

drop database $(db)

originally posted 2013-9-7

1	originally posted 2013-9-7

There are many more female names in the census data from which this dataset was generated, so statistically the resultset is likely to be lop-sided. If this matters, then extra steps should be taken to balance the M to F ratio.

“Random” SQL database backup (random.zip)

/*
 Random generation of person records inserted into a [GeneratedPeople] table when [dbo].[spGeneratePeople] is executed.
 The source [Address], [Domain], [First], and [Last] tables must first be populated with seed values.
*/


/*
 Seed with address data
 [Id] field must be a unique sequential integer
*/
CREATE TABLE [dbo].[Address](
 [Id] [int] NOT NULL,
 [City] [nvarchar](64) NOT NULL,
 [State] [nvarchar](2) NOT NULL,
 [Zip] [nvarchar](5) NOT NULL,
 [County] [nvarchar](255) NOT NULL,
 [AreaCode] [nvarchar](16) NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
 Seed with domain names, e.g. google.com
 [Id] field must be a unique sequential integer
*/
CREATE TABLE [dbo].[Domain](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
 Seed with person first names and gender
 [Id] field must be a unique sequential integer
 (a good source is the U.S. census)
*/
CREATE TABLE [dbo].[First](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](255) NOT NULL,
 [Gender] [nvarchar](1) NOT NULL,
 CONSTRAINT [PK_First] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*
 Seed with person surnames
 [Id] field must be a unique sequential integer
 (a good source is the U.S. census)
*/
CREATE TABLE [dbo].[Last](
 [Id] [int] NOT NULL,
 [Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Last] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/*
 This table is created and populated with person records when the [dbo].[spGeneratePeople] procedure is executed
*/
CREATE TABLE [dbo].[GeneratedPeople](
 [Guid] [uniqueidentifier] NULL,
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [nvarchar](100) NULL,
 [LastName] [nvarchar](100) NULL,
 [Gender] [nvarchar](2) NULL,
 [DateOfBirth] [date] NULL,
 [Email] [nvarchar](100) NULL,
 [Phone] [nvarchar](12) NULL,
 [City] [nvarchar](64) NULL,
 [State] [nvarchar](2) NULL,
 [Zip] [nvarchar](5) NULL,
 [County] [nvarchar](255) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GeneratedPeople] ADD  DEFAULT (newsequentialid()) FOR [Guid]
GO


/*
 Called by [dbo].[spGeneratePeople] to generate a birth date
*/
create procedure [dbo].[spGenerateDateOfBirth](@maxPastYears int = 100, @date date out)
as
begin
 set @date = null;
 if(@maxPastYears is null or @maxPastYears &amp;lt; 0) set @maxPastYears = 100
 declare @year int = 0;
 declare @value nvarchar(10) = null;

 while(isdate(@value)=0)
 begin
  while( @year &amp;lt;  year(getdate())-@maxPastYears or @year &amp;gt; year(getdate()))
   select @year = case when ceiling(rand(convert(varbinary,newid())) * 2) = 1 then '19' else '20' end +  right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(99))),(2))

  declare @month int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(12))),(2))
  declare @day int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(31))),(2))
  set @value = convert(nvarchar(4),@year) + '-' + convert(nvarchar(2),@month) + '-' + convert(nvarchar(2),@day) 
 end
 set @date = convert(date,@value);
 return;
end
GO

/*
 Called by [dbo].[spGeneratePeople] to create a subset of the records in the Address table
*/
create function [dbo].[tfGetAddresses](@state nvarchar(2) = null, @county nvarchar(255) = null,  @city nvarchar(255) = null)
returns @addrs table
(
[Id] int
,[City] nvarchar(64)
,[State] nvarchar(2)
,[Zip] nvarchar(5)
,[County] nvarchar(255)
,[AreaCode] nvarchar(3)
)
as
begin
 insert @addrs
 select [Id] = ROW_NUMBER() OVER( ORDER BY [Zip]),[City],[State],[Zip],[County],[AreaCode]
 from [Address] 
 where ((@state is not null and [State]= @state) or (@state is null))
 and ((@county is not null and [County]= @county) or (@county is null))
 and ((@city is not null and [City]= @city) or (@city is null))

 return;
end
GO


/*
 Generates people records into a [GeneratedPeople] table (which is dropped and re-created each time)
 @number - the number of records to generate
 @state - if not null, filters addresses to the two char state code
 @county - if not null, filters addresses to the county
 @city - if not null, filters addresses to the city
*/
create procedure [dbo].[spGeneratePeople](@number int, @state nvarchar(2), @county nvarchar(255),@city nvarchar(255))
as 
begin
 declare @count int = 0;
 if(@number is null or @number&amp;lt;=0) set @number = 1

 if exists (select name from sys.tables where name = 'GeneratedPeople') drop table [GeneratedPeople]
 create table [GeneratedPeople]
 (
  [Guid] uniqueidentifier default newsequentialid()
  ,[Id] int identity(1,1)
  ,[FirstName] nvarchar(100)
  ,[LastName] nvarchar(100)
  ,[Gender] nvarchar(2)
  ,[DateOfBirth] date
  ,[Email] nvarchar(100)
  ,[Phone] nvarchar(12)
  ,[City] nvarchar(64)
  ,[State] nvarchar(2)
  ,[Zip] nvarchar(5)
  ,[County] nvarchar(255)
 )

 declare @addrs table
 (
 [Id] int
 ,[City] nvarchar(64)
 ,[State] nvarchar(2)
 ,[Zip] nvarchar(5)
 ,[County] nvarchar(255)
 ,[AreaCode] nvarchar(3)
 )

 insert @addrs
 select * 
 from  tfGetAddresses(@state,@county,@city)

 declare @maxAddrs int = (select max([Id]) from @addrs)
 declare @maxFirsts int = (select max([Id]) from [First])
 declare @maxLasts int = (select max([Id]) from [Last])
 declare @maxDomains int = (select max([Id]) from [Domain])

 while(@count&amp;lt;@number)
 begin
  declare @date date = null
  exec dbo.[spGenerateDateOfBirth] default,@date out
  
  declare @rnd float =rand(convert(varbinary,newid()));

  insert [GeneratedPeople] ([FirstName],[LastName],[Gender],[DateOfBirth],[Email],[Phone],[City],[State],[Zip],[County])
  select 
  [FirstName] = [First].[Name]
  ,[LastName] = [Last].[Name]
  ,[Gender] = [First].[Gender]
  ,[DateOfBirth] = @date
  ,[Email] = lower([First].[Name] + '_' + [Last].[Name] + '@' + [Domain].[Name])
  ,[Phone] = ((((a.[AreaCode] +'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(999))),(3)))+'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(9999))),(4)))
  ,a.[City]
  ,a.[State]
  ,a.[Zip]
  ,a.[County]
  from [First],[Last],[Domain],@addrs a
  where [First].[Id] = (select ceiling(@rnd * @maxFirsts))
  and [Last].[Id] = (select ceiling(@rnd * @maxLasts))
  and [Domain].[Id] = (select ceiling(@rnd * @maxDomains))
  and a.[Id] = (select ceiling(@rnd * @maxAddrs))

  set @count=@count+1
 end
end
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

Random generation of person records inserted into a [GeneratedPeople] table when [dbo].[spGeneratePeople] is executed.

The source [Address], [Domain], [First], and [Last] tables must first be populated with seed values.

Seed with address data

[Id] field must be a unique sequential integer

CREATE TABLE [dbo].[Address](

[Id] [int] NOT NULL,

[City] [nvarchar](64) NOT NULL,

[State] [nvarchar](2) NOT NULL,

[Zip] [nvarchar](5) NOT NULL,

[County] [nvarchar](255) NOT NULL,

[AreaCode] [nvarchar](16) NOT NULL,

CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Seed with domain names, e.g. google.com

[Id] field must be a unique sequential integer

CREATE TABLE [dbo].[Domain](

[Id] [int] NOT NULL,

[Name] [nvarchar](100) NOT NULL,

CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Seed with person first names and gender

[Id] field must be a unique sequential integer

(a good source is the U.S. census)

CREATE TABLE [dbo].[First](

[Id] [int] NOT NULL,

[Name] [nvarchar](255) NOT NULL,

[Gender] [nvarchar](1) NOT NULL,

CONSTRAINT [PK_First] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Seed with person surnames

[Id] field must be a unique sequential integer

(a good source is the U.S. census)

CREATE TABLE [dbo].[Last](

[Id] [int] NOT NULL,

[Name] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_Last] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

This table is created and populated with person records when the [dbo].[spGeneratePeople] procedure is executed

CREATE TABLE [dbo].[GeneratedPeople](

[Guid] [uniqueidentifier] NULL,

[Id] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [nvarchar](100) NULL,

[LastName] [nvarchar](100) NULL,

[Gender] [nvarchar](2) NULL,

[DateOfBirth] [date] NULL,

[Email] [nvarchar](100) NULL,

[Phone] [nvarchar](12) NULL,

[City] [nvarchar](64) NULL,

[State] [nvarchar](2) NULL,

[Zip] [nvarchar](5) NULL,

[County] [nvarchar](255) NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[GeneratedPeople] ADD DEFAULT (newsequentialid()) FOR [Guid]

Called by [dbo].[spGeneratePeople] to generate a birth date

create procedure [dbo].[spGenerateDateOfBirth](@maxPastYears int = 100, @date date out)

begin

set @date = null;

if(@maxPastYears is null or @maxPastYears &lt; 0) set @maxPastYears = 100

declare @year int = 0;

declare @value nvarchar(10) = null;

while(isdate(@value)=0)

begin

while( @year &lt; year(getdate())-@maxPastYears or @year &gt; year(getdate()))

select @year = case when ceiling(rand(convert(varbinary,newid())) * 2) = 1 then '19' else '20' end + right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(99))),(2))

declare @month int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(12))),(2))

declare @day int = right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(31))),(2))

set @value = convert(nvarchar(4),@year) + '-' + convert(nvarchar(2),@month) + '-' + convert(nvarchar(2),@day)

end

set @date = convert(date,@value);

return;

end

Called by [dbo].[spGeneratePeople] to create a subset of the records in the Address table

create function [dbo].[tfGetAddresses](@state nvarchar(2) = null, @county nvarchar(255) = null, @city nvarchar(255) = null)

returns @addrs table

(

[Id] int

,[City] nvarchar(64)

,[State] nvarchar(2)

,[Zip] nvarchar(5)

,[County] nvarchar(255)

,[AreaCode] nvarchar(3)

)

begin

insert @addrs

select [Id] = ROW_NUMBER() OVER( ORDER BY [Zip]),[City],[State],[Zip],[County],[AreaCode]

from [Address]

where ((@state is not null and [State]= @state) or (@state is null))

and ((@county is not null and [County]= @county) or (@county is null))

and ((@city is not null and [City]= @city) or (@city is null))

return;

end

Generates people records into a [GeneratedPeople] table (which is dropped and re-created each time)

@number - the number of records to generate

@state - if not null, filters addresses to the two char state code

@county - if not null, filters addresses to the county

@city - if not null, filters addresses to the city

create procedure [dbo].[spGeneratePeople](@number int, @state nvarchar(2), @county nvarchar(255),@city nvarchar(255))

begin

declare @count int = 0;

if(@number is null or @number&lt;=0) set @number = 1

if exists (select name from sys.tables where name = 'GeneratedPeople') drop table [GeneratedPeople]

create table [GeneratedPeople]

(

[Guid] uniqueidentifier default newsequentialid()

,[Id] int identity(1,1)

,[FirstName] nvarchar(100)

,[LastName] nvarchar(100)

,[Gender] nvarchar(2)

,[DateOfBirth] date

,[Email] nvarchar(100)

,[Phone] nvarchar(12)

,[City] nvarchar(64)

,[State] nvarchar(2)

,[Zip] nvarchar(5)

,[County] nvarchar(255)

)

declare @addrs table

(

[Id] int

,[City] nvarchar(64)

,[State] nvarchar(2)

,[Zip] nvarchar(5)

,[County] nvarchar(255)

,[AreaCode] nvarchar(3)

)

insert @addrs

select *

from tfGetAddresses(@state,@county,@city)

declare @maxAddrs int = (select max([Id]) from @addrs)

declare @maxFirsts int = (select max([Id]) from [First])

declare @maxLasts int = (select max([Id]) from [Last])

declare @maxDomains int = (select max([Id]) from [Domain])

while(@count&lt;@number)

begin

declare @date date = null

exec dbo.[spGenerateDateOfBirth] default,@date out

declare @rnd float =rand(convert(varbinary,newid()));

insert [GeneratedPeople] ([FirstName],[LastName],[Gender],[DateOfBirth],[Email],[Phone],[City],[State],[Zip],[County])

select

[FirstName] = [First].[Name]

,[LastName] = [Last].[Name]

,[Gender] = [First].[Gender]

,[DateOfBirth] = @date

,[Email] = lower([First].[Name] + '_' + [Last].[Name] + '@' + [Domain].[Name])

,[Phone] = ((((a.[AreaCode] +'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(999))),(3)))+'-')+right('0'+rtrim(ceiling(rand(CONVERT([varbinary],newid(),0))*(9999))),(4)))

,a.[City]

,a.[State]

,a.[Zip]

,a.[County]

from [First],[Last],[Domain],@addrs a

where [First].[Id] = (select ceiling(@rnd * @maxFirsts))

and [Last].[Id] = (select ceiling(@rnd * @maxLasts))

and [Domain].[Id] = (select ceiling(@rnd * @maxDomains))

and a.[Id] = (select ceiling(@rnd * @maxAddrs))

set @count=@count+1

end

originally posted 2014-03-24

1	originally posted 2014-03-24

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[System]') AND type in (N'U'))
DROP TABLE [dbo].[System]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fIsLocked]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fIsLocked]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGetLockProgress]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fGetLockProgress]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGenerateLockName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fGenerateLockName]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spTest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spTest]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetProgress]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spSetProgress]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetLock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spSetLock]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spLongRunningQuery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spLongRunningQuery]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIsLockedWait]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spIsLockedWait]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spClearLock2]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spClearLock]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCancel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spCancel]
GO

CREATE TABLE [dbo].[System](
  [Id] [uniqueidentifier] NOT NULL default newsequentialid(),
  [Lock] [nvarchar](100) NULL,
  [LockDateTime] [datetime] NULL,
  [Progress] [int] NULL,
  [Cancel] [bit] NOT NULL default 0,
  [LockWaitTimeoutSeconds] [int] NOT NULL default 10,
  [LockExpireMinutes] [int] NOT NULL default 5,
 CONSTRAINT [PK_System] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

TRUNCATE TABLE [dbo].[System]
GO

INSERT [dbo].[System] ([Progress]) values (null)
GO

create procedure [dbo].[spCancel]
as
begin
  if([dbo].[fIsLocked]() = 1) update [dbo].[System] set [Cancel] = 1
end
GO

create procedure [dbo].[spClearLock](@lockname nvarchar(100) = null) as
begin
  update [dbo].[System] 
  set [Lock] = null
  where [Lock] like @lockname
end
GO

create procedure [dbo].[spClearLock2] as
begin
  update [dbo].[System] set [Lock] = null
end
GO

create procedure [dbo].[spIsLockedWait] (@waitForSeconds int)
as
begin
  --if @waitForSeconds &amp;gt; 0 then wait for @waitForSeconds for the lock to clear
  --if @waitForSeconds = 0 then don't wait for the lock to clear
  --if @waitForSeconds is null then use system setting
  --if @waitForSeconds &amp;lt; 0 then wait until the is cleared.
  --returns 1 if locked, else 0
  if (@waitForSeconds is null) select top 1 @waitForSeconds = [LockWaitTimeoutSeconds] from [dbo].[System] with (nolock)
  
  declare @dt datetime = getdate()
  declare @timedout bit = 0

  while([dbo].[fIsLocked]()=1)
  begin
    if(@waitForSeconds=0 or (@waitForSeconds &amp;gt; 0 and datediff(s,@dt,getdate())&amp;gt;@waitForSeconds))
    begin
      set @timedout = 1
      break;
    end
    waitfor delay '0:00:01';
  end

  if(@timedout=1) return [dbo].[fIsLocked]();
  return 0;
end
GO

create procedure [dbo].[spSetLock](@lockname nvarchar(100), @waitSeconds int) as
begin
  --if @waitForSeconds &amp;gt; 0 then wait for @waitForSeconds for the lock to clear
  --if @waitForSeconds = 0 or null then don't wait for the lock to clear
  --if @waitForSeconds is null then use System.[LockTimeoutMinutes] value
  --if @waitForSeconds &amp;lt; 0 then wait until the is cleared.
  
  if ([dbo].[fIsLocked]()=0)
  begin
    --set lock
    update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();
    return 0;
  end
  else
  begin
    --lock already set
    declare @lockExpireMinutes int
    declare @LockDateTime datetime

    select top 1 
    @lockExpireMinutes = [LockExpireMinutes] 
    ,@LockDateTime = [LockDateTime]
    from [dbo].[System] with (nolock)

    if((select datediff(n, @LockDateTime, getdate())) &amp;lt; @lockExpireMinutes)
    begin
      --not within the automatic clear time of @lockExpireMinutes

      --wait for lock for @waitSeconds
      declare @islocked bit = null;
      exec @islocked = [dbo].[spIsLockedWait] @waitSeconds
      if(@islocked = 0)
      begin
        --the existing lock cleared in time, set a new lock
        update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();
      end
      else
      begin
        declare @msg nvarchar(max) = 'The system is currently running a locking process ''' + (select top 1 [Lock] from [dbo].[System]) + ''' that has locked the requested resources. You may try again once the running process completes.'
        raiserror(@msg,11,100);
        return -1;
      end
    end
    else
    begin
      --automatically clear the existing lock after @lockTimeoutMinutes and set a new lock
      update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();
      return 0;
    end
  end
end
GO

create procedure [dbo].[spSetProgress](@percent int) as
begin
  declare @isLocked bit = [dbo].[fIsLocked]();
  if(@percent &amp;lt;= 0) set @percent = 1;
  if(@percent is null or @percent &amp;gt; 100 or @isLocked = 0) set @percent = 100;

  update [dbo].[System] set [Progress] = @percent

  if(@isLocked = 1) 
  begin
    declare @cancel bit = 0
    select top 1 @cancel = [Cancel] from [dbo].[System] with (nolock)
    if(@cancel = 1) 
    begin
      update [dbo].[System] set [Cancel] = 0 --clear cancel flag 
      raiserror(' cancel requested.',11,100);
      return -1;
    end
  end
end
GO

create function  [dbo].[fGenerateLockName](@seedname nvarchar(50) = null)
returns nvarchar(100)
as
begin
  declare @name nvarchar(100) = convert(nvarchar(25),datediff(s,'1970-1-1',sysutcdatetime()));
  if(@seedname is not null) set @name = @seedname + '_' + @name;
  return @name;
end
GO

create function [dbo].[fGetLockProgress]() returns nvarchar(100)
as
begin
  declare @progress int = 100;
  if([dbo].[fIsLocked]()=1)
  begin
    select @progress = [Progress]
    from [dbo].[System] with (nolock)
  end
  return @progress;
end
GO

create function [dbo].[fIsLocked]() returns bit 
as
begin
  if exists(select top 1 [Lock] 
  from [dbo].[System] with (nolock) 
  where [Lock] is not null and len(ltrim(rtrim([Lock])))&amp;gt;0) return 1;
  return 0;
end
GO

create procedure [dbo].[spLongRunningQuery]
as
begin
  declare @errored bit = 0;
  declare @msg nvarchar(4000)
  declare @intrans bit = 0;
  declare @lock_result int = null;
  declare @lock nvarchar(100) = [dbo].[fGenerateLockName]('spLongRunningQuery');
  declare @count int = 0;

  begin try
    --acquire lock
    exec @lock_result = [dbo].[spSetLock] @lock, null

    exec [dbo].[spSetProgress] 10

    begin transaction
    set @intrans = 1

    while(@count&amp;lt;100)
    begin
        exec [dbo].[spSetProgress] @count
      set @msg = convert(nvarchar(10),@count)
      raiserror (@msg, 0, 1) with nowait --print immediately
      waitfor delay '0:00:01'; --pause for a second
      set @count = @count + 10
    end

    commit transaction

  end try
  begin catch
    --handle error
    set @errored = 1;
    set @msg = 'Error : ' + isnull(error_message(),'');
    
    if (@intrans = 1) 
    begin
      rollback transaction
    end
  end catch

  --finalize
  if(@lock_result = 0) 
  begin
    --last progress update, ignore any error thrown
    begin try
      exec [dbo].[spSetProgress] 100
    end try
    begin catch
      dummy:
    end catch

    --release lock
    exec [dbo].[spClearLock] @lock
  end

  if(@errored=1) 
  begin
    raiserror(@msg,11,100);
    return -1
  end 
  return 0;
end
GO

create procedure [dbo].[spTest]
as
begin
  --TEST QUERY
  --STEP 1 : exec [spLongRunningQuery]
  --STEP 2 : Run this test query
  --NOTE: execute [spCancel] to cancel and rollback changes of [spLongRunningQuery]

  declare @msg nvarchar(300)
  declare @percent int = ''
  declare @lpercent int = ''

  while(1=1)
  begin
    if([dbo].[fIsLocked]()=1)
    begin
      set @percent = [dbo].[fGetLockProgress]() 
      set @msg = 'Progress : ' +  convert(nvarchar(100),@percent) + ' percent'
      if(@lpercent&amp;lt;&amp;gt;@percent) raiserror (@msg, 0, 1) with nowait --print immediately
      set @lpercent = @percent
    end
    else break;

    waitfor delay '0:00:00';
  end
  raiserror ('done.', 0, 1) with nowait --print immediately
end
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[System]') AND type in (N'U'))

DROP TABLE [dbo].[System]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fIsLocked]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fIsLocked]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGetLockProgress]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fGetLockProgress]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGenerateLockName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fGenerateLockName]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spTest]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spTest]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetProgress]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spSetProgress]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetLock]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spSetLock]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spLongRunningQuery]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spLongRunningQuery]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIsLockedWait]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spIsLockedWait]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock2]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spClearLock2]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spClearLock]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCancel]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spCancel]

CREATE TABLE [dbo].[System](

[Id] [uniqueidentifier] NOT NULL default newsequentialid(),

[Lock] [nvarchar](100) NULL,

[LockDateTime] [datetime] NULL,

[Progress] [int] NULL,

[Cancel] [bit] NOT NULL default 0,

[LockWaitTimeoutSeconds] [int] NOT NULL default 10,

[LockExpireMinutes] [int] NOT NULL default 5,

CONSTRAINT [PK_System] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

TRUNCATE TABLE [dbo].[System]

INSERT [dbo].[System] ([Progress]) values (null)

create procedure [dbo].[spCancel]

begin

if([dbo].[fIsLocked]() = 1) update [dbo].[System] set [Cancel] = 1

end

create procedure [dbo].[spClearLock](@lockname nvarchar(100) = null) as

begin

update [dbo].[System]

set [Lock] = null

where [Lock] like @lockname

end

create procedure [dbo].[spClearLock2] as

begin

update [dbo].[System] set [Lock] = null

end

create procedure [dbo].[spIsLockedWait] (@waitForSeconds int)

begin

--if @waitForSeconds &gt; 0 then wait for @waitForSeconds for the lock to clear

--if @waitForSeconds = 0 then don't wait for the lock to clear

--if @waitForSeconds is null then use system setting

--if @waitForSeconds &lt; 0 then wait until the is cleared.

--returns 1 if locked, else 0

if (@waitForSeconds is null) select top 1 @waitForSeconds = [LockWaitTimeoutSeconds] from [dbo].[System] with (nolock)

declare @dt datetime = getdate()

declare @timedout bit = 0

while([dbo].[fIsLocked]()=1)

begin

if(@waitForSeconds=0 or (@waitForSeconds &gt; 0 and datediff(s,@dt,getdate())&gt;@waitForSeconds))

begin

set @timedout = 1

break;

end

waitfor delay '0:00:01';

end

if(@timedout=1) return [dbo].[fIsLocked]();

return 0;

end

create procedure [dbo].[spSetLock](@lockname nvarchar(100), @waitSeconds int) as

begin

--if @waitForSeconds &gt; 0 then wait for @waitForSeconds for the lock to clear

--if @waitForSeconds = 0 or null then don't wait for the lock to clear

--if @waitForSeconds is null then use System.[LockTimeoutMinutes] value

--if @waitForSeconds &lt; 0 then wait until the is cleared.

if ([dbo].[fIsLocked]()=0)

begin

--set lock

update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();

return 0;

end

else

begin

--lock already set

declare @lockExpireMinutes int

declare @LockDateTime datetime

select top 1

@lockExpireMinutes = [LockExpireMinutes]

,@LockDateTime = [LockDateTime]

from [dbo].[System] with (nolock)

if((select datediff(n, @LockDateTime, getdate())) &lt; @lockExpireMinutes)

begin

--not within the automatic clear time of @lockExpireMinutes

--wait for lock for @waitSeconds

declare @islocked bit = null;

exec @islocked = [dbo].[spIsLockedWait] @waitSeconds

if(@islocked = 0)

begin

--the existing lock cleared in time, set a new lock

update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();

end

else

begin

declare @msg nvarchar(max) = 'The system is currently running a locking process ''' + (select top 1 [Lock] from [dbo].[System]) + ''' that has locked the requested resources. You may try again once the running process completes.'

raiserror(@msg,11,100);

return -1;

end

else

begin

--automatically clear the existing lock after @lockTimeoutMinutes and set a new lock

update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();

return 0;

end

create procedure [dbo].[spSetProgress](@percent int) as

begin

declare @isLocked bit = [dbo].[fIsLocked]();

if(@percent &lt;= 0) set @percent = 1;

if(@percent is null or @percent &gt; 100 or @isLocked = 0) set @percent = 100;

update [dbo].[System] set [Progress] = @percent

if(@isLocked = 1)

begin

declare @cancel bit = 0

select top 1 @cancel = [Cancel] from [dbo].[System] with (nolock)

if(@cancel = 1)

begin

update [dbo].[System] set [Cancel] = 0 --clear cancel flag

raiserror(' cancel requested.',11,100);

return -1;

end

create function [dbo].[fGenerateLockName](@seedname nvarchar(50) = null)

returns nvarchar(100)

begin

declare @name nvarchar(100) = convert(nvarchar(25),datediff(s,'1970-1-1',sysutcdatetime()));

if(@seedname is not null) set @name = @seedname + '_' + @name;

return @name;

end

create function [dbo].[fGetLockProgress]() returns nvarchar(100)

begin

declare @progress int = 100;

if([dbo].[fIsLocked]()=1)

begin

select @progress = [Progress]

from [dbo].[System] with (nolock)

end

return @progress;

end

create function [dbo].[fIsLocked]() returns bit

begin

if exists(select top 1 [Lock]

from [dbo].[System] with (nolock)

where [Lock] is not null and len(ltrim(rtrim([Lock])))&gt;0) return 1;

return 0;

end

create procedure [dbo].[spLongRunningQuery]

begin

declare @errored bit = 0;

declare @msg nvarchar(4000)

declare @intrans bit = 0;

declare @lock_result int = null;

declare @lock nvarchar(100) = [dbo].[fGenerateLockName]('spLongRunningQuery');

declare @count int = 0;

begin try

--acquire lock

exec @lock_result = [dbo].[spSetLock] @lock, null

exec [dbo].[spSetProgress] 10

begin transaction

set @intrans = 1

while(@count&lt;100)

begin

exec [dbo].[spSetProgress] @count

set @msg = convert(nvarchar(10),@count)

raiserror (@msg, 0, 1) with nowait --print immediately

waitfor delay '0:00:01'; --pause for a second

set @count = @count + 10

end

commit transaction

end try

begin catch

--handle error

set @errored = 1;

set @msg = 'Error : ' + isnull(error_message(),'');

if (@intrans = 1)

begin

rollback transaction

end

end catch

--finalize

if(@lock_result = 0)

begin

--last progress update, ignore any error thrown

begin try

exec [dbo].[spSetProgress] 100

end try

begin catch

dummy:

end catch

--release lock

exec [dbo].[spClearLock] @lock

end

if(@errored=1)

begin

raiserror(@msg,11,100);

return -1

end

return 0;

end

create procedure [dbo].[spTest]

begin

--TEST QUERY

--STEP 1 : exec [spLongRunningQuery]

--STEP 2 : Run this test query

--NOTE: execute [spCancel] to cancel and rollback changes of [spLongRunningQuery]

declare @msg nvarchar(300)

declare @percent int = ''

declare @lpercent int = ''

while(1=1)

begin

if([dbo].[fIsLocked]()=1)

begin

set @percent = [dbo].[fGetLockProgress]()

set @msg = 'Progress : ' + convert(nvarchar(100),@percent) + ' percent'

if(@lpercent&lt;&gt;@percent) raiserror (@msg, 0, 1) with nowait --print immediately

set @lpercent = @percent

end

else break;

waitfor delay '0:00:00';

end

raiserror ('done.', 0, 1) with nowait --print immediately

end

originally posted 2014-04-13

1	originally posted 2014-04-13

I have a query for which the keys must appear distinctly, but they are duplicated in the resultset. I want to keep only one of the duplicated rows. That is, if I find a record with a duplicated key, I want to keep only one of the records and all of its other data. I don’t care which of the rows get dumped. Admittedly, this is a strange and rare situation (all the more reason to document it).

if (object_id('tempdb..#temp') is not null) drop table #temp
create table #temp ([Number] int,[State] nvarchar(100) default 'OK')
insert #temp ([Number]) values (1),(2),(2),(2),(3)

select [Comment] = 'The idea is to remove all but one of the rows where the value for the [Number] column appears more than once.'

select [Comment] = 'original table',* from #temp

if (object_id('tempdb..#temp2') is not null) drop table #temp2
select 
*
,[DuplicateRowNumber] = row_number() over (partition by [Number] order by [Number]) 
into #temp2
from #temp

select [Comment] = 'copy table with the dupe finder column',* from #temp2

update #temp2
set [State] = 'DUPLICATED!'
from
(
  select distinct 
  [Number]
  from #temp2
  where [DuplicateRowNumber] &amp;gt; 1
) as dupes
where #temp2.[Number] = dupes.[Number]

select [Comment] = 'copy table with [State] field marked with a comment for the duplicated rows (optional)',* from #temp2

delete from #temp2 where [DuplicateRowNumber] &amp;gt; 1

select [Comment] = 'copy table all but one of the duplicated rows removed',* from #temp2

if (object_id('tempdb..#temp') is not null) drop table #temp

create table #temp ([Number] int,[State] nvarchar(100) default 'OK')

insert #temp ([Number]) values (1),(2),(2),(2),(3)

select [Comment] = 'The idea is to remove all but one of the rows where the value for the [Number] column appears more than once.'

select [Comment] = 'original table',* from #temp

if (object_id('tempdb..#temp2') is not null) drop table #temp2

select

,[DuplicateRowNumber] = row_number() over (partition by [Number] order by [Number])

into #temp2

from #temp

select [Comment] = 'copy table with the dupe finder column',* from #temp2

update #temp2

set [State] = 'DUPLICATED!'

from

(

select distinct

[Number]

from #temp2

where [DuplicateRowNumber] &gt; 1

) as dupes

where #temp2.[Number] = dupes.[Number]

select [Comment] = 'copy table with [State] field marked with a comment for the duplicated rows (optional)',* from #temp2

delete from #temp2 where [DuplicateRowNumber] &gt; 1

select [Comment] = 'copy table all but one of the duplicated rows removed',* from #temp2

originally posted 2014-04-22

1	originally posted 2014-04-22

--delete all records that contain a specified value for a given key field...
--... for all tables that have the key field and that conform to a certain name pattern

--concatenate a list of IDs into a delimited string for use in an IN statement
declare @ids nvarchar(max)
declare @sql nvarchar(max)

;with cteIds as
(
  --source table for the key values for the deletion
  select [DistributionCodeId] 
  from [SIP_NPT_Posted]
)

select
@ids = stuff(
(
    select ',''' + cte.[DistributionCodeId] + ''''
    from cteIds cte
    order by cte.[DistributionCodeId]
    for xml path('')
),1,1,'')

--the concatenated IDs 
select @ids

--create a delete script for each table that conforms to the name pattern using the concatenated IDs for the IN statement of the WHERE clause
;with cteTables as
(
  select [sql] = 'delete from [' + t.[name] + '] where [DistributionCodeId] in (' + isnull(@ids,'''''') + ')'
  from sys.tables t
  inner join sys.columns c on t.object_id = c.object_id
  where t.[type] = 'U'
  and t.[name] like 'SIP_NPT_POST%' -- table name pattern
  and c.[name] = 'DistributionCodeId' --column name 
  and t.name &amp;lt;&amp;gt; 'SIP_NPT_Posted' --exclude the [SIP_NPT_Posted] table b/c deletion for it is handled elsewhere.
)

--concatenate the delete scripts into a single string of SQL
select
@sql = stuff(
(
    select ';' + cte.[sql]
    from cteTables cte
    order by cte.[sql]
    for xml path('')
),1,1,'')

select [@sql] = @sql

--execute the SQL
--exec(@sql); --UNCOMMENT ME TO DELETE!

/*

[OUTPUT]

@ids contains this...

'100009-010','100222-010'


@sql contains this...

delete from [SIP_NPT_POST_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnDocuments] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnSessions] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnTransactions] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_FedTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_FedTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobPriorExpenses] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobRemainingPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Payees] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PaySummary] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PostPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PostTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Projects] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_StateTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_StateTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_WorkersCompensation] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_PostedPayee] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_PostedTran] where [DistributionCodeId] in ('100009-010','100222-010')

*/

--delete all records that contain a specified value for a given key field...

--... for all tables that have the key field and that conform to a certain name pattern

--concatenate a list of IDs into a delimited string for use in an IN statement

declare @ids nvarchar(max)

declare @sql nvarchar(max)

;with cteIds as

(

--source table for the key values for the deletion

select [DistributionCodeId]

from [SIP_NPT_Posted]

)

select

@ids = stuff(

(

select ',''' + cte.[DistributionCodeId] + ''''

from cteIds cte

order by cte.[DistributionCodeId]

for xml path('')

),1,1,'')

--the concatenated IDs

select @ids

--create a delete script for each table that conforms to the name pattern using the concatenated IDs for the IN statement of the WHERE clause

;with cteTables as

(

select [sql] = 'delete from [' + t.[name] + '] where [DistributionCodeId] in (' + isnull(@ids,'''''') + ')'

from sys.tables t

inner join sys.columns c on t.object_id = c.object_id

where t.[type] = 'U'

and t.[name] like 'SIP_NPT_POST%' -- table name pattern

and c.[name] = 'DistributionCodeId' --column name

and t.name &lt;&gt; 'SIP_NPT_Posted' --exclude the [SIP_NPT_Posted] table b/c deletion for it is handled elsewhere.

)

--concatenate the delete scripts into a single string of SQL

select

@sql = stuff(

(

select ';' + cte.[sql]

from cteTables cte

order by cte.[sql]

for xml path('')

),1,1,'')

select [@sql] = @sql

--execute the SQL

--exec(@sql); --UNCOMMENT ME TO DELETE!

[OUTPUT]

@ids contains this...

'100009-010','100222-010'

@sql contains this...

delete from [SIP_NPT_POST_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnDocuments] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnSessions] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnTransactions] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_FedTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_FedTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobPriorExpenses] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobRemainingPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Payees] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PaySummary] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PostPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PostTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Projects] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_StateTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_StateTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_WorkersCompensation] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_PostedPayee] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_PostedTran] where [DistributionCodeId] in ('100009-010','100222-010')

<jco-blog/>

SQL and Uniqueidentifiers as Keys

Drop database objects and dependencies using object full name

Unix Timestamp in SQL Server

Windows Forms Inactivity Timeout Class

The Best SQL Script Ending

Close All Connections and Drop Database

Random generation of person records for demo and testing purposes

Simple Application-Level Locking and Progress Reporting for Long Running Queries

Deleting all but one duplicated row

Dynamic deletion of records from qualified tables