The first step requires that you turn on the option ‘Show Advanced Options’ via the built in system stored procedure sp_configure.

1

exec sp_configure 'show advanced options', 1

Next, you must run ‘Reconfigure’ to “install”, as SQL Server calls it, to set the changes:

1

Reconfigure

Once advanced options are available, you can set the ‘Web Assistant Procedures’ option to true (i.e. 1):

1

exec sp_configure 'Web Assistant Procedures', 1

Again, you must run ‘Reconfigure’ to reset the SQL Server to accept your changes:

1

Reconfigure

SQL Server has a built-in stored procedure for writing data to files (I’m assuming this would work just as well to output a CSV or other delimited file type, but I haven’t tried it yet) called ’sp_makewebtask’. Using the following code, I was able to export an excel complete with column headers & a report title (hence the @colheaders & @resultstitle Parameters):

1
2
3
4
5

EXEC sp_makewebtask 
	@outputfile = 'D:\MyDirectory\vwNodeTrackingMatrix.xls', 
	@query = 'SELECT * FROM LoopManagement.dbo.vwNodeTrackingMatrix', 
	@colheaders = 1, 
	@FixedFont=0,@lastupdated=0,@resultstitle='LoopManagement.dbo.vwNodeTrackingMatrix'

For more thorough information, you can see the links above.

Index was out of range. Must be non-negative and less than the size of the collection.

This error just greeted me while testing a log page that uses an ASP.NET GridView control for displaying tabular data. When navigating to the last page and then trying to navigate back up to a more recent page, my code was failing on a line in the RowCommand event, which seemed strange to me. Then, I realized that the pager is in the header, which is a row, itself, and any actions on the header row go through the RowCommand function and the code was failing on a call to set thisRow = gv.rows(e.CommandArgument mod gv.PageSize). When the header row calls the RowCommand function, e.CommandArgument = -1, apparently.

A couple of solutions are:

1. Wrap the entire contents of the RowCommand function with an if-statement to check for e.CommandArgument > -1
2. Wrap the contents of the RowCommand function with an if-statement to check for e.CommandName = “Page”

I found solution 2 on a message board. Apparently, when you’re clicking on one of the pager buttons in the header, the CommandName is set to “Page”.

This has taken about 2 hours to troubleshoot, and the solution was simple–yet another A-S-S (Always Something Simple) solution…

dim str as new StringBuilder("")

str.Append("Select col1, col2, col3 From Table1 as t1")

str.Append("inner join Table2 as t2.col1 = t1.col1")

if WhereClause() <> "" then

str.Append(”Where 1=1″)

if checkbox1 = clicked then

str.Append(” and col1 = checkbox1.text”)

elseif checkbox2.checked = true then

str.Append(” and col2 = checkbox2.text”)

end if

end if

Time constraints have prevented us from researching better approaches, but we have always hoped to find a way of using stored procedures, passing in where clauses as parameters when necessary.

Today I stumbled on a better approach while browsing through the questions on StackOverflow.

Caution should be taken when using either of these approaches, especially if the report setup filters include text box entries–your database could be opened up to injection attacks.

I’ll append to this blog as newer methods are discovered.

I’ve found another link:  http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

I’ve also gotten additional responses on Stack Overflow concerning creating a temporary table and performing the filter on the temp table like so:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
Drop table #tmp;
CREATE TABLE #tmp (col1 varchar(10))
INSERT #tmp (col1)
EXEC spTest
declare @spstr as nvarchar(Max)
declare @whereClause as varchar(300)
set @whereClause = 'Where col1 = ''1'''
set @spStr = 'Select * from #tmp ' + @whereClause
exec(@spStr)

One more link:  http://sqlservernation.com/blogs/development/archive/2009/12/07/using-stored-procedures-to-query-objects-with-dynamic-sql.aspx

If, in the item template, a label is used to display text, I’ve gone around-and-around, via trial and error, trying to display dates with the short date format. Today I stumbled upon a link that provides solutions for both BoundFields & labels inside of TemplateFields: http://www.codedigest.com/Articles/ASPNET/137_How_to_format_DateTime_in_GridView_BoundColumn_and_TemplateColumn.aspx

The solution for the label inside of the TemplateField, which I’m most concerned about here, is:

1. Be sure to use Eval rather than Bind (I always use Bind–this was the ‘Gotcha!’) in the text property of the label
2. in Eval, include the format string in the second parameter (i.e.  Eval(”Field_Name”, “{0:d}”) )

That was a simple fix that took me 2 hours of trial and error to get nowhere before finally searching for a solution elsewhere!

This first blog will be about music, but I will also mix in some software development topics as I add more posts down the road.

About a year and a half ago, I purchased a Lakland Joe Osborn Signature 5-string bass.  When it came time to change my strings, I settled on Ernie Ball slinkies, as I’ve always used them on my Stingray 5.  One small problem:  The B string tapered off inside of the first fret near the nut–The string was too short!  I tried several different sets, and none seemed to work–all were too short.  No one at our local music stores seemed to understand what my problem was.  They all assumed, after I told them about my problem, “…Oh!  You need tapered strings….try these.”  I kept trying to figure out what was wrong with my explanation of my problem.  I thought my explanation was very clear:  “I’m trying to find strings to fit my bass; for every set I’ve tried, the B strings have been too short.”  That seems pretty clear to me! No?

Finally, someone understood my dilemma.  They recommended DR Long Scale strings.  These are the only strings that I’ve tried that fit the full length of my bass.  So, I hereby recommend those strings to anyone having similar problems.

Please comment below, leaving any similar experiences and any other string type suggestions for the Joe Osborn 5-string.  I’m also looking for an acoustic 5-string set for a Michael Kelly Dragonfly.