Passing parameters to and from dynamic queries

by Codewiz51 26. February 2013 19:54

I had a most interesting experience today.  I've been assigned to correct and improve an SQL procedure that was performing poorly during a critical holiday period.

The queries contained in the procedure had been transformed to dynamic queries in order to handle linked servers based on vendor type and geography.  From the start, it was apparent that the original author did not understand how to pass parameters to and from parameterized queries.  Instead, the transformed queries inserted records into a table var for later recovery.

Well, there is an excellent mechanism for passing parameters to dynamic SQL using sp_executesql. A quick check of MDSN presents several excellent examples.  Here's my rendition of the problem.

DECLARE @sqlCommand nvarchar(1000)

DECLARE @campgn1 varchar(20)

declare @startdate datetime

declare @campgnname varchar(50)

SET @campgn1 = 'ABC1234'

SET @sqlCommand =  'SELECT @stdt=startdate, @cname=campaignname FROM ' + @linkserver + '.' + @targetdb + '.dbo.mktcampaign WHERE campaigncode = @campgncode' 

EXECUTE sp_executesql @sqlCommand, N'@campgncode nvarchar(20), @stdt datetime output, @cname varchar(50) OUTPUT' , @campgncode=@campgn11, @stdt=@startdate OUTPUT, @cname=@campgnname OUTPUT

select @startdate as [Start Date], @campgnname as [Campaign Name]

As you can see, it's pretty simple to pass parameters into and out of dynamic sql using sp_executesql.

Tags: , ,


Resolving problems with JSON2.js on IE 7

by Codewiz51 1. January 2013 07:35

I'm having a problem with JSON on IE 7.  I've followed the recommended fixes: referencing Crockford's JSON2.js, setting type and language attributes, etc.  

I am still getting a 'JSON' is undefined exception on the IE 9 script console when running in IE 7 standards mode.  I am successfully using JSON2.js on the server side in my ASP pages, so this error is just a bit confusing. Based on this post on, I am guessing there is some sort of character set/code page issue.  I don't have any of the typo's associated with the post.  I'll have to play around with charset, language and type attributes to see if I can resolve the issue.

I've tested this on my home network, using IIS 7.5 and 8, along with IE 9 running in IE 7 standards mode and it works fine.  This is only happening at work, where I am using IE 9 running in IE 7 standards mode on Windows 7.  The servers are Windows 2003 running IIS 6.  Fiddler doesn't display anything odd that would indicate the script file is not loading correctly, and the charset/code page is 1252, which is normal.


It seems I need to specify utf-8 in the script file attributes.  This does not appear to be a universal requirement, but it got me past the problem at work where IE 7 verification was not working.  The final script tag is:

<script type="text/javascript" language="javascript" charset="utf-8" src="/script/json2.js"></script>

My guess as to why this fixed my issue? JSON2.js contains unicode character definitions as part of it's regex matching/substitutions code.  Apparently, IE 7 mode on my work computer needed to be told the file contained unicode characters.  Whatever...  At least I'm not chewing cycles trying to fix an arcane issue on a minor and very old browser.

Update 2:

Found one more issue with including json2.js for IE 7.  If you specify your tag as

<script type="application/javascript" language="javascript" src='/js/json2.js'></script>

, then IE 7 will generate an exception when viewing the console.  Make sure you specify your type as type="text/javascript".

Tags: , , ,

Design | Programming

I love this quote. It applies as well today as it did three years ago...

by Codewiz51 31. December 2012 17:53

Unfortunately, this isn't my own thought. It's from a comment to a post by Mike Taylor. It sums up a lot of my own thinking. The details are different, but the thought is pure:

My biggest gripe with modern programming is the sheer volume of arbitrary stuff I need to know. My current project has so far required me to know about Python, Django, Google App Engine and it’s datastore, XHTML, CSS, JQuery, Javascript, JSON, and a clutch of XML schema, APIs and the like. Don’t get me wrong, I’m grateful for all of it, but it just doesn’t seem like what I was promised when I followed SICP for the first time. It just feels like I spend most of my time scouring through documentation and trying to remember umpteen different sets of syntax and class names rather than actually thinking in code.

Tags: , ,


Didn't keep good development notes? Here's some help

by Codewiz51 31. December 2012 15:13

If you use TFS, then you have help when you need to remember what you checked in over a period of time:

This command includes a date range:

tf history "$/<Your Project Path>/" /recursive /noprompt /format:detailed /user:<your user name> /version:D"11/1/12"~D"12/31/12"

How to run this command:

Start -> Visual Studio 2010->Visual Studio Tools-> Visual Studio 2010 Command Prompt.

Once the command prompt is ready, edit the above command, paste it into the command line, and you’ll soon have your list.  It’s big.  Really BIG!  It’ll overrun the output buffer of your command prompt window!

I suggest actually spooling the output to a text file:

tf history "$/DCDFLIB/" /recursive /noprompt /format:detailed /user:Gene /version:D"12/1/12"~D"12/31/12" > c:\mywork.txt

Then bring up mywork.txt in your favorite editor to see what you did last month!


Tags: , ,

Infrastructure | Programming

Your WebPages are redirected to Login.aspx

by Codewiz51 29. December 2012 09:21

You've just started using WebPages in Visual Studio 2012.  You've updated everything to use Razor 2.  Low and behold, you are redirected to the login.aspx page when you start your web site.  And you don't know why.

The problem is the component WebMatrix.WebData.dll.  You may not be using this component, but you probably will be in the future.  Instead of removing WebData, you can add a couple of settings to your web.config file.  These settings will allow allow you to get back to work, learning and using Microsoft WebPages.


    <add key="webpages:Version" value=""/>
    <add key="PreserveLoginUrl" value="true"/>
    <add key="PageInspector:ServerCodeMappingSupport" value="Enabled"/>
    <add key="VisualStudioDesignTime:Enabled" value="true"/>
    <add key="autoFormsAuthentication" value="false" />
    <add key="enableSimpleMembership" value="false" />


Tags: , , , ,



This blog represents my personal hobby, observations and views. It does not represent the views of my employer, clients, especially my wife, children, in-laws, clergy, the dog, the cats or my daughter's horse. In fact, I am not even sure it represents my views when I take the time to reread postings.

© Copyright 2008-2011