Max VB

A simple example that shows where if you have a set of columns, and you need to quickly find a maximum value among the set.  This code will give you a path with a clever use of cross apply to find the maximal value from a set of values that exist on a record.

This creates an arbitrary table (using sysobjects as a surrogate numbers table), and creates time stamps. It then shows how to get a maximum timestamp from those time stamps quickly, and easier to write than cascading case statements.

       drop table if exists #tmp_somevalues;

 

       Create table #tmp_somevalues

       (

                     ID                         int identity(1,1)

              ,      InsertTS                    datetime not null

              ,      ModifiedTS           datetime

              ,      DeletedTS                   datetime null

              ,      OtherTS                           datetime

              ,      YourTimeStamp datetime

       )

      

       insert into #tmp_somevalues

       (

 

              InsertTS     

              ,ModifiedTS  

              ,DeletedTS   

              ,OtherTS            

              ,YourTimeStamp

       )

       SELECT

              InsertTS      = getdate() - so.id

              ,ModifiedTS   = getdate() + so.id

              ,DeletedTS    = case when id % 2 = 0 then getdate()  + so.id  + 1 else null end

              ,OtherTS      = getdate()

              ,YourTimeStamp = case when id % 3 = 0 then getdate()  + so.id  - 1 else null end

              --select *

       FROM sysobjects so

       where so.id <= 50

 

       select * from #tmp_somevalues

       cross apply

       (

 

              SELECT max(tstamps) as MaxTS FROM (

          VALUES(InsertTS),

        (ModifiedTS),

                      (DeletedTS),

                      (OtherTS),

                      (YourTimeStamp)

                    

                      )

          AS MaxCandidates(tstamps)

   ) maxval

A simplified version of this code:

SELECT max(id) as MaxID FROM (

VALUES(1),

        (2),

        (5),

        (10),

        (11),

        (12),

        (20)

        )

AS MaxCandidates(Id)

Bitwise Value Interpretation in SQL from a character based source using like statements

This code is an example of bitwise operators in SQL that are being derived from a well formed data source where the bit positioning is contained as character values of a specific length and shape.  In this case we receive a document that gives us values of specific attributes a person has, and those attributes are stored in a bitwise field where an attribute will correspond to a 1 in a specific position in a field.

/*

Christopher Harsch

Simple example for bitwise operators in SQL.  This is an example, it may not be the bbst, but it is certainly a workable approach.  This uses simple operators that work with strings, which is likely what will be received from a vendor

or external partner.

There specialized operators in SQL for this, but those operators work against non string types.  Which is likely

why you are here, vs another page.

*/

DROP TABLE IF EXISTS #tableofbitwisemasktranslations;

CREATE TABLE #tableofbitwisemasktranslations
             (
                          id           int IDENTITY(1,1) ,
                          bitwisemask  char(4) ,
                          bitwisevalue varchar(255)
             );

INSERT INTO #tableofbitwisemasktranslations
            (
                        bitwisemask ,
                        bitwisevalue
            )

SELECT '1___'        AS BitWiseMask ,

       'Good Enough' AS BitWiseValue

UNION ALL

SELECT '_1__'         AS BitWiseMask ,

       'Smart Enough' AS BitWiseValue

UNION ALL

SELECT '__1_'       AS BitWiseMask ,

       'Well Liked' AS BitWiseValue

UNION ALL

SELECT '___1'          AS BitWiseMask ,

       'The Quiet One' AS BitWiseValue;

 

DROP TABLE IF EXISTS #tmp_example_table_withdata;

 

CREATE TABLE #tmp_example_table_withdata

             (
                          id               INT IDENTITY(1,1) ,
                          thebitwisecolumn CHAR(4)
             );

INSERT INTO #tmp_example_table_withdata
            (

            thebitwisecolumn

            )

          SELECT '0100'

UNION ALL

SELECT '0110'

UNION ALL

SELECT '0101'

UNION ALL

SELECT '1100'

UNION ALL

SELECT '0000'

UNION ALL

SELECT '0110'

UNION ALL

SELECT '1111';

-- Note, this whole process should be to ONLY be creating this data into a proper
-- normalized schema. You should _NOT_ be doing this as a report, or use the data as
-- part of a where criteria.  Put this into a proper 1 to many table, and then filter against that later
-- in your reporting.
--- Example 1 - Get the data as discrete values

SELECT      *
FROM        #tmp_example_table_withdata a
OUTER apply
            (
                   SELECT Trim(Isnull(b.bitwisevalue,'')) AS ValuesAsCSV ,
                          b.id
                   FROM   #tableofbitwisemasktranslations b
                   WHERE  1=1
                   AND    a.thebitwisecolumn LIKE b.bitwisemask ) theData;

--Example 2 - Put the data into a commma separated value list

SELECT      *
FROM        #tmp_example_table_withdata a
OUTER apply
            (
                   SELECT String_agg(Trim(Isnull(b.bitwisevalue,'')) ,', ') AS ValuesAsCSV
                   FROM   #tableofbitwisemasktranslations b
                   WHERE  1=1
                   AND    a.thebitwisecolumn LIKE b.bitwisemask ) theData;

 I had the misfortune of needing to know the value of the header in excel for any specific ordinal position.  This is code that gives you those header values using sql.

DROP TABLEIF EXISTS #tmp_letters;

CREATE TABLE #tmp_letters

             (
                          id     INT IDENTITY(1,1) ,
                          letter CHAR(1)
             )INSERT INTO #tmp_letters
            (
                        letter
            )
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'F'
UNION ALL
SELECT 'G'
UNION ALL
SELECT 'H'
UNION ALL
SELECT 'I'
UNION ALL
SELECT 'J'
UNION ALL
SELECT 'K'
UNION ALL
SELECT 'L'
UNION ALL
SELECT 'M'
UNION ALL
SELECT 'N'
UNION ALL
SELECT 'O'
UNION ALL
SELECT 'P'
UNION ALL
SELECT 'Q'
UNION ALL
SELECT 'R'
UNION ALL
SELECT 'S'
UNION ALL
SELECT 'T'
UNION ALL
SELECT 'U'
UNION ALL
SELECT 'V'
UNION ALL
SELECT 'W'
UNION ALL
SELECT 'X'
UNION ALL
SELECT 'Y'
UNION ALL
SELECT 'Z';

DROP TABLEIF EXISTS #tmp_letter_data;CREATE TABLE #tmp_letter_data
             (
                          id      INT IDENTITY(1,1) ,
                          batchid INT ,
                          letters VARCHAR(255)
             )INSERT INTO #tmp_letter_data
            (
                        batchid ,
                        letters
            )
SELECT Batch = 1 ,
       letter
FROM   #tmp_lettersDECLARE @BatchID INT = 1;WHILE(
(
       SELECT Count(*)
       FROM   #tmp_letter_data) < 1020)
BEGIN
  PRINT 'Working it batches'
  DECLARE @Letter      VARCHAR(10)
  DECLARE @NextBatchID INT = @BatchID + 1;
  DECLARE mycursor CURSOR FOR
  SELECT   letters
  FROM     #tmp_letter_data
  WHERE    batchid = @BatchID
  ORDER BY id ASC
  OPEN mycursor
  FETCH next
  FROM  mycursor
  INTO  @Letter
  WHILE @@FETCH_STATUS = 0
  BEGIN
    DECLARE @message VARCHAR(4000);
    PRINT ' '
    SELECT @message = 'Letter PRINT: ' + Cast(@Letter AS VARCHAR(255))
    PRINT @message
    INSERT INTO #tmp_letter_data
                (
                            batchid ,
                            letters
                )
    SELECT      Batch =       2 ,
                tld.letters + ltrs.letter
    FROM        #tmp_letter_data tld
    CROSS apply
                (
                       SELECT letter
                       FROM   #tmp_letters l ) AS ltrs
    WHERE       tld.letters = @Letter
    FETCH next
    FROM  mycursor
    INTO  @Letter
  END
  CLOSE mycursor;
  DEALLOCATE mycursor
  SET @BatchID = @BatchID + 1;
END

SSIS DB not loading

Right now you have tried to update your server and move from one machine to another. You have tried to set trustworthy to on, you have set the ownership to sa.  Nothing has worked. You even have used your super secret password and set master key decryption. 

Here is the way to get this to work. The secret sauce below is to create your SSISDB catalog, empty, and create it with the SAME password that you are going to use when you mount your old version of the database.  This is unintuitive, and I hate it.  But this is what worked for my team.

1.  CREATE THE SSIS CATALOG USING THE WIZARD

     1a.  IN THE WIZARD put the password you had for the original database.

2.  Now that it is made, pull in the back up of the original database into the system.

3.  Now that the backup is pulled in.

Run the following commands:

OPEN master Key decryption by password = '<your password>';

ALTER Master Key ADD encryption by Service Master Key

ALTER AUTHORIZATION ON DATABASE::SSISDB TO sa;

ALTER DATABASE SSISDB SET TRUSTWORTHY ON

The error this fixes is:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=14.0.0.0,

Unicode to ASCII convert with replacement

This should be a relatively trivial problem, take a unicode string and change it to a functional equivalent in ASCII.  For some reason I was not able to find a very good example of this anywhere so here is my C# implementation of this.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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

using System.Text; namespace UnicodeUtility { /// <summary> /// Poorly developed by Christopher Harsch /// /// The mapping for this was created from /// https://lexsrv3.nlm.nih.gov/LexSysGroup/Projects/lvg/current/docs/designDoc/UDF/unicode/DefaultTables/symbolTable.html /// /// </summary> class UnicodeToAsciiUtility { /// <summary> /// Takes a string input that is unicode, makes several attempts to convert some characters to ASCII equivilants, then hammers the rest out into ? characters. /// /// </summary> /// <param name="inputstring">String you want to hammer into ASCII</param> /// <returns></returns> public static string unicodeToASCIIHammer(string inputstring) { byte[] _unicodeBytes = Encoding.Unicode.GetBytes(new StringBuilder(inputstring).Replace('\u00AB', '"') .Replace('\u00AD', '-') .Replace('\u00B4', '\'') .Replace('\u00BB', '"') .Replace('\u00F7', '/') .Replace('\u01C0', '|') .Replace('\u01C3', '!') .Replace('\u02B9', '\'') .Replace('\u02BA', '"') .Replace('\u02BC', '\'') .Replace('\u02C4', '^') .Replace('\u02C6', '^') .Replace('\u02C8', '\'') .Replace('\u02CB', '`') .Replace('\u02CD', '_') .Replace('\u02DC', '~') .Replace('\u0300', '`') .Replace('\u0301', '\'') .Replace('\u0302', '^') .Replace('\u0303', '~') .Replace('\u030B', '"') .Replace('\u030E', '"') .Replace('\u0331', '_') .Replace('\u0332', '_') .Replace('\u0338', '/') .Replace('\u0589', ':') .Replace('\u05C0', '|') .Replace('\u05C3', ':') .Replace('\u066A', '%') .Replace('\u066D', '*') .Replace('\u200B', ' ') .Replace('\u2010', '-') .Replace('\u2011', '-') .Replace('\u2012', '-') .Replace('\u2013', '-') .Replace('\u2014', '-') .Replace('\u2015', '-') .Replace('\u2016', '|') .Replace('\u2017', '_') .Replace('\u2018', '\'') .Replace('\u2019', '\'') .Replace('\u201A', ',') .Replace('\u201B', '\'') .Replace('\u201C', '"') .Replace('\u201D', '"') .Replace('\u201E', '"') .Replace('\u201F', '"') .Replace('\u2032', '\'') .Replace('\u2033', '"') .Replace('\u2034', '\'') .Replace('\u2035', '`') .Replace('\u2036', '"') .Replace('\u2037', '\'') .Replace('\u2038', '^') .Replace('\u2039', '<') .Replace('\u203A', '>') .Replace('\u203D', '?') .Replace('\u2044', '/') .Replace('\u204E', '*') .Replace('\u2052', '%') .Replace('\u2053', '~') .Replace('\u2060', ' ') .Replace('\u20E5', '\\') .Replace('\u2212', '-') .Replace('\u2215', '/') .Replace('\u2216', '\\') .Replace('\u2217', '*') .Replace('\u2223', '|') .Replace('\u2236', ':') .Replace('\u223C', '~') .Replace('\u2264', '<') .Replace('\u2265', '>') .Replace('\u2266', '<') .Replace('\u2267', '>') .Replace('\u2303', '^') .Replace('\u2329', '<') .Replace('\u232A', '>') .Replace('\u266F', '#') .Replace('\u2731', '*') .Replace('\u2758', '|') .Replace('\u2762', '!') .Replace('\u27E6', '[') .Replace('\u27E8', '<') .Replace('\u27E9', '>') .Replace('\u2983', '{') .Replace('\u2984', '}') .Replace('\u3003', '"') .Replace('\u3008', '<') .Replace('\u3009', '>') .Replace('\u301B', ']') .Replace('\u301C', '~') .Replace('\u301D', '"') .Replace('\u301E', '"') .Replace('\uFEFF', ' ').ToString()); byte[] _asciiBytes = Encoding.Convert(Encoding.Unicode, Encoding.ASCII, _unicodeBytes); char[] asciiChars = new char[Encoding.ASCII.GetCharCount(_asciiBytes, 0, _asciiBytes.Length)]; Encoding.ASCII.GetChars(_asciiBytes, 0, _asciiBytes.Length, asciiChars, 0); return new string(asciiChars); // return \); } } }