ЈЦЅГЇП €ΘΘΚ

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

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

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

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

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

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

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

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

         select * from #NoCodeTables

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

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

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

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

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

                                         exec (@sql);
                                 end

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

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

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

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

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

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

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

 end catch

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

RUN THIS SCRIPT FROM THE *TARGET* DATABASE

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

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

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

use [MyTargetDatabaseHere];

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

declare @deleteTargetData bit = 1;

declare @message nvarchar(max) = null;

begin try

--disable triggers and constraints for target db

exec sp_MSforeachtable 'alter table ? disable trigger all;

alter table ? nocheck constraint all;'

--create results temp table

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

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

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

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

select

[Table]

,[Schema]

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

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

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

into #Tables

from

(

select top 10000

[Table] = t.name

,[TableObjectId] = t.object_id

,[Schema] = s.[Name]

,[Columns] = substring(

(

select

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

from sys.columns c

where t.object_id = c.object_id

order by c.[Name]

for xml path('')

)

,1,8000)

from sys.tables t

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

where t.type = 'U'

order by t.name

) as f1

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

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

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

order by [Schema],[Table]

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

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

select *

into #NoCodeTables

from #Tables

where [HasCodes] = 0

select * from #NoCodeTables

declare @table nvarchar(255)

declare @schema nvarchar(255)

declare @schemaAndTable nvarchar(1000)

declare @columns nvarchar(max)

declare @hasIdentity bit

declare @sql nvarchar(max)

--iterate the #NoCodeTables rows and do the copying

while(1=1)

begin

set @table = null;

set @sql = null;

set @message = null;

--get a row to process from #NoCodeTables

select top 1

@table = [Table]

,@schema = [Schema]

,@columns = [Columns]

,@hasIdentity = [HasIdentity]

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

from #NoCodeTables

order by [Table],[Schema]

if(@table is not null)

begin

begin try

--optionally delete the target table data

if(@deleteTargetData = 1)

begin

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

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

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

set @sql = 'begin try

truncate table ' + @schemaAndTable + ';

end try

begin catch

delete ' + @schemaAndTable + ';

end catch';

exec (@sql);

end

--copy from source to target table

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

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

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

set @sql =

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

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

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

--print @sql

exec(@sql);

--success! set messages

set @message = @schemaAndTable + ' done.'

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

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

end try

begin catch

--fail. set message

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

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

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

end catch

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

delete from #NoCodeTables where [Table] = @table

end

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

end

--display results

select *

from #Results

order by [Name]

end try

begin catch

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

set @message = error_message()

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

--enable triggers and constraints for target db

exec sp_MSforeachtable 'alter table ? enable trigger all;

alter table ? check constraint all;'

end catch

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

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

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

(This is a C# LinqPad script)

Using setvar in T-SQL

Finds the SQL Command Variable definitions in a SQL script and parses their names and values.
Finds the SQL Command Variable references and parses their names.
Determines which references have no definitions.
Determines which definitions have no references.

The definition names and reference names are case insensitive.
A definition is in the form of :setvar name “value”
A reference is in the form of $(name)

void Main()
{
	//sql file path
	string file = @"C:\temp\my.sql";
	
	//Token definition pattern for :setvar name "value"
	string tokenDefPattern = @"(\:setvar [^""]* ""[^""]*"")";
	string tokenDefNamePattern = @"\:setvar ([^ ""]*)";
	string tokenDefValuePattern = @"""([^""]*)""";	
	
	//Token reference pattern for $(name)
	string tokenRefPattern = @"(\$\([^)]*\))";
	string tokenRefNamePattern = @"\$\(([^)]*)\)";
	
	string text = System.IO.File.ReadAllText(file);
	
	var tokenDefs = Token.GetAllFirstCapturedMatches(text,tokenDefPattern).Distinct().ToDictionary(x=>x,y=>new Token(y,tokenDefNamePattern,tokenDefValuePattern));
	var tokenRefs = Token.GetAllFirstCapturedMatches(text,tokenRefPattern).Distinct().ToDictionary(x=>x,y=>new Token(y,tokenRefNamePattern,null));
	
	tokenDefs.Select(x=>x.Value).OrderBy (x => x.Name).ToList().Dump("Token Definitions");
	tokenDefs.Where(x=> !tokenRefs.Any(y => y.Value.Name == x.Value.Name)).Select(x=>x.Value).OrderBy(x => x.Name).ToList().Dump("Token definitions without a reference");
	tokenRefs.Where(x=> !tokenDefs.Any(y => y.Value.Name == x.Value.Name)).Select(x=>x.Value).OrderBy(x => x.Name).ToList().Dump("Token references without a definition");
}

public class Token
{
	public Token(){}
	
	public Token(string Raw, string namePattern, string  valuePattern)
	{
		this.Raw = Raw;
		if(!string.IsNullOrWhiteSpace(namePattern)) Name = Token.GetFirstCapturedMatch(Raw,namePattern);
		if(!string.IsNullOrWhiteSpace(valuePattern)) Value = Token.GetFirstCapturedMatch(Raw,valuePattern);
	}

	public string Name {get;set;}
	public string Value {get;set;}
	public string Raw {get;set;}
	
	public static string GetFirstCapturedMatch(string value, string pattern)
	{
		var match = Regex.Match(value,pattern);
		if(match!=null && match.Success && match.Groups.Count>1)
		{
			return match.Groups[1].Value;
		}
		return null;
	}
	
	public static List<string> GetAllFirstCapturedMatches(string value, string pattern)
	{
		var list = new List<string>();
		var matches = Regex.Matches(value,pattern);
		foreach(Match match in matches)
		{
			if(match!=null && match.Success && match.Groups.Count>1) 
			{
				list.Add(match.Groups[1].Value);
			}
		}
		return list;
	}
}

void Main()

{

//sql file path

string file = @"C:\temp\my.sql";

//Token definition pattern for :setvar name "value"

string tokenDefPattern = @"(\:setvar [^""]* ""[^""]*"")";

string tokenDefNamePattern = @"\:setvar ([^ ""]*)";

string tokenDefValuePattern = @"""([^""]*)""";

//Token reference pattern for $(name)

string tokenRefPattern = @"(\$$[^)]*$)";

string tokenRefNamePattern = @"\$$([^)]*)$";

string text = System.IO.File.ReadAllText(file);

var tokenDefs = Token.GetAllFirstCapturedMatches(text,tokenDefPattern).Distinct().ToDictionary(x=>x,y=>new Token(y,tokenDefNamePattern,tokenDefValuePattern));

var tokenRefs = Token.GetAllFirstCapturedMatches(text,tokenRefPattern).Distinct().ToDictionary(x=>x,y=>new Token(y,tokenRefNamePattern,null));

tokenDefs.Select(x=>x.Value).OrderBy (x => x.Name).ToList().Dump("Token Definitions");

tokenDefs.Where(x=> !tokenRefs.Any(y => y.Value.Name == x.Value.Name)).Select(x=>x.Value).OrderBy(x => x.Name).ToList().Dump("Token definitions without a reference");

tokenRefs.Where(x=> !tokenDefs.Any(y => y.Value.Name == x.Value.Name)).Select(x=>x.Value).OrderBy(x => x.Name).ToList().Dump("Token references without a definition");

}

public class Token

{

public Token(){}

public Token(string Raw, string namePattern, string valuePattern)

{

this.Raw = Raw;

if(!string.IsNullOrWhiteSpace(namePattern)) Name = Token.GetFirstCapturedMatch(Raw,namePattern);

if(!string.IsNullOrWhiteSpace(valuePattern)) Value = Token.GetFirstCapturedMatch(Raw,valuePattern);

}

public string Name {get;set;}

public string Value {get;set;}

public string Raw {get;set;}

public static string GetFirstCapturedMatch(string value, string pattern)

{

var match = Regex.Match(value,pattern);

if(match!=null && match.Success && match.Groups.Count>1)

{

return match.Groups[1].Value;

}

return null;

}

public static List<string> GetAllFirstCapturedMatches(string value, string pattern)

{

var list = new List<string>();

var matches = Regex.Matches(value,pattern);

foreach(Match match in matches)

{

if(match!=null && match.Success && match.Groups.Count>1)

{

list.Add(match.Groups[1].Value);

}

return list;

}

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

Using setvar in T-SQL

:setvar NAMESPACE "demo"

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

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

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

:setvar NAMESPACE "demo"

--find records with duplicated [Order] values

SELECT

[Duplicated Order Value] = count(*)

,[Namespace]

--,[Group_Id]

,[Order]

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

where [Namespace] = '$(NAMESPACE)'

group by

[Namespace]

--,[Group_Id]

,[Order]

having count(*) > 1

order by

--[Group_Id],

[Order]

--update [Order] values

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

from

(

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

from

(

select

f.[Name]

,fr.[Field_Id]

,fr.[Namespace]

,fr.[Order]

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

from [peeps].[FieldRegistration] fr

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

) as x1

where [Row] <> [Order]

) as x2

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

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

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

--display results

select * from peeps.[FieldRegistration]

where [Namespace] = '$(NAMESPACE)'

order by [Namespace],[Order]

It appears that the virtual machine manager in HyperV (VMM) reports the VM as running though the OS may not yet be fully loaded. We need to wait until the OS is responsive before continuing VM configuration. This observed behavior is more empirical than scientific and requires investigation, but a stop-gap solution is to wait until the VMM reports that the VM is running and then wait for the machine to respond to pings. This code does the second part.

using System.Net.NetworkInformation;

public class Test
{
	public static bool Ping(string name, int waitForPostivePingTimeout = 0) 
	{
		if(string.IsNullOrEmpty(name)) return false;
		if(waitForPostivePingTimeout < 0) waitForPostivePingTimeout = 0;
		var timeout = DateTime.Now.AddMilliseconds(waitForPostivePingTimeout);
		
		do
		{
			try
			{
				Ping p = new Ping();
				PingReply reply = p.Send(name, 2000);
				if(reply.Status == IPStatus.Success) return true;
			}
			catch{}
		} while(DateTime.Now < timeout);
		return false;
	}
}

using System.Net.NetworkInformation;

public class Test

{

public static bool Ping(string name, int waitForPostivePingTimeout = 0)

{

if(string.IsNullOrEmpty(name)) return false;

if(waitForPostivePingTimeout < 0) waitForPostivePingTimeout = 0;

var timeout = DateTime.Now.AddMilliseconds(waitForPostivePingTimeout);

{

try

{

Ping p = new Ping();

PingReply reply = p.Send(name, 2000);

if(reply.Status == IPStatus.Success) return true;

}

catch{}

} while(DateTime.Now < timeout);

return false;

}

SQL CMD tokens are replaced with their literal values prior TSQL parsing, so they can be used in places where a @ variable wouldn’t be permitted. Typically used for running queries within the SQL Server Management Studio, but such queries can be processed externally by calling SQLCMD.EXE (C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE). The literals are not scoped by GO statements like an @ variable and they can be re-defined later in the TSQL

Enable SQL CMD processing from the SQL Server Management Studio

Example SQL

:setvar DB "ADatabase"
:setvar NUMBER "10"

select top $(NUMBER) * from [$(DB)].sys.tables order by name
go
:setvar DB "AnotherDatabase"
select top $(NUMBER) * from [$(DB)].sys.tables order by name

:setvar DB "ADatabase"

:setvar NUMBER "10"

select top $(NUMBER) * from [$(DB)].sys.tables order by name

:setvar DB "AnotherDatabase"

select top $(NUMBER) * from [$(DB)].sys.tables order by name

Example C# – Using SQLCMD.EXE to run scripts

private bool processExited = true;
private StringBuilder sqlCmdStdError = null;
private StringBuilder sqlCmdStdOutput = null;
        
private void ExecuteScriptFileViaSqlCmdUtility(string connectionString, string scriptPath, string outputPath)
{
  const string exe = "sqlcmd.exe"; //C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE
  try
  {
      // open the sql script file
      SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder(connectionString);

      System.Diagnostics.Debug.Write(string.Format("\t\t\tConnection = '{0}.{1}'", ssb.DataSource, ssb.InitialCatalog));
      string arguments = String.Format("-b {0} {1} -S {2} -d {3} -I -r1 -i \"{4}\"", //-o \"{5}\"
          !string.IsNullOrEmpty(ssb.UserID) ? "-U " + ssb.UserID : null,
          !string.IsNullOrEmpty(ssb.Password) ? "-P " + ssb.Password : null,
          ssb.DataSource,
          ssb.InitialCatalog,
          scriptPath,
          outputPath);

      using (Process proc = new Process())
      {
          string sqlout = string.Empty;
          proc.StartInfo.FileName = exe; 
          proc.StartInfo.Arguments = arguments;
          proc.StartInfo.UseShellExecute = false;
          proc.StartInfo.RedirectStandardOutput = true;
          proc.StartInfo.RedirectStandardError = true;
          proc.StartInfo.CreateNoWindow = true;
          proc.Exited += new EventHandler(ProcessExited);
          proc.EnableRaisingEvents = true;
          processExited = false;
          proc.OutputDataReceived += (sender, args) => WriteStdOutput(args.Data);
          proc.ErrorDataReceived += (sender, args) => WriteStdError(args.Data);
          proc.Start();
          proc.BeginOutputReadLine();
          proc.BeginErrorReadLine();

          // Synchronously read the standard output of the spawned process.
          if (!proc.WaitForExit(2000))
          {
              while (true)
              {
                  if (proc == null || proc.WaitForExit(100) || processExited) break;
              }
          }
          bool hasExited = proc.HasExited;

          int code = proc.ExitCode;
          proc.Close();

          if (sqlCmdStdOutput != null)
          {
              string results = sqlCmdStdError.ToString();
              if (!string.IsNullOrEmpty(results))
              {
                  System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");
                  System.Diagnostics.Debug.Write(string.Format(results));
                  System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");
              }
          }

          if (sqlCmdStdError != null)
          {
              string errors = sqlCmdStdError.ToString();
              if (!string.IsNullOrEmpty(errors))
              {
                  if (Regex.IsMatch(errors, "Msg .*, Level .*, State .*, Server .*")
                      || errors.IndexOf("Error") >= 0
                      || errors.IndexOf("Invalid") >= 0)
                  {
                      System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");
                      System.Diagnostics.Debug.Write(string.Format(errors));
                      System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");
                      throw new Exception(errors);
                  }
              }
          }
      }
  }
  catch (Exception ex)
  {
      throw new Exception(string.Format("Execute script file (via SqlCmd) failed @ '{0}'.\n\nError = {1}.\n\n", scriptPath, ex.Message), ex);
  }
}

void WriteStdError(string content)
{
  if (sqlCmdStdError == null) sqlCmdStdError = new StringBuilder();
  sqlCmdStdError.Append(content);
}

void WriteStdOutput(string content)
{
  if (sqlCmdStdOutput == null) sqlCmdStdOutput = new StringBuilder();
  sqlCmdStdOutput.Append(content);
}

private void ProcessExited(Object source, EventArgs e)
{
  processExited = true;
}

100

101

102

103

private bool processExited = true;

private StringBuilder sqlCmdStdError = null;

private StringBuilder sqlCmdStdOutput = null;

private void ExecuteScriptFileViaSqlCmdUtility(string connectionString, string scriptPath, string outputPath)

{

const string exe = "sqlcmd.exe"; //C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE

try

{

// open the sql script file

SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder(connectionString);

System.Diagnostics.Debug.Write(string.Format("\t\t\tConnection = '{0}.{1}'", ssb.DataSource, ssb.InitialCatalog));

string arguments = String.Format("-b {0} {1} -S {2} -d {3} -I -r1 -i \"{4}\"", //-o \"{5}\"

!string.IsNullOrEmpty(ssb.UserID) ? "-U " + ssb.UserID : null,

!string.IsNullOrEmpty(ssb.Password) ? "-P " + ssb.Password : null,

ssb.DataSource,

ssb.InitialCatalog,

scriptPath,

outputPath);

using (Process proc = new Process())

{

string sqlout = string.Empty;

proc.StartInfo.FileName = exe;

proc.StartInfo.Arguments = arguments;

proc.StartInfo.UseShellExecute = false;

proc.StartInfo.RedirectStandardOutput = true;

proc.StartInfo.RedirectStandardError = true;

proc.StartInfo.CreateNoWindow = true;

proc.Exited += new EventHandler(ProcessExited);

proc.EnableRaisingEvents = true;

processExited = false;

proc.OutputDataReceived += (sender, args) => WriteStdOutput(args.Data);

proc.ErrorDataReceived += (sender, args) => WriteStdError(args.Data);

proc.Start();

proc.BeginOutputReadLine();

proc.BeginErrorReadLine();

// Synchronously read the standard output of the spawned process.

if (!proc.WaitForExit(2000))

{

while (true)

{

if (proc == null || proc.WaitForExit(100) || processExited) break;

}

bool hasExited = proc.HasExited;

int code = proc.ExitCode;

proc.Close();

if (sqlCmdStdOutput != null)

{

string results = sqlCmdStdError.ToString();

if (!string.IsNullOrEmpty(results))

{

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");

System.Diagnostics.Debug.Write(string.Format(results));

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL OUTPUT RESULT = '{0}']\r\n\r\n\r\n");

}

if (sqlCmdStdError != null)

{

string errors = sqlCmdStdError.ToString();

if (!string.IsNullOrEmpty(errors))

{

if (Regex.IsMatch(errors, "Msg .*, Level .*, State .*, Server .*")

|| errors.IndexOf("Error") >= 0

|| errors.IndexOf("Invalid") >= 0)

{

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[BEGIN SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");

System.Diagnostics.Debug.Write(string.Format(errors));

System.Diagnostics.Debug.Write("\r\n\r\n\r\n[END SQL ERROR RESULT = '{0}']\r\n\r\n\r\n");

throw new Exception(errors);

}

catch (Exception ex)

{

throw new Exception(string.Format("Execute script file (via SqlCmd) failed @ '{0}'.\n\nError = {1}.\n\n", scriptPath, ex.Message), ex);

}

void WriteStdError(string content)

{

if (sqlCmdStdError == null) sqlCmdStdError = new StringBuilder();

sqlCmdStdError.Append(content);

}

void WriteStdOutput(string content)

{

if (sqlCmdStdOutput == null) sqlCmdStdOutput = new StringBuilder();

sqlCmdStdOutput.Append(content);

}

private void ProcessExited(Object source, EventArgs e)

{

processExited = true;

}

“Abstracted use case specific UML by implementing Java API used by the UI layer.”

The candidate could not explain what he meant by this statement and, since 2004, he has been periodically reminded of this. Yes, we hired him, yes it was the right choice. I am still receiving his paycheck garnishments.

“Please kindly do the needful”

The end of a cover letter from a person in India answering a posting for a local-only position. This became our team’s mantra for several years and is now a part of our regular language.

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

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

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

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

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

select [@@SERVERNAME] = @@SERVERNAME
go

select [@@SERVERNAME] = @@SERVERNAME

sp_helpserver --show config before

exec sp_dropserver 'template'

sp_addserver 'foo', local

sp_helpserver --show config after

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

--RESTART SQL INSTANCE AT THIS POINT

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

select [@@SERVERNAME] = @@SERVERNAME

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

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

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

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

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

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

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

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

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

imports

using System.Management;

using System.Management.Automation;

using System.Management.Automation.Runspaces;

using System.Data.SqlClient;

//rename the default SQL server instance

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

{

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

begin

declare @sql nvarchar(max);

begin try

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

exec(@sql);

end try

begin catch

declare @dummy bit

end catch

begin try

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

exec(@sql);

end try

begin catch

declare @dummy2 bit

end catch

end";

SqlConnection connection = null;

try

{

var cb = new SqlConnectionStringBuilder()

{

DataSource = machineName,

InitialCatalog = "master",

UserID = uid,

Password = pwd

};

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

connection.Open();

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

try

{

command.ExecuteNonQuery();

}

catch { }

connection.Close();

}

finally

{

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

}

private void RestartSqlServer(string machineName)

{

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

$svc.Stop()

$svc.WaitForStatus('Stopped')

#Write-Host Stopped

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

$svc.Start()

$svc.WaitForStatus('Running')

#Write-Host Running";

var sessionState = InitialSessionState.CreateDefault();

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

{

ps.Commands.Clear();

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

ps.Invoke();

}

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

SQL Server configuration for customer VMs

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

Windows firewall configuration for VMs

Windows firewall properties for Domain, Private, and Public profiles

Inbound connections : block by default
Outbound connections allow by default

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

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

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

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

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

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

Implementation #1

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

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

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

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

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

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

begin

declare @default nvarchar(25)

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

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

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

declare @neg bit

set @neg = 0

if(@datetime1 > @datetime2)

begin

declare @dt datetime

set @dt = @datetime1

set @datetime1 = @datetime2

set @datetime2 = @dt

set @neg = 1

end

declare @elapsed_time datetime

declare @elapsed_days int

declare @elapsed_hours int

declare @elapsed_minutes int

declare @elapsed_seconds int

-- Get elapsed time as the difference between 2 datetimes

select @elapsed_time = @datetime2-@datetime1

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

select @elapsed_hours = datepart(hour,@elapsed_time)

select @elapsed_minutes = datepart(minute,@elapsed_time)

select @elapsed_seconds = datepart(second,@elapsed_time)

declare @result nvarchar(50)

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

set @result =

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

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

end +

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

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

end +

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

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

end +

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

else convert(varchar(20),@elapsed_seconds)

end

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

end

#1 Results

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

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

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

--00.05:25:00

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

--05.03:00:00

Implementation #2

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

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

CREATE function [dbo].[fGetElapsedTimeX]

(

@dt1 datetime

,@dt2 datetime

)

returns nvarchar(20)

begin

declare @result nvarchar(20)

set @result =

convert(nvarchar(20),

convert(varchar(10),

convert(int,

convert(float,@dt2) -

convert(float, @dt1)

) * 24 /* hours over 24 */

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

)

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

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

)

return @result

end

#2 Results

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

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

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

--5:25:00

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

--123:00:00

<jco-blog/>

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

Creating out-of-band SQL backups

Find definitions and references of SQL Command Variables in a SQL script

Make duplicated [Order] values distinct

Ping until a machine responds

SQL CMD

Resume Gems

“Abstracted use case specific UML by implementing Java API used by the UI layer.”

“Please kindly do the needful”

“Renaming” SQL Server created from a template VM

SQL Server “Roach Motel” configuration

Windows firewall properties for Domain, Private, and Public profiles

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

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

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

SQL Elapsed Time

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

“Abstracted use case specific UML by implementing Java API used by the UI layer.”

“Please kindly do the needful”

Windows firewall properties for Domain, Private, and Public profiles

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

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

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