SharePoint 2007 Forms Authentication Error "Value cannot be null."

Every so often, users logging into a SharePoint 2007 portal via forms authentication with a custom membership provider get the following error message. Once the message starts to be received, it will continue to function in that manner.

Value cannot be null.
Parameter name: value at System.String.EndsWith(String value, StringComparison comparisonType)
at Microsoft.SharePoint.ApplicationRuntime.SPRequestModule.PostAuthenticateRequestHandler(Object oSender, EventArgs ea)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

The error was not resulting from any of my code which made this incredibly hard to figure out. If you restart the IIS and the OWSTimer, the issue goes away (for a while), but doesn't fix this issue.

This morning I was searching around with a very odd set keywords and ran across this article, Fixing the Elusive “Value Cannot Be Null” FBA Authentication Error. The page doesn't contain the error text or a specific solution, but it does contain a screen shot of exactly what I was experiencing.

I have included the text and solution in this blog to make it crawl-able.

The solution:
The issue appears to be the fact that the web.config, C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\web.config, in the layouts directory of the hive has batch compilation set to false.

<compilation batch="false" batchTimeout="600" maxBatchSize="10000" maxBatchGeneratedFileSize="10000" />

The line should read:

<compilation batch="true" batchTimeout="600" maxBatchSize="10000" maxBatchGeneratedFileSize="10000" />

After making this change, the performance of the site in general improved significantly on the Windows and Forms authentication sides. It looks like an all-around win.


Batch Compilation Information:
From Microsoft's KB article, How to use the "batch" attribute of the Web.config file compilation element in SharePoint Server 2007 and in Windows SharePoint Services 3.0:

The batch attribute is used by the ASP.NET compilation element. This attribute controls all compilation for applications to which the Web.config file applies.

When the attribute is set to "true," the delay that you experience when you access files for the first time can be decreased. This is because, when the batch attribute is set to "true," all uncompiled files will be compiled in batch mode by ASP.NET.

However, for larger applications, there may be a significant delay when files are compiled for the first time because there are more file batches to compile. After the initial compilation, delays are decreased when you access the compiled files.

Microsoft has an excellent web performance best practices site, Developing High-Performance ASP.NET Applications. The pertinent part for our issue:

Consider precompiling
A Web application is batch-compiled on the first request for a resource such as an ASP.NET Web page. If no page in the application has been compiled, batch compilation compiles all pages in a directory in chunks to improve disk and memory usage. You can use the ASP.NET Compilation Tool (Aspnet_compiler.exe) to precompile a Web application. For in-place compilation, the compilation tool calls the ASP.NET runtime to compile the site in the same manner as when a user requests a page from the Web site. You can precompile a Web application so that the UI markup is preserved, or precompile the pages so that source code cannot be changed. For more information, see How to: Precompile ASP.NET Web Sites.

This article explains in depth how debug and batch compilation works. It is worth the read. ASP.NET Resources - Beware Of Deploying Debug Code In Production

XQuery Rename/Replace Xml Node and Update K2 Workflow Process Instance Xml Fields

I recently ran across a case where a property in our application was re-termed due to a change in requirements. Updating the code was very easy, however it meant that all of the Xml documents needed to be updated and that was not going to be as easy. It would involve updating the serialized object xml documents in our workflow data repository and K2.

Out of curiosity I ran the process using the old Xml to see what would happen. Deserializing the old Xml documents resulted in everything after that renamed property being omitted from the deserialization and it didn't throw and error. That was unexpected, but good information to know.

As it turns out, updating the workflow data repository was easy. K2 has many issues which resulted in the K2 update sql. The code below shows how I accomplished this:

CREATE PROCEDURE [dbo].[usp_brock_RenameXmlNode]
AS
BEGIN
 -- UPDATE Workflow Instances
 UPDATE [WorkflowInstances] SET InstanceData.modify('
  insert element NewTag {/ObjectXML/OldTag/text()} after (/ObjectXML/OldTag)[1]
 ')
 WHERE  WorkflowID='7B4DEB62-D462-4FE8-A8E2-2057B5F31B19'
   AND InstanceData.exist('/ObjectXML/OldTag') = 1
 UPDATE [WorkflowInstances] SET InstanceData.modify('
  delete (//OldTag)[1]
 ')
 WHERE  WorkflowID='7B4DEB62-D462-4FE8-A8E2-2057B5F31B19'
   AND InstanceData.exist('/ObjectXML/OldTag') = 1
 
 -- UPDATE K2 Workflows
 -- Create Table Var and Populate
 DECLARE @K2ProcXml2XmlType table
 (
  ProcInstID INT PRIMARY KEY,
  WorkflowName VARCHAR(128),
  WorkflowID UNIQUEIDENTIFIER,
  InstanceData XML,
  IsModified BIT
 )
 PRINT 'POPULATE TABLE VAR';
 INSERT INTO @K2ProcXml2XmlType 
  SELECT pin.ID
     ,ps.[Name] WorkflowName
     , wi.WorkflowID
     ,CONVERT(XML,REPLACE(CAST(f.string as nvarchar(max)),'<?xml version="1.0" encoding="utf-8"?>','')) InstanceData
     , 0 --IsModified
    FROM [K2].[dbo]._FieldOnDemand f INNER JOIN [K2].[dbo]._ProcInst pin ON f.ProcInstID=pin.ID
    INNER JOIN [DemoDatabase].dbo.WorkflowInstances wi ON wi.ProcessID=pin.ID
    INNER JOIN [K2].[dbo]._Proc p ON pin.ProcID = p.ID
    INNER JOIN [K2].[dbo]._ProcSet ps ON p.ProcSetID = ps.ID
  WHERE  (ps.[Name] in ('WorkflowX'))

 UPDATE @K2ProcXml2XmlType
 SET InstanceData.modify('insert element NewTag {/ObjectXML/OldTag/text()} after (/ObjectXML/OldTag)[1]'), IsModified = 1
 WHERE (InstanceData IS NOT NULL) 
  AND (InstanceData.exist('/ObjectXML/OldTag') = 1)

 UPDATE @K2ProcXml2XmlType
 SET InstanceData.modify('delete (//OldTag)[1]'), IsModified = 1
 WHERE (InstanceData IS NOT NULL) 
  AND (InstanceData.exist('/ObjectXML/OldTag') = 1)


 -- End Result of all updates
 --PRINT 'QUERY RESULT:';
 --SELECT
 --        [ProcInstID]
 --   , WorkflowName
 --   , InstanceData
 --  FROM @K2ProcXml2XmlType src
 --WHERE src.IsModified = 1;

 -- Populate Original Table and Query
 PRINT 'POPULATE TABLE FROM VAR:';
 UPDATE dst
 SET dst.String = CONVERT(ntext,'<?xml version="1.0" encoding="utf-8"?>'+CAST(src.InstanceData as nvarchar(max)))
 FROM @K2ProcXml2XmlType src INNER JOIN [K2].[dbo].[_FieldOnDemand] dst ON src.ProcInstID = dst.ProcInstID
 WHERE src.IsModified = 1;

        -- END K2 Update
END;
GO

exec dbo.usp_brock_RenameXmlNode

DROP PROCEDURE dbo.usp_brock_RenameXmlNode

It runs quite fast.

There are a couple caveats, you can't create the XPath location dynamically (unless you are using dynamic sql which I try to avoid at all costs). This means that you can't pass old and new name parameters into the stored procedure and update the records. Disappointing as it is, any future renames should require an easy find and replace.

I tried using a cursor to do this type of updating, but the resulting SQL in execution times in more than more than 90 minutes (I cancelled it at that point) and verified with the DBA's that I was not doing something wrong, I abandoned that path and went back to the set based updates.

Visual Studio 2008 Post-Build Event and Minify Using YUI Compressor

A time tested way to shrink and optimize JavaScript and CSS is to minify and obfuscate your files. Yahoo (High Performance Web Sites Rule 10) and Google (Minimize Payload Size) have best practices recommending minifying JavaScript.

The YUI Compressor is an excellent open source tool for this; the best I have found. My real world use reduced the size of the regular CSS and JavaScript files by 45%. Gzipping the files reduced CSS files by an additional 25% and JavaScript files by an additional 36%. Resulting in 75%+ smaller files.

Here is a batch file snippet which should speed up implementing this into your build process:

SET YUICOMPRESSOR=%Minify%\yuicompressor-2.4.2.jar
IF "%JAVA_HOME%" == "" (
 ECHO Searching for Java...
 for /f %%j in ("java.exe") do (set JAVA_HOME=%%~dp$PATH:j)
)
ECHO Java: %JAVA_HOME%
ECHO YUICOMPRESSOR: %YUICOMPRESSOR%
IF EXIST "%YUICOMPRESSOR%" (
 IF NOT "%JAVA_HOME%" == "" (
  ECHO Deleting Minified files, TFS can make these readonly and the compressor will not overwrite read only files...
  del /F "%CD%\<Path To Script Directory>\core.min.js"
  del /F "%CD%\<Path To Styles Directory>\core.min.css"
  ECHO Done.

  "%JAVA_HOME%java" -jar "%YUICOMPRESSOR%" -v --type JS -o "%CD%\<Path To Script Directory>\core.min.js" "%CD%\<Path To Script Directory>\core.js"
  "%JAVA_HOME%java" -jar "%YUICOMPRESSOR%" -v --type CSS -o "%CD%\<Path To Styles Directory>\core.min.css" "%CD%\<Path To Styles Directory>\core.css"
 ) ELSE (
  ECHO ERROR: Java is not installed, no Minification
 )
) ELSE (
 ECHO ERROR: YUI Compressor not found.
)

Also note that if a build event echos a line starting with "ERROR", not sure if it is case sensitive, the Visual Studio and Team Foundation Server will set that step as a failure and alert as expected. Remove the "ERROR:" text from the echo lines to allow the build to proceed if it cannot find the compressor or java.

jQuery Predicate Selectors and ASP.NET

Well, I finally get to blog on something that I get to use in my every day life. There is not much information on the internet regarding to jQuery predicate selectors, so I figured I would blog about how I used them.

The need came up when I was trying to move JavaScript into an external file. The code was put on the page because it referenced ASP.NET controls on the page requiring the use of the old dynamic JavaScript/ASP.NET client ID injection pattern. It seemed like jQuery should be able to find the controls given the last part of the ID. After many google searches and changing of terminologies (I can't remember the search that led me to the solution), I ran across a post from Ben Nadel titled Cool jQuery Predicate Selectors. He describes exactly what I was looking for and more:

Start With: ^=
The ^= operator will filter elements whose attribute starts with the given value.

Ends With: $=
The $= operator will filter elements whose attribute ends with the given value.

Contains: *=
The *= operator will filter elements whose attribute contains the given value.

He goes on to demonstrate how to use these tools, but doesn't go into how it can be used in ASP.NET; that's where I pick up.

Using basic JavaScript to reference an ASP.NET control you have to put a block of code in the aspx page. For example:

<script>
aspnetControl = document.getElementById("≶%= serverControl.ClientID %>");
</script>

This is all well and good until there are a large number of ClientID's in your page. They are generally very long and can bloat the size of your rendered page. We can use the predicate selectors to improve this case. Lets assume the ClientID ends with "serverControl", per the example above, and that it renders an html input tag.

<script>
aspnetControl = $("input[id$='serverControl']");
</script>

Short and sweet. This can be refactored and placed into an external file which can be cached.

As for performance, it is safe to assume that the document.getElementById call would be fastest followed by using the jQuery ID selector (which uses the getElementById command) then the predicate selectors. In my real world use of predicate selectors, it seems to be as fast or faster than the the other methods using IE 8 and Firefox 3.0/3.5.

This can go a long way to decreasing the page size and loading/rendering speed of the page. According to Google's Page Speed Best Practices:

You should see a benefit for any file that can be reduced by 25 bytes or more (less than this will not result in any appreciable performance gain).

SQL Server 2005 Extract Bitwise Flags

UPDATE: 8/20/2009 - This post has been updated several times, so read through for all of the information. I ran into a scenario where I had a numeric field which was composed of flags and I needed to extract them. I was told that you couldn't do bitwise (AND/OR) operations, but some undocumented bitwise system functions existed (like fn_IsBitSetInBitmask). Trying to dig into the system function was not very easy and having no real documentation outside of the "exec sp_helptext 'fn_IsBitSetInBitmask'" query, I wasn't very successful.

I started to have memories from my Digital Logic class where we had to do something similar using modulus with a constrained feedback loop. I pseudo-coded an example function, but found that SQL doesn't support arrays (thinking now, I probably could have used a table variable, but that would have needlessly complicated the situation). Regardless of SQL's deficiencies, using the modulus/feedback method does work, but wasn't going to fit my vision of the solution.

function BitFlags(int val) {
 bool[] arr = new bool[8];
 int modulus;
 for(int i=7; i >= 0; i--) {
  int pwr = (2^i);
  if (val == pwr) {
   arr[i] = true;
  } else if (val > pwr ){
   modulus = val % (2 ^ i);
   if (modulus != 0) {
    arr[i] = true;
    val = modulus;
   }
  }
 }
 return arr;
}

For those who haven't used their math skills in a while, here is an example. My Digital Logic skills are a bit rusty, so I may be incorrect, feel free to correct me if I am wrong. We want to find the bit flags for the number 76; using the modulus/feedback method, we get something like:

// Background
2^7 = 128
2^6 = 64
2^5 = 32
2^4 = 16
2^3 = 8
2^2 = 4
2^1 = 2
2^0 = 1

// Now the fun part, cycle through the range 0-7 for 2^i:
// Calc         Output    Description
76 % 128 = 0    [0]    // Feedback 76
76 % 64  = 12   [1]    // Feedback 12
12 % 32  = --   [0]    // 12 is smaller than 32, Feedback 12
12 % 16  = --   [0]    // 12 is smaller than 16, Feedback 12
12 % 8   = 4    [1]    // Feedback 4
4  % 4   = --   [1]    // 4 == 4, so the we get a 1 for the output and there is nothing to feedback, so everything past that point is 0
0  % 2   = --   [0]    // 0 is smaller than 2, Feedback 0
0  % 2   = --   [0]    // 0 is smaller than 2, Feedback 0

Something just did not settle well with me. You just had to be able to do bitwise operations; it seems like elementary funcitonality. I took my C skills and tried to them out in SQL. It threw me for a loop when after evaluating the @val & <number> expression that I would get back the <number> where I was expecting a 1, but 0's everywhere else. I new I was on the write path and was almost there. I did some googling and found:

http://www.sqlusa.com/articles2005/binarypattern/

Which was doing something similar, but didn't exactly fit the situation. It gave me a DUH! moment when I realized that I should just divide by the <number> and POOF, I had the elegant solution I was expecting.

Try it out for your self.

DECLARE @val as tinyint
SET @val = 128+64+16+1

SELECT  (@val&128)/128
      , (@val&64)/64
      , (@val&32)/32
      , (@val&16)/16
      , (@val&8)/8
      , (@val&4)/4
      , (@val&2)/2
      , (@val&1)/1

--RESULT:
--11010001

UPDATE: My good friend Christopher Lauer used my code and showed me how he used it. I did not find anything like this when I googled and thinking that it would be useful for other people he gave me permission to include it.

USE MSDB;

if not exists (select * from sys.schemas where name = 'my_utility')
 exec('create schema my_utility')
go

/*****************************************************************************************
 DESCRIPTION: The MSDB.dbo.sysSchedules has a column named freq_interval, this 
 column may contain a packed bit that needs unpacked to understand what days
 the schedule is set to run on.  See books on line for information on this
 table and column.  This function will unpack the bit and return a string 
 of weekday names that the bit respresents.

 Base code provided by Brock Moeller
 --SELECT [Monday] = (@bitWise&2)/2, [Tuesday] = (@bitWise&4)/4, [Wednesday]= (@bitWise&8)/8, [Thursday] = (@bitWise&16)/16, [Friday] = (@bitWise&32)/32, [Saturday] = (@bitWise&64)/64, [Sunday] = (@bitWise&128)/128

 MAINTENANCE: 
 [date:name]      [description of the change/maintenance being done]
 ==============================  ==================================================
 8/19/2009: Christopher M. Lauer  Created function.
******************************************************************************************/
CREATE FUNCTION [my_utility].[udf_getDayNameFromBitWise](@bitWise AS INTEGER)
RETURNS Varchar(62) AS
BEGIN
 --SET NOCOUNT ON;
 /*
 DECLARE @bitWise INT;
 --SET @bitWise = 63; --Monday, Tuesday, Wednesday, Thursday, Friday
 */

 /***** LOCALS ******/
 DECLARE @ErrorCode INT, @RowsAffected INT;
 SELECT @ErrorCode = 0, @RowsAffected = 0; -- default
 /***** END LOCALS ******/

 DECLARE @WeekTable TABLE(dName VARCHAR(10), isUsed bit, dNumber tinyint, bitWise int)
 INSERT INTO @WeekTable(dName, isUsed, dNumber, bitWise)
 SELECT 'Monday', (@bitWise&2)/2, 1, 2
 UNION ALL 
 SELECT 'Tuesday', (@bitWise&4)/4, 2, 4
 UNION ALL 
 SELECT 'Wednesday', (@bitWise&8)/8, 3, 8
 UNION ALL 
 SELECT 'Thursday', (@bitWise&16)/16, 4, 16
 UNION ALL 
 SELECT 'Friday', (@bitWise&32)/32, 5, 32
 UNION ALL 
 SELECT 'Saturday', (@bitWise&64)/64, 6, 64
 UNION ALL 
 SELECT 'Sunday', (@bitWise&128)/128, 7, 128
 --SELECT * FROM @WeekTable

 DECLARE @ReturnValue as VARCHAR(50)
 SELECT @ReturnValue = COALESCE(@ReturnValue, '') + dName + '; ' 
 FROM @WeekTable
 WHERE isUsed = 1;
 
 IF(LEN(@ReturnValue) > 1) 
    begin
   SET @ReturnValue = substring(@ReturnValue,1,len(@ReturnValue) -1)
    end
 RETURN @ReturnValue
END

UPDATE: 8/20/2009 - I had a little free time and discovered an odd occurrence with bitwise operations.

DECLARE @val as tinyint
SET @val = 128+64+16+1
SELECT (@val&10000001)
--RESULT 129
SELECT (@val&01000001)
--RESULT 65

SELECT (@val&00100000) -- @val&32
--RESULT 128  ---- Incorrect!  Should be '0'.  

It appears that SQLServer 2005 implicitly interprets the number as binary. I can't find any documentation on this issue. The results are accurate for scenarios where what you are looking for matches. However, if you test on something that doesn't match the left value, it doesn't return the correct number. I suppose an answer will have to wait.

On a side note, hexadecimal numbers work as expected like their decimal counterparts.

DECLARE @val as tinyint
SET @val = 128+64+16+1
SELECT (@val&0x40)
--RESULT 64

SELECT (@val&0x20)
--RESULT 0

It still appears that sticking with base 10 is easiest, but if you are good with hex you may find that usefull.

It's MSDN, but More Usable

So, I started writting this blog back in April and completely forgot about it.  Better to post late than never I suppose.

I use the MSDN site on occasion and have always found it rather wanting.  Not because of a lack of content, but the speed of the site and the small content view caused by the large header (which you previously could not get rid of without some GreaseMonkey magic).

It appears that people are listening and there are some easter eggs that you can play around with.  Scott Hanselman has a great write-up on the easter eggs and Jon Galloway has a more focused post.  Apparently some of this information came out late last summer, but I had not heard of it until a couple months ago and it's never too late to spread the information.

There are 3 easter eggs that I am a huge fan of, namely the Low Bandwidth view, PDA view, and the Visual Studio 2010 view. 

To get at the easter eggs, it requires some URL magic.  The MSDN developers use a form of URL routing (read Scott's post if you want a more information) using the contents of the parathesises as switches.  You may have seen the "(printer)" prefixed to the "printer friendly" view before.  This uses the same concepts.

My favorite is the "Low Bandwidth" view: (loband)

You can test it out by checking out: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spsolution(loband).aspx

The "PDA" version (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spsolution(pda).aspx) is very nice when you just want the information and just the information.  The Visual Studio 2010 version (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spsolution(dev10ide).aspx) will probably be my favorite in a little while... well it would be if it still worked.  Perhaps Microsoft is pulling a trick from the TV studio's and getting read of the good stuff.  At any rate, at least they the loband and pda version's still work.

User Interface Engineering

It must be a reoccurring trend that every 6 months I find myself searching for new User Interface best practices. I figured I would blog about it, so I can reference it later and update it as I find new gems.


Jakob Nielsen is a user interface design expert and has a lot of information on his web site (http://www.useit.com) and blog (http://www.useit.com/alertbox/).  Some of his styles might seem a bit bland, but his design principles are very useful.  One post in particular "Top Ten Mistakes in Web Design" (http://www.useit.com/alertbox/9605.html), is a be very useful starting point in designing pages and general content.

There are 2 best practice rules of thumb that stem from #8 summary in the link above:
1) look at how other sites handle the situation and pick the best one
2)  people will spend most of their time using other sites, so unless the company is a web innovator, follow the heard and don't stray too far from it.

Donald Norman (http://www.jnd.org) is another user interface design expert.  He focuses on emotional design and human-centered design.  He does a good job of analyzing interface design in many areas most people probably don't think about (not just for the web) and does it
without getting into technical jargon.  He has a lot of blogs (http://www.jnd.org/index.xml) and essays (http://www.jnd.org/dn.pubs.html) on his site for free.

Jef Raskin (http://en.wikipedia.org/wiki/Jef_Raskin) is one of the most famous interface design experts.  He is credited with the interface design for the Apple Macintosh UI.  He focuses on intuitive interface design.  He wrote an excellent book called "The Humane Interface" (http://books.google.com/books?id=D39vjmLfO3kC&dq=jef+raskin&printsec=fr
ontcover&source=bl&ots=COpDb3aX-a&sig=oNwG4mfm_cALGt3NlRAQr9UJOnA). I read it in college.  "Jef Raskin on 'Intuitive Interfaces'" (http://www.asktog.com/papers/raskinintuit.html) would be a good read to further understand Jakob's #8.

- Other good references -
Rules of Thumb for Web Design
http://www.firelily.com/opinions/thumb.html

Yale Style Manual - Web Style Guide
http://www.webstyleguide.com/index.html?/contents.html

GreaseMonkey Daptiv Fix to Allow Multiple Item Popup Windows

I have found that the paradigm that Daptiv uses to display items in a list to be very prohibitive. The biggest issue I have found is that by default (and they offer no way to change the behavior) that they only allow one item window to be open at the same time. I have contacted Daptiv to see if there was a way to change it (there wasn't). So I turned to GreaseMonkey to save the day. Here is the GreaseMonkey script to allow multiple item windows open at the same time.

// ==UserScript==
// @name           Daptiv Change Window Name and Title
// @namespace      http://na04.daptiv.com/global
// @include        http://na04.daptiv.com/global/itembrowser.aspx
// ==/UserScript==

if (window.name == "IB_Grid") {
 window.name = "b"+String(Math.random());
 // Change the title
 var items = document.getElementsByClassName("itembrowser-itemlabel");
 if (items.length > 0) {
  var item = items[0].innerHTML.replace(/^\s+|\s+$/, '');
  document.title = "Item: " + item + " - " + document.title;
 }
}

I added a bonus to the script in that it will change the title of the window to include the "Name" of the item you clicked on.

This in addition to the speed improvements in FireFox 3 (I can't wait for 3.5 to come out) make Daptiv actually usable. There are other improvements I would like to see, but I will leave fixing those things to another time.

Twitter... Great Service, Seeking Interest

I signed up for a Twitter account, http://twitter.com/brockmoeller.

I get writer's block while blogging from time to time and find that it takes a while to get into blogging. With most things that I end of making myself do.
Not sure how much I am going to use it. It seems like a great service and is definitely useful in many situations. The big downside being that I tend to think in questions/statements that are larger than 140 characters which will likely hinder efforts to use it - we'll see. Perhaps my interest will be peeked if I find enough people to follow and/or enough people want to follow me.

SQL Server 2005 Table Row Counts... One Select Statement, No Table Scan

A big change that occurred with SQL Server 2005 (from 2000) is that the system table information is always in sync with the actual tables.

When the system tables get out of sync in SQL Server 2000 the following SQL statement must be run to synchronize them.

dbcc updateusage(0)
GO 

According to the MSDN site on DBCC UPDATEUSAGE (Transact-SQL) contains this little bit of information:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Googling for information on getting table counts without a row scan yields several posts on the issue, but they involve creating functions and needlessly complicating a simple SQL statement.

There are 2 possible solutions. 1) Common Table Expressions and 2) Use a join. The first option looks like this:

WITH CTETableRowCount
AS
(
 SELECT 
   rows rowCnt
 , ID ObjectID 
 --, si.*
        FROM sysindexes si
        WHERE indid < 2
)
SELECT 
  so.[name]
, cte.[rowCnt] as [Row Count]
--, so.*
FROM sysobjects so LEFT JOIN CTETableRowCount cte ON so.id=cte.ObjectID
WHERE type='U' and so.name != 'dtproperties'
ORDER BY so.[name]

This works and is reliable, but a bit overkill. There is nothing in the Common Table Expression that really requires it to be like that and in fact just adds lines to the query with no benefit The above query can be simplified to the following:

SELECT 
   so.[name]
 , si.[rows] as [Row Count]
 --, so.*
FROM sysobjects so LEFT JOIN sysindexes si ON so.id=si.ID
WHERE (type='U') and (so.name != 'dtproperties') and (si.indid < 2)
ORDER BY so.[name]

This is probably the best solution to the issue.

Duplicate Entries in a Mapping Table with All Guid/Uniqueidentifier Columns

I recently ran into a case where a custom .net membership provider was incorrectly adding a record to a map table every time a user was updated. This wouldn't normally be a problem, except SharePoint's user security has a couple hang-ups when the number of groups doesn't match the distinct group list.

Needless to say, there was a LOT of duplicate records in the table; more than I was willing to weed through by hand. After performing the obligated web search, I couldn't find anything that fit my situation. Common Table Expressions looked promising, but had several limitations that I wasn't able to work around.

The problem could be prevented from the beginning with the correct keys/constraints, but that was not the case and I needed to clean up the table before making the correction.

The first bit of information that tells me how I am going to solve the issue is the DBMS. In this case it is an SQL Server 2005 instance.

Lets setup an example, you have a table with 3 "uniqueidentifier" columns: "ID", "FromMap", "ToMap".

CREATE TABLE [dbo].[DuplicateGuidMap](
 [ID] [uniqueidentifier] NOT NULL,
 [FromMap] [uniqueidentifier] NOT NULL,
 [ToMap] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

Now lets populate the table. You can skip this part, nothing unusual here.

INSERT INTO [Test].[dbo].[DuplicateGuidMap]
           ([ID]
           ,[FromMap]
           ,[ToMap])
SELECT '0f6f2339-4597-4933-b8b0-de04804f4c04','e0bb5dad-f30e-4bcb-a4ec-12363af0e6cd','8a15fde9-f9f3-4ecb-acd4-9c78274f9d95' UNION ALL SELECT
'1a0d0991-6a12-4621-901d-0ca58a4fbf5a','88c3d3aa-663c-41e9-bdcf-f9022604b5fc','d4757f79-3452-4a3e-a4d8-1f2ea3a26c2a' UNION ALL SELECT
'14bc9408-9537-4d2a-b917-6e1b44099799','778cffba-a14b-447f-8648-96d640ae1b93','dd20127d-33ab-4d91-9202-8196a2ca90d6' UNION ALL SELECT
'3801a162-cdef-4ffc-b4b5-928f345fe68f','77121133-8c4f-4e28-b9fa-048707c41a7a','faae6e93-e91d-4729-8b69-367401130a42' UNION ALL SELECT
'e8386333-0e89-4ec0-8b2a-e16ae9544290','d385ea64-68f9-421c-bda0-3c0d9fd785e8','442fd8b3-a5d3-4074-9886-e9104d0f2421' UNION ALL SELECT
'a4b5dacb-3f21-43ea-b516-e7055c3831d1','10b7ec11-0cb8-4128-a146-2ca2ef0a9594','c09aa0e7-96fd-41fa-8fce-105a4ce908ee' UNION ALL SELECT
'2c340b29-cd5d-4036-92af-7ae24bdc47d9','dde6379c-6626-46c0-ac70-b7ddd0377e62','5ac17542-fc1d-4581-a616-5dd602e5f34f' UNION ALL SELECT
'd6d56501-98e0-4662-a72c-116b6e2a2e4c','08d0200d-375a-4022-af9e-bdc5ff4a57fe','4e001ae6-d8a0-45b5-a30a-1ab95b66dae4' UNION ALL SELECT
'ee392830-d077-48c0-a817-5a2169dc8bb6','abd1ed2d-3f4d-4f8d-9240-7aebc46ff021','3fdc2214-e11d-411d-b524-7cdd57037f6c' UNION ALL SELECT
'a37b664d-1735-444b-a326-2d23f8bf0b55','c0a1a8a0-efdd-4740-807a-f5f0aaba90ed','f95a288d-3e6c-43a4-9b59-54d382825e99' UNION ALL SELECT
'1d8a2558-0f50-4765-98a9-dd2cf2127bcf','e0bb5dad-f30e-4bcb-a4ec-12363af0e6cd','8a15fde9-f9f3-4ecb-acd4-9c78274f9d95' UNION ALL SELECT
'577a05de-4bbc-4b8d-93dc-22f9dd838e43','e446232a-02bb-47a0-b9a1-e5fab5c0aba3','faae6e93-e91d-4729-8b69-367401130a42'

We will assume that the relationship of "FromMap" to "ToMap" is 1 to Many, meaning that each "ToMap" should be unique. A good place to start would be to find out how many "ToMap" duplicates there are.

SELECT DISTINCT ToMap
  FROM DuplicateGuidMap
GROUP BY ToMap
HAVING Count(ToMap) > 1

There are 2 potential sets of duplicate records in the table now. ToMap:

• FAAE6E93-E91D-4729-8B69-367401130A42
• 8A15FDE9-F9F3-4ECB-ACD4-9C78274F9D95

Getting the list of all of the duplicated records is a trivial join, but I want a query that would return only the duplicate rows (leaving one of the duplicated records). Many DBA's who have worked for years would use cursors and possibly create functions. This is all well and good, but they are rather slow and consume a lot of CPU time. The idea of one SQL statement to rule them all doesn't come to mind. I tend to think in sets. I haven't found too many things I can't do in one Select statement. Knowing this, I banked heavily on my understanding of how SQL works and created the following query.

SELECT 
        dgm.ToMap
   , dgm.FromMap
   , dgm.ID
  FROM DuplicateGuidMap dgm
WHERE ToMap IN (
 SELECT DISTINCT ToMap
   FROM DuplicateGuidMap
 GROUP BY ToMap
 HAVING Count(ToMap) > 1
) AND ID NOT IN (
 SELECT TOP 1 ID
   FROM DuplicateGuidMap
 WHERE ToMap = dgm.ToMap
)
ORDER BY ToMap

The meat of the query lies in the Where clause.

ID NOT IN (
 SELECT TOP 1 ID
   FROM DuplicateGuidMap
 WHERE ToMap = dgm.ToMap
)

What's happening here? Since we are currently in the Where clause, we are banking on the fact that it is evaluated for every row that passes the first condition. So in the subquery, we are selecting the record we want to keep. The Where clause "ToMap = dgm.ToMap" grabs the "ToMap" in the main query for use in the subquery. This functionality works almost the same way as a SQL function without having to load and unload the function from memory every time. Having this included in the statement makes the query run faster. In a table with ~10,000 records, ~79 unique duplicates, and ~196 duplicates to remove the query took "00:00" (min:sec).

There is a couple points to make before we proceed. The Guid 'FAAE6E93-E91D-4729-8B69-367401130A42' case is not a valid dupicate and never occurred in the scenario. I added this case to dive farther into controlling which duplicates are actually valid. The only valid duplicate is '8A15FDE9-F9F3-4ECB-ACD4-9C78274F9D95' because the "FromMap"'s are the same. This is the solution I developed for originally.

We need to look at the "ID" Where clause. We can refine it to be: "FromMap = dgm.FromMap AND ToMap = dgm.ToMap".

The final query looks like the following. It returns a list of the duplicate records ready to delete leaving one of the duplicate records.

SELECT 
        dgm.ToMap
   , dgm.FromMap
   , dgm.ID
  FROM DuplicateGuidMap dgm
WHERE ToMap IN (
 SELECT DISTINCT ToMap
   FROM DuplicateGuidMap
 GROUP BY ToMap
 HAVING Count(ToMap) > 1
) AND ID NOT IN (
 SELECT TOP 1 ID
   FROM DuplicateGuidMap
 WHERE FromMap = dgm.FromMap AND ToMap = dgm.ToMap
)
ORDER BY ToMap

Never stop thinking outside the box. Most of the time you can find a simple answer with a little extra thought.

So That's What I've Been Doing All Along

I think a fascinating part about life is the things that I come up with in my mind, only to discover later on (sometimes years) a different vocabulary in which to use which describes the same ideas and principles I had come up with. I don't care that I wasn't the first to come up with them because I am a student of knowledge and am always excited to learn new things. Even if it is adjusting my vocabulary so I can dive into a newly "discovered" subject to gain new perspectives and further understand.

I tend to find human interaction and communication very interesting and sometimes a bit foreign. I tend to look at situations in the third person; putting emotions into a situation tends to exaserbate it.

My interest in the interactions, controlling them, and analyze them started in high school when I started reading a lot of philosophy. Being a quiet geek in school, I had a lot of time to observe and ponder. I later "experimented" with seeing if there were ways to control situations or at least influence them. I realized very quickly how powerful of a tool this is/was and that it could be used for good and bad. I resolved myself to thought experiments.

Logic, philosophy, and critical thinking have been quiet personal hobby of mine ever since. I took several excellent courses in college on them. I attribute them to my distaste for bad reporting on many new stations.

Several good web resources:

• http://www.google.com/search?hl=en&safe=off&client=firefox-a&rls=org.mozilla%3Aen-US%3Aofficial&hs=c0x&q=Critical+thinking&btnG=Search
• http://www.nizkor.org/features/fallacies/
• http://people.hofstra.edu/Stefan_Waner/RealWorld/logic/logicintro.html
• http://books.google.com/books?hl=en&id=sAe6GppFPDAC&dq=introduction+to+logic&printsec=frontcover&source=web&ots=ubOD-xO6Vv&sig=tHir3LuRQNf9-9WDu9ApwGqbSoU&sa=X&oi=book_result&resnum=9&ct=result#PPR5,M1

I used them to enhance and refine my thought experiments.

A little while ago I ran across a blog post (sorry, I can't remember which one) from Mind Your Decisions by Presh Talwalkar and discovered that much of what I was doing in my mind was a major subject. Presh blogs on "Game Theory". The subject applies to many aspects of hard and soft sciences and mathematics. I find it quite amazing how the same ideas and principles I use to analyze situations in the sciences, mathematics and life are used by other people in a different vocabulary.

Discovering a new vocabulary that explains your own ideas is a lot like having a library of books in an unknown language and then suddenly being able to understand it. With a side of Christmas morning. Since that time, I have taken to any knowledge of game theory like a starving person to a sandwich.

Oh what I will learn tomorrow... I can hardly wait.

Happy Learning.

Finding the Root Node Name with XQuery and SQL Server 2005

I recently had a case where i needed to test for the existence of a node in a query involving an XML. After researching I found a lot of posts indicating this as the solution:

DECLARE @x xml
SET @x = '
123 Main St.
'

DECLARE @query varchar(30)
SET @query = '/address/street'

SELECT @x.exist('sql:variable("@query")')

A key point to make is that the ".exist" is case sensitive; using ".Exist" causes an error. The result is "1", however when i would change @query to an invalid xpath, I would still get "1". Never the less, it was still a good start toward figuring out how to find the root node tag name.

Many posts I found used the 'sql:variable("@variable")' as a parameter in a xpath query. I am quite experienced w/ XPath, especially with Xsl, so I applied it to my situation and came up with this (simplified for example)...

DECLARE @tag varchar(30)
SET @tag = 'address'

SELECT ID, XmlField
FROM sometable
WHERE XmlField.exist('/*[1][local-name() = sql:variable("@tag")]')

The result of this one was 1. If a changed @tag to an invalid input, the result was 0. This is exactly what I was expecting and thus my solution.

Taking this further, the tag name can be returned by extending the previous example.

DECLARE @tag varchar(30)
SET @tag = 'address'

SELECT ID, XmlField.query('local-name(/*[1])'), XmlField
FROM sometable
WHERE XmlField.exist('/*[1][local-name() = sql:variable("@tag")]')

There you go, hope it helps.

Remote Debugging and Loading a Symbol File

While doing some exploratory SharePoint development, I ran across a peculiar situation. My development methods generally don't require a lot of remote debugging, but since I was exploring possibilities, I dove farther into it. I deployed my solution to my server, started remote debugging on the server, and attached to the w3wp worker processes.

I opened the Modules window (Ctrl+Alt+U) in Visual Studio and located my assembly. (Note: The image on the left does not contain my assembly, but is just for reference) The Symbol Status column had the phrase "Cannot find or open the PDB file." and didn't say "Symbols loaded." as I had expected. So, I tried to load the symboles by:

Right click on module -> "Load Symbols From" -> "Symbol Path"

This opened up an open file dialog. Selecting the PDB file on my hard drive produced an error about not being able to find the file. It was not immediately obvious why it couldn't find the file. After some experimentation, I discovered that the paths in the "Symbol File" column are specific to the computer being debugged and not translated by the remote debugging functionality. I had assummed that the symbol files loaded were from my local computer and not from the remote server. After I discovered this, I was able to correctly place the PDB file on the remote server, re-attach to the processes, and magicly the symbol loaded.

There isn't much information on the web on this topic, so hopefully this will save someone a headache.

Where Did Debug Go?

A little while ago I loaded a project into a new install of Visual Studio 2008. I didn't think about it much at the time, but the "Solution Configuration" dropdown list (the one w/ Debug/Release compile options) was disabled on the screen. I tried to do some debugging and set my breakpoints. When the debugger attached to the process it couldn't set the breakpoints; the code wasn't compiling in debug mode.

I looked at the Modules panel in VS2008 and found that the assembly had compiled with optimization "on". Apparently the last time I compiled I my project, it was in Release mode. I look at the project properties and found the settings to be that of the Release, confirming my previous thought. I looked at the top of the build properties page and there was no dropdowns to switch to a different build configuration.

I thought perhaps something in the project file was not setup correct. So I opened the project file in notepad and looked for the build options. Everything looked like it was in the correct place and further confirmed the Release mode settings.

My next place to check was in Tools -> Options. I looked in all of the options in the tree, nothing jumped out at me. I talked with my colleagues and no one knew how to fix my issue.

I turned to my friend Google and several searches after finding nothing that seemed to fix my issue I had a glimmer of hope. The forum question:

Visual Studio 2008's Build Configuration ONLY offers DEBUG build option -- where did RELEASE GO?

That was amazingly similar to what I was experiencing but I was stuck in Release mode. Awesome, I could feel the solution to my issue was at hand. I looked to find the post marked as the answer only to find disappointment. I continued reading the posts after that and found a post by InteXX which stated:

It's in Tools\Options\Projects and Solutions. Select the Show advanced build configurations checkbox and click OK.

Sweet success! I have added a screen shot to help.


I hope this post saves people time hunting around to fix this issue. There isn't much on the web pertaining to it and the option isn't very well located.