Apr 192007

After responding to a post from a mailing list regarding strange truncation of varchar(8000) fields when added together as a varchar(max), I noticed that I could only get 64K of text out of SSMS to the clipboard, although the length of the field was reporting the correct length.

It turns out that there is an option in SSMS to control the size of the result set, with defaults of 64K for a non-XML grid, 2MB for XML in a grid, and 256 characters in Text/Save to File. These options are available under the Tools | Options | Query Results | SQL Server section.

Apr 182007

After getting back from my honeymoon, I was immediately thrust onto a site which was suffering a serious error on the development server (Windows 2000, IIS5, Classic ASP). Every time a file in the ASP application was accessed an “Internal Error 500″ error would pop up as the Friendly Error Message, and “The system cannot find the file specified” as the “unfriendly” message. Allowing anonymous access to the site allowed the page to load, but the webapp wasn’t particularly useful without integrated security.

As it seemed to be a security setting, we turned off all permissions – still no luck. Looking through the log files, it was reporting as a HTTP error 401 (Unauthorised). After some more hunting, it was established that it was suberror 5, but IIS5 doesn’t easily report the suberror code for 401. Suberror 5 indicates that it was a custom ISAPI/CGI web application returning the error, and yes, there was a third party ISAPI component installed, by the name of UpFile. Disabling this ISAPI filter caused the problem to disappear.

I’m still not sure what the original problem was, but I’m leaning to a Windows hotfix installed on the development server breaking the component. As the problem hasn’t shown up in production, it’s still a mystery.

What was learned from this experience? First, it’s hard to find classic ASP information on the web, an impossible for this particular error, and secondly running a third party component from 2002 may not work in 2007 if anything changes on the machine (e.g., the operating system).

Apr 172007

I went to my first SQL Server user group meeting today in Microsoft’s Southbank Melbourne offices. It was quite an interesting night – tasty drinks, enough pizza to satisfy even the hungriest ninja turtle, and an excellent 2 hour session by Greg Low on the CLR integration in SQL Server.

I’ve listened to all of Greg’s SQLDownUnder podcasts over the past 12 months, and always enjoyed them, so it was great to actually be able to see him while he’s speaking. I have had a brief play around with the CLR integration in SQL Server, but nothing much past creating a simple UDF while I had a spare morning between projects. In his session, Greg took us through a quick rundown of what .NET is, and why the developers are begging the DBAs to let it into the database, and then launched into examples of what you can do with managed code running inside SQL Server. Interspersed throughout the benefits were some stern warnings of what NOT to do with managed code – although Greg didn’t explicitly say not to write, say, a SMTP server…

All in all, a great night!