Stripping special characters from SQL text

Reposting only to keep this from disappearing.  Simple redundancy and I do not claim any part of this work as my known.

This is a repost from Sean McWherter's blog.  

Excerpt from his blog:
Was looking for a better solution than a mile long REPLACE statement, found this. Originally written by Christian d’Heureuse, modified slightly by me. Removes all characters except: 0-9, a-z, A-Z, and spaces (remove “or @c = 32” if you want to also remove spaces).

Here is my usage.  Only added fx_ to function name.

CREATE function dbo.fx_RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c = 32
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

What do to if Google tags your blogger blog with a Malware warning

So yesterday, I discovered that Google had tagged my Digital Rockhound's site.

So I used Google WebMaster's tools and verified that my site was clean.
I used this site  http://www.unmaskparasites.com to confirm that I had links to a site with malware.
Turns out I did.

I used a Google site search to help narrow where the links were.  I had two links in a link gadget to the offending site.  Simply removed them.

Then, I used WebMaster's tools a requested a reindex of my site. Good news here is that the links were at root level so the indexing was fast.  


After Google re-indexed my site, no more scary malware warnings.

SQL 2005/2008 Max(bit)

I'm certain it was possible to Max(bit) in SQL 2000 but that changed in SQL 2005/2008.

 SELECT Max(Col1+0)
   FROM SomeTable

Displaying a public key for an assembly.

This was way harder that it needs to be....

Use sn.exe with -Tp argument.  However,  to make this useful instead of just seeing it in cmd windows,
Follow the steps in this article: http://goo.gl/lFx1B

Use the following path for sn.exe:

For me on a Windows 7 64-bit using VS2010, the path to sn.exe was C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\sn.exe

Related stackoverflow link :http://stackoverflow.com/a/1123831/44597

Did this so I could attempt to unit test a MVC application that is strongly signed.

ASP.NET MVC - How to Unit Test an Action Method which returns JsonResult?

WebPart Tip: How to quickly view the public key for an assembly in the output window

I saw this handy little helper function being used on a MOSS screencast by Todd Bleeker and decided to figure out how he did it :). Basically it allows you to view the public key token of an assembly from within Visual Studio's output window, which saves time when you need to grab the key for use with the safe control entries in the web.config for example.

First step, fire up your copy of Visual Studio 2005 and open up a webpart class library project. Then from the Tools menu select External Tools. This will bring up the following dialog:

 

From the options, click Add and then give your utility a title. Now all we're doing here is essentially grabbing the output from the sn.exe command, so you will need to provide the path to your local sn.exe file. Mine was located in the following folder:

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe 

Now, just like if we were running the strong name utility from the command line, we're going to need to pass in the correct arguments. Firstly we'll need to pass the -Tp argument, as we want to get the token for the public key, together with the public key itself. Then we need to specify the target assembly, which can be done by clicking on the button to the right of the Arguments field and selecting 'Target Path'. This basically gets the full qualified path to item which is being built, in our case a webpart assembly. Lastly just tick the 'Use Output Window' checkbox and then click ok and you're good to go :)

The new utility you've just created should now show up in the Tools menu, you can go ahead and click it and you should now see the output window popup with the details of the public key. This is a really nice feature of Visual Studio and I'm sure this can be tailored for many other uses.

Here is an odd bug with an even odder workaround solution.

In a large C# solution, I have numerous projects and some COM wrapped .net controls.


After running the solution, a rebuild might fail with DLL cannot be copied to \bin locked by another process. It was not happening everytime so I thought may one of my components were not getting disposed correctly.  The frequency of occurrence jump 10x after I moved to Windows 7 64bit and VS 2010.  A GIS map control assembly always failed to build after it was run.  Again, first this I thought was my implementation must incorrect.  After some refactoring,I still have the problem, then I found this question on stackoverflow.


The workaround eliminated the issue for me.  


It would be easy to blame VS 2010 or Windows 7 but given the complexity of the two products, little oddities are par for the course.  Just glad for Stackoverflow!

Visual Studio build fails: unable to copy exe-file from obj\debug to bin\debug

Seems it is caused by using default assembly version numbers.

This same problem was discussed on Stack Overflow in this question: http://stackoverflow.com/q/2895898/310001

The following answer was proven to be one possible workaround:
http://stackoverflow.com/questions/2895898/visual-studio-build-fails-unable-to-copy-exe-file-from-obj-debug-to-bin-debug/2994502#2994502

I have verified both the bug and the above mentioned workaround in the attached project. In short, what to do to avoid this error is:

1. Open AssemblyInfo.cs in the WindowsService1 project
2. Change the line [assembly: AssemblyVersion("1.0.*")] to [assembly: AssemblyVersion("1.0.0")]
3. Save All solution files
4. Clean solution
5. Using a file browser, go into WindowsService1\bin\Debug\ and rename the locked file Solution6.Module.dll (e.g. to Solution6.Module1.dll - you must use rename, because you won't be allowed to delete it)
6. Go back into Visual Studio
7. Rebuild Solution twice
8. If the error still happens, go to step 4 and repeat again (I had to repeat once - it makes no sense, and I have no idea why, but it works)

When you get this far, everything should now be OK. You can rebuild as often you need to without any problems. Yes, you'll have to take care of incrementing AssemblyVersion manually, but it is a tiny tiny price to pay compared to many of the other "hacks" out there to work around this.


So in short: avoid using default Revision/Build numbers in AssemblyVersion!

Source:VSIP

Using stored proc with table parameters

Sample stored procedure. SQL Server 2008+

CREATE PROCEDURE [dbo].[usp_audit_select_criticalresults]
    @exams dbo.ExamIDType READONLY    
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT DISTINCT x.ExamID, 
           case 
             WHEN sap2.ParameterValue is not null then CONVERT(bit,sap2.ParameterValue)
             else CONVERT(bit,0)
           END            
    FROM @exams x
    LEFT JOIN SessionActivityParameters sap
        ON sap.ParameterName='ExamId' AND x.ExamID = sap.ParameterValue
    LEFT JOIN SessionActivityParameters sap2 
        ON sap2.ParameterName='confirmed' AND sap.SessionActivityId =sap2.SessionActivityId
    LEFT JOIN SessionActivity sa
        ON sa.SessionActivityId = sap.SessionActivityId
    WHERE (sa.AttributeId='55CD62C2-AED5-4216-A3C6-FABD0183E130' or sa.AttributeId is null)

            
END

Executing stored procedure with test data in T-SQL:

DECLARE @exams dbo.ExamIDType;

INSERT INTO @exams (ExamID)
SELECT '00993736'
UNION SELECT '00993737'
UNION SELECT '00993738'
UNION SELECT '00993749'
UNION SELECT '00993750'
UNION SELECT '00993766'
UNION SELECT '00993767'

--SELECT * FROM @exams

exec usp_audit_select_criticalresults @exams

Less Msiérables (LessMSI) - Extraction tool for MSI

Less Msiérables (LessMSI)

A great little tool to extract files from a MSI without installing it.  Had to use this for office 11 (2003) interop assemblies install without having to have office 2003 installed.

Source:

http://code.google.com/p/code4all/downloads/detail?name=Lessmsierables-20051110.zip&can=2&q=

My Dropbox link (because things on the internet tend to disappear)

https://dl.dropbox.com/u/33380348/Optional%20Tools/ExtractsFilesFromMSI-lessmsi-v1.0.8.zip