Writing on Tablets Tech stuff from @yeltzland

Using a parameter in a SELECT TOP statement in SQL

Originally posted on my old MSDN blog

As usual, this may well be common knowledge but I found this useful.

In a stored procedure we were passing in a parameter (say @maxRows) which we wanted to use to dynamically limit the number of rows returned by the query.

For ages we couldn't find a way to do this without building up the SQL in a string using the parameter and then executing it e.g. something horrible like 'SELECT TOP ' + CAST(@MaxRows AS varchar(10)) + '...'

Now it turns out you are supposed to put the value used by the TOP in brackets anyway - see http://msdn2.microsoft.com/en-us/library/ms189463.aspx - so putting the parameter in brackets e.g. SELECT TOP (@MaxRows) ... is not only correct but gets around the horrible cast.

SQL Server Tip

debugger command in JavaScript

Originally posted on my old MSDN blog

I don't do much client-side JavaScript programming, and I've always had intermittent issues when trying to attach a Visual Studio debugger to a instance of IE when I'm trying to figure out what's going on.

However in August 2007's MSDN Magazine (doesn't seem to be available online yet - page 52 in magazine) it talks briefly about the debugger JavaScript command, which forces the browser to bring up the elusive "debugger dialog". Very useful!

I'm not sure how well this is supported across different browsers, but for my non-expert use knowing about the debugger command certainly will make my life easier when I step into the crazy world of JavaScript.

Some more details at http://support.microsoft.com/kb/816173 

JavaScript Tip

SQL Parameters and Types

Originally posted on my old MSDN blog

I found the following page interesting when having issues setting a value in a SqlParameter constructor.

From http://msdn2.microsoft.com/en-us/library/0881fz2y.aspx:

When you specify an Object in the value parameter, the SqlDbType is inferred from the Microsoft .NET Framework type of the Object.

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates

Parameter = new SqlParameter("@pname", Convert.ToInt32(0));

I guess it makes sense once it's explained, but it's not immediately clear when you just use 0 rather than Convert.ToInt32(0) why it doesn't work as expected

SQL Server

Performance Counters Fun

Originally posted on my old MSDN blog

Recently had some issues setting up some custom perf counters to install and run from an ASP.Net page that I thought I'd share.

We began by using the information in http://support.microsoft.com/kb/555129 which states:

"It turns out that the permission set required to update performance counters is much smaller than running as an Administrator or Power User.  In the registry key HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib, set the Access Control List so that the necessary user has Full Control. In our original example, the ASPNET user would be granted Full Control, but access can be granted to anyone who needs to update a performance counter."

However I mistakenly assumed that as that setting those permissions would also enable the counters to be created, which is wrong. From the comments in http://objectsharp.com/cs/blogs/bruce/archive/2003/12/05/222.aspx we found out it's more complicated than that:

"For 2003 I did the following changes:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009 - Added permissions for Network Service
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services - Added permissions for Network Service
C:\WINNT\system32\perf*009.dat - Added full permissions for Network Service on all matching files.
After that it worked like a charm."

We didn't try the above solution, as after a little more thinking we gave up trying to install the counters on the fly and wrote some code that runs as part of the site installation (and hence runs as an Administrator which means it will definitely have permission to create the counters).

It's a lot cleaner doing it that way, and changes less permissions on the server which is always a good thing from a security point of view.

If you're interested. the code uses the System.Diagnostics.CounterCreationData class to create the counters on the web server if they're not already installed - see the documentation if required as it's pretty easy to use.

Performance Counters Windows

CTRL + Click opens new tab in IE7

Originally posted on my old MSDN blog

Is this a well known trick and I'm just completely out of touch? Probably, but I was very excited when I found out that holding down CTRL when clicking a link in IE7 will open the link in a new tab.

To think all this time I was using right click and then "Open In New Tab". I guess my productivity has just gone up by 0.1% :-)

Internet Explorer