Mar 312009
 

I must have changed my password to my email server a LONG time ago, prostate because it has been ages since I have been getting emails from my blog.

I finally took time to look into this and have reconfigured it to send me emails again.

If there are any unanswered emails out there, please let me know and I will answer them.

 

Sorry, as I do really want to hear from you!

Mar 302009
 

I ran a file that had a null character in it, health and this is the error that I received:

<?xml version=”1.0″?>
<Result State=”FAILED”>
    <Error>
        <HResult>0xC00CEE2B</HResult>
        <Description>
            <![CDATA[Illegal xml character.
            ]]>
        </Description>
        <Source>XML SAX Parser</Source>
        <Type>FATAL</Type>
    </Error>
</Result>

Mar 252009
 

I’ve many times needed to find a value within an entire database.

Here’s a stored procedure that accomplishes that.

CREATE PROCEDURE [dbo].[FindMatchingValueInAnyTable]
(
    @Value VARCHAR(64)
)
AS
BEGIN
    DECLARE @sql VARCHAR(MAX)
    DECLARE @TableName VARCHAR(64)
    DECLARE @ColumnName VARCHAR(64)
    CREATE TABLE #Results
    (
        TableName VARCHAR(64)
    ,    ColumnName VARCHAR(64)
    )
    DECLARE TABLES CURSOR
    FOR
    SELECT
        o.name
    ,    c.name
    FROM syscolumns c
    INNER JOIN sysobjects o
    ON c.id = o.id
    WHERE o.type = ‘U’
    AND c.xtype IN (167, 175, 231, 239)
    ORDER BY
        o.name
    ,    c.name
    OPEN TABLES
    FETCH NEXT FROM TABLES
    INTO @TableName
    ,    @ColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @TableName + ‘] ‘
        SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @ColumnName + ‘])) LIKE ”%’ + @value + ‘%”) ‘
        SET @sql = @sql + ‘INSERT INTO #Results ( TableName, ColumnName ) VALUES (”’ + @TableName + ”’, ”’
        SET @sql = @sql + @ColumnName + ”’)’
        EXEC(@sql)
    FETCH NEXT FROM TABLES
    INTO @TableName
    ,    @ColumnName
    END
    CLOSE TABLES
    DEALLOCATE TABLES
    SELECT *
    FROM #Results
    DROP TABLE #Results
END

Mar 232009
 

I was attempting to debug a stored procedure that calls CLR functions (however this story can be told for any CLR object) and the debugger would simply hang.

There are two steps to get around this issue:

First is to disable and re-enable CLR debugging. To do this, vcialis 40mg in Visual Studio right click the SQL Server and uncheck the Allow SQL/CLR Debugging line.

Allow SQL CLR Debugging

Then re enable it, cure where you will see the following dialog where you should press Yes.

Managed Threads Dialog

The second step is to disable affinity for multiple cpu machines.

In SQL Management Studio, right click the server in the tree, and go to properties. There click on the Processors, Uncheck the Automatically set processor affinity mask for all processors:

affinity

This worked for me. I am wondering if there is a hotfix for this?

Mar 202009
 

I wanted to get all of the rows that were time stamped today from a table that has a datetime column.

Here is the query that I used:

SELECT COUNT(*) AS [Today's File Count] FROM dbo.Envelope WHERE (CONVERT(varchar, entry, 101) = CONVERT(varchar, GETDATE(), 101))

Mar 172009
 
^ The pattern has to appear at the beginning of a string. ^cat matches any string that begins with cat
$ The pattern has to appear at the end of a string. cat$ matches any string that ends with cat
. Matches any character. cat. matches catT and cat2 but not catty
[] Bracket expression. Matches one of any characters enclosed. gr[ae]y matches gray or grey
[^] Negates a bracket expression. Matches one of any characters EXCEPT those enclosed. 1[^02] matches 13 but not 10 or 12
[-] Range. Matches any characters within the range. [1-9] matches any single digit EXCEPT 0
? Preceeding item must match one or zero times. colou?r matches color or colour but not colouur
+ Preceeding item must match one or more times. be+ matches be or bee but not b
* Preceeding item must match zero or more times. be* matches b or be or beeeeeeeeee
() Parentheses. Creates a substring or item that metacharacters can be applied to a(bee)?t matches at or abeet but not abet
{n} Bound. Specifies exact number of times for the preceeding item to match. [0-9]{3} matches any three digits
{n, clinic } Bound. Specifies minimum number of times for the preceeding item to match. [0-9]{3,} matches any three or more digits
{n,m} Bound. Specifies minimum and maximum number of times for the preceeding item to match. [0-9]{3,5} matches any three, four, or five digits
| Alternation. One of the alternatives has to match. July (first|1st|1) will match July 1st but not July 2

POSIX Character Classes

[:alnum:] alphanumeric character [[:alnum:]]{3} matches any three letters or numbers, like 7Ds
[:alpha:] alphabetic character, any case [[:alpha:]]{5} matches five alphabetic characters, any case, like aBcDe
[:blank:] space and tab [[:blank:]]{3,5} matches any three, four, or five spaces and tabs
[:digit:] digits [[:digit:]]{3,5} matches any three, four, or five digits, like 3, 05, 489
[:lower:] lowercase alphabetics [[:lower:]] matches a but not A
[:punct:] punctuation characters [[:punct:]] matches ! or . or , but not a or 3
[:space:] all whitespace characters, including newline and carriage return [[:space:]] matches any space, tab, newline, or carriage return
[:upper:] uppercase alphabetics [[:upper:]] matches A but not a

Perl-Style Metacharacters

// Default delimiters for pattern /colou?r/ matches color or colour
i Append to pattern to specify a case insensitive match /colou?r/i matches COLOR or Colour
 A word boundary, the spot between word (w) and non-word (W) characters /fred/i matches Fred but not Alfred or Frederick
B A non-word boundary /fredB/i matches Frederick but not Fred
d A single digit character /adb/i matches a2b but not acb
D A single non-digit character /aDb/i matches aCb but not a2b

The newline character. (ASCII 10) /
/
matches a newline

The carriage return character. (ASCII 13) /
/
matches a carriage return
s A single whitespace character /asb/ matches a b but not ab
S A single non-whitespace character /aSb/ matches a2b but not a b
The tab character. (ASCII 9) / / matches a tab.
w A single word character – alphanumeric and underscore /w/ matches 1 or _ but not ?
W A single non-word character /aWb/i matches a!b but not a2b
Mar 122009
 

I wanted a way to not show the MS-DOS prompt while I was working and the Event Log Emailer was running.  So I found two methods to have the Event Log Emailer or any MS-DOS prompt not show up while a batch is executing.

Shortcut Method:  To have an MS-DOS Batch file run in a minimized window Create a shortcut to the Batch file. Right-click the shortcut and click properties. In the properties dialogue, symptoms choose run “Minimized” in the pull-down box. Run the Batch file from the shortcut.

image

VB Script Method:  To have an MS-DOS Batch file run in a hidden window with no MS-DOS prompt nor any corresponding Taskbar button, pill begin your batch with @ECHO OFF and end with CLS and/or EXIT, for sale or the process may stall invisibly.

If my batch was called C:BizTalkEvent Log EmailerTEST.bat , create the following .VBS file:

CreateObject(“Wscript.Shell”).Run “””C:\BizTalk\Event Log Emailer\TEST.bat”””,0

Notice the extra pair of quotes around the path/filename and escape “\” to ensure the vb script can find the file even with the spaces in the path/filename.

This method requires Windows Script Host to be installed. Double-click or execute the .VBS file. For example, with the START command: start MyFile.VBS to run the Batch file invisibly.

Mar 102009
 

The easiest way I’ve found to do HL numbering is to use a global variable inside of a map and increment it for every HL segment you need to produce.

Here’s an example:

image

This is the 2nd HL segment, so here I’m needing an incrementing HL01 and the previous HL number in the HL02.

The inline C# script for the HL01 would  be:

int HL01;
public int increaseHL01()
{
HL01++;
return HL01;
}

The inline C# script for the HL02 would be:

public int HL02()
{
return HL01 -1;
}