Ken’s Blog

Here’s one that always took me some time to figure out (until I broke down and just ran a search against the file structure to find it).

You know when you right-click on “Stored Procedures” to create a new stored procedure and you age the hieroglyphics version of a stored procedure template from deep in the minds of a Microsoft engineer? You decide you want to change it and you go through Microsoft’s instructions telling you to go to View > Template Explorer then dig into the Stored Procedure group. You do that and you find that Create Procedure (New Menu) template which you change. Then you try it again and it doesn’t work.

Then you take a stab and some logic and feel this has got to be it. You go into Documents and Settings > Application Data for your profile, then:

Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\Stored Procedure

And you’re like, score! I found you. You make your changes, save it and go back into SMS only to not see your changes reflected in the New Stored Procedure command (I think it only affects the File > New). Dammit. Now what?

The net won’t tell you, everything points to the same thing I mentioned above. Then you do a file search like I did and lo and behold, you find Microsoft’s hidden pile of gold:

Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql\Stored Procedure

You make the change to the same file (has the exact same name) and BAM. You’re done.

Same goes for any other template. It would seem the IDE gets its own “profile” or something.

Anway, you’re welcome. Go get some cake or something. You deserve it.

I don’t know how many times I’ve run into errors caused by the fact that I wasn’t paying attention to dependencies between SP’s that call other SP’s.

Got this one because I added a parameter and forgot to check for dependencies. The resulting shift of parameters (calling SPs would be one param short so it’s like they were shifted up/left by one) caused this error because the parameter that was next was an XML param:

Operand type clash: int is incompatible with xml

Never know what you’re gonna get.

UPDATE: All Web Dev content is being moved. This entry can be found here now. Be advised as this content will eventually get archived into oblivion!!!