part music, part software, part music software

Thanks to information from sqlteam and kodyaz, I was able to export data from SQL Server to Excel in seconds (70,000 rows of data were exported from a table in 8 seconds)…Wow!!

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…

In an effort to consolidate reporting on our web apps, we normally create a central report setup page that allows a user to select a report and various combinations of filters based on the data in the database. Internally, our best known approach has been to generate a SQL string in the VB behind our ASP.NET pages and send the string in as a hard-coded “Select…” statement via the SQL connection. We’ve always had a feeling, however, that this isn’t the “best” approach, especially when considering how labor intensive it might be to edit code that could be much longer than something like:

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

I’m blogging about this as it is a constant speedbump I find myself crossing. We’ve been using TemplateFields in gridviews exclusively for a couple of years now since we’ve always found ourselves having to convert BoundFields to TemplateFields in order to get extra functionality that we need in most cases. There’s extra coding to do, but we only use what we need, and, thus, i’m assuming that it’s a little more efficient. I’ll reasearch the topic and include it in another blog.

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!