/*
 RUN THIS SCRIPT FROM THE *TARGET* DATABASE
 Copies table data from the @sourceDb database to the target database.
 Only tables that *do not* have segment code columns are copied.
 Optionally deletes the target table data if @deleteTargetData is set to 1
 */

 use [MyTargetDatabaseHere];
 declare @sourceDb nvarchar(255) = 'MySourceDatabaseHere';
 declare @deleteTargetData bit = 1;
 declare @message nvarchar(max) = null;

 begin try
         --disable triggers and constraints for target db
         exec sp_MSforeachtable 'alter table ? disable trigger all;
         alter table ? nocheck constraint all;'

         --create results temp table
         if (object_id('tempdb..#Results') is not null) drop table #Results
         create table #Results ([Name] nvarchar(1000), [Success] bit, [Error] nvarchar(max));

         --create #Table that contains the schema, table and column names of all tables in the database
         if (object_id('tempdb..#Tables') is not null) drop table #Tables

         select 
         [Table]
         ,[Schema]
         ,[Columns] = left([Columns],len(columns)-1) 
         ,[HasCodes] = convert(bit,case when charindex('sCodeID',[Columns], 1) > 0 then 1 else 0 end)
         ,[HasIdentity] = [Identity].[HasIdentity]
         into #Tables
         from
         (
                 select top 10000 
                 [Table] = t.name
                 ,[TableObjectId] = t.object_id
                 ,[Schema] = s.[Name]
                 ,[Columns] = substring(
                 (
                 select
                 '[' + c.name + '], '
                 from sys.columns c 
                 where t.object_id = c.object_id
                 order by c.[Name]
                 for xml path('')
                 )
                 ,1,8000)
                 from sys.tables t
                 join sys.schemas s on t.[schema_id]= s.[schema_id]
                 where t.type = 'U'
                 order by t.name
         ) as f1
         outer apply(select top 1 [HasIdentity] = [is_identity] from sys.columns where object_id = f1.[TableObjectId]) as [Identity]
         where f1.[Table] not like 'sys%'
         group by [Table],[Schema],[Columns],[HasIdentity]
         order by [Schema],[Table]

         
         --create #NoCodeTables from #Table that contains only those tables that do not contain segment code columns
         if (object_id('tempdb..#NoCodeTables') is not null) drop table #NoCodeTables

         select *
         into #NoCodeTables
         from #Tables
         where [HasCodes] = 0

         select * from #NoCodeTables

         declare @table nvarchar(255)
         declare @schema nvarchar(255)
         declare @schemaAndTable nvarchar(1000)
         declare @columns nvarchar(max)
         declare @hasIdentity bit
         declare @sql nvarchar(max)

         --iterate the #NoCodeTables rows and do the copying
         while(1=1)
         begin
                 set @table = null;
                 set @sql = null;
                 set @message = null;

                 --get a row to process from #NoCodeTables
                 select top 1
                 @table = [Table]
                 ,@schema = [Schema]
                 ,@columns = [Columns]
                 ,@hasIdentity = [HasIdentity]
                 ,@schemaAndTable = '[' + @schema + '].[' + @table + ']' --combine schema and table names here for convenience
                 from #NoCodeTables
                 order by [Table],[Schema]

                 if(@table is not null)
                 begin
                         begin try
                                 --optionally delete the target table data
                                 if(@deleteTargetData = 1)
                                 begin
                                         --if a truncate doesn't work then fall back to a delete
                                         set @message = @schemaAndTable + ' emptying...'
                                         raiserror (@message, 0, 1) with nowait --print immediately

                                         set @sql = 'begin try
                                                 truncate table ' + @schemaAndTable + ';
                                         end try
                                         begin catch
                                                 delete ' + @schemaAndTable + ';
                                         end catch';

                                         exec (@sql);
                                 end

                                 --copy from source to target table
                                 set @message = @schemaAndTable + ' copying...'
                                 raiserror (@message, 0, 1) with nowait --print immediately

                                 --some tables have identity columns, if so, set to allow copying first
                                 set @sql = 
                                         case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] ON;' else '' end +
                                         'insert ' + @schemaAndTable + ' (' + @columns + ') select ' + @columns + ' from [' + @sourceDb + '].' + @schemaAndTable + ';' + 
                                         case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] OFF;' else '' end
                         
                                 --print @sql
                                 exec(@sql);

                                 --success! set messages
                                 set @message = @schemaAndTable + ' done.'
                                 insert #Results ([Name],[Success]) values (@schemaAndTable, 1) --insert
                                 raiserror (@message, 0, 1) with nowait --print immediately
                         end try
                         begin catch
                                 --fail. set message
                                 set @message = @schemaAndTable + ' copy FAILED.' + error_message()
                                 insert #Results ([Name],[Success],[Error]) values (@schemaAndTable, 0, @message)
                                 raiserror (@message, 0, 1) with nowait --print immediately
                         end catch

                         --delete the completed row from #NoCodeTables in preparation to move on to the next one
                         delete from #NoCodeTables where [Table] = @table        
                 end
                 else break; --all #NoCodeTables rows have been processed, break out of the loop
         end

         --display results
         select * 
         from #Results
         order by [Name]

 end try
 begin catch
         --error of some kind, print it and re-enable triggers and constraints (which may fail if a partial copy situation)
         set @message = error_message()
         raiserror (@message, 0, 1) with nowait --print immediately

         --enable triggers and constraints for target db
         exec sp_MSforeachtable 'alter table ? enable trigger all;
         alter table ? check constraint all;'

 end catch

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

RUN THIS SCRIPT FROM THE *TARGET* DATABASE

Copies table data from the @sourceDb database to the target database.

Only tables that *do not* have segment code columns are copied.

Optionally deletes the target table data if @deleteTargetData is set to 1

use [MyTargetDatabaseHere];

declare @sourceDb nvarchar(255) = 'MySourceDatabaseHere';

declare @deleteTargetData bit = 1;

declare @message nvarchar(max) = null;

begin try

--disable triggers and constraints for target db

exec sp_MSforeachtable 'alter table ? disable trigger all;

alter table ? nocheck constraint all;'

--create results temp table

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

create table #Results ([Name] nvarchar(1000), [Success] bit, [Error] nvarchar(max));

--create #Table that contains the schema, table and column names of all tables in the database

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

select

[Table]

,[Schema]

,[Columns] = left([Columns],len(columns)-1)

,[HasCodes] = convert(bit,case when charindex('sCodeID',[Columns], 1) > 0 then 1 else 0 end)

,[HasIdentity] = [Identity].[HasIdentity]

into #Tables

from

(

select top 10000

[Table] = t.name

,[TableObjectId] = t.object_id

,[Schema] = s.[Name]

,[Columns] = substring(

(

select

'[' + c.name + '], '

from sys.columns c

where t.object_id = c.object_id

order by c.[Name]

for xml path('')

)

,1,8000)

from sys.tables t

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

where t.type = 'U'

order by t.name

) as f1

outer apply(select top 1 [HasIdentity] = [is_identity] from sys.columns where object_id = f1.[TableObjectId]) as [Identity]

where f1.[Table] not like 'sys%'

group by [Table],[Schema],[Columns],[HasIdentity]

order by [Schema],[Table]

--create #NoCodeTables from #Table that contains only those tables that do not contain segment code columns

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

select *

into #NoCodeTables

from #Tables

where [HasCodes] = 0

select * from #NoCodeTables

declare @table nvarchar(255)

declare @schema nvarchar(255)

declare @schemaAndTable nvarchar(1000)

declare @columns nvarchar(max)

declare @hasIdentity bit

declare @sql nvarchar(max)

--iterate the #NoCodeTables rows and do the copying

while(1=1)

begin

set @table = null;

set @sql = null;

set @message = null;

--get a row to process from #NoCodeTables

select top 1

@table = [Table]

,@schema = [Schema]

,@columns = [Columns]

,@hasIdentity = [HasIdentity]

,@schemaAndTable = '[' + @schema + '].[' + @table + ']' --combine schema and table names here for convenience

from #NoCodeTables

order by [Table],[Schema]

if(@table is not null)

begin

begin try

--optionally delete the target table data

if(@deleteTargetData = 1)

begin

--if a truncate doesn't work then fall back to a delete

set @message = @schemaAndTable + ' emptying...'

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

set @sql = 'begin try

truncate table ' + @schemaAndTable + ';

end try

begin catch

delete ' + @schemaAndTable + ';

end catch';

exec (@sql);

end

--copy from source to target table

set @message = @schemaAndTable + ' copying...'

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

--some tables have identity columns, if so, set to allow copying first

set @sql =

case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] ON;' else '' end +

'insert ' + @schemaAndTable + ' (' + @columns + ') select ' + @columns + ' from [' + @sourceDb + '].' + @schemaAndTable + ';' +

case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] OFF;' else '' end

--print @sql

exec(@sql);

--success! set messages

set @message = @schemaAndTable + ' done.'

insert #Results ([Name],[Success]) values (@schemaAndTable, 1) --insert

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

end try

begin catch

--fail. set message

set @message = @schemaAndTable + ' copy FAILED.' + error_message()

insert #Results ([Name],[Success],[Error]) values (@schemaAndTable, 0, @message)

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

end catch

--delete the completed row from #NoCodeTables in preparation to move on to the next one

delete from #NoCodeTables where [Table] = @table

end

else break; --all #NoCodeTables rows have been processed, break out of the loop

end

--display results

select *

from #Results

order by [Name]

end try

begin catch

--error of some kind, print it and re-enable triggers and constraints (which may fail if a partial copy situation)

set @message = error_message()

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

--enable triggers and constraints for target db

exec sp_MSforeachtable 'alter table ? enable trigger all;

alter table ? check constraint all;'

end catch

When we make manual backups we typically do so as insurance before applying updates, or to get a copy of a database for testing. We aren’t making backups to contribute to the backup regime, as that is the purvey of the automated backup jobs. The recommendation is to make out-of-band backups as described in the PDF document. An out-of-band backup can be deleted without causing problems and should be deleted when no longer needed.

We don’t (want to) know the particulars of our self-hosted customers’ backup regime, so it’s best that we not affect it, so out-of-band is the way to go.

Creating-an-out-of-band-SQL-database-backup.pdf

Note: The [Order] field is not unique in the database. The [Namespace] field is for multi-tenancy support.

Using setvar in T-SQL

:setvar NAMESPACE "demo"

--find records with duplicated [Order] values
SELECT 
[Duplicated Order Value] = count(*)
,[Namespace]
--,[Group_Id]
,[Order]
FROM [peeps].[FieldRegistration] --[FieldCaseManagement]
where [Namespace] = '$(NAMESPACE)'
group by       
[Namespace]
--,[Group_Id]
,[Order]
having count(*) > 1
order by 
--[Group_Id], 
[Order]

--update [Order] values
update peeps.[FieldRegistration] set [Order] = x2.[Row]
from
(
	select [Namespace],[Name],[Field_Id],[Row]
	from 
	(
		select
		f.[Name]
		,fr.[Field_Id]
		,fr.[Namespace]
		,fr.[Order]
		,[Row] = (row_number() over (partition by fr.[Namespace] order by fr.[Order],f.[Name]) + 8) -- the +8 is because there are nine system fields stored elsewhere (only for [FieldRegistration] table).
		from [peeps].[FieldRegistration] fr
		join [peeps].[Field] f on f.[Id] = fr.[Field_Id]
	) as x1
	where [Row] <> [Order]
) as x2
where peeps.[FieldRegistration].[Namespace] = x2.[Namespace] 
and peeps.[FieldRegistration].[Field_Id] = x2.[Field_Id]
and peeps.[FieldRegistration].[Namespace] = '$(NAMESPACE)'

--display results
select * from peeps.[FieldRegistration]
where [Namespace] = '$(NAMESPACE)'
order by [Namespace],[Order]

:setvar NAMESPACE "demo"

--find records with duplicated [Order] values

SELECT

[Duplicated Order Value] = count(*)

,[Namespace]

--,[Group_Id]

,[Order]

FROM [peeps].[FieldRegistration] --[FieldCaseManagement]

where [Namespace] = '$(NAMESPACE)'

group by

[Namespace]

--,[Group_Id]

,[Order]

having count(*) > 1

order by

--[Group_Id],

[Order]

--update [Order] values

update peeps.[FieldRegistration] set [Order] = x2.[Row]

from

(

select [Namespace],[Name],[Field_Id],[Row]

from

(

select

f.[Name]

,fr.[Field_Id]

,fr.[Namespace]

,fr.[Order]

,[Row] = (row_number() over (partition by fr.[Namespace] order by fr.[Order],f.[Name]) + 8) -- the +8 is because there are nine system fields stored elsewhere (only for [FieldRegistration] table).

from [peeps].[FieldRegistration] fr

join [peeps].[Field] f on f.[Id] = fr.[Field_Id]

) as x1

where [Row] <> [Order]

) as x2

where peeps.[FieldRegistration].[Namespace] = x2.[Namespace]

and peeps.[FieldRegistration].[Field_Id] = x2.[Field_Id]

and peeps.[FieldRegistration].[Namespace] = '$(NAMESPACE)'

--display results

select * from peeps.[FieldRegistration]

where [Namespace] = '$(NAMESPACE)'

order by [Namespace],[Order]

When a VM is created from a template machine that has SQL Server installed, that SQL instance must be told that the machine name has changed.

If a “foo” machine is created from a template named “template”, the T-SQL statement run against the default SQL instance select @@SERVERNAME returns “template” instead of the desired “foo”.

The solution is to execute the following T-SQL and then restart the SQL Server process.

select [@@SERVERNAME] = @@SERVERNAME
go
sp_helpserver --show config before
go
exec sp_dropserver 'template'
go
sp_addserver 'foo', local
go
sp_helpserver --show config after
go

------------------------------------
--RESTART SQL INSTANCE AT THIS POINT
------------------------------------

select [@@SERVERNAME] = @@SERVERNAME
go

select [@@SERVERNAME] = @@SERVERNAME

sp_helpserver --show config before

exec sp_dropserver 'template'

sp_addserver 'foo', local

sp_helpserver --show config after

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

--RESTART SQL INSTANCE AT THIS POINT

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

select [@@SERVERNAME] = @@SERVERNAME

Here’s a C# version of the rename and the SQL Server process restart using PowerShell and the SqlDataClient.

/*
imports
using System.Management;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
using System.Data.SqlClient;
*/

//rename the default SQL server instance 
private void RenameSqlServer(string machineName, string uid, string pwd)
{
    const string sql = @"if('{0}' <> @@servername)
    begin
      declare @sql nvarchar(max);
      begin try
        set @sql = 'sp_dropserver ''' + @@servername + ''''
        exec(@sql);
      end try
      begin catch
        declare @dummy bit
      end catch

      begin try
        set @sql = 'sp_addserver ''{0}'', local'
        exec(@sql);
      end try
      begin catch
        declare @dummy2 bit
      end catch
    end";

    SqlConnection connection = null;
    try
    {
        var cb = new SqlConnectionStringBuilder()
        {
             DataSource = machineName,
             InitialCatalog = "master",
             UserID = uid,
             Password = pwd
        };

        connection = new SqlConnection(cb.ToString());
        connection.Open();
        var command = new SqlCommand(string.Format(sql,machineName), connection);
        try
        {
            command.ExecuteNonQuery();
        }
        catch { }
        connection.Close();
    }
    finally
    {
        if (connection != null) connection.Close();
    }
}

private void RestartSqlServer(string machineName)
{
    const string script = @"$svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""
    $svc.Stop()
    $svc.WaitForStatus('Stopped')
    #Write-Host Stopped

    $svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""
    $svc.Start()
    $svc.WaitForStatus('Running')
    #Write-Host Running";

    var sessionState = InitialSessionState.CreateDefault();
    using (var ps = PowerShell.Create(sessionState))
    {
       ps.Commands.Clear();
       ps.AddScript(string.Format(script, machineName));
       ps.Invoke();
    }
}

imports

using System.Management;

using System.Management.Automation;

using System.Management.Automation.Runspaces;

using System.Data.SqlClient;

//rename the default SQL server instance

private void RenameSqlServer(string machineName, string uid, string pwd)

{

const string sql = @"if('{0}' <> @@servername)

begin

declare @sql nvarchar(max);

begin try

set @sql = 'sp_dropserver ''' + @@servername + ''''

exec(@sql);

end try

begin catch

declare @dummy bit

end catch

begin try

set @sql = 'sp_addserver ''{0}'', local'

exec(@sql);

end try

begin catch

declare @dummy2 bit

end catch

end";

SqlConnection connection = null;

try

{

var cb = new SqlConnectionStringBuilder()

{

DataSource = machineName,

InitialCatalog = "master",

UserID = uid,

Password = pwd

};

connection = new SqlConnection(cb.ToString());

connection.Open();

var command = new SqlCommand(string.Format(sql,machineName), connection);

try

{

command.ExecuteNonQuery();

}

catch { }

connection.Close();

}

finally

{

if (connection != null) connection.Close();

}

private void RestartSqlServer(string machineName)

{

const string script = @"$svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""

$svc.Stop()

$svc.WaitForStatus('Stopped')

#Write-Host Stopped

$svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""

$svc.Start()

$svc.WaitForStatus('Running')

#Write-Host Running";

var sessionState = InitialSessionState.CreateDefault();

using (var ps = PowerShell.Create(sessionState))

{

ps.Commands.Clear();

ps.AddScript(string.Format(script, machineName));

ps.Invoke();

}

A configuration for SQL Server to prevent outgoing access to networked (off-machine) SQL Server instances from cloud VMs, while allowing management machines to access these instances.

SQL Server configuration for customer VMs

All SQL instances to be the default SQL instance on each machine (no named instances)
All SQL instances to use only TCP protocol (disable named pipes, and shared memory)
All SQL instances use port 1433 (already the case with template VMs)
All SQL instances do not run the SQL Browser Service (already the case with template VMs)
Management machines with SQL Server would not implement any special firewall rules aside from their current rule to allow incoming SQL requests.

Windows firewall configuration for VMs

Windows firewall properties for Domain, Private, and Public profiles

Inbound connections : block by default
Outbound connections allow by default

Create the following Windows firewall rules to block outgoing SQL requests:

Management machines with SQL Server would not implement these rules
DENY TCP remote port 1433,1434, any local port, any network profile, any program
DENY UDP remote port 1433,1434, any local port, any network profile, any program

Create the following Windows firewall rules to allow incoming SQL requests:

ALLOW TCP local port 1433 any remote port, any network profile, any program
ALLOW UDP local port 1433, any remote port, any network profile, any program

Use group policy to prevent users other than domain admins from changing firewall settings.

An alternate solution is to put a single firewall on the network between all machines and configure rules accordingly. This would make central management easier, but complicate the configuration.

Implementation #1

CREATE function [dbo].[fGetElapsedTime](@datetime1 datetime, @datetime2 datetime) returns nvarchar(25)
as
begin
  declare @default nvarchar(25)
  set @default =  '00.00:00:00';
  if (@datetime1 is null or @datetime2 is null) return null;
  if (@datetime1 = @datetime2) return  @default;
  
  declare @neg bit
  set @neg = 0
  if(@datetime1 &gt; @datetime2)
  begin
    declare @dt datetime
    set @dt = @datetime1
    set @datetime1 = @datetime2
    set @datetime2 = @dt
    set @neg = 1
  end

  declare @elapsed_time datetime
  declare @elapsed_days int
  declare @elapsed_hours int
  declare @elapsed_minutes int
  declare @elapsed_seconds int
  
  -- Get elapsed time as the difference between 2 datetimes
  select @elapsed_time = @datetime2-@datetime1

  select @elapsed_days = datediff(d,@datetime1,@datetime2)
  select @elapsed_hours = datepart(hour,@elapsed_time)
  select @elapsed_minutes = datepart(minute,@elapsed_time)
  select @elapsed_seconds = datepart(second,@elapsed_time)
  
  declare @result nvarchar(50)

  if(@elapsed_days = 0 and @elapsed_hours = 0 and @elapsed_minutes = 0 and @elapsed_seconds = 0) return @default
    
  set @result = 
    case when @elapsed_days &lt; 10 then '0' + convert(varchar(20),@elapsed_days) + '.' 
    else convert(varchar(20),@elapsed_days) + '.' 
    end  + 
    case when @elapsed_hours &lt; 10 then '0' + convert(varchar(20),@elapsed_hours) + ':' 
    else convert(varchar(20),@elapsed_hours) + ':' 
    end  + 
    case when @elapsed_minutes &lt; 10 then '0' + convert(varchar(20),@elapsed_minutes) + ':' 
    else convert(varchar(20),@elapsed_minutes) + ':' 
    end  + 	
    case when @elapsed_seconds &lt; 10 then '0' + convert(varchar(20),@elapsed_seconds)
    else convert(varchar(20),@elapsed_seconds)
    end 	

  return case when @neg=0 then @result else '- ' + @result end
end

CREATE function [dbo].[fGetElapsedTime](@datetime1 datetime, @datetime2 datetime) returns nvarchar(25)

begin

declare @default nvarchar(25)

set @default = '00.00:00:00';

if (@datetime1 is null or @datetime2 is null) return null;

if (@datetime1 = @datetime2) return @default;

declare @neg bit

set @neg = 0

if(@datetime1 > @datetime2)

begin

declare @dt datetime

set @dt = @datetime1

set @datetime1 = @datetime2

set @datetime2 = @dt

set @neg = 1

end

declare @elapsed_time datetime

declare @elapsed_days int

declare @elapsed_hours int

declare @elapsed_minutes int

declare @elapsed_seconds int

-- Get elapsed time as the difference between 2 datetimes

select @elapsed_time = @datetime2-@datetime1

select @elapsed_days = datediff(d,@datetime1,@datetime2)

select @elapsed_hours = datepart(hour,@elapsed_time)

select @elapsed_minutes = datepart(minute,@elapsed_time)

select @elapsed_seconds = datepart(second,@elapsed_time)

declare @result nvarchar(50)

if(@elapsed_days = 0 and @elapsed_hours = 0 and @elapsed_minutes = 0 and @elapsed_seconds = 0) return @default

set @result =

case when @elapsed_days < 10 then '0' + convert(varchar(20),@elapsed_days) + '.'

else convert(varchar(20),@elapsed_days) + '.'

end +

case when @elapsed_hours < 10 then '0' + convert(varchar(20),@elapsed_hours) + ':'

else convert(varchar(20),@elapsed_hours) + ':'

end +

case when @elapsed_minutes < 10 then '0' + convert(varchar(20),@elapsed_minutes) + ':'

else convert(varchar(20),@elapsed_minutes) + ':'

end +

case when @elapsed_seconds < 10 then '0' + convert(varchar(20),@elapsed_seconds)

else convert(varchar(20),@elapsed_seconds)

end

return case when @neg=0 then @result else '- ' + @result end

end

#1 Results

select [ElapsedTime] = [dbo].[fGetElapsedTime](dateadd(hh,-5,dateadd(n,-25,getdate())),getdate())
--00.05:25:00

select [ElapsedTime] = [dbo].[fGetElapsedTime](dateadd(d,-5,dateadd(hh,-3,getdate())),getdate())
--05.03:00:00

select [ElapsedTime] = [dbo].[fGetElapsedTime](dateadd(hh,-5,dateadd(n,-25,getdate())),getdate())

--00.05:25:00

select [ElapsedTime] = [dbo].[fGetElapsedTime](dateadd(d,-5,dateadd(hh,-3,getdate())),getdate())

--05.03:00:00

Implementation #2

CREATE function [dbo].[fGetElapsedTimeX]
(
@dt1 datetime
,@dt2 datetime
) 
returns nvarchar(20)
as
begin

declare @result nvarchar(20)
set @result = 
	convert(nvarchar(20),
		convert(varchar(10),
			convert(int, 
				convert(float,@dt2) - 
				convert(float, @dt1)
			) * 24 /* hours over 24 */
			+ datepart(hh, @dt2 - @dt1) /* hours */
		)
		+ ':' + right('0' + convert(varchar(2), datepart(mi, @dt2 - @dt1)), 2) /* minutes */
		+ ':' + right('0' + convert(varchar(2), datepart(ss, @dt2 - @dt1)), 2) /* seconds */
	)
	return @result
end

CREATE function [dbo].[fGetElapsedTimeX]

(

@dt1 datetime

,@dt2 datetime

)

returns nvarchar(20)

begin

declare @result nvarchar(20)

set @result =

convert(nvarchar(20),

convert(varchar(10),

convert(int,

convert(float,@dt2) -

convert(float, @dt1)

) * 24 /* hours over 24 */

+ datepart(hh, @dt2 - @dt1) /* hours */

)

+ ':' + right('0' + convert(varchar(2), datepart(mi, @dt2 - @dt1)), 2) /* minutes */

+ ':' + right('0' + convert(varchar(2), datepart(ss, @dt2 - @dt1)), 2) /* seconds */

)

return @result

end

#2 Results

select [ElapsedTime] = [dbo].[fGetElapsedTimeX](dateadd(hh,-5,dateadd(n,-25,getdate())),getdate())
--5:25:00

select [ElapsedTime] = [dbo].[fGetElapsedTimeX](dateadd(d,-5,dateadd(hh,-3,getdate())),getdate())
--123:00:00

select [ElapsedTime] = [dbo].[fGetElapsedTimeX](dateadd(hh,-5,dateadd(n,-25,getdate())),getdate())

--5:25:00

select [ElapsedTime] = [dbo].[fGetElapsedTimeX](dateadd(d,-5,dateadd(hh,-3,getdate())),getdate())

--123:00:00

A system to store property values as strings that are coerced and formatted according a data type. Supported data types are defined in the [services].[ValueType] table.

This is an alternative to using multiple typed columns or a single sql_variant type column in the database. All have their benefits. This is just another way handle user-defined typed data.

Partial Schema

CREATE SCHEMA [services]
GO

CREATE TABLE [services].[ValueType](
  [Name] [nvarchar](25) NULL,
  [Default] [bit] NULL CONSTRAINT [DF_ValueType_Default]  DEFAULT ((0)),
  [Entity] [bit] NULL CONSTRAINT [DF_ValueType_Entity]  DEFAULT ((0))
) ON [PRIMARY]
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Name', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Text', 1, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Integer', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'DateTime', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Date', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Time', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Money', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Decimal', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'User', 0, 1)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Customer', 0, 1)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Boolean', 0, 0)
GO
INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'List', 0, 0)
GO

CREATE TABLE [services].[Service](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Capture_Id]  DEFAULT (newsequentialid()),
	[Namespace] [nvarchar](100) NOT NULL,
	--...
	[ServicePropertiesAsText] [nvarchar](max) NULL,
 CONSTRAINT [PK_Service] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [services].[ServiceProperty](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServiceProperty_Id]  DEFAULT (newsequentialid()),
	[Namespace] [nvarchar](100) NOT NULL,
	[ServiceId] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[ValueTypeName] [nvarchar](25) NOT NULL,
	[ServicePropertyValuesAsText] [nvarchar](max) NULL,
	[Order] [int] NOT NULL CONSTRAINT [DF_ServiceProperty_Order]  DEFAULT ((0)),
 CONSTRAINT [PK_ServiceProperty_Id] 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],
 CONSTRAINT [UIX_ServiceProperty_ServiceId_Name] UNIQUE NONCLUSTERED 
(
	[ServiceId] ASC,
	[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [services].[ServicePropertyValue](
	[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServicePropertyValue_Id]  DEFAULT (newsequentialid()),
	[ServicePropertyId] [uniqueidentifier] NOT NULL,
	[Namespace] [nvarchar](100) NOT NULL,
	[Value] [nvarchar](4000) NULL,
	[EntityId] [uniqueidentifier] NULL,
	[Count] [int] NOT NULL CONSTRAINT [DF_ServicePropertyValue_Count]  DEFAULT ((1)),
	[CoercionFault] [bit] NOT NULL CONSTRAINT [DF_ServicePropertyValue_CoercionFault]  DEFAULT ((0)),
 CONSTRAINT [PK_ServicePropertyValue] 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

CREATE SCHEMA [services]

CREATE TABLE [services].[ValueType](

[Name] [nvarchar](25) NULL,

[Default] [bit] NULL CONSTRAINT [DF_ValueType_Default] DEFAULT ((0)),

[Entity] [bit] NULL CONSTRAINT [DF_ValueType_Entity] DEFAULT ((0))

) ON [PRIMARY]

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Name', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Text', 1, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Integer', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'DateTime', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Date', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Time', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Money', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Decimal', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'User', 0, 1)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Customer', 0, 1)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Boolean', 0, 0)

INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'List', 0, 0)

CREATE TABLE [services].[Service](

[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Capture_Id] DEFAULT (newsequentialid()),

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

--...

[ServicePropertiesAsText] [nvarchar](max) NULL,

CONSTRAINT [PK_Service] 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] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [services].[ServiceProperty](

[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServiceProperty_Id] DEFAULT (newsequentialid()),

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

[ServiceId] [uniqueidentifier] NOT NULL,

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

[ValueTypeName] [nvarchar](25) NOT NULL,

[ServicePropertyValuesAsText] [nvarchar](max) NULL,

[Order] [int] NOT NULL CONSTRAINT [DF_ServiceProperty_Order] DEFAULT ((0)),

CONSTRAINT [PK_ServiceProperty_Id] 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],

CONSTRAINT [UIX_ServiceProperty_ServiceId_Name] UNIQUE NONCLUSTERED

(

[ServiceId] ASC,

[Name] ASC

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

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [services].[ServicePropertyValue](

[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServicePropertyValue_Id] DEFAULT (newsequentialid()),

[ServicePropertyId] [uniqueidentifier] NOT NULL,

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

[Value] [nvarchar](4000) NULL,

[EntityId] [uniqueidentifier] NULL,

[Count] [int] NOT NULL CONSTRAINT [DF_ServicePropertyValue_Count] DEFAULT ((1)),

[CoercionFault] [bit] NOT NULL CONSTRAINT [DF_ServicePropertyValue_CoercionFault] DEFAULT ((0)),

CONSTRAINT [PK_ServicePropertyValue] 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]

Base SQL Functions

CREATE function [dbo].fLTrim(@value varchar(max))
returns varchar(max)
as
begin
  if(@value is not null)
    if (ascii(left(@value, 1)) < 33) 
      set @value = stuff(@value, 1, patindex('%[^' + char(0) + '-' + char(32) + ']%', @value) - 1,'');
  return @value;
end
go

CREATE function [dbo].fRTrim(@value varchar(max))
returns varchar(max)
as
begin
  if(@value is not null)
    set @value =  reverse([dbo].fLTrim(reverse(@value)));
  return @value;
end
go

CREATE function [dbo].fTrim(@value varchar(max))
returns varchar(max)
as
begin
  if(@value is not null) return [dbo].fLTrim([dbo].fRTrim(@value));
    return @value;
end
go

CREATE function [services].[fFormatNumericValue](@value nvarchar(max))
returns nvarchar(max)
as
begin
	declare @pattern nvarchar(20);
	set @pattern = '%[^0-9.]%';

	declare @incorrectCharLoc smallint
	set @incorrectCharLoc = patindex(@pattern, @value)
	while @incorrectCharLoc > 0
	begin
		set @value = stuff(@value, @incorrectCharLoc, 1, '')
		set @incorrectCharLoc = patindex(@pattern, @value)
	end
	return @value
end
go

CREATE function [services].[fFormatDecimalValue](@value nvarchar(max))
returns nvarchar(max)
as
begin
  set @value = [services].[fFormatNumericValue](@value)
  if(isnumeric(@value) = 1)
  begin
    set @value = convert(nvarchar(max),convert(decimal(18,2),@value));
  end
  return @value
end
go

CREATE function [services].[fFormatIntegerValue](@value nvarchar(max))
returns nvarchar(max)
as
begin
  set @value = [services].[fFormatNumericValue](@value)
  if(isnumeric(@value) = 1)
  begin
    set @value = convert(nvarchar(max),convert(int,round(@value, 0)));
  end
  return @value
end
go

CREATE function [services].[fFormatMoneyValue](@value nvarchar(max))
returns nvarchar(max)
as
begin
  set @value = [services].[fFormatNumericValue](@value)
  if(isnumeric(@value) = 1)
  begin
    set @value = convert(nvarchar(max),convert(money,@value));
  end
  return @value
end
go

CREATE function [services].[fFormatTemporalValue]
(
  @value as nvarchar(max)
  ,@valuetypename varchar(10)
)
returns nvarchar(max)
as
begin
  declare @out nvarchar(max)
  if(@value is null) return null;
  set @out = @value;
  if(isDate(@value) = 1)
  begin
    set @valuetypename = [services].[fGetValueTypeNameFromName](@valuetypename,0)
    set @out = 
      case when @valuetypename = 'DateTime' then replace(replace(convert(nvarchar(20), convert(datetime,@value), 100),'AM',' AM'),'PM',' PM') --Mon DD YYYY HH:MI:SS:MMMAM (or PM)
      when @valuetypename = 'Date' then convert(nvarchar(20), convert(date,@value), 107) --Mon DD, YYYY
      when @valuetypename = 'Time' then REPLACE(REPLACE(RIGHT(LTRIM(RIGHT(CONVERT(varchar,convert(time,@value),100),7)),7),'AM',' AM'),'PM',' PM') --hh:mm:ss
      end
  end
  return @out;
end
go

CREATE function [dbo].[fFilterAsciiControlChars](@value varchar(max))
returns varchar(max)
as
begin
  --filter ascii chars 0-31, but not 9 (tab),10 (newline) &amp; 13 (carriage return).
  if(@value is null) return @value;
  
  declare @newValue nvarchar(max)
  declare @ascii int;
  declare @pos int;
  set @newValue = '';
  set @pos = 1;

  while (1 = 1)
  begin
    if(@pos <= len(@value))
    begin
      set @ascii = ascii(substring(@value,@pos,1));
      if(@ascii >= 0 and @ascii <=31 and @ascii <> 9 and @ascii <> 10 and @ascii <> 13) set @newValue= @newValue + char(@ascii)
      set @pos = @pos + 1
    end
    else break;
  end
  return @newValue;
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

134

135

136

CREATE function [dbo].fLTrim(@value varchar(max))

returns varchar(max)

begin

if(@value is not null)

if (ascii(left(@value, 1)) < 33)

set @value = stuff(@value, 1, patindex('%[^' + char(0) + '-' + char(32) + ']%', @value) - 1,'');

return @value;

end

CREATE function [dbo].fRTrim(@value varchar(max))

returns varchar(max)

begin

if(@value is not null)

set @value = reverse([dbo].fLTrim(reverse(@value)));

return @value;

end

CREATE function [dbo].fTrim(@value varchar(max))

returns varchar(max)

begin

if(@value is not null) return [dbo].fLTrim([dbo].fRTrim(@value));

return @value;

end

CREATE function [services].[fFormatNumericValue](@value nvarchar(max))

returns nvarchar(max)

begin

declare @pattern nvarchar(20);

set @pattern = '%[^0-9.]%';

declare @incorrectCharLoc smallint

set @incorrectCharLoc = patindex(@pattern, @value)

while @incorrectCharLoc > 0

begin

set @value = stuff(@value, @incorrectCharLoc, 1, '')

set @incorrectCharLoc = patindex(@pattern, @value)

end

return @value

end

CREATE function [services].[fFormatDecimalValue](@value nvarchar(max))

returns nvarchar(max)

begin

set @value = [services].[fFormatNumericValue](@value)

if(isnumeric(@value) = 1)

begin

set @value = convert(nvarchar(max),convert(decimal(18,2),@value));

end

return @value

end

CREATE function [services].[fFormatIntegerValue](@value nvarchar(max))

returns nvarchar(max)

begin

set @value = [services].[fFormatNumericValue](@value)

if(isnumeric(@value) = 1)

begin

set @value = convert(nvarchar(max),convert(int,round(@value, 0)));

end

return @value

end

CREATE function [services].[fFormatMoneyValue](@value nvarchar(max))

returns nvarchar(max)

begin

set @value = [services].[fFormatNumericValue](@value)

if(isnumeric(@value) = 1)

begin

set @value = convert(nvarchar(max),convert(money,@value));

end

return @value

end

CREATE function [services].[fFormatTemporalValue]

(

@value as nvarchar(max)

,@valuetypename varchar(10)

)

returns nvarchar(max)

begin

declare @out nvarchar(max)

if(@value is null) return null;

set @out = @value;

if(isDate(@value) = 1)

begin

set @valuetypename = [services].[fGetValueTypeNameFromName](@valuetypename,0)

set @out =

case when @valuetypename = 'DateTime' then replace(replace(convert(nvarchar(20), convert(datetime,@value), 100),'AM',' AM'),'PM',' PM') --Mon DD YYYY HH:MI:SS:MMMAM (or PM)

when @valuetypename = 'Date' then convert(nvarchar(20), convert(date,@value), 107) --Mon DD, YYYY

when @valuetypename = 'Time' then REPLACE(REPLACE(RIGHT(LTRIM(RIGHT(CONVERT(varchar,convert(time,@value),100),7)),7),'AM',' AM'),'PM',' PM') --hh:mm:ss

end

return @out;

end

CREATE function [dbo].[fFilterAsciiControlChars](@value varchar(max))

returns varchar(max)

begin

--filter ascii chars 0-31, but not 9 (tab),10 (newline) & 13 (carriage return).

if(@value is null) return @value;

declare @newValue nvarchar(max)

declare @ascii int;

declare @pos int;

set @newValue = '';

set @pos = 1;

while (1 = 1)

begin

if(@pos <= len(@value))

begin

set @ascii = ascii(substring(@value,@pos,1));

if(@ascii >= 0 and @ascii <=31 and @ascii <> 9 and @ascii <> 10 and @ascii <> 13) set @newValue= @newValue + char(@ascii)

set @pos = @pos + 1

end

else break;

end

return @newValue;

end

Core Coercion Objects

CREATE function [services].[tfCoerceValueType]
(
  @value nvarchar(max)
  ,@valueTypeName nvarchar(25)
)
returns @tab table
(
  [Coerceable] bit
  ,[Value] nvarchar(max) 
)
as
begin
  declare @coercedValue nvarchar(max);
  declare @coerceable bit;
  set @coerceable = 0;
  
  set @coercedValue = @value;

  set @valueTypeName = [services].[fGetValueTypeNameFromName](@valueTypeName,0)
  if([dbo].[fIsNullOrWhitespace](@valueTypeName) = 1)
  begin
    --any value for an unknown type is never coerceable
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue)
    return; 
  end
  
  if(@value is null)
  begin
    --null for a known type is always coerceable
    set @coerceable = 1;
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue)
    return; 	
  end

  --trim and filter ascii chars 0-31, but not 9,10 &amp; 13.
  set @value = [dbo].[fTrim]([dbo].[fFilterAsciiControlChars](@value));

  --check text, customer, user, and list types
  if(@valueTypeName = 'Text' 
    or @valueTypeName = 'Customer' 
    or @valueTypeName = 'User' 
    or @valueTypeName = 'List')
  begin
    set @coerceable = 1;
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue)
    return;
  end

  --check integer
  if(@valueTypeName = 'Integer')
  begin
    if(isnumeric(@value)=1)
    begin
      declare @int int;
      set @value = [services].[fFormatIntegerValue](@value)
      set @int = convert(int,@value)
      set @coercedValue = convert(nvarchar(250),@int)
      set @coerceable = 1;
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end
    
  --check date
  if(@valueTypeName = 'Date')
  begin
    if(isdate(@value)=1)
    begin
      declare @d datetime;
      set @d = convert(datetime,@value)
      set @coercedValue = convert(nvarchar(25), @d, 23) --yyyy-mm-dd
      set @coerceable = 1;
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end
    
  --check time
  if(@valueTypeName = 'Time')
  begin
    if(isdate(convert(nvarchar(25), getdate(), 23) + ' ' + @value)=1) --add an arbitrary yyyy-mm-dd date part to the time so IsDate() can be used.
    begin		
      declare @t time;
      set @t = convert(time,@value)
      set @coercedValue = convert(nvarchar(25), @t, 108) --hh:mm:ss
      set @coerceable = 1;
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end		
     
  --check datetime
  if(@valueTypeName = 'DateTime')
  begin
    if(isdate(@value)=1)
    begin
      declare @dt datetime;
      set @dt = convert(datetime,@value)
      set @coercedValue = [dbo].[fFormatDateTimeAsISOStandard](@dt) --yyyy-mm-dd hh:mm:ss.mmm
      set @coerceable = 1;
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end
      
  --check decimal
  if(@valueTypeName = 'Decimal')
  begin
    if(isnumeric(@value)=1)
    begin	
      set @value = [services].[fFormatDecimalValue](@value)
      declare @dec decimal(18,4);
      set @dec = convert(decimal(18,4),@value)					
      set @coercedValue = convert(nvarchar(250),@dec)
      set @coerceable = 1;
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end

  --check money
  if(@valueTypeName = 'Money')
  begin
    if(isnumeric(@value)=1)
    begin
      set @value = [services].[fFormatMoneyValue](@value)	
      declare @mon money;
      set @mon = convert(money,@value)					
      set @coercedValue = convert(nvarchar(250),@mon)
      set @coerceable = 1;
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end	

  --check boolean
  if(@valueTypeName = 'Boolean')
  begin
    if(@value = 'Yes' or @value = 'True' or @value = '1' or @value = 'Y' or @value = 'T')
    begin
        set @coercedValue = 'Yes'
        set @coerceable = 1;			
    end
    else if(@value = 'No' or @value = 'False' or @value = '0' or @value = 'N' or @value = 'F')
    begin
        set @coercedValue = 'No'
        set @coerceable = 1;	
    end
    insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
    return;
  end	

  --unhandled known type
  insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);
  return;
end
go

CREATE function [services].[fCoerceValueType]
(
@value nvarchar(max)
,@valueTypeName varchar(10)
) returns nvarchar(max)
as
begin
  declare @newValue nvarchar(max);
  select top 1 @newValue = [Value] from [services].[tfCoerceValueType](@value, @valueTypeName);
  return @newValue;
end
go

CREATE procedure [services].[spCoerceValueType]
(
@value nvarchar(max)
,@valueTypeName nvarchar(25) out
,@coerceable bit out
,@coercedValue nvarchar(max) out
) 
as
begin
  
  select top 1 @coercedValue = [Value], @coerceable = [Coerceable] from [services].[tfCoerceValueType](@value, @valueTypeName);
  return;
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

CREATE function [services].[tfCoerceValueType]

(

@value nvarchar(max)

,@valueTypeName nvarchar(25)

)

returns @tab table

(

[Coerceable] bit

,[Value] nvarchar(max)

)

begin

declare @coercedValue nvarchar(max);

declare @coerceable bit;

set @coerceable = 0;

set @coercedValue = @value;

set @valueTypeName = [services].[fGetValueTypeNameFromName](@valueTypeName,0)

if([dbo].[fIsNullOrWhitespace](@valueTypeName) = 1)

begin

--any value for an unknown type is never coerceable

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue)

return;

end

if(@value is null)

begin

--null for a known type is always coerceable

set @coerceable = 1;

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue)

return;

end

--trim and filter ascii chars 0-31, but not 9,10 & 13.

set @value = [dbo].[fTrim]([dbo].[fFilterAsciiControlChars](@value));

--check text, customer, user, and list types

if(@valueTypeName = 'Text'

or @valueTypeName = 'Customer'

or @valueTypeName = 'User'

or @valueTypeName = 'List')

begin

set @coerceable = 1;

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue)

return;

end

--check integer

if(@valueTypeName = 'Integer')

begin

if(isnumeric(@value)=1)

begin

declare @int int;

set @value = [services].[fFormatIntegerValue](@value)

set @int = convert(int,@value)

set @coercedValue = convert(nvarchar(250),@int)

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--check date

if(@valueTypeName = 'Date')

begin

if(isdate(@value)=1)

begin

declare @d datetime;

set @d = convert(datetime,@value)

set @coercedValue = convert(nvarchar(25), @d, 23) --yyyy-mm-dd

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--check time

if(@valueTypeName = 'Time')

begin

if(isdate(convert(nvarchar(25), getdate(), 23) + ' ' + @value)=1) --add an arbitrary yyyy-mm-dd date part to the time so IsDate() can be used.

begin

declare @t time;

set @t = convert(time,@value)

set @coercedValue = convert(nvarchar(25), @t, 108) --hh:mm:ss

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--check datetime

if(@valueTypeName = 'DateTime')

begin

if(isdate(@value)=1)

begin

declare @dt datetime;

set @dt = convert(datetime,@value)

set @coercedValue = [dbo].[fFormatDateTimeAsISOStandard](@dt) --yyyy-mm-dd hh:mm:ss.mmm

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--check decimal

if(@valueTypeName = 'Decimal')

begin

if(isnumeric(@value)=1)

begin

set @value = [services].[fFormatDecimalValue](@value)

declare @dec decimal(18,4);

set @dec = convert(decimal(18,4),@value)

set @coercedValue = convert(nvarchar(250),@dec)

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--check money

if(@valueTypeName = 'Money')

begin

if(isnumeric(@value)=1)

begin

set @value = [services].[fFormatMoneyValue](@value)

declare @mon money;

set @mon = convert(money,@value)

set @coercedValue = convert(nvarchar(250),@mon)

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--check boolean

if(@valueTypeName = 'Boolean')

begin

if(@value = 'Yes' or @value = 'True' or @value = '1' or @value = 'Y' or @value = 'T')

begin

set @coercedValue = 'Yes'

set @coerceable = 1;

end

else if(@value = 'No' or @value = 'False' or @value = '0' or @value = 'N' or @value = 'F')

begin

set @coercedValue = 'No'

set @coerceable = 1;

end

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

--unhandled known type

insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue);

return;

end

CREATE function [services].[fCoerceValueType]

(

@value nvarchar(max)

,@valueTypeName varchar(10)

) returns nvarchar(max)

begin

declare @newValue nvarchar(max);

select top 1 @newValue = [Value] from [services].[tfCoerceValueType](@value, @valueTypeName);

return @newValue;

end

CREATE procedure [services].[spCoerceValueType]

(

@value nvarchar(max)

,@valueTypeName nvarchar(25) out

,@coerceable bit out

,@coercedValue nvarchar(max) out

)

begin

select top 1 @coercedValue = [Value], @coerceable = [Coerceable] from [services].[tfCoerceValueType](@value, @valueTypeName);

return;

end

Stored procedures for updating concatenated lists of property values

CREATE procedure [services].[spRefreshServicePropertyValuesAsTextForServiceProperties](@namespace nvarchar(100))
as begin
  BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON 
    
    update [services].[serviceproperty] 
    set	[ServicePropertyValuesAsText] = [result].[ServicePropertyValuesAsText]
    from
    (
      select
      [ServicePropertyId]
      ,[ServicePropertyValuesAsText] = substring([ServicePropertyValuesAsText],0,len([ServicePropertyValuesAsText])) 
      from
      (
        select
          [ServicePropertyId] = sp.[id]
          ,[ServicePropertyValuesAsText] = 
            substring(
            (
              select isnull(spv.[value],'') + ','	
              from [services].[servicepropertyvalue] spv 
              where sp.[id] = spv.[servicepropertyid]
              ORDER BY spv.[value]
              FOR XML PATH('')
            ), 1, 100000)				
        from [services].[serviceproperty] sp
        where sp.[namespace] = @namespace
      ) as [concat]
    ) as [result]
    where [Id] = [result].[ServicePropertyId]
    
  END TRY
  BEGIN CATCH
     --IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC [spHandleError]
     RETURN -1
  END CATCH   		
end
go

CREATE function [services].[tfGetServicePropertyValuesAsTextForServiceProperties](@namespace nvarchar(100), @optionalServicePropertyId uniqueidentifier = null)
returns @tab table
(
  [ServiceId] uniqueidentifier not null
  ,[ServicePropertyId] uniqueidentifier not null
  ,[ServicePropertyName] nvarchar(100)
  ,[ServicePropertyValuesAsText] nvarchar(max)
)
as begin
  set @namespace = [dbo].[fFormatNamespace](@namespace);

  insert @tab
  select
  [ServiceId]
  ,[ServicePropertyId]
  ,[ServicePropertyName]
  ,[ServicePropertyValuesAsText] = substring([ServicePropertyValuesAsText],0,len([ServicePropertyValuesAsText])) 
  from
  (
    select 
      [ServiceId]  = sp.[serviceid]
      ,[ServicePropertyId] = sp.[id]
      ,[ServicePropertyName] = sp.[name]
      ,[ServicePropertyValuesAsText] = 
        substring(
        (
          select
          isnull(spv.[value],'') + ','
          from [services].[servicepropertyvalue] spv 
          where sp.[id] = spv.[servicepropertyid]
          ORDER BY spv.[value]
          FOR XML PATH('')
        ), 1, 100000)				
    from [services].[serviceproperty] sp
    where sp.[namespace] = @namespace
    and (@optionalServicePropertyId is null or (@optionalServicePropertyId is not null and sp.id = @optionalServicePropertyId))
  ) as [concat]
  return;
end
go

CREATE procedure [services].[spRefreshServicePropertiesAsTextForServices](@namespace nvarchar(100))
as begin
  BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON 
    declare @tab table ([ServiceId] uniqueidentifier, [Text] nvarchar(max));
    
    update [services].[service]
    set	[ServicePropertiesAsText] = result.[Text]
    from
    (
      select
      [ServiceId]
      ,[Text] = [ServicePropertiesAsText]
      from [services].[tfGetServicePropertiesAsTextForServices](@namespace, null) 
    ) as result
    where [Id] = result.[ServiceId]
    
  END TRY
  BEGIN CATCH
     --IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC [spHandleError]
     RETURN -1
  END CATCH   		
end
go

100

101

102

103

104

105

106

107

108

CREATE procedure [services].[spRefreshServicePropertyValuesAsTextForServiceProperties](@namespace nvarchar(100))

as begin

BEGIN TRY

SET NOCOUNT ON

SET XACT_ABORT ON

update [services].[serviceproperty]

set [ServicePropertyValuesAsText] = [result].[ServicePropertyValuesAsText]

from

(

select

[ServicePropertyId]

,[ServicePropertyValuesAsText] = substring([ServicePropertyValuesAsText],0,len([ServicePropertyValuesAsText]))

from

(

select

[ServicePropertyId] = sp.[id]

,[ServicePropertyValuesAsText] =

substring(

(

select isnull(spv.[value],'') + ','

from [services].[servicepropertyvalue] spv

where sp.[id] = spv.[servicepropertyid]

ORDER BY spv.[value]

FOR XML PATH('')

), 1, 100000)

from [services].[serviceproperty] sp

where sp.[namespace] = @namespace

) as [concat]

) as [result]

where [Id] = [result].[ServicePropertyId]

END TRY

BEGIN CATCH

--IF @@trancount > 0 ROLLBACK TRANSACTION

EXEC [spHandleError]

RETURN -1

END CATCH

end

CREATE function [services].[tfGetServicePropertyValuesAsTextForServiceProperties](@namespace nvarchar(100), @optionalServicePropertyId uniqueidentifier = null)

returns @tab table

(

[ServiceId] uniqueidentifier not null

,[ServicePropertyId] uniqueidentifier not null

,[ServicePropertyName] nvarchar(100)

,[ServicePropertyValuesAsText] nvarchar(max)

)

as begin

set @namespace = [dbo].[fFormatNamespace](@namespace);

insert @tab

select

[ServiceId]

,[ServicePropertyId]

,[ServicePropertyName]

,[ServicePropertyValuesAsText] = substring([ServicePropertyValuesAsText],0,len([ServicePropertyValuesAsText]))

from

(

select

[ServiceId] = sp.[serviceid]

,[ServicePropertyId] = sp.[id]

,[ServicePropertyName] = sp.[name]

,[ServicePropertyValuesAsText] =

substring(

(

select

isnull(spv.[value],'') + ','

from [services].[servicepropertyvalue] spv

where sp.[id] = spv.[servicepropertyid]

ORDER BY spv.[value]

FOR XML PATH('')

), 1, 100000)

from [services].[serviceproperty] sp

where sp.[namespace] = @namespace

and (@optionalServicePropertyId is null or (@optionalServicePropertyId is not null and sp.id = @optionalServicePropertyId))

) as [concat]

return;

end

CREATE procedure [services].[spRefreshServicePropertiesAsTextForServices](@namespace nvarchar(100))

as begin

BEGIN TRY

SET NOCOUNT ON

SET XACT_ABORT ON

declare @tab table ([ServiceId] uniqueidentifier, [Text] nvarchar(max));

update [services].[service]

set [ServicePropertiesAsText] = result.[Text]

from

(

select

[ServiceId]

,[Text] = [ServicePropertiesAsText]

from [services].[tfGetServicePropertiesAsTextForServices](@namespace, null)

) as result

where [Id] = result.[ServiceId]

END TRY

BEGIN CATCH

--IF @@trancount > 0 ROLLBACK TRANSACTION

EXEC [spHandleError]

RETURN -1

END CATCH

end

Query and Update Property Values Manually

--query
select 
spv.[Id]
,sp.[Namespace]
,sp.[Name]
,sp.[ValueTypeName]
,spv.[Value]
,cvt.[Coerceable]
,[CoercedValue] = cvt.[Value]
from [services].[ServicePropertyValue] spv 
join [services].[ServiceProperty] sp on sp.[Id] = spv.[ServicePropertyId]
outer apply
(
  select * from [services].[tfCoerceValueType](spv.[Value], sp.[ValueTypeName])
) as cvt

--update
update [services].[ServicePropertyValue]
set [services].[ServicePropertyValue].[CoercionFault] = 
  case when spv.[Coerceable] = 1 then 0
  else 1
  end
,[services].[ServicePropertyValue].[Value] = spv.[Value]
from 
(
  select 
  [spvid] = spv.[Id]
  ,cvt.[Coerceable]
  ,cvt.[Value]
  from [services].[ServicePropertyValue] spv 
  join [services].[ServiceProperty] sp on sp.[Id] = spv.[ServicePropertyId]
  cross apply
  (
    select * from [services].[tfCoerceValueType](spv.[Value], sp.[ValueTypeName])
  ) as cvt
) as spv
where [Id] = spv.[spvid]

--query

select

spv.[Id]

,sp.[Namespace]

,sp.[Name]

,sp.[ValueTypeName]

,spv.[Value]

,cvt.[Coerceable]

,[CoercedValue] = cvt.[Value]

from [services].[ServicePropertyValue] spv

join [services].[ServiceProperty] sp on sp.[Id] = spv.[ServicePropertyId]

outer apply

(

select * from [services].[tfCoerceValueType](spv.[Value], sp.[ValueTypeName])

) as cvt

--update

update [services].[ServicePropertyValue]

set [services].[ServicePropertyValue].[CoercionFault] =

case when spv.[Coerceable] = 1 then 0

else 1

end

,[services].[ServicePropertyValue].[Value] = spv.[Value]

from

(

select

[spvid] = spv.[Id]

,cvt.[Coerceable]

,cvt.[Value]

from [services].[ServicePropertyValue] spv

join [services].[ServiceProperty] sp on sp.[Id] = spv.[ServicePropertyId]

cross apply

(

select * from [services].[tfCoerceValueType](spv.[Value], sp.[ValueTypeName])

) as cvt

) as spv

where [Id] = spv.[spvid]

C#

Convert string array to string

var array = new string[]{"Hello","Doctor","Name","Continue","Yesterday","Tomorrow"};
string result = string.Join(string.Empty, array);
string result2 = string.Join(", ", array);

var array = new string[]{"Hello","Doctor","Name","Continue","Yesterday","Tomorrow"};

string result = string.Join(string.Empty, array);

string result2 = string.Join(", ", array);

result = HelloDoctorNameContinueYesterdayTomorrow
result2 = Hello, Doctor, Name, Continue, Yesterday, Tomorrow

Format Decimal as currency

decimal amount = 32.95M;
var result = string.Format("{0:C}", amount);}

1 2	decimal amount = 32.95M; var result = string.Format("{0:C}", amount);}

result = $32.95

Format Decimal as string with N places

public string FormatAsDecimalString(decimal? value, int places = 2)
{
        if (value == null) value = 0;
        if (places &gt;= 0) places = 2;
        return String.Format("{0:N" + places.ToString() + "}", value);
}

public string FormatAsDecimalString(decimal? value, int places = 2)

{

if (value == null) value = 0;

if (places >= 0) places = 2;

return String.Format("{0:N" + places.ToString() + "}", value);

}

Optionally Running Debug Code

public void DoSomething()
{
   if (System.Diagnostics.Debugger.IsAttached)
   {
      //called only when debugger is attached
   }

   DoSomethingDebuggey();
}

//called only when set to debug build
[Conditional("DEBUG")]
private void DoSomethingDebuggey();
{
}

public void DoSomething()

{

if (System.Diagnostics.Debugger.IsAttached)

{

//called only when debugger is attached

}

DoSomethingDebuggey();

}

//called only when set to debug build

[Conditional("DEBUG")]

private void DoSomethingDebuggey();

{

}

SQL

Generate Sequential Guids
arguably not the most commonly used capability, but here it is…

declare @tab table ([id] uniqueidentifier default newsequentialid(), [dummy] bit)
declare @generate int = 50
declare @count int = 1
while(@count &lt;= @generate)
begin
	insert @tab ([dummy]) values (1)
	set @count = @count + 1
end
select [id] from @tab order by [id]

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

declare @generate int = 50

declare @count int = 1

while(@count <= @generate)

begin

insert @tab ([dummy]) values (1)

set @count = @count + 1

end

select [id] from @tab order by [id]

Print immediately to the Messages window when running queries in SQL Management Studio

declare @message nvarchar(1000);
set @message = '...something, something, something, dark side...'
raiserror (@message, 0, 1) with nowait --print immediately (doesn't interrupt query flow)

declare @message nvarchar(1000);

set @message = '...something, something, something, dark side...'

raiserror (@message, 0, 1) with nowait --print immediately (doesn't interrupt query flow)

Delete temp table if exists

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

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

SQL Server database stuck in “Restoring” state

use master;
RESTORE DATABASE [DatabaseNameHere] WITH RECOVERY

1 2	use master; RESTORE DATABASE [DatabaseNameHere] WITH RECOVERY

Disable / Enable triggers and constraints for the database

--disable
exec sp_MSforeachtable 'alter table ? disable trigger all;
alter table ? nocheck constraint all;'

--enable
exec sp_MSforeachtable 'alter table ? enable trigger all;
alter table ? check constraint all;'

--disable

exec sp_MSforeachtable 'alter table ? disable trigger all;

alter table ? nocheck constraint all;'

--enable

exec sp_MSforeachtable 'alter table ? enable trigger all;

alter table ? check constraint all;'

Clear the cache without re-starting

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

1 2	DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE

Check if file exists using T-SQL
Note: The identity under which the SQL Server process is running will require permissions to read the file system. Right-click the folder/file from file explorer, from the “Security” tab, assign appropriate permissions to “NetworkService” (or whatever credential SQL Server is using). Don’t forget to remember not to forget to remember that the file system is seen from the perspective of the SQL Server!

create function [dbo].[SIP_NPT_fFileExists](@path varchar(512))
returns bit
as
begin
     declare @result int
     exec master.dbo.xp_fileexist @path, @result output
     return cast(@result as bit)
end

create function [dbo].[SIP_NPT_fFileExists](@path varchar(512))

returns bit

begin

declare @result int

exec master.dbo.xp_fileexist @path, @result output

return cast(@result as bit)

end

Close all database connections
Using setvar in T-SQL

:setvar DB "MyDb"
alter database [$(DB)] set single_user with rollback immediate
alter database [$(DB)] set multi_user

:setvar DB "MyDb"

alter database [$(DB)] set single_user with rollback immediate

alter database [$(DB)] set multi_user

Other

Excel 2013 Cell Limit
Cell XFD1048576 is the max cell in Excel. It’s “right down” there.
That’s 16,383 columns x 1,048,576 rows.
excel-specifications-and-limits

Excel Guid Formula
=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),”-“,DEC2HEX(RANDBETWEEN(0,65535),4),”-“,DEC2HEX(RANDBETWEEN(16384,20479),4),”-“,DEC2HEX(RANDBETWEEN(32768,49150),4),”-“,DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))

Remove hiberfil.sys from a server
If it’s a Citrix VM, hibernation must first be enabled in the “firmware” by issuing the following command, and re-start the VM.

xe vm-param-add uuid=<your VM> param-name=platform acpi_s4=true

Run the command interpreter “Run as Administrator” and enter:
powercfg -h off

originally posted 2013-10-2

1	originally posted 2013-10-2

The chart of accounts (COA) is different for each organization. The COA code segments are expressed as individual columns in the table structure, making it difficult to query based on code values without resorting to dynamic SQL.

A possible solution is, for each table that contains the COA codes, to create a single calculated-persisted column that consolidates the multi-column values into a single delimited column. In this case, the values are consolidated with a caret (^) character. This example determines the formula for the new calculated-persisted field by examining the COA definition in the tblSegmentInfo table, then it creates the new [CodeSequence] column for each target table. The schema is checked to see if the column and its associated index already exist, and if so, it drops them before (re-)creating them. The tables involved only need to be the ones that the integrating application cares about, in this particular case, they are…

tblBLTrans – budget detail table
tblENTrans – encumbrance detail table
tblDLTrans – expense detail table
tblGLBLBalance – transaction summary table
tblDistCodeDetail – distribution codes, which are named sequences of codes without the GL (0) segment

declare @sequence nvarchar(2000)
set @sequence = '';

--Determine the number of COA segments
declare @total int
select @total = count(*) from tblSegmentInfo

declare @index int
set @index=0;

--Build the formula string that will be used in the calculated column
while(@index<@total)
begin
  set @sequence = @sequence + 'ltrim(rtrim(isnull([sCodeIDf_' + convert(nvarchar(10),@index) + '] ,''''))) + ''^'''
  if(@index+1 < @total) set @sequence = @sequence + ' + ';
  set @index=@index+1
end

--Build the sql template string to create the new [CodeSequence] calculated-persisted field and its index. Drop both first if the column exists.
--The {0} is a placeholder for the table name 
declare @sqlTemplate nvarchar(max);
set @sqlTemplate = N'
if exists 
(
  select * 
  from sys.columns 
  where [name] = N''CodeSequence'' 
  and [is_computed] = 1
  and [object_id] = Object_ID(N''{0}'')
)
begin
  drop index [dbo].[{0}].[IX_{0}_CodeSequence];
  alter table [dbo].[{0}] drop column [CodeSequence];
end
alter table [dbo].[{0}] add [CodeSequence] as ' + @sequence + ' PERSISTED;
create nonclustered index [IX_{0}_CodeSequence] on [dbo].[{0}] ([CodeSequence] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];';

declare @sql nvarchar(max);

--Replace the {0} in the sql template string with the tablename and execute the script for each table of interest

--tblDistCodeDetail - no gl code
set @sql = replace(@sqlTemplate,'{0}','tblDistCodeDetail');
set @sql = replace(@sql,'ltrim(rtrim(isnull([sCodeIDf_0] ,''''))) + ''^'' + ','');
exec(@sql);

--tblEnTrans - all codes
set @sql = replace(@sqlTemplate,'{0}','tblEnTrans');
exec(@sql);

--tblDlTrans - all codes
set @sql = replace(@sqlTemplate,'{0}','tblDlTrans');
exec(@sql);

--tblBlTrans - all codes
set @sql = replace(@sqlTemplate,'{0}','tblBlTrans');
exec(@sql);

--tblGLBLBalance - all codes
set @sql = replace(@sqlTemplate,'{0}','tblGLBLBalance');
exec(@sql);

declare @sequence nvarchar(2000)

set @sequence = '';

--Determine the number of COA segments

declare @total int

select @total = count(*) from tblSegmentInfo

declare @index int

set @index=0;

--Build the formula string that will be used in the calculated column

while(@index<@total)

begin

set @sequence = @sequence + 'ltrim(rtrim(isnull([sCodeIDf_' + convert(nvarchar(10),@index) + '] ,''''))) + ''^'''

if(@index+1 < @total) set @sequence = @sequence + ' + ';

set @index=@index+1

end

--Build the sql template string to create the new [CodeSequence] calculated-persisted field and its index. Drop both first if the column exists.

--The {0} is a placeholder for the table name

declare @sqlTemplate nvarchar(max);

set @sqlTemplate = N'

if exists

(

select *

from sys.columns

where [name] = N''CodeSequence''

and [is_computed] = 1

and [object_id] = Object_ID(N''{0}'')

)

begin

drop index [dbo].[{0}].[IX_{0}_CodeSequence];

alter table [dbo].[{0}] drop column [CodeSequence];

end

alter table [dbo].[{0}] add [CodeSequence] as ' + @sequence + ' PERSISTED;

create nonclustered index [IX_{0}_CodeSequence] on [dbo].[{0}] ([CodeSequence] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];';

declare @sql nvarchar(max);

--Replace the {0} in the sql template string with the tablename and execute the script for each table of interest

--tblDistCodeDetail - no gl code

set @sql = replace(@sqlTemplate,'{0}','tblDistCodeDetail');

set @sql = replace(@sql,'ltrim(rtrim(isnull([sCodeIDf_0] ,''''))) + ''^'' + ','');

exec(@sql);

--tblEnTrans - all codes

set @sql = replace(@sqlTemplate,'{0}','tblEnTrans');

exec(@sql);

--tblDlTrans - all codes

set @sql = replace(@sqlTemplate,'{0}','tblDlTrans');

exec(@sql);

--tblBlTrans - all codes

set @sql = replace(@sqlTemplate,'{0}','tblBlTrans');

exec(@sql);

--tblGLBLBalance - all codes

set @sql = replace(@sqlTemplate,'{0}','tblGLBLBalance');

exec(@sql);

originally posted 2013-10-4

1	originally posted 2013-10-4

There are probably way more elegant ways to do this… but “Mission Accomplished”.

The gotcha in all of this is the biweekly (26 payments per year) schedule, because you need to know which week in a given month should be the chosen one. You can’t just assume it’s always the second week of a given month because the second week in that month may land on the first week of a month down the line. The SQL function below starts from a reference date with which it calibrates the start of the bi-weekly pay schedule and calculates the rest of the pay dates through the end date. Most organizations have a payroll system that calculates new pay dates from the last pay date of record (that’s the reference date here). The bottom line is an actual pay date of record must be known from which future pay dates can be calculated. The weekly (52 payments per year) and semi-monthly (24 payments per year) don’t have this problem, though the latter can have pay days that land on weekend days for which special handling is required… an aspect which is included in the T-SQL below.

create function [dbo].[SIP_MIP_GetWeekly52PayDate]
(
  @startDate date
  ,@dayOfWeek int
  ,@nextDateOnly bit = 1
)
returns date as
begin
  declare @outDate date = null;
  if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day
  if(@dayOfWeek is null or @dayOfWeek &lt;1) return null;
  
  declare @dow int = datepart(weekday, @startDate)
  if(@dow=@dayOfWeek) set @outDate = @startDate;
  else set @outDate = dateadd(d,@dayofWeek-@dow,@startDate)
  if(@outDate&lt;@startDate) set @outDate = dateadd(ww,1,@outDate)
  return @outDate
end
GO

create function [dbo].[SIP_MIP_GetSemiMonthly24PayDate]
(
  @startDate date
  ,@payDay1 int
  ,@payDay2 int
  ,@nextDateOnly bit = 1
)
returns date as
begin
  declare @outDate date = null;
  if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day
  
  if(@payDay1&gt;@payDay2)
  begin
    --swap sm24PayDay values
    declare @tmp int = @payDay1;
    set @payDay1 = @payDay2;
    set @payDay2 = @tmp;
  end

  --adjust if the last day of the month doesn't have @payDay2 days
  declare @endOfMonth date = dateadd(s,-1,dateadd(mm, datediff(m,0,@startDate)+1,0))
  if(@payDay2&gt;day(@endOfMonth)) set @payDay2 = day(@endOfMonth) --use the last day of the month if the month doesn't have @payDay2 days

  declare @firstPayDateOfMonth date = convert(date, convert(nvarchar(5),Year(@startDate)) + '-' + convert(nvarchar(2),Month(@startDate)) + '-' + convert(nvarchar(2),@payDay1));
  if(datepart(weekday, @firstPayDateOfMonth) = 1) set @firstPayDateOfMonth = dateadd(d,-2,@firstPayDateOfMonth)
  else if(datepart(weekday, @firstPayDateOfMonth) = 7) set @firstPayDateOfMonth = dateadd(d,-1,@firstPayDateOfMonth)
  if(@startDate&lt;=@firstPayDateOfMonth) set @outDate = @firstPayDateOfMonth;
  else
  begin
    declare @secondPayDateOfMonth date = convert(date, convert(nvarchar(5),Year(@startDate)) + '-' + convert(nvarchar(2),Month(@startDate)) + '-' + convert(nvarchar(2),@payDay2));
    if(datepart(weekday, @secondPayDateOfMonth) = 1) set @secondPayDateOfMonth = dateadd(d,-2,@secondPayDateOfMonth)
    else if(datepart(weekday, @secondPayDateOfMonth) = 7) set @secondPayDateOfMonth = dateadd(d,-1,@secondPayDateOfMonth)
    if(@startDate&lt;=@secondPayDateOfMonth) set @outDate = @secondPayDateOfMonth;
    else
    begin
      declare @firstPayDateOfNextMonth date = dateadd(m,1,@startDate);
      set @firstPayDateOfNextMonth =convert(date, convert(nvarchar(5),Year(@firstPayDateOfNextMonth)) + '-' + convert(nvarchar(2),Month(@firstPayDateOfNextMonth)) + '-' + convert(nvarchar(2),@payDay1));
      if(datepart(weekday, @firstPayDateOfNextMonth) = 1) set @firstPayDateOfNextMonth = dateadd(d,-2,@firstPayDateOfNextMonth)
      else if(datepart(weekday, @firstPayDateOfNextMonth) = 7) set @firstPayDateOfNextMonth = dateadd(d,-1,@firstPayDateOfNextMonth)
      if(@startDate&lt;=@firstPayDateOfNextMonth) set @outDate = @firstPayDateOfNextMonth;
      else
      begin
        declare @secondPayDateOfNextMonth date = dateadd(m,1,@startDate);
        set @secondPayDateOfNextMonth =convert(date, convert(nvarchar(5),Year(@secondPayDateOfNextMonth)) + '-' + convert(nvarchar(2),Month(@secondPayDateOfNextMonth)) + '-' + convert(nvarchar(2),@payDay2));
        if(datepart(weekday, @secondPayDateOfNextMonth) = 1) set @secondPayDateOfNextMonth = dateadd(d,-2,@secondPayDateOfNextMonth)
        else if(datepart(weekday, @secondPayDateOfNextMonth) = 7) set @secondPayDateOfNextMonth = dateadd(d,-1,@secondPayDateOfNextMonth)
        if(@startDate&lt;=@secondPayDateOfNextMonth) set @outDate = @secondPayDateOfNextMonth;
      end
    end
  end
  return @outDate;
end
GO

create function [dbo].[SIP_MIP_GetBiWeekly26PayDate]
(
  @startDate date
  ,@referenceDate date
  ,@nextDateOnly bit = 1
)
returns date as
begin
  declare @outDate date = null;
  if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day
  
  --determine how many weeks are between the start and reference dates
  declare @weeks int = datediff(ww,@referenceDate,@startDate);
  declare @weekly52StartDate date
  if(@weeks=0)
  begin
    --start date is less than a week away from the reference date
    if(@referenceDate&lt;&gt;@startDate) set @outDate = dateadd(ww,2,@referenceDate);
    else set @outDate = @startDate;
  end
  else
  begin
    --start date is one or more weeks away from the reference date
    declare @modulo int = @weeks % 2
    if (@modulo&lt;&gt;0) set @weeks = @weeks+@modulo --make the offset number of weeks from the ref date is even
    if(@weeks&lt;0) set @weeks = @weeks+2 --the ref date is later than the start date, adjust to the pay period on or after the start date
    set @outDate = dateadd(ww,@weeks,@referenceDate);
    if(@outDate&lt;@startDate) set @outDate = dateadd(ww,2,@outDate);
  end
  return @outDate
end
GO

create function [SIP_NPT_tfGetPayDays]
(
@startDate date
,@endDate date
,@biWeekly26ReferencePayDate date
,@semiMonthly24PayDay1 int
,@semiMonthly24PayDay2 int
)
returns @tab table ([Date] date, [Type] nvarchar(25),[Days] int, [WorkDays] int)
as
begin
  if(@startDate is null) return;

  declare @dt date
  declare @sm24Days int = 1;
  declare @bw26Days int = 1;
  declare @w52Days int = 1;
  declare @sm24WorkDays int = 1;
  declare @bw26WorkDays int = 1;
  declare @w52WorkDays int = 1;
  declare @sm24PayDate date
  declare @bw26PayDate date
  declare @w52PayDate date

  --Determine first pay dates for BiWeekly26, Weekly52, and SemiMonthly24
  set @bw26PayDate = [dbo].[SIP_MIP_GetBiWeekly26PayDate](@startDate,@biWeekly26ReferencePayDate,0);
  set @w52PayDate = [dbo].[SIP_MIP_GetWeekly52PayDate](@startDate, datepart(weekday, @bw26PayDate),0);
  set @sm24PayDate = [dbo].[SIP_MIP_GetSemiMonthly24PayDate](@startDate,@semiMonthly24PayDay1,@semiMonthly24PayDay2,0);
  set @dt = @startDate

  while(1=1)
  begin
    --SemiMonthly24
    if(@dt=@sm24PayDate)
    begin
      --record and calc next pay date
      insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'SemiMonthly24',@sm24Days,@sm24WorkDays)		
      set @sm24Days = 1;
      set @sm24WorkDays = 1;
      set @sm24PayDate = [dbo].[SIP_MIP_GetSemiMonthly24PayDate](@sm24PayDate,@semiMonthly24PayDay1,@semiMonthly24PayDay2,1);
    end
    else
    begin
      --accumulate days and workdays
      set @sm24Days = @sm24Days+1;
      if(datepart(weekday, @dt) not in (1,7)) set @sm24WorkDays = @sm24WorkDays + 1
    end

    --BiWeekly26
    if(@dt=@bw26PayDate)
    begin
      --record and calc next pay date
      insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'BiWeekly26',@bw26Days,@bw26WorkDays)
      set @bw26Days = 1;
      set @bw26WorkDays = 1;
      set @bw26PayDate = [dbo].[SIP_MIP_GetBiWeekly26PayDate](@bw26PayDate,@biWeekly26ReferencePayDate,1);
    end
    else
    begin
      --accumulate days and workdays
      set @bw26Days = @bw26Days+1;
      if(datepart(weekday, @dt) not in (1,7)) set @bw26WorkDays = @bw26WorkDays + 1
    end

    --Weekly52
    if(@dt=@w52PayDate)
    begin
      --record and calc next pay date
      insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'Weekly52',@w52Days,@w52WorkDays)
      set @w52Days = 0;
      set @w52WorkDays = 0;
      set @w52PayDate = [dbo].[SIP_MIP_GetWeekly52PayDate](@w52PayDate, datepart(weekday, @w52PayDate),1);
    end
    else
    begin
      --accumulate days and workdays
      set @w52Days = @w52Days+1;
      if(datepart(weekday, @dt) not in (1,7)) set @w52WorkDays = @w52WorkDays + 1
    end

    --move to the next day
    set @dt = dateadd(d,1,@dt);
    
    --stop when at the end date
    if(@dt&gt;=@endDate) break;
  end
  return;
end
GO

--TEST
select * 
from [SIP_NPT_tfGetPayDays]('2013-1-1','2013-12-31','2009-12-18',1,15)
order by [Type],[Date]
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

create function [dbo].[SIP_MIP_GetWeekly52PayDate]

(

@startDate date

,@dayOfWeek int

,@nextDateOnly bit = 1

)

returns date as

begin

declare @outDate date = null;

if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day

if(@dayOfWeek is null or @dayOfWeek <1) return null;

declare @dow int = datepart(weekday, @startDate)

if(@dow=@dayOfWeek) set @outDate = @startDate;

else set @outDate = dateadd(d,@dayofWeek-@dow,@startDate)

if(@outDate<@startDate) set @outDate = dateadd(ww,1,@outDate)

return @outDate

end

create function [dbo].[SIP_MIP_GetSemiMonthly24PayDate]

(

@startDate date

,@payDay1 int

,@payDay2 int

,@nextDateOnly bit = 1

)

returns date as

begin

declare @outDate date = null;

if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day

if(@payDay1>@payDay2)

begin

--swap sm24PayDay values

declare @tmp int = @payDay1;

set @payDay1 = @payDay2;

set @payDay2 = @tmp;

end

--adjust if the last day of the month doesn't have @payDay2 days

declare @endOfMonth date = dateadd(s,-1,dateadd(mm, datediff(m,0,@startDate)+1,0))

if(@payDay2>day(@endOfMonth)) set @payDay2 = day(@endOfMonth) --use the last day of the month if the month doesn't have @payDay2 days

declare @firstPayDateOfMonth date = convert(date, convert(nvarchar(5),Year(@startDate)) + '-' + convert(nvarchar(2),Month(@startDate)) + '-' + convert(nvarchar(2),@payDay1));

if(datepart(weekday, @firstPayDateOfMonth) = 1) set @firstPayDateOfMonth = dateadd(d,-2,@firstPayDateOfMonth)

else if(datepart(weekday, @firstPayDateOfMonth) = 7) set @firstPayDateOfMonth = dateadd(d,-1,@firstPayDateOfMonth)

if(@startDate<=@firstPayDateOfMonth) set @outDate = @firstPayDateOfMonth;

else

begin

declare @secondPayDateOfMonth date = convert(date, convert(nvarchar(5),Year(@startDate)) + '-' + convert(nvarchar(2),Month(@startDate)) + '-' + convert(nvarchar(2),@payDay2));

if(datepart(weekday, @secondPayDateOfMonth) = 1) set @secondPayDateOfMonth = dateadd(d,-2,@secondPayDateOfMonth)

else if(datepart(weekday, @secondPayDateOfMonth) = 7) set @secondPayDateOfMonth = dateadd(d,-1,@secondPayDateOfMonth)

if(@startDate<=@secondPayDateOfMonth) set @outDate = @secondPayDateOfMonth;

else

begin

declare @firstPayDateOfNextMonth date = dateadd(m,1,@startDate);

set @firstPayDateOfNextMonth =convert(date, convert(nvarchar(5),Year(@firstPayDateOfNextMonth)) + '-' + convert(nvarchar(2),Month(@firstPayDateOfNextMonth)) + '-' + convert(nvarchar(2),@payDay1));

if(datepart(weekday, @firstPayDateOfNextMonth) = 1) set @firstPayDateOfNextMonth = dateadd(d,-2,@firstPayDateOfNextMonth)

else if(datepart(weekday, @firstPayDateOfNextMonth) = 7) set @firstPayDateOfNextMonth = dateadd(d,-1,@firstPayDateOfNextMonth)

if(@startDate<=@firstPayDateOfNextMonth) set @outDate = @firstPayDateOfNextMonth;

else

begin

declare @secondPayDateOfNextMonth date = dateadd(m,1,@startDate);

set @secondPayDateOfNextMonth =convert(date, convert(nvarchar(5),Year(@secondPayDateOfNextMonth)) + '-' + convert(nvarchar(2),Month(@secondPayDateOfNextMonth)) + '-' + convert(nvarchar(2),@payDay2));

if(datepart(weekday, @secondPayDateOfNextMonth) = 1) set @secondPayDateOfNextMonth = dateadd(d,-2,@secondPayDateOfNextMonth)

else if(datepart(weekday, @secondPayDateOfNextMonth) = 7) set @secondPayDateOfNextMonth = dateadd(d,-1,@secondPayDateOfNextMonth)

if(@startDate<=@secondPayDateOfNextMonth) set @outDate = @secondPayDateOfNextMonth;

end

return @outDate;

end

create function [dbo].[SIP_MIP_GetBiWeekly26PayDate]

(

@startDate date

,@referenceDate date

,@nextDateOnly bit = 1

)

returns date as

begin

declare @outDate date = null;

if(@nextDateOnly=1) set @startDate = dateadd(d,1,@startDate) --to make sure the input isn't returned as a pay date, increment the input by one day

--determine how many weeks are between the start and reference dates

declare @weeks int = datediff(ww,@referenceDate,@startDate);

declare @weekly52StartDate date

if(@weeks=0)

begin

--start date is less than a week away from the reference date

if(@referenceDate<>@startDate) set @outDate = dateadd(ww,2,@referenceDate);

else set @outDate = @startDate;

end

else

begin

--start date is one or more weeks away from the reference date

declare @modulo int = @weeks % 2

if (@modulo<>0) set @weeks = @weeks+@modulo --make the offset number of weeks from the ref date is even

if(@weeks<0) set @weeks = @weeks+2 --the ref date is later than the start date, adjust to the pay period on or after the start date

set @outDate = dateadd(ww,@weeks,@referenceDate);

if(@outDate<@startDate) set @outDate = dateadd(ww,2,@outDate);

end

return @outDate

end

create function [SIP_NPT_tfGetPayDays]

(

@startDate date

,@endDate date

,@biWeekly26ReferencePayDate date

,@semiMonthly24PayDay1 int

,@semiMonthly24PayDay2 int

)

returns @tab table ([Date] date, [Type] nvarchar(25),[Days] int, [WorkDays] int)

begin

if(@startDate is null) return;

declare @dt date

declare @sm24Days int = 1;

declare @bw26Days int = 1;

declare @w52Days int = 1;

declare @sm24WorkDays int = 1;

declare @bw26WorkDays int = 1;

declare @w52WorkDays int = 1;

declare @sm24PayDate date

declare @bw26PayDate date

declare @w52PayDate date

--Determine first pay dates for BiWeekly26, Weekly52, and SemiMonthly24

set @bw26PayDate = [dbo].[SIP_MIP_GetBiWeekly26PayDate](@startDate,@biWeekly26ReferencePayDate,0);

set @w52PayDate = [dbo].[SIP_MIP_GetWeekly52PayDate](@startDate, datepart(weekday, @bw26PayDate),0);

set @sm24PayDate = [dbo].[SIP_MIP_GetSemiMonthly24PayDate](@startDate,@semiMonthly24PayDay1,@semiMonthly24PayDay2,0);

set @dt = @startDate

while(1=1)

begin

--SemiMonthly24

if(@dt=@sm24PayDate)

begin

--record and calc next pay date

insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'SemiMonthly24',@sm24Days,@sm24WorkDays)

set @sm24Days = 1;

set @sm24WorkDays = 1;

set @sm24PayDate = [dbo].[SIP_MIP_GetSemiMonthly24PayDate](@sm24PayDate,@semiMonthly24PayDay1,@semiMonthly24PayDay2,1);

end

else

begin

--accumulate days and workdays

set @sm24Days = @sm24Days+1;

if(datepart(weekday, @dt) not in (1,7)) set @sm24WorkDays = @sm24WorkDays + 1

end

--BiWeekly26

if(@dt=@bw26PayDate)

begin

--record and calc next pay date

insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'BiWeekly26',@bw26Days,@bw26WorkDays)

set @bw26Days = 1;

set @bw26WorkDays = 1;

set @bw26PayDate = [dbo].[SIP_MIP_GetBiWeekly26PayDate](@bw26PayDate,@biWeekly26ReferencePayDate,1);

end

else

begin

--accumulate days and workdays

set @bw26Days = @bw26Days+1;

if(datepart(weekday, @dt) not in (1,7)) set @bw26WorkDays = @bw26WorkDays + 1

end

--Weekly52

if(@dt=@w52PayDate)

begin

--record and calc next pay date

insert @tab ([Date],[Type],[Days],[WorkDays]) values (@dt,'Weekly52',@w52Days,@w52WorkDays)

set @w52Days = 0;

set @w52WorkDays = 0;

set @w52PayDate = [dbo].[SIP_MIP_GetWeekly52PayDate](@w52PayDate, datepart(weekday, @w52PayDate),1);

end

else

begin

--accumulate days and workdays

set @w52Days = @w52Days+1;

if(datepart(weekday, @dt) not in (1,7)) set @w52WorkDays = @w52WorkDays + 1

end

--move to the next day

set @dt = dateadd(d,1,@dt);

--stop when at the end date

if(@dt>=@endDate) break;

end

return;

end

--TEST

select *

from [SIP_NPT_tfGetPayDays]('2013-1-1','2013-12-31','2009-12-18',1,15)

order by [Type],[Date]

<jco-blog/>

Copy data from source db to target db avoiding tables with specific field names (used for MIP COFA conversion work)

Creating out-of-band SQL backups

Make duplicated [Order] values distinct

“Renaming” SQL Server created from a template VM

SQL Server “Roach Motel” configuration

Windows firewall properties for Domain, Private, and Public profiles

Create the following Windows firewall rules to block outgoing SQL requests:

Create the following Windows firewall rules to allow incoming SQL requests:

Use group policy to prevent users other than domain admins from changing firewall settings.

SQL Elapsed Time

SQL Type Coercion

Miscellaneous stuff (that I can’t remember)

C#

SQL

Other

Consolidated Segment Code Column for MIP Transaction Tables

Calculating SemiMonthly24, BiWeekly26, and Weekly52 Pay Periods

Category Archives: SQL Server

Windows firewall properties for Domain, Private, and Public profiles

Create the following Windows firewall rules to block outgoing SQL requests:

Create the following Windows firewall rules to allow incoming SQL requests:

Use group policy to prevent users other than domain admins from changing firewall settings.

C#

SQL

Other