Send alert for Windows Service Error

First, we need to create the Powershell script (Source from HowToGeek.com):

$EmailFrom = "emailID@emaildomain.com"
$EmailTo = "destination@somedomain.com"
$Subject = "The subject of your email"
$Body = "What do you want your email to say"
$SMTPServer = "smtp.email.server.address"
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
$SMTPClient.EnableSsl = $true
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("usr", "pass");
$SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

Then we need a script to invoke the powershell script

powershell & "C:\PathToPowerShellScript\myPowerShellEmailScript.ps1"

Finally, modify the service to execute a command on failure:

SC failure serviceName command="C:\PathToBat\MyScript.bat"

Power Query VLOOKUP Function

<pThis is a custom VLOOKUP function for Power Query, created by Ken Puls

let pqVLOOKUP = (lookup_value as any, table_array as table, col_index_number as number, optional approximate_match as logical ) as any =>
let
/*Provide optional match if user didn't */
matchtype =
if approximate_match = null
then true
else approximate_match,
/*Get name of return column */
Cols = Table.ColumnNames(table_array),
ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColName_match = Record.Field(ColTable{0},"Column1"),
ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"),
/*Find closest match */
SortData = Table.Sort(table_array,{{ColName_match, Order.Descending}}),
RenameLookupCol = Table.RenameColumns(SortData,{{ColName_match, "Lookup"}}),
RemoveExcess = Table.SelectRows(RenameLookupCol, each [Lookup] <= lookup_value),
ClosestMatch=
if Table.IsEmpty(RemoveExcess)=true
then "#N/A"
else Record.Field(RemoveExcess{0},"Lookup"),
/*What should be returned in case of approximate match? */
ClosestReturn=
if Table.IsEmpty(RemoveExcess)=true
then "#N/A"
else Record.Field(RemoveExcess{0},ColName_return),
/*Modify result if we need an exact match */
Return =
if matchtype=true
then ClosestReturn
else
if lookup_value = ClosestMatch
then ClosestReturn
else "#N/A"
in Return
in pqVLOOKUP

Grant rights to Active Directory Group on SQL Server

Rather than adding each user from an AD security group, use that group to globally manage rights on SQL Server to all of those users, where appropriate, of course.

USE master;
GO
CREATE LOGIN [DOMAIN\Group Name] FROM WINDOWS;
GO
USE DatabaseName;
GO
CREATE USER [DOMAIN\Group Name] FROM LOGIN [DOMAIN\Group Name];
GO
-- assign the user a role
EXEC sp_addrolemember db_datareader, [DOMAIN\Group Name];
GO

Return Value From SQL Server to C#

On some occasions it is important to retrieve a value from a Store Procedure and return it to a C# function. This example is an excerpt from a project to create an image library. File names are pushed into SQL Server using a Stored Procedure. Part of it is to record the path. Paths are stored in one table, file names in another. The record ID of the path is required to create the image record.

SQL Server Stored Procedure

CREATE PROCEDURE [dbo].[sp_newPath]
@path nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- NewPathID is used to retain the Record ID of the existing or new path ID
DECLARE @NewPathID int

-- If the path already has a record, get the ID
SELECT TOP 1 @NewPathID = dbo.Paths.path_ID from dbo.Paths WHERE dbo.Paths.path_path = @path;

-- If there is no record, @NewPathID will be 0 or NULL
if @NewPathID = 0 OR @NewPathID IS NULL
BEGIN

-- Insert the new path record into the Table
INSERT dbo.Paths(path_path)
OUTPUT INSERTED.path_ID INTO @TblforID
VALUES(@path)

-- Get the new Identity value
SELECT @NewPathID = SCOPE_IDENTITY()
END
RETURN (@NewPathID)
END

C# function to push data into SQL Server and retrieve the record ID.

private static int InsertPathSQL(string dirPath)
{

string sqlConnStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Image_Library;Integrated Security=true";
SqlConnection sqlConn = new SqlConnection(sqlConnStr);

SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlComm.CommandText = "dbo.sp_newPath";
sqlComm.Parameters.Add("@path", System.Data.SqlDbType.NVarChar).Direction = System.Data.ParameterDirection.Input;
sqlComm.Parameters["@path"].SqlValue = dirPath;
sqlComm.Parameters.Add("@returnValue", System.Data.SqlDbType.Int, 4).Direction = System.Data.ParameterDirection.ReturnValue;
sqlComm.Connection.Open();
sqlComm.ExecuteNonQuery();
int j = (int)sqlComm.Parameters["@returnValue"].Value;
sqlComm.Connection.Close();
return j;
}

Bootable ISO to External Drive

  1. Format the drive
    • Run CMD as Administrator
    • diskpart
    • list disk (or list volume to identify the drive by volume number)
    • select disk # (or select volume #)
    • Note: this will remove all data and partitions from the drive. Be careful not to run this on the wrong directory.clean
    • create partition primary
    • format fs=ntfs quick
    • assign
    • exit
  2. Mount the ISO as a virtual drive
  3. Make the external drive bootable (assumptions, Virtual Drive is V: and External Drive is X:
    • V:
    • cd boot
    • bootsect.exe /nt60 X:
  4. Copy the installer files xcopy V:\*.* X: /e /f /h

SQL Server Instances

Finding running instances of SQL Server on a computer

Launch PowerShell

Get-Service

Lists all services on the computer. Look for SQL Server

Named instances are displayed as MSSQL$NAME

Default instance will display as MSSQLSERVER

SQL Server Express Edition installs as a Named Instance