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 \); } } }

Microsoft SQL Server Error, 27187

Microsoft SQL Server Error, 27187

If you receive this error while trying to deploy a package from SSIS, the reason you are receiving this error is because the management account for SSIS does not have the proper permissions to the SSIS_Admin role.

Once you do this, the problem should clear itself up as long as the user doing the deployment also has ssis_admin privileges.  At some point during the deployment process it hands over security to the AllSchemaOwner database user.

This error is related to: Microsoft SQL Server, Error: 27118

It may actually be the same error but someone fat fingered the error code somewhere down the line.

IF you have a deeper issue with this, the following blog post was very helpful.

https://dba.stackexchange.com/questions/105770/failed-to-deploy-the-project-try-again-later-microsoft-sql-server-error-271

Time in 00:00:00 text format with the need to convert it to seconds then back

This is just a quick example of converting a text format "time" field back and forth into seconds so that summations and such can be done.

DECLARE @fauxTime VARCHAR(8) = '00:01:00'; -- we have a free form "time" field

DECLARE @fauxTimeInSeconds INT = (SELECT Datediff(second, Cast('00:00:00' AS TIME), Cast(@fauxTime AS TIME))) -- Convert it to seconds\

DECLARE @fauxTimeBackToTimefromSeconds TIME =(SELECT Cast(CONVERT(VARCHAR, Dateadd(ss, @fauxTimeInSeconds, 0), 108) AS TIME))

DECLARE @fauxTimeBackToHHMMSSfromSeconds VARCHAR(8) =(SELECT CONVERT(VARCHAR,Dateadd(ss, @fauxTimeInSeconds, 0), 108)) -- convert back to a character based time field

PRINT @fauxTime

PRINT @fauxTimeInSeconds

PRINT @fauxTimeBackToTimefromSeconds

PRINT @fauxTimeBackToHHMMSSfromSeconds

OUTPUT:

00:01:00 -- Original.

60 -- Converted to seconds.

00:01:00.0000000 -- Converted back into a time field.

00:01:00 - Converted back to a character field.

Error: 8624, Severity: 16, State: 116 -> When things go really wrong

If you are reading about this, it is likely that you just hit the error: Error: 8624, Severity: 16, State: 116

This is an error that means you are having a bad day, because somewhere in your code SQL was generated and that SQL isn't working.

If you haven't already figured out what the SQL is, the pro tip is this: If it the SQL is like 100 lines of joins, then it has some IN statements and in that IN statement is a 100 lines of joins, then that is likely the SQL you are looking for.

If you can't find the SQL with that tip, then go through SQL Server Profiler and figure out where it is.  Remember, somewhere you have a mess that looks something like

SELECT * FROM table join (lots of other stuff()  where mykey in (select mykey from table join (lots of other stuff)) over and over and over.

Now that you have found this code, here are my recommendations:

Figure out how to craft the SQL better so that it isn't using so much logic.

The place that I found this problem was with code that was generated dynamically by other code, and my suggestion is that you fix the underlying problem of having generated unusable code. If you can't do it, you have an option to "just make it run" but this option is going to trade the code working, for very slow code.

You can put an option called "OPTION (FORCE ORDER)" at the end of the code, this will FORCE SQL Server to execute the code with the most naive execution plan possible.  This isn't great, in fact, it is really bad. Any code with that many joins is probably going to be slow even in a perfect world, but it will work.

Here are some good articles I found on this when we hit this problem where I work.

https://blogs.msdn.microsoft.com/sqlserverfaq/2014/07/15/troubleshooting-error-8624-severity-16-state-21-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan/

https://www.simple-talk.com/sql/performance/controlling-execution-plans-with-hints/

Count vs Count distinct in SQL

Had a new coder ask me how count works, vs count distinct. So I made a small example:

create table #tmp_example

(

       id int identity(1,1)

       , value varchar(255)

)

insert into #tmp_example

(

       value

)

select '1'

UNION select '2'

UNION select '3'

UNION select NULL

select

count(*)

, count(distinct value)

, count(distinct case when value = 1 then null else value end)

from #tmp_example

In this example it returns 4, 3, 2

4 because there are 4 rows (*) in the system.

3 because there are 3 distinct values where NULL is not counted a value in count distinct.

2 because there are 2 distinct values when 1 is cast as null during the counting process.

Google Chrome does not work but Internet Explorer does

This is for those of you who have gone through the effort of checking the internet explorer->internet options-> and UNCHECK the proxy server, and it didn't work for you.

Another possible solution is to flush the DNS cache of your computer. Sometimes Google just outright loses the ip it needs to connect to and won't work on getting a new one.

Go to the command prompt by going to the start menu, and in the command box tying "cmd" this will bring up a dos box.

In the dos box type "Ipconfig /flushdns" and see if that works for you.  I have seen people think I am "amazing" for knowing how to do this, but this used to be quite a common problem in older versions of windows, and the trick still works sometimes with common connectivity problems.  Now why it allows one browser to work when the other won't... that still confuses me, but there you go.

Advanced Debugging in SSIS no Script Task Related

http://www.techbrothersit.com/2013/12/ssis-how-to-watch-value-of-variables.html

I wanted to just have this out there.  More of a re-blog because it needs to be pushed up.  This is how you do this properly when you are having an issue and are unsure of the exact value of a record. I also tend to log every variable using a script task as well during run time so that I can review the ssis logs for it.