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 2015-3-2

1	originally posted 2015-3-2

Note that this doesn’t work so well with method name obfuscation!

using System.Reflection;
void Main()
{
  new Foo().DoSomething();
  Helper.GetOperationsAsString().Dump("Operations");
}

public class Foo
{
    public void DoSomething()
    {
        var mb = MethodBase.GetCurrentMethod();
        Helper.AppendOperation(mb, "something 1");
        DoSomethingElse();
        Helper.AppendOperation(mb, "something 2");
    }

    public void DoSomethingElse()
    {
        var mb = MethodBase.GetCurrentMethod();
        Helper.AppendOperation(mb, "something else 1");
    }
}

public static class Helper
{  
    public static List<string> Operations = new List<string>();

    public static string GetOperationsAsString()
    {
         return String.Join("\n", Operations.ToArray());
    }

    public static void AppendOperation(System.Reflection.MethodBase mb, string message = null, params object[] messageArgs)
    {
        if (mb != null)
        {
            string msg = string.Empty;
            if (!string.IsNullOrEmpty(message))
            {
                msg = " : " + string.Format(message, messageArgs);
            }
            Operations.Add(string.Format("{0}.{1}{2}", mb.DeclaringType.Name, mb.Name, msg));
        }
    }
}

using System.Reflection;

void Main()

{

new Foo().DoSomething();

Helper.GetOperationsAsString().Dump("Operations");

}

public class Foo

{

public void DoSomething()

{

var mb = MethodBase.GetCurrentMethod();

Helper.AppendOperation(mb, "something 1");

DoSomethingElse();

Helper.AppendOperation(mb, "something 2");

}

public void DoSomethingElse()

{

var mb = MethodBase.GetCurrentMethod();

Helper.AppendOperation(mb, "something else 1");

}

public static class Helper

{

public static List<string> Operations = new List<string>();

public static string GetOperationsAsString()

{

return String.Join("\n", Operations.ToArray());

}

public static void AppendOperation(System.Reflection.MethodBase mb, string message = null, params object[] messageArgs)

{

if (mb != null)

{

string msg = string.Empty;

if (!string.IsNullOrEmpty(message))

{

msg = " : " + string.Format(message, messageArgs);

}

Operations.Add(string.Format("{0}.{1}{2}", mb.DeclaringType.Name, mb.Name, msg));

}

Results
[Operations]
Foo.DoSomething : something 1
Foo.DoSomethingElse : something else 1
Foo.DoSomething : something 2

originally posted 2012-8-24

1	originally posted 2012-8-24

WCF throws System.ServiceModel.FaultExceptions (FE) between the client and server. The FE is a System.Exception so the client does not need to explicitly catch FaultException, but the FE is missing something. It does not take an InnerException in its constructor, so the server-side chain of exceptions and stack trace data are not available to the client. The typical response to this is “That’s the way it’s supposed to work!”, but if you own/control the consumers of your web service and you want the exception chain and stack trace data, then you’ll want to use FaultException.

The first thing is to decorate your service operations with the FaultContract attribute:

//service interface
public interface IService
{

 [OperationContract(IsOneWay = false)]
 [FaultContract(typeof(ExceptionDetail))]
 Foo GetFoo(Guid id);
}

//service interface

public interface IService

{

[OperationContract(IsOneWay = false)]

[FaultContract(typeof(ExceptionDetail))]

Foo GetFoo(Guid id);

}

By default, the service implementation will throw all unhandled exceptions back to the client as a regular FaultException. To avoid this, catch exceptions and re-throw them as FaultException as such…

//service implementation
public sealed partial class Service : IService
{
 Foo IService.GetFoo(Guid id)
        {
            try
            {
         
                return FooLib.GetFoo(id);
            }
            catch (Exception ex)
            {
                throw new FaultException<ExceptionDetail>(new ExceptionDetail(ex), ex.Message);
            }
        }
}

//service implementation

public sealed partial class Service : IService

{

Foo IService.GetFoo(Guid id)

{

try

{

return FooLib.GetFoo(id);

}

catch (Exception ex)

{

throw new FaultException<ExceptionDetail>(new ExceptionDetail(ex), ex.Message);

}

Great! All that detail is on its way to your client.

FaultException and FaultException<>; are derived from System.Exception, so when you don’t care about the detail, just catch Exception. When you want the detail, catch FaultException and inspect …

try
{
 ...
 ServiceReference.Foo foo = proxy.CreateFoo(fooId);

}
catch (FaultException<ExceptionDetail> fe)
{
 string message = fe.Message; //the equivalent of Exception.Message

 ExceptionDetail detail = fe.Detail; //the latest exception data
 string trace = detail.StackTrace; //stack info
 ExceptionDetail nextError = detail.InnerException; //exception chain

}
catch (Exception ex)
{
 ...
}

try

{

...

ServiceReference.Foo foo = proxy.CreateFoo(fooId);

}

catch (FaultException<ExceptionDetail> fe)

{

string message = fe.Message; //the equivalent of Exception.Message

ExceptionDetail detail = fe.Detail; //the latest exception data

string trace = detail.StackTrace; //stack info

ExceptionDetail nextError = detail.InnerException; //exception chain

}

catch (Exception ex)

{

...

}

An instance of ExceptionDetail stores a copy of the underlying Exception data as well as the Exception’s InnerException data, which is also converted to a ExceptionDetail, thus preserving the data of the Exception chain. This means that custom exception types thrown by the server cannot specifically be caught by the client. It also means that the client does not need to reference these exception types. However, the client can determine the type of exception thrown by the server…

The client wishes to detect the ABC.EmptyGuidException thrown by a library called by the service implementation.

//a library called by the service implementation
public static class FooLib
{
 public static GetFoo(Guid id)
 {
  if(Guid.Equals(Guid.Empty) throw new ABC.EmptyGuidException("No!")
  ...
 
 }
}
 
//the client 
try
{
 ...
 ServiceReference.Foo foo = proxy.CreateFoo(fooId);

}
catch (FaultException<ExceptionDetail> fe)
 {
 ExceptionDetail detail = fe.Detail;
 switch(detail.Type) //this is the type's FullName!
 {
  case "ABC.EmptyGuidException":
   //do something here
   break;
  ...
 }
}
catch (Exception ex)
{
 ...
}

//a library called by the service implementation

public static class FooLib

{

public static GetFoo(Guid id)

{

if(Guid.Equals(Guid.Empty) throw new ABC.EmptyGuidException("No!")

...

}

//the client

try

{

...

ServiceReference.Foo foo = proxy.CreateFoo(fooId);

}

catch (FaultException<ExceptionDetail> fe)

{

ExceptionDetail detail = fe.Detail;

switch(detail.Type) //this is the type's FullName!

{

case "ABC.EmptyGuidException":

//do something here

break;

...

}

catch (Exception ex)

{

...

}

Clunky, but attainable.

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]

originally posted 2013-4-1

1	originally posted 2013-4-1

select [Table] = '[' +[Name] + ']'
,[Columns] = '''' + left([Columns],len(columns)-1) + ''''
from
(
 select top 10000 
 t.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
 
 where t.type = 'U'
 order by t.name
) as f1
group by [Name],[Columns]

select [Table] = '[' +[Name] + ']'

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

from

(

select top 10000

t.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

where t.type = 'U'

order by t.name

) as f1

group by [Name],[Columns]

originally posted 2013-4-10

1	originally posted 2013-4-10

Thanks to Jacob Sebastian’s post, here’s some good info to assist in SQL 2012 script conversion…

http://beyondrelational.com/modules/2/blogs/28/Posts/15429/replacement-for-system-stored-procedure-spdboption-in-sql-server-2012-denali.aspx

originally posted 2013-4-19

1	originally posted 2013-4-19

Using setvar in T-SQL
Set the values of the two SQLCMD variables.

In this example, the script will only run the shrink if the ‘foo’ database has at least 100 MB to potentially free.

The script runs a shrink on each file of the database until the reduction value drops to zero.

Note: We use the “Simple” recovery mode for non-production databases, and ‘Full’ for those in production. This may affect how much the transaction log size can be reduced, specifically, the time since the last SQL backup may affect the trans-log’s shrinkability when the database is set to use ‘Full’ recovery mode.

--shrinks *ALL* files for a single database  
--that has a minimum MB of freeable space
--------------------------------------------
:setvar DbName "foo"
:setvar FreeAtLeastMB "100"

use [$(DbName)];   

declare @logname nvarchar(256)  
,@name nvarchar(256) 
,@sizeBefore int 
,@sizeAfter int  
,@reduction int  
,@totalReduction int  
,@rowcount int
,@iteration int      
,@msg nvarchar(1000)
,@originalSpaceFreeMB int
,@originalSizeMB int
,@newSpaceFreeMB int
,@newSizeMB int

set @msg = 'Working on $(DbName)';
raiserror (@msg, 0, 1) with nowait --print immediately

select 
@originalSpaceFreeMB = sum([SpaceFreeMB])
,@originalSizeMB = sum([SizeMB])
from 
(
 select 
 sf.[DbName]
 ,sf.[LogicalName]
 ,sf.[FileName]
 ,sf.[SizeMB]
 ,sf.[SpaceUsedMB]
 ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
 from
 (
  select 
  [DbName] = db_name()
  ,[LogicalName] = [Name]
  ,[Filename]
  ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))
  ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
  from sysfiles
 ) as sf
) as total

set @msg = '$(DbName) size is ' + convert(nvarchar(100),@originalSizeMB) + ' MB and has a potential of ' + convert(nvarchar(100),@originalSpaceFreeMB) + ' MB to free. Minimum freeable required is $(FreeAtLeastMB) MB.';
raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB > $(FreeAtLeastMB)) 
begin
 declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)   

 insert @tab  select [name], [physical_name], convert(int,null),convert(int,null)
 from sys.master_files 
 where [database_id] = db_id() 
 and [type] in (0,1)   

  --iterate files for the database  
 while(1=1)  
 begin   
  set @sizeBefore = 0;   
  set @sizeAfter = 0;   
  set @totalReduction = 0;  
  set @reduction = null;   
  set @iteration = 1; 
  set @name = null; 

  select top 1 @name = [name] 
  from @tab 
  where [reduction] is null;
  
  set @msg = 'Shrinking logical file ' + @name + ' of database ' + db_name();
  raiserror (@msg, 0, 1) with nowait
  
  if(@name is not null and @name <> '')   
  begin    
   --shrink the current file until the reduction is zero
   while(@reduction is null or @reduction > 0)    
   begin
    set @msg = 'shrink pass #' + convert(nvarchar(20),@iteration) + ' ...'; 
    raiserror (@msg, 0, 1) with nowait
       
    select top 1 @sizeBefore = [size] 
    from sysfiles 
    where fileid = file_id(@name);     
    
    dbcc shrinkfile (@name,20) with no_infomsgs;
    
    select top 1 @sizeAfter = [size] 
    from sysfiles 
    where fileid = file_id(@name); 
    
    set @reduction = @sizeBefore-@sizeAfter   ;  
    set @totalReduction = @totalReduction + @reduction;   
    set @iteration = @iteration + 1;
   end    
  
   update @tab 
   set [size] = ceiling((@sizeAfter * 8.) / 1024.)
   ,[reduction] =  ceiling((@totalReduction * 8.) / 1024.)
   where [name] = @name; 
  end 
  else break; 
 end   

 select 
 [Logical Name] = [name] 
 ,[File] = [filename] 
 ,[Reduction MB] = reduction 
 from @tab   
 order by [name];  
 
 select 
 @newSpaceFreeMB = sum([SpaceFreeMB])
 ,@newSizeMB = sum([SizeMB])
 from 
 (
  select 
  sf.[DbName]
  ,sf.[LogicalName]
  ,sf.[FileName]
  ,sf.[SizeMB]
  ,sf.[SpaceUsedMB]
  ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
  from
  (
   select 
   [DbName] = db_name()
   ,[LogicalName] = [Name]
   ,[Filename]
   ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))
   ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
   from sysfiles
  ) as sf
 ) as total
 
 select 
 [OriginalSizeMB] =  @originalSizeMB
 ,[OriginalSpaceFreeMB] = @originalSpaceFreeMB
 ,[NewSizeMB] = @newSizeMB
 ,[NewSpaceFreeMB] = @newSpaceFreeMB
 ,[ReductionMB] = @originalSizeMB-@newSizeMB
end
else
begin
 set @msg = 'Skipping shrink b/c $(DbName) does not have the requisite $(FreeAtLeastMB) MB free.';
 raiserror (@msg, 0, 1) with nowait
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

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

--shrinks *ALL* files for a single database

--that has a minimum MB of freeable space

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

:setvar DbName "foo"

:setvar FreeAtLeastMB "100"

use [$(DbName)];

declare @logname nvarchar(256)

,@name nvarchar(256)

,@sizeBefore int

,@sizeAfter int

,@reduction int

,@totalReduction int

,@rowcount int

,@iteration int

,@msg nvarchar(1000)

,@originalSpaceFreeMB int

,@originalSizeMB int

,@newSpaceFreeMB int

,@newSizeMB int

set @msg = 'Working on $(DbName)';

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

select

@originalSpaceFreeMB = sum([SpaceFreeMB])

,@originalSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

set @msg = '$(DbName) size is ' + convert(nvarchar(100),@originalSizeMB) + ' MB and has a potential of ' + convert(nvarchar(100),@originalSpaceFreeMB) + ' MB to free. Minimum freeable required is $(FreeAtLeastMB) MB.';

raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB > $(FreeAtLeastMB))

begin

declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)

insert @tab select [name], [physical_name], convert(int,null),convert(int,null)

from sys.master_files

where [database_id] = db_id()

and [type] in (0,1)

--iterate files for the database

while(1=1)

begin

set @sizeBefore = 0;

set @sizeAfter = 0;

set @totalReduction = 0;

set @reduction = null;

set @iteration = 1;

set @name = null;

select top 1 @name = [name]

from @tab

where [reduction] is null;

set @msg = 'Shrinking logical file ' + @name + ' of database ' + db_name();

raiserror (@msg, 0, 1) with nowait

if(@name is not null and @name <> '')

begin

--shrink the current file until the reduction is zero

while(@reduction is null or @reduction > 0)

begin

set @msg = 'shrink pass #' + convert(nvarchar(20),@iteration) + ' ...';

raiserror (@msg, 0, 1) with nowait

select top 1 @sizeBefore = [size]

from sysfiles

where fileid = file_id(@name);

dbcc shrinkfile (@name,20) with no_infomsgs;

select top 1 @sizeAfter = [size]

from sysfiles

where fileid = file_id(@name);

set @reduction = @sizeBefore-@sizeAfter ;

set @totalReduction = @totalReduction + @reduction;

set @iteration = @iteration + 1;

end

update @tab

set [size] = ceiling((@sizeAfter * 8.) / 1024.)

,[reduction] = ceiling((@totalReduction * 8.) / 1024.)

where [name] = @name;

end

else break;

end

select

[Logical Name] = [name]

,[File] = [filename]

,[Reduction MB] = reduction

from @tab

order by [name];

select

@newSpaceFreeMB = sum([SpaceFreeMB])

,@newSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

select

[OriginalSizeMB] = @originalSizeMB

,[OriginalSpaceFreeMB] = @originalSpaceFreeMB

,[NewSizeMB] = @newSizeMB

,[NewSpaceFreeMB] = @newSpaceFreeMB

,[ReductionMB] = @originalSizeMB-@newSizeMB

end

else

begin

set @msg = 'Skipping shrink b/c $(DbName) does not have the requisite $(FreeAtLeastMB) MB free.';

raiserror (@msg, 0, 1) with nowait

end

Here’s the same thing as a stored procedure.

use [master];
create procedure spShrinkDatabase(@dbname nvarchar(255), @freeAtLeastMB int)
as
begin
	--------------------------------------------
	--shrinks *ALL* files for a single database  
	--that has a minimum MB of freeable space
	--------------------------------------------
	if(@dbname is null or len(@dbname)=0) 
	begin
		raiserror ('No database name specified.', 0, 1) with nowait --print immediately
		return;
	end

	if(@freeAtLeastMB is null or @freeAtLeastMB <= 0) set @freeAtLeastMB = 1;
	declare @freeAtLeastMBStr nvarchar(20);
        set = @freeAtLeastMBStr = convert(nvarchar(20),@freeAtLeastMB);

	declare @sql nvarchar(max);
	set @sql = 'use [' +  @dbname + '];   
	declare @logname nvarchar(256)  
	,@name nvarchar(256) 
	,@sizeBefore int 
	,@sizeAfter int  
	,@reduction int  
	,@totalReduction int  
	,@rowcount int
	,@iteration int      
	,@msg nvarchar(1000)
	,@originalSpaceFreeMB int
	,@originalSizeMB int
	,@newSpaceFreeMB int
	,@newSizeMB int

	set @msg = ''Working on ' +  @dbname + ''';
	raiserror (@msg, 0, 1) with nowait --print immediately

	select 
	@originalSpaceFreeMB = sum([SpaceFreeMB])
	,@originalSizeMB = sum([SizeMB])
	from 
	(
	 select 
	 sf.[DbName]
	 ,sf.[LogicalName]
	 ,sf.[FileName]
	 ,sf.[SizeMB]
	 ,sf.[SpaceUsedMB]
	 ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
	 from
	 (
	  select 
	  [DbName] = db_name()
	  ,[LogicalName] = [Name]
	  ,[Filename]
	  ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))
	  ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
	  from sysfiles
	 ) as sf
	) as total

	set @msg = ''' +  @dbname + ' size is '' + convert(nvarchar(100),@originalSizeMB) + '' MB and has a potential of '' + convert(nvarchar(100),@originalSpaceFreeMB) + '' MB to free. Minimum freeable required is ' +  @freeAtLeastMBStr + ' MB.'';
	raiserror (@msg, 0, 1) with nowait

	if(@originalSpaceFreeMB >= ' + @freeAtLeastMBStr + ') 
	begin
	 declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)   

	 insert @tab  select [name], [physical_name], convert(int,null),convert(int,null)
	 from sys.master_files 
	 where [database_id] = db_id() 
	 and [type] in (0,1)   

	  --iterate files for the database  
	 while(1=1)  
	 begin   
	  set @sizeBefore = 0;   
	  set @sizeAfter = 0;   
	  set @totalReduction = 0;  
	  set @reduction = null;   
	  set @iteration = 1; 
	  set @name = null; 

	  select top 1 @name = [name] 
	  from @tab 
	  where [reduction] is null;
  
	  set @msg = ''Shrinking logical file '' + @name + '' of database '' + db_name();
	  raiserror (@msg, 0, 1) with nowait
  
	  if(@name is not null and @name <> '''')   
	  begin    
	   --shrink the current file until the reduction is zero
	   while(@reduction is null or @reduction > 0)    
	   begin
		set @msg = ''shrink pass #'' + convert(nvarchar(20),@iteration) + '' ...''; 
		raiserror (@msg, 0, 1) with nowait
       
		select top 1 @sizeBefore = [size] 
		from sysfiles 
		where fileid = file_id(@name);     
    
		dbcc shrinkfile (@name,20) with no_infomsgs;
    
		select top 1 @sizeAfter = [size] 
		from sysfiles 
		where fileid = file_id(@name); 
    
		set @reduction = @sizeBefore-@sizeAfter   ;  
		set @totalReduction = @totalReduction + @reduction;   
		set @iteration = @iteration + 1;
	   end    
  
	   update @tab 
	   set [size] = ceiling((@sizeAfter * 8.) / 1024.)
	   ,[reduction] =  ceiling((@totalReduction * 8.) / 1024.)
	   where [name] = @name; 
	  end 
	  else break; 
	 end   

	 select 
	 [Logical Name] = [name] 
	 ,[File] = [filename] 
	 ,[Reduction MB] = reduction 
	 from @tab   
	 order by [name];  
 
	 select 
	 @newSpaceFreeMB = sum([SpaceFreeMB])
	 ,@newSizeMB = sum([SizeMB])
	 from 
	 (
	  select 
	  sf.[DbName]
	  ,sf.[LogicalName]
	  ,sf.[FileName]
	  ,sf.[SizeMB]
	  ,sf.[SpaceUsedMB]
	  ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
	  from
	  (
	   select 
	   [DbName] = db_name()
	   ,[LogicalName] = [Name]
	   ,[Filename]
	   ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))
	   ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
	   from sysfiles
	  ) as sf
	 ) as total
 
	 select 
	 [OriginalSizeMB] =  @originalSizeMB
	 ,[OriginalSpaceFreeMB] = @originalSpaceFreeMB
	 ,[NewSizeMB] = @newSizeMB
	 ,[NewSpaceFreeMB] = @newSpaceFreeMB
	 ,[ReductionMB] = @originalSizeMB-@newSizeMB
	end
	else
	begin
	 set @msg = ''Skipping shrink b/c ' +  @dbname + ' does not have the requisite ' + @freeAtLeastMBStr + ' MB free.'';
	 raiserror (@msg, 0, 1) with nowait
	end';

	--select @sql
	exec(@sql);
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

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

use [master];

create procedure spShrinkDatabase(@dbname nvarchar(255), @freeAtLeastMB int)

begin

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

--shrinks *ALL* files for a single database

--that has a minimum MB of freeable space

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

if(@dbname is null or len(@dbname)=0)

begin

raiserror ('No database name specified.', 0, 1) with nowait --print immediately

return;

end

if(@freeAtLeastMB is null or @freeAtLeastMB <= 0) set @freeAtLeastMB = 1;

declare @freeAtLeastMBStr nvarchar(20);

set = @freeAtLeastMBStr = convert(nvarchar(20),@freeAtLeastMB);

declare @sql nvarchar(max);

set @sql = 'use [' + @dbname + '];

declare @logname nvarchar(256)

,@name nvarchar(256)

,@sizeBefore int

,@sizeAfter int

,@reduction int

,@totalReduction int

,@rowcount int

,@iteration int

,@msg nvarchar(1000)

,@originalSpaceFreeMB int

,@originalSizeMB int

,@newSpaceFreeMB int

,@newSizeMB int

set @msg = ''Working on ' + @dbname + ''';

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

select

@originalSpaceFreeMB = sum([SpaceFreeMB])

,@originalSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

set @msg = ''' + @dbname + ' size is '' + convert(nvarchar(100),@originalSizeMB) + '' MB and has a potential of '' + convert(nvarchar(100),@originalSpaceFreeMB) + '' MB to free. Minimum freeable required is ' + @freeAtLeastMBStr + ' MB.'';

raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB >= ' + @freeAtLeastMBStr + ')

begin

declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)

insert @tab select [name], [physical_name], convert(int,null),convert(int,null)

from sys.master_files

where [database_id] = db_id()

and [type] in (0,1)

--iterate files for the database

while(1=1)

begin

set @sizeBefore = 0;

set @sizeAfter = 0;

set @totalReduction = 0;

set @reduction = null;

set @iteration = 1;

set @name = null;

select top 1 @name = [name]

from @tab

where [reduction] is null;

set @msg = ''Shrinking logical file '' + @name + '' of database '' + db_name();

raiserror (@msg, 0, 1) with nowait

if(@name is not null and @name <> '''')

begin

--shrink the current file until the reduction is zero

while(@reduction is null or @reduction > 0)

begin

set @msg = ''shrink pass #'' + convert(nvarchar(20),@iteration) + '' ...'';

raiserror (@msg, 0, 1) with nowait

select top 1 @sizeBefore = [size]

from sysfiles

where fileid = file_id(@name);

dbcc shrinkfile (@name,20) with no_infomsgs;

select top 1 @sizeAfter = [size]

from sysfiles

where fileid = file_id(@name);

set @reduction = @sizeBefore-@sizeAfter ;

set @totalReduction = @totalReduction + @reduction;

set @iteration = @iteration + 1;

end

update @tab

set [size] = ceiling((@sizeAfter * 8.) / 1024.)

,[reduction] = ceiling((@totalReduction * 8.) / 1024.)

where [name] = @name;

end

else break;

end

select

[Logical Name] = [name]

,[File] = [filename]

,[Reduction MB] = reduction

from @tab

order by [name];

select

@newSpaceFreeMB = sum([SpaceFreeMB])

,@newSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

select

[OriginalSizeMB] = @originalSizeMB

,[OriginalSpaceFreeMB] = @originalSpaceFreeMB

,[NewSizeMB] = @newSizeMB

,[NewSpaceFreeMB] = @newSpaceFreeMB

,[ReductionMB] = @originalSizeMB-@newSizeMB

end

else

begin

set @msg = ''Skipping shrink b/c ' + @dbname + ' does not have the requisite ' + @freeAtLeastMBStr + ' MB free.'';

raiserror (@msg, 0, 1) with nowait

end';

--select @sql

exec(@sql);

end

originally posted 2013-4-3

1	originally posted 2013-4-3

Store hierachical organization data
All tables in a multi-tenant database would have a [OrganizationName] field.
The Organization table stores the hierarchical relationships for each organization.
SQL Server 2008+ supports the hierarchyID data type (not used here), which would make some of the hierarchical manipulations easier. I needed to support SQL Server 2005 as well, so I didn’t have the pleasure.

CREATE TABLE [dbo].[Organization](
 [Name] [nvarchar](100) NOT NULL,
 [DisplayName] [nvarchar](100) NULL,
 [ParentName] [nvarchar](100) NULL,
 [Enabled] [bit] NOT NULL,
 CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED 
(
 [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]
GO

--get the hierarchical path for an organization
create function [dbo].[fGetOrganizationPath] (@organizationName nvarchar(100))
returns nvarchar(max)
as
begin
 declare @retVal nvarchar(max);
 set @retVal = N''

 select
 @retVal = 
 (
  select
   case when [OrganizationName] &lt;&gt; 'root' then N'/' + [OrganizationName] 
   else '' 
   end
  from [dbo].tfGetOrganizationNames_Ascend(@organizationName)
  for xml path(''), type, root).value('root[1]','nvarchar(max)'
 )
 return @retVal;
end
GO

--get parent organization names
create function [dbo].[tfGetOrganizationNames_Ascend] 
(
 @organizationName nvarchar(100)
)
returns @tab table ([OrganizationName] nvarchar(100), ParentNamespace nvarchar(100), Level int)
as
begin
 with c as (
 select
  n1.[OrganizationName]
  ,n1.[ParentOrganizationName]
  ,[Level] = 0
 from
  [dbo].[OrganizationName] n1
  left outer join [dbo].[OrganizationName] n2 on n1.[ParentOrganizationName] = n2.[OrganizationName] 
 where
  n1.[OrganizationName] = @organizationName
 union all
 select
  n1.[OrganizationName]
  ,n1.[ParentOrganizationName]
  ,[Level] = c.[Level] + 1
 from
  [dbo].[OrganizationName] n1
  inner join c on c.[ParentOrganizationName] = n1.[OrganizationName])
  
 insert @tab 
 select top 1000
  [OrganizationName]
  ,[ParentOrganizationName]
  ,[Level]
 from
  c
 order by
  [Level] desc
 option(maxrecursion 1000)
 
 return;
end
GO

--generic error handler
create procedure [dbo].[spHandleError] as
begin
 declare @errmsg nvarchar(2048)
 declare @severity tinyint
 declare @state tinyint
 declare @errno int
 declare @proc sysname
 declare @lineno int
            
 select @errmsg = error_message(), @severity = error_severity()
 ,@state  = error_state(), @errno = error_number()
 ,@proc   = error_procedure(), @lineno = error_line()
        
 IF @errmsg NOT LIKE '[[%]]'
 begin 
  select @errmsg = @errmsg + ' [' + isnull(quotename(@proc),'') + '] ' 
  raiserror(@errmsg, @severity, @state)
 end
 else raiserror(@errmsg, @severity, @state)
end
GO

--trigger to make sure organizations added to the Organization table meet the requirements
create trigger [dbo].[tCheckOrganizationName]
on [dbo].[Organization]
after insert,update
as
 begin try
  set nocount on
  set xact_abort on 
  declare @root nvarchar(10);
  set @root = 'root'; 
  
  --verifies new organization names against requirements prior to insert/update

  declare @tab table ([Name] nvarchar(100), [ParentName] nvarchar(100))
  declare @dummyTab table ([Name] nvarchar(100), [ParentName] nvarchar(100))
  
  --combine the existing and pending data. pending data for a organization name is used if the organization name is also found in existing data
  insert @tab
  select [Name],[ParentName] from inserted
  union 
  select [Name],[ParentName] from [dbo].[Name] where [Name] not in (select [Name] from inserted)
  
  --a non-root organization name must have a non-null parent organization name
  if exists (select [Name] from inserted where [Name] &lt;&gt; @root and [ParentName] is null)
  begin
   --error: non-root organization name requires a parent
   raiserror ('A non-root organization name must specify a parent organization name.',11,1)
  end 
  
  --the ROOT organization name must have a null parent organization name
  if exists (select [Name] from inserted where [Name] = @root and [ParentName] is not null)
  begin
   --error: root organization name cannot have a parent
   raiserror ('The root organization name must specify a null parent organization name.',11,1)
  end 
  
     --a organization name's parent organization name must exist
     if exists
     ( 
   select t2.[Name] 
   from @tab t 
   left outer join @tab t2 on t.[ParentName] = t2.[Name]
   where t.[ParentName] is not null and t2.[Name] is null
  )
  begin
   --error: non-existent parent specified
   raiserror ('A organization name specifies a parent organization name that does not exist.',11,1)
  end      
  
  --look for circular references in the pending hierarchy
  begin try
   declare @dummy bit; --trick the sql compiler. it wants a statement that ends with a ';' prior to the 'with' statement.

   with c as 
   (
    select distinct
     n1.[Name]
     ,n1.[ParentName]
    from
     @tab n1
     left outer join @tab n2 on n1.[ParentName] = n2.[Name] 
     
    union all
    
    select 
     n1.[Name]
     ,n1.[ParentName]
    from
     @tab n1
     inner join c on c.[ParentName] = n1.[Name]
   )
   
   insert @dummyTab
   select distinct *
   from c
   option(maxrecursion 1000) --this will throw an exception if circular references exist
   
  end try
  begin catch
   --error: cicular reference(s) found
   IF error_number() = 530 raiserror ('Operation would cause an illegal circular reference in organization name',11,1)
   else exec spHandleError
  end catch

 end try
 begin catch
  exec spHandleError
 end catch
GO
 
--called by the application when it creates a new SqlConnection
--the context travels with the connection for its lifetime.
--The context can be inspected by using T-SQL or C#.
create procedure [dbo].[spSetContext](@context nvarchar(100)) as
begin
 set nocount on
 declare @ctx varbinary(128)

 if([dbo].fIsNullOrWhitespace(@context) = 0) 
 begin
  
  declare @c varchar(127)
  set @c = @context
  declare @length tinyint 
  set @length = len(@c)
  select @ctx = convert(binary(1), @length) + convert(varbinary(127), @c)
 end
 else set @ctx = convert(binary(1), 0)
 set context_info @ctx
end
GO

--retrieve the context set on the current database connection
create function [dbo].[fGetContext]()
returns nvarchar(100) as 
begin
 declare @context nvarchar(100)
 set @context = null
 declare @ctx varbinary(128) 
 set @ctx = CONTEXT_INFO();
 declare @length tinyint 
 set @length = convert(tinyint, substring(@ctx, 1, 1))
 if(@length &gt; 0) select @context = convert(varchar(127), substring(@ctx, 2, 1 + @length))
 return @context
end
GO

--example:
--if the connection was created with the proper context (an organization name)
--a view can take advantage of the framework to select data for the current organization, 
--including data for its child organizations.
--This effectively creates a consolidated view of the data for a mult-tenant schema (one that stores a OrganizationName in each table)

create view [dbo].[v_SomeTable] as
select * 
from [npt_system32].[system].[tfGetOrganizationNamesForContext]() o
join [dbo].[SomeTable] t on o.[Organization] = t.[Organization]

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

CREATE TABLE [dbo].[Organization](

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

[DisplayName] [nvarchar](100) NULL,

[ParentName] [nvarchar](100) NULL,

[Enabled] [bit] NOT NULL,

CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED

(

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

--get the hierarchical path for an organization

create function [dbo].[fGetOrganizationPath] (@organizationName nvarchar(100))

returns nvarchar(max)

begin

declare @retVal nvarchar(max);

set @retVal = N''

select

@retVal =

(

select

case when [OrganizationName] <> 'root' then N'/' + [OrganizationName]

else ''

end

from [dbo].tfGetOrganizationNames_Ascend(@organizationName)

for xml path(''), type, root).value('root[1]','nvarchar(max)'

)

return @retVal;

end

--get parent organization names

create function [dbo].[tfGetOrganizationNames_Ascend]

(

@organizationName nvarchar(100)

)

returns @tab table ([OrganizationName] nvarchar(100), ParentNamespace nvarchar(100), Level int)

begin

with c as (

select

n1.[OrganizationName]

,n1.[ParentOrganizationName]

,[Level] = 0

from

[dbo].[OrganizationName] n1

left outer join [dbo].[OrganizationName] n2 on n1.[ParentOrganizationName] = n2.[OrganizationName]

where

n1.[OrganizationName] = @organizationName

union all

select

n1.[OrganizationName]

,n1.[ParentOrganizationName]

,[Level] = c.[Level] + 1

from

[dbo].[OrganizationName] n1

inner join c on c.[ParentOrganizationName] = n1.[OrganizationName])

insert @tab

select top 1000

[OrganizationName]

,[ParentOrganizationName]

,[Level]

from

order by

[Level] desc

option(maxrecursion 1000)

return;

end

--generic error handler

create procedure [dbo].[spHandleError] as

begin

declare @errmsg nvarchar(2048)

declare @severity tinyint

declare @state tinyint

declare @errno int

declare @proc sysname

declare @lineno int

select @errmsg = error_message(), @severity = error_severity()

,@state = error_state(), @errno = error_number()

,@proc = error_procedure(), @lineno = error_line()

IF @errmsg NOT LIKE '[[%]]'

begin

select @errmsg = @errmsg + ' [' + isnull(quotename(@proc),'') + '] '

raiserror(@errmsg, @severity, @state)

end

else raiserror(@errmsg, @severity, @state)

end

--trigger to make sure organizations added to the Organization table meet the requirements

create trigger [dbo].[tCheckOrganizationName]

on [dbo].[Organization]

after insert,update

begin try

set nocount on

set xact_abort on

declare @root nvarchar(10);

set @root = 'root';

--verifies new organization names against requirements prior to insert/update

declare @tab table ([Name] nvarchar(100), [ParentName] nvarchar(100))

declare @dummyTab table ([Name] nvarchar(100), [ParentName] nvarchar(100))

--combine the existing and pending data. pending data for a organization name is used if the organization name is also found in existing data

insert @tab

select [Name],[ParentName] from inserted

union

select [Name],[ParentName] from [dbo].[Name] where [Name] not in (select [Name] from inserted)

--a non-root organization name must have a non-null parent organization name

if exists (select [Name] from inserted where [Name] <> @root and [ParentName] is null)

begin

--error: non-root organization name requires a parent

raiserror ('A non-root organization name must specify a parent organization name.',11,1)

end

--the ROOT organization name must have a null parent organization name

if exists (select [Name] from inserted where [Name] = @root and [ParentName] is not null)

begin

--error: root organization name cannot have a parent

raiserror ('The root organization name must specify a null parent organization name.',11,1)

end

--a organization name's parent organization name must exist

if exists

(

select t2.[Name]

from @tab t

left outer join @tab t2 on t.[ParentName] = t2.[Name]

where t.[ParentName] is not null and t2.[Name] is null

)

begin

--error: non-existent parent specified

raiserror ('A organization name specifies a parent organization name that does not exist.',11,1)

end

--look for circular references in the pending hierarchy

begin try

declare @dummy bit; --trick the sql compiler. it wants a statement that ends with a ';' prior to the 'with' statement.

with c as

(

select distinct

n1.[Name]

,n1.[ParentName]

from

@tab n1

left outer join @tab n2 on n1.[ParentName] = n2.[Name]

union all

select

n1.[Name]

,n1.[ParentName]

from

@tab n1

inner join c on c.[ParentName] = n1.[Name]

)

insert @dummyTab

select distinct *

from c

option(maxrecursion 1000) --this will throw an exception if circular references exist

end try

begin catch

--error: cicular reference(s) found

IF error_number() = 530 raiserror ('Operation would cause an illegal circular reference in organization name',11,1)

else exec spHandleError

end catch

end try

begin catch

exec spHandleError

end catch

--called by the application when it creates a new SqlConnection

--the context travels with the connection for its lifetime.

--The context can be inspected by using T-SQL or C#.

create procedure [dbo].[spSetContext](@context nvarchar(100)) as

begin

set nocount on

declare @ctx varbinary(128)

if([dbo].fIsNullOrWhitespace(@context) = 0)

begin

declare @c varchar(127)

set @c = @context

declare @length tinyint

set @length = len(@c)

select @ctx = convert(binary(1), @length) + convert(varbinary(127), @c)

end

else set @ctx = convert(binary(1), 0)

set context_info @ctx

end

--retrieve the context set on the current database connection

create function [dbo].[fGetContext]()

returns nvarchar(100) as

begin

declare @context nvarchar(100)

set @context = null

declare @ctx varbinary(128)

set @ctx = CONTEXT_INFO();

declare @length tinyint

set @length = convert(tinyint, substring(@ctx, 1, 1))

if(@length > 0) select @context = convert(varchar(127), substring(@ctx, 2, 1 + @length))

return @context

end

--example:

--if the connection was created with the proper context (an organization name)

--a view can take advantage of the framework to select data for the current organization,

--including data for its child organizations.

--This effectively creates a consolidated view of the data for a mult-tenant schema (one that stores a OrganizationName in each table)

create view [dbo].[v_SomeTable] as

select *

from [npt_system32].[system].[tfGetOrganizationNamesForContext]() o

join [dbo].[SomeTable] t on o.[Organization] = t.[Organization]

[C#] version of [dbo].[spSetContext]

protected void SetSqlContext(SqlConnection connection)
{
    const string sql = @"set context_info @ctx;"; //if not exists(select [Name] from (select [name] = dbo.fgetcontext()) as f1 where [Name] = '" + ns + "') raiserror('context not set!',11,1)";
    string ns = Namespace.Id;
    byte[] bytes = null;
    if (!string.IsNullOrWhiteSpace(ns))
    {
        //set the context
        //encode the namespace into a byte array
        var encoding = new System.Text.ASCIIEncoding();
        byte[] tempbytes = encoding.GetBytes(ns);
        bytes = new byte[tempbytes.Length + 1];
        tempbytes.CopyTo(bytes, 1);
        bytes[0] = Convert.ToByte(ns.Length);
    }
    else bytes = new byte[0]; //clear the context
            
    if (connection == null) return;
    SqlCommand command = GetNewSqlCommand(connection);
    command.CommandType = System.Data.CommandType.Text;
    command.CommandText = sql;
    var p1 = command.Parameters.Add("@ctx", System.Data.SqlDbType.VarBinary, 128);
    p1.Value = bytes;
    command.ExecuteNonQuery();
}

protected void SetSqlContext(SqlConnection connection)

{

const string sql = @"set context_info @ctx;"; //if not exists(select [Name] from (select [name] = dbo.fgetcontext()) as f1 where [Name] = '" + ns + "') raiserror('context not set!',11,1)";

string ns = Namespace.Id;

byte[] bytes = null;

if (!string.IsNullOrWhiteSpace(ns))

{

//set the context

//encode the namespace into a byte array

var encoding = new System.Text.ASCIIEncoding();

byte[] tempbytes = encoding.GetBytes(ns);

bytes = new byte[tempbytes.Length + 1];

tempbytes.CopyTo(bytes, 1);

bytes[0] = Convert.ToByte(ns.Length);

}

else bytes = new byte[0]; //clear the context

if (connection == null) return;

SqlCommand command = GetNewSqlCommand(connection);

command.CommandType = System.Data.CommandType.Text;

command.CommandText = sql;

var p1 = command.Parameters.Add("@ctx", System.Data.SqlDbType.VarBinary, 128);

p1.Value = bytes;

command.ExecuteNonQuery();

}

<jco-blog/>

SQL Type Coercion

Miscellaneous stuff (that I can’t remember)

C#

SQL

Other

C# Current Class.Method Logging

WCF Client-Side Exceptions (getting full service-side exception detail)

Consolidated Segment Code Column for MIP Transaction Tables

Calculating SemiMonthly24, BiWeekly26, and Weekly52 Pay Periods

Get delimited list of column names for each table in a database

SQL 2012 Replacement of sp_dboption with ALTER DATABASE

Shrink All Physical Files of a Database

Multi-Tenant Hierachical Framework w/ SQL Server

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

C#

SQL

Other