Matt Pavey's Blog [RSS]

Move/Scroll Page to Top After Asynchronous Postback in UpdatePanel in ASP.Net AJAX

Matt Pavey — 2009-07-30 09:18:57

   function endRequest(sender, args) {
      window.scrollTo(0, 0);
   }
</script>

http://www.codedigest.com/CodeDigest/120-Move-Scroll-Page-to-Top-After-Asynchronous-Postback-in-UpdatePanel-in-ASP-Net-AJAX.aspx

New ASP.NET Charting Control:

Matt Pavey — 2009-01-22 09:17:03

Another great article on ScottGu's Blog.

Microsoft recently released a cool new ASP.NET server control - <asp:chart /> - that can be used for free with ASP.NET 3.5 to enable rich browser-based charting scenarios:

Check out the article for more information on dowloading the free chart control, samples and documentation:

http://weblogs.asp.net/scottgu/archive/2008/11/24/new-asp-net-charting-control-lt-asp-chart-runat-quot-server-quot-gt.aspx

"<asp:chart /> supports a rich assortment of chart options - including pie, area, range, point, circular, accumulation, data distribution, ajax interactive, doughnut, and more. You can statically declare chart data within the control declaration, or alternatively use data-binding to populate it dynamically. At runtime the server control generates an image (for example a .PNG file) that is referenced from the client HTML of the page using a <img/> element output by the <asp:chart/> control. The server control supports the ability to cache the chart image, as well as save it on disk for persistent scenarios. It does not require any other server software to be installed, and will work with any standard ASP.NET page."

Hosting options for WCF services

Matt Pavey — 2009-01-12 20:54:49

"These hosting options range from running inside a console application to server environments such as a Windows service running within a worker process managed by Internet Information Services (IIS) or by Windows Process Activation Service (WAS). Developers choose the hosting environment that satisfies the service's deployment requirements. These requirements might derive from the platform on which the application is deployed, the transport on which it must send and receive messages, or on the type of process recycling and other process management required to ensure adequate availability, or on some other management or reliability requirements."

http://msdn.microsoft.com/en-us/library/ms730158.aspx

The article above provides some great information on how to use the different hosting services and explains in detail the key benefits and limitations to each.

For more specific information about each hosting service check out the following links also referenced in the above article:

- Self-Hosting in a Managed Application
- Managed Windows Services
- Internet Information Services (IIS)
- Windows Process Activation Service (WAS)

These links were very helpful in understanding the different hosting options available for WCF services and more importantly understanding the right hosting environment to use based on "... the version of Windows on which it is deployed, the transports it requires to send messages and the type of process and application domain recycling it requires."

Creating your first WCF service

Matt Pavey — 2009-01-12 20:34:11

If you are just getting started with Windows Communication Foundation (WCF) here's a great resource with videos that really help get you moving in the right direction.

The videos will make more sense if watched in the following order:

http://channel9.msdn.com/shows/Endpoint/Endpoint-Screencasts-Creating-Your-First-WCF-Service/

http://channel9.msdn.com/shows/Endpoint/Endpoint-Screencasts-Configuring-Services-with-Endpoints/

http://channel9.msdn.com/shows/Endpoint/Endpoint-Screencasts-Hosting-WCF-Services-in-IIS/

http://channel9.msdn.com/shows/Endpoint/Endpoint-Screencasts-Self-hosting-WCF-Services/

The videos demonstrate the following:

- Creating a WCF service

- Configuring a WCF service with various endpoints

- How to host a WCF service in IIS

- How to self-host a WCF service in your own applications

For additional information on WCF, check out the WCF Dev Center on MSDN and the .NET Endpoint team blog.

SQL Server 2008 Management Studio

Matt Pavey — 2008-12-28 22:16:04

Here is the error I received:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal "UserName" is not able to access the database "DatabaseName" under the current security context. (Microsoft SQL Server, Error: 916)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3239&EvtSrc=MSSQLServer&
EvtID=916&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

After doing some digging I found the answer on Aaron Bertrand's SQL blog.

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/07/07/a-little-management-studio-oops.aspx

"For those of you who connect to database servers where you are not in full control over all databases, or where some of your databases are ever offline (or auto-closed), the new version of Management Studio that is shipping with SQL Server 2008 is going to bring you some surprises, when you try to present a list of databases in Object Explorer."

"It seems that the ability to do so is hinged upon the columns that are set up in Object Explorer Details by default. In the case I came across yesterday, the offender was "Collation." The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL. Well, that's not really the problem... the real problem is that SSMS throws its hands in the air when it comes across NULL for these values, and assumes this is NULL because you don't have permission."

"And then refuses to show the data for ANY database, instead of just hiding the one(s) that caused the error. And this is true whether or not you have Object Explorer Details even open (my guess here is that the contents of OED are cached behind the scenes, even when it is disabled... which I speculate may be part of the reason behind the sluggishness that many have complained about). You can read more about this in Connect #354322 and in Connect #354291."

"For those of you that connect to databases that are hosted by 3rd party providers, some of which are accustomed to leaving as many databases in auto-close mode as possible, you are first going to have to go to Object Explorer Details, right-click the column header list, and un-check Collation. At this point, if you refresh the Databases node in Object Explorer, you should again see all of the databases on the server, even those where you don't have access. Strangely enough, OED still shows you some other data by default, that it probably shouldn't (e.g. recovery model, last backup, owner)."

Check/Uncheck all items in a CheckBoxList using ASP.NET and JavaScript

Matt Pavey — 2008-11-18 19:32:06

Here's an easy way to check or uncheck all items in a CheckBoxList.

http://www.dotnetcurry.com/ShowArticle.aspx?ID=77&AspxAutoDetectCookieSupport=1

JavaScript

function CheckBoxListSelect(cbControl, state)

{

var chkBoxList = document.getElementById(cbControl);

var chkBoxCount = chkBoxList.getElementsByTagName("input");

for(var i=0; i < chkBoxCount.length; i++)

{

chkBoxCount[i].checked = state;

}

return false;

}

ASP.Net CheckBoxList

<div>

<a href="javascript:void(0)" onclick="javascript: CheckBoxListSelect('<%=chkStates.ClientID %>', true)">Select All</a> | <a href="javascript:void(0)" onclick="javascript: CheckBoxListSelect('<%=chkStates.ClientID %>', false)">Select None</a>

</div>

<div>

<asp:CheckBoxList ID="chkStates" RepeatColumns="6" Width="100%" runat="server" />

</div>

SSIS loading Excel validation error

Matt Pavey — 2008-11-11 20:24:50

I ran into a situation today where I needed to have an Excel Source for an Excel file that would not always exist at the path specified in the ExcelFilePath property. This would cause the SSIS package to fail when the Excel file did not exist since the Data Flow Task could not validate the Excel file.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2170106&SiteID=1

The simple fix was to right click the Data Flow Task and go to Properties and set the DelayValidation property to True.

Importing Excel data with SQL Server Integration Services SSIS with unicode and non-unicode data issues

Matt Pavey — 2008-11-11 20:24:29

"If you have used SSIS to import Excel data into SQL Server you may have run into the issue of having to convert data from Unicode to non-Unicode."

This article discusses some different options for handling Unicode to non-Unicode warning messages that are fairly common if you deal with importing data from one source to another, for example from Excel to SQL.

http://www.mssqltips.com/tip.asp?tip=1393

In my case today I ended up just having to add a Data Conversion transformation between my Excel Source and SQL Server Destination to convert my data-types accordingly.

SQL Server 2008 Designer Behavior Change: Saving Changes Not Permitted

Matt Pavey — 2008-10-30 12:49:47

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

"This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck Prevent saving changes that require table re-creation."

New Date Data Types in Microsoft SQL Server 2008

Matt Pavey — 2008-10-21 18:47:35

Here's another good article article written by Scott Mitchell discussing the new date data types available in Microsoft SQL Server 2008.

"This article explores the time and date data types and shows how they can be used and formatted from within an ASP.NET page. This article also includes a short discussion on the datetime2 and datetimeoffset and compares and constrasts SQL Server 2008's six different date data types."

http://aspnet.4guysfromrolla.com/articles/101508-1.aspx

Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.

Web Platform Installer: Trying to make it easier to setup for web development

Matt Pavey — 2008-10-21 18:40:11

http://www.hanselman.com/blog/WebPlatformInstallerTryingToMakeItEasierToSetupForWebDevelopment.aspx

Lightbox2

Matt Pavey — 2008-10-03 15:27:47

"Lightbox is a simple, unobtrusive script used to overlay images on the current page. It's a snap to setup and works on all modern browsers."

http://www.huddletogether.com/projects/lightbox2

Adobe Spry framework for AJAX

Matt Pavey — 2008-09-24 12:31:07

Spry is a JavaScript-based framework that enables the rapid development of Ajax-powered web pages. Not a JavaScript guru? No problem. Spry was designed to feel like an extension of HTML and CSS, so anyone with basic web-production skills can create next-generation web experiences by adding the power of Ajax to their pages.

Spry can be used with any server-side technology (ColdFusion, PHP, ASP.Net etc.). By building the front-end of your web application with Spry you enable a more efficient designer-developer workflow by keeping UI separated from back-end application logic.

Home:
http://labs.adobe.com/technologies/spry/home.html

Demos:
http://labs.adobe.com/technologies/spry/samples/

The Yahoo! User Interface Library (YUI)

Matt Pavey — 2008-09-24 12:22:22

The Yahoo! User Interface (YUI) Library is a set of utilities and controls, written in JavaScript, for building richly interactive web applications using techniques such as DOM scripting, DHTML and AJAX. The YUI Library also includes several core CSS resources. All components in the YUI Library have been released as open source under a BSD license and are free for all uses.

http://developer.yahoo.com/yui/

Granting execute permissions to all stored procedures in a database

Matt Pavey — 2008-09-11 01:03:55

"Whilst SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will). This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005."

http://www.sqldbatips.com/showarticle.asp?ID=8

Sending Email in ASP.NET 2.0

Matt Pavey — 2008-08-18 10:51:50

Coincidentally for the 3rd time in the past week an issue I was working on led me to an article written by Scott Mitchell.

http://aspnet.4guysfromrolla.com/articles/072606-1.aspx

"The relay server information used by the SmtpClient class can be specified programmatically, through the SmtpClient class's properties, or can be centralized in Web.config. To use the Web.config approach, add a <system.net> element within the <configuration> element. Then, add a <mailSettings> element that contains an <smtp> element whose settings are specified within its <network> child element, like so:"

<configuration>
     
     <system.net>
          <mailSettings>
               <smtp>
                    <network
                         host="relayServerHostname"
                         port="portNumber"
                         userName="username"
                         password="password" />
               </smtp>
          </mailSettings>
     </system.net>

     <system.web>
          ...
     </system.web>
</configuration>

Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.

Taking an ASP.NET 2.0 Application Offline

Matt Pavey — 2008-08-17 18:20:23

Here's another good article written by Scott Mitchell.

http://aspnet.4guysfromrolla.com/articles/110707-1.aspx

"When updating a web application that's currently in production, it is best to take the application offline so that users understand that the application is being worked on. This can be accomplished in a variety of ways, from simply stopping the web server software to displaying a web page that informs the user that the site is offline for maintenance. What you don't want to do is have the application appear to work, only to have some error pop up later because you are in the middle of updating the production server. This is a sure-fired way to frustrate your users."

The article discusses 4 ways to take an application offline:

Taking a Website Offline through IIS
Disabling the <httpRuntime>
Using App_Offline.htm
Creating a "Manage Offline Status" Page

Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.

Displaying the Sizes of Your SQL Server's Database's Tables

Matt Pavey — 2008-08-12 23:30:07

By Scott Mitchell

http://www.4guysfromrolla.com/webtech/032906-1.shtml

"SQL Server has a handy little system stored procedure named sp_spaceused that will return the space used by a database or by a particular table. To determine the size used by the database, simply run:"

EXEC sp_spaceused

"This will return two result sets, the first one containing the database name, size, and unallocated space and the second containing a breakdown of the database's size into how much size is reserved and how much of that is taken up by data, how much by indexes, and how much remains unused."

"To return information about a particular table, simply call sp_spaceused passing in as the first parameter the name of the table. To determine the space used by the Orders table in the Northwind database use:"

EXEC sp_spaceused 'Orders'

This will return a single result set that provides the following information:

Name - the name of the table
Rows - the number of rows in the table
Reserved - amount of total reserved space for the table
Data - amount of space used by the data for the table
Index_Size - amount of space used by the table's indexes
Unused - amount of usused space in the table

"While sp_spaceused can be used to return the space usage for a single table, more than likely we want a web page where we can view the space used for all tables in a database. There are a number of ways to accomplish this, but in short we need to execute sp_spaceused once for every table in the database. In order to accomplish that we could do one of two things:"

Query the sysobjects table to get a list of tables in the database, then use a CURSOR to iterate through these table results one at a time, executing sp_spaceused for each one.
Use the undocumented sp_MSforeachtable stored procedure, which takes in a command and executes that command against all of the user tables in the database.

"The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. A list of these handy stored procedures can be found at SQL Server 2000 Useful Undocumented Stored Procedures. In short, you can use sp_MSforeachtable like so:"

EXEC sp_MSforeachtable @command1="command to run"

"In the command to run put a ? where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use:"

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This will execute EXEC sp_spaceused 'TableName' for each user table in the database.

"An alternative option is to create a stored procedure from which a temporary table is created and populated with one record for each result set returned by sp_spaceused. The entire results, then, can be returned to the ASP.NET page as a single result set by selecting the entire contents of the temporary table at the end of the stored procedure."

create table #spaceused (name varchar(128), rows bigint, reserved varchar(25), data varchar(25), index_size varchar(25), unused varchar(25))

EXEC sp_MSforeachtable @command1="insert into #spaceused EXEC sp_spaceused '?'"

select * from #spaceused

drop table #spaceused

The above SQL code courtesy of Toby.

Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.

Scheduling Backups for SQL Server 2005 Express

Matt Pavey — 2008-07-31 18:50:26

"One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server."

This article provides an easy solution for using the SQL BACKUP command to backup a database and shows you how to use SQLCMD.EXE with Scheduled Tasks to schedule it to run automatically, thus giving you the scheduled backups functionality you would typically have in SQL Server Agent.

http://www.mssqltips.com/tip.asp?tip=1174

Web Farms and ASP.NET ViewState

Matt Pavey — 2008-07-31 18:49:49

"The default ASP.NET settings ensure that forms authentication tickets are tamper proof and encrypted, and that ViewState is tamper proof. This ensures that any modification of the ViewState or authentication tickets either on the client's computer or over the network is detected when the server processes the data."

For example, when the viewstate generated on server A is posted back to server B you may receive a viewstate validation error if the <machineKey> is not the same on all of the servers in the web farm or cluster.

Here is one of the exceptions you may receive:

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

This is because the viewstate is salted with a unique, autogenerated machine key from the originating server's machine.config file.

"This is done to prevent users from somehow tampering with the ViewState. Any change to the ViewState data on the client will be detected. But this has a side effect: it also prevents multiple servers from processing the same ViewState. One solution is to force every server in your farm to use the same key -- generate a hex encoded 64-bit or 128-bit <machineKey> and put that in each server's machine.config"

If you do not have access to the machine.config files in the web farm or cluster you can disable the enableViewStateMac using a simple page directive:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="MyPage.aspx.vb"
Inherits="MyAssembly.MyPage" enableViewStateMac="false" %>

Alternately, you can modify the pages element in Web.config:

<system.web>
<pages enableViewStateMac="false" />
</system.web>

Of course caution should be taken when setting the enableViewStateMac to false since the viewstate could potentially be tampered with.

If you have a web farm or cluster and receive this error the optimal solution would be to update the machine.config files on all of the servers in the web farm or cluster to ensure that the <machineKey> configuration specifies the same validationKey and validation algorithm.

http://www.codinghorror.com/blog/archives/000132.html

http://blogs.msdn.com/rich_crane/archive/2004/05/12/130693.aspx

http://msdn.microsoft.com/en-us/library/ms998288.aspx

Checking All CheckBoxes in a GridView Using Client-Side Script and a Check All CheckBox

Matt Pavey — 2008-05-22 09:47:09

"In this article we saw how to add a "Check/Uncheck All" header CheckBox to the GridView that, using client-side techniques, provides a means to quickly check or uncheck all row-level CheckBoxes. The header CheckBox, along with the "Check All" and "Uncheck All" buttons, provide the user with multiple ways for checking or unchecking all of the grid's CheckBoxes. Since client-side script is used to handle checking and unchecking the CheckBoxes, the interface provides a snappy user experience."

http://aspnet.4guysfromrolla.com/articles/053106-1.aspx

LINQ Dynamic Queries

Matt Pavey — 2008-05-06 10:46:50

I came across an article this morning that helped me with a LINQ query issue that I was trying to work through regarding dynamic queries.

http://www.rocksthoughts.com/blog/archive/2008/01/24/linq-to-sql-dynamic-queries.aspx

My situation was very similar; however, in my case I was querying an ENTITY rather than SQL directly; however, the same rules applied.

I knew I could easily write a query that was not type safe and not checked at compile time; however, the thought of doing that really bothered me because I wanted to leverage the full power of LINQ and keep everything type safe and checked at compile time.

I ended up taking advantage of Lambda Expressions:

Public Function Search(ByVal Name As String, ByVal City As String) As IEnumerable(Of MyTable)
     Dim MyEntities As New DbEntities()
     Dim MyQuery As ObjectQuery(Of MyTable) = MyEntities.MyTableSet
     Dim MyResults = From x In MyQuery Select x

     If Not IsBlank(Name) Then
          MyResults = MyResults.Where(Function(e) e.Name.Equals(Name))
     End If

     If Not IsBlank(City) Then
          MyResults = MyResults.Where(Function(e) e.City.Equals(City))
     End If

     Return MyResults.AsEnumerable
End Function

More information on Lambda Expressions can be found here:

http://weblogs.asp.net/scottgu/archive/2007/04/08/new-orcas-language-feature-lambda-expressions.aspx

ADO.NET Entity Framework Beta 3

Matt Pavey — 2008-05-06 10:29:01

I started working on a new application for a client this week that is going to use the ADO.NET Entity Framework. Since it is not final release yet I had to download and setup the ADO.NET Entity Framework Beta 3. It wasn't too bad to get installed but I did run into a few issues along the way that I thought I'd share to save you some time if you end up having to do something similar.

I ended up having to install the following in this order:

ADO.NET Entity Framework Beta 3

http://www.microsoft.com/downloads/details.aspx?FamilyId=15DB9989-1621-444D-9B18-D1A04A21B519&displaylang=en

ADO.NET Entity Framework Tools Preview can only be installed if a necessary Visual Studio patch is installed.

http://go.microsoft.com/fwlink/?LinkID=104985

ADO.Net Entity Framework Tools Dec 07 Community Technology Preview

http://www.microsoft.com/downloads/details.aspx?FamilyId=D8AE4404-8E05-41FC-94C8-C73D9E238F82&displaylang=en

At this point I had everything installed so I could add a ADO.NET Entity Data Model to my project.

Another good resource for ADO.NET related info can be found here:

http://blogs.msdn.com/adonet/

One other thing I did notice when working with the ADO.NET Entity Data Model was that it all seemed to be very straight forward to setup and test if your UI and data access were in the same project; however, I found that when setting up a n-tier architecture I ran into some annoyances. The data layer I setup contained my ADO.NET Entity Data Model and it automatically creates an App.Config file with the necessary connection information and it creates the .csdl file, .ssdl file and .msl file in the data layer's /bin folder; however, when you add a reference to your data layer from your UI or business layer it does not automatically copy the connection string information into your Web.config file and it does not copy the .csdl file, .ssdl file and .msl file into the web application's /bin folder. So I ran into error messages until I finally figured out was going on.

To work arond this I created a Config/ConnectionStrings.config file and copied the connection information from the App.Config file into the new config file and had the Web.config reference the new config file. Then I copied the .csdl file, .ssdl file and .msl file into the web application's /bin folder. Once I did this I was able to use the ADO.NET Entity Data Model as my data access and isolate the data access to the data layer. The issue with this is that if you change your model you have to manually copy the files to the web application's /bin folder again. I'm hoping that I'm either just not doing something right or this is just because it is still Beta and will be easier in the final release.

I also found that quite a few code samples I came across had the Using ... End Using notation around their entity code; however anytime I returned a generic list, for example IEnumerable(Of T), from the data layer to another layer the connection would be lost and I would get an error that said:

A connection string must be set on the connection before attempting the operation.

It wasn't apparent to me what the problem was initially but then I took out the Using ... End Using notation and everything worked perfect. The End Using code from the code samples was forcing certain objects to be destroyed, including the connection.

I've tested retrieving data, adding data, deleting data, updating data, filtering data using standard LINQ syntax as well as Lambda Expressions and binding IEnumerable(Of T) data to a GridView.

So aside from the minor annoyances I ran into along the way I now have a working project that uses the ADO.NET Entity Data Model.

Temporary Tables and Dynamic SQL

Matt Pavey — 2008-05-01 08:54:27

When programming in SQL sometimes you need to generate temporary tables inside a stored procedure. That's pretty straight forward with code like this:

SELECT TrackingGroupID,
Tag

INTO #temp
FROM MyTable

And you can access #temp in the rest of your stored procedure and drop the temp table when complete.

-- testing
SELECT COUNT(*) FROM #temp

-- drop temp table

DROP TABLE #temp

But if you ever have a situation where you also need to use dynamic SQL you will likely have scope issues. In my particular case I ran into a situation where I needed to apply a dynamic filter to a query and store that data in a temporary table so I could access it after the filter was applied and do further manipulation and analysis and grouping with the data.

Note: Typically when I work with temp tables I try to use tables-variables like so:

-- create temp table
DECLARE @Temp TABLE
(
TrackingGroupID INT,
Tag VARCHAR(50) NOT NULL DEFAULT ''
)

However, in this case I was not able to get a table-variable to work. So I ended up using a #temp table instead.

My first attempt looked something like this:

-- declare variables
DECLARE @SQL VARCHAR(8000)

-- apply filter
SET @SQL = 'SELECT      TrackingGroupID,
                                   Tag
                   INTO         #temp
                   FROM        MyTable
                   WHERE       1=1 ' + @Where + '
                   GROUP BY TrackingGroupID, Tag'

-- apply filter
EXEC(@SQL)

-- do further manipulation or analysis on #temp table
....

-- drop temp table
DROP TABLE #temp

But I received this error:

Msg 208, Level 16, State 0, Line 18
Invalid object name '#temp'.

I knew it was a scoping issue, but I wasn't sure how to work around it exactly. Researching on Google I came across this article:

http://blog.shkedy.com/2007/01/temporary-tables-and-dynamic-sql.html

"The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed."

So I modified my code like so:

-- create temp table

CREATE TABLE #temp
(
TrackingGroupID INT,
Tag VARCHAR(50) NOT NULL DEFAULT ''
)

-- declare variables
DECLARE @SQL VARCHAR(8000)

-- apply filter
SET @SQL = 'INSERT INTO #temp
                        (TrackingGroupID, Tag)
                        SELECT       TrackingGroupID,
                                         Tag
                        FROM         MyTable
                        WHERE       1=1 ' + @Where + '
                        GROUP BY TrackingGroupID, Tag'

-- apply filter
EXEC(@SQL)

-- do further manipulation or analysis on #temp table
....

-- drop temp table
DROP TABLE #temp

Now I had access to the #temp table throughout the scope of the stored procedure!

I've been told that ##temp might have solved it also since it's global; however, I'm not as familiar with ## so I didn't go down that road.

SQL Server 2008 New DATETIME DataTypes

Matt Pavey — 2008-04-18 09:46:05

"The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well."

http://www.sql-server-performance.com/articles/dev/datetime_2008_p1.aspx

SQL Server 2008 Demos and Videos

Matt Pavey — 2008-04-17 21:56:14

Learn more about SQL Server 2008 through these demos and videos.

http://www.microsoft.com/sqlserver/2008/en/us/demos.aspx

I found the Application Development Videos particularly useful.

Enterprise Library 4.0

Matt Pavey — 2008-04-02 15:11:06

Enterprise Library 4.0 March 2008 CTP download site

Sys WebForms Page Request Manager Parser Error Exception

Matt Pavey — 2008-03-26 14:57:17

"If you've used the Microsoft ASP.NET AJAX UpdatePanel control, there's a good chance you've hit the Sys.WebForms.PageRequestManagerParserErrorException error."

This article provides some details on what this error is, common reasons it occurs, and ways to avoid getting the error.

http://weblogs.asp.net/leftslipper/archive/2007/02/26/sys-webforms-pagerequestmanagerparsererrorexception-what-it-is-and-how-to-avoid-it.aspx

MSXML2.ServerXMLHTTP

Matt Pavey — 2008-03-14 11:42:48

I don't get to dabble in classic ASP much anymore, which is ok with me, but for the first time in years I had to debug some ASP code this morning to figure out an error for someone who is consuming one of our webservices.

The code was as simple as this:

Set MyXmlHttp = Server.CreateObject("MSXML2.ServerXMLHTTP")
MyXmlHttp.open "get", "https://www.test.com/webservice.asmx/GetSites?StudyID=35", False
MyXmlHttp.setRequestHeader "Content-Type", "text/xml"
MyXmlHttp.send()
XmlData = MyXmlHttp.responseText

Response.Write(Server.HtmlEncode(XmlData))

The error was:

HTTP Error 403.1 - Forbidden: Execute access is denied

Can you spot the problem?

MyXmlHttp.open "GET", "https://www.test.com/webservice.asmx/GetSites?StudyID=35", False

It might not be obvious what I changed, but simply changing the method from "get" to "GET" fixed the problem.

I wondered if this was a bug but this MSDN article seems to confirm that the method is case-sensitive and must be in UPPER case.

http://msdn2.microsoft.com/en-us/library/ms763809(VS.85).aspx

"The HTTP method used to open the connection, such as PUT or PROPFIND. For ServerXMLHTTP, this parameter is case-sensitive and the method name must be entered in all upper-case letters."

Creating a Data Access Layer with LINQ to SQL

Matt Pavey — 2008-03-02 11:48:02

Here's an interesting article regarding the challenges of creating a true data access layer using LINQ to SQL.

http://krisvandermotten.wordpress.com/2006/11/30/creating-a-data-access-layer-with-linq-to-sql-part-2/

It's an older article; however, it covers some interesting questions. I'd recommend in addition to reading the article actually reading through the responses as well at the end of the article.

"... it is absolutely not clear where a developer should draw the line between what’s business logic, and what belongs in the DAL ..."

It seems the primary concerns are what exactly the data access layer should be returning and where exactly the actual query should be executed.

"That is, the data access layer should be returning just arrays of entities (T[] or IEnumerable). It should [not] be returning IQueryable. Returning IQueryable gives you more rope to hang yourself with because (i) the caller (business logic layer) can redefine the query that ultimately gets sent to the database (ii) the caller (business logic layer) now executes the query because of deferred query execution (iii) the distinction between the caller (business logic layer) and the DAL blurs."

If you return IQueryable(Of T) objects from the data access layer the queries are not actually getting 'executed' until you use them later, for example when you enumerate over the IQueryable(Of T) data in your business or UI. So by returning List(Of T), IEnumerable(Of T) or an Array of entities you draw a fine line with what the data access layer handles vs. what the business layer or UI handles.

I've played around with returning IQueryable(Of T), List(Of T), IEnumerable(Of T) and an Array of entities. Each has their own advantages or caveats. My research so far leads me to believe that IEnumerable(Of T) is the preferred approach.

"The main design pattern that arises from LINQ is to prefer IEnumerable(Of T). This is because LINQ operates on IEnumerable(Of T), and if you design your application around IEnumerable(Of T), you will find many places where a LINQ query will provide an elegant solution to your problem."

Here's a couple articles that discuss IEnumerable(Of T) in more detail:

http://www.winterdom.com/weblog/2006/10/13/RediscoveringIEnumerableltTgt.aspx

http://jonkruger.com/blog/2007/10/19/iqueryable-vs-ienumerable-in-linq-to-sql-queries/

It will be interesting to see how each developer decides to implement LINQ into their projects and how consistent it will be with what others are doing.

"I hate it when developers have to make choices like that during routine development. Choosing takes time, and that’s not likely to improve productivity. But much worse is the fact that different developers will make different choices. Even a single developer may make different choices from one day to the next. That leads to inconsistencies in the code. Developers will spend more time trying to understand the code they’re reading, because it doesn’t always follow the same pattern. That’s bad for productivity. In the worst case scenario, developers start rewriting each other’s code, just so it matches their choice of the day. That kills productivity."

I think having a nice set of standards before fully integrating LINQ into a project is going to be a smart move, especially in team environments.

Extension Methods

Matt Pavey — 2008-02-26 16:42:50

"Extension methods allow developers to add new methods to the public contract of an existing CLR type, without having to sub-class it or recompile the original type. Extension Methods help blend the flexibility of "duck typing" support popular within dynamic languages today with the performance and compile-time validation of strongly-typed languages."

http://weblogs.asp.net/scottgu/archive/2007/03/13/new-orcas-language-feature-extension-methods.aspx

LINQ to XML

Matt Pavey — 2008-02-26 16:32:45

"LINQ to XML is a built-in LINQ data provider that is implemented within the "System.Xml.Linq" namespace in .NET 3.5."

"LINQ to XML provides a clean programming model that enables you to read, construct and write XML data. You can use LINQ to XML to perform LINQ queries over XML that you retrieve from the file-system, from a remote HTTP URL or web-service, or from any in-memory XML content."

"LINQ to XML provides much richer (and easier) querying and data shaping support than the low-level XmlReader/XmlWriter API in .NET today. It also ends up being much more efficient (and uses much less memory) than the DOM API that XmlDocument provides."

http://weblogs.asp.net/scottgu/archive/2007/08/07/using-linq-to-xml-and-how-to-build-a-custom-rss-feed-reader-with-it.aspx

LINQ to SQL Debug Visualizer

Matt Pavey — 2008-02-26 16:25:10

"One of the nice development features that LINQ to SQL supports is the ability to use a "debug visualizer" to hover over a LINQ expression while in the VS 2008 debugger and inspect the raw SQL that the ORM will ultimately execute at runtime when evaluating the LINQ query expression."

http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

LINQ to SQL

Matt Pavey — 2008-02-26 13:37:35

"Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code."

"LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it."

"LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model."

Part 1: Introduction to LINQ to SQL

Part 2: Defining our Data Model Classes

Part 3: Querying our Database

Part 4: Updating our Database

Part 5: Binding UI using the ASP:LinqDataSource Control

Part 6: Retrieving Data Using Stored Procedures

Part 7: Updating our Database using Stored Procedures

Part 8: Executing Custom SQL Expressions

Part 9: Using a Custom LINQ Expression with the <asp:LinqDatasource> control

The ADO.NET Entity Framework Overview

Matt Pavey — 2008-02-26 13:27:44

"When one takes a look at the amount of code that the average application developer must write to address the impedance mismatch across various data representations (for example objects and relational stores) it is clear that there is an opportunity for improvement. Indeed, there are many scenarios where the right framework can empower an application developer to focus on the needs of the application as opposed to the complexities of bridging disparate data representations."

"A primary goal of the upcoming version of ADO.NET is to raise the level of abstraction for data programming, thus helping to eliminate the impedance mismatch between data models and between languages that application developers would otherwise have to deal with. Two innovations that make this move possible are Language-Integrated Query and the ADO.NET Entity Framework. The Entity Framework exists as a new part of the ADO.NET family of technologies. ADO.NET will LINQ-enable many data access components: LINQ to SQL, LINQ to DataSet and LINQ to Entities."

"This document describes the ADO.NET Entity Framework, what problem spaces it is targeting and how its various components address those problems."

http://msdn2.microsoft.com/en-us/library/aa697427(VS.80).aspx

First Look at Silverlight 2

Matt Pavey — 2008-02-26 13:23:55

"Silverlight 2 includes a cross-platform, cross-browser version of the .NET Framework, and enables a rich .NET development platform that runs in the browser. Developers can write Silverlight applications using any .NET language (including VB, C#, JavaScript, IronPython and IronRuby). We will ship Visual Studio 2008 and Expression Studio tool support that enables great developer / designer workflow and integration when building Silverlight applications."

http://weblogs.asp.net/scottgu/archive/2008/02/22/first-look-at-silverlight-2.aspx

Silverlight 2 Tutorials: Building A Simple Digg Client

Part 1: Creating "Hello World" with Silverlight 2 and VS 2008

Part 2: Using Layout Management

Part 3: Using Networking to Retrieve Data and Populate a DataGrid

Part 4: Using Style Elements to Better Encapsulate Look and Feel

Part 5: Using the ListBox and DataBinding to Display List Data

Part 6: Using User Controls to Implement Master/Details Scenarios

Part 7: Using Templates to Customize Control Look and Feel

Part 8: Creating a Digg Desktop Version of our Application using WPF

GridView BoundField with "mailto" email address

Matt Pavey — 2008-02-05 15:27:49

<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" HtmlEncode="False" ReadOnly="True" DataFormatString="<a href='mailto:{0}'>{0}</a>" />

IE 8: On the Path to Web Standards Compliance - ACID 2 Test Pass Complete

Matt Pavey — 2008-01-24 01:01:10

Wed, Dec 19, 2007 4:36 PM
"The IE team has been very hard at work on IE 8 for the past several months and they hit a huge milestone last Friday evening. The IE dev team checked in a bunch of code that included several new features implemented in the core rendering engine that enable IE to pass the ACID 2 test! This is great news for web developers: IE 8 is going to be our most standards compliant browser to date. Passing ACID 2 is really a combined side effect of all the new features that have been developed for IE 8."

http://channel9.msdn.com/showpost.aspx?postid=367207

101 LINQ Samples

Matt Pavey — 2008-01-23 16:20:52

Visual Basic

http://msdn2.microsoft.com/en-us/vbasic/bb688088.aspx

http://msdn2.microsoft.com/en-us/vcsharp/aa336746.aspx

Is LINQ the future of database development?

Matt Pavey — 2008-01-23 00:43:36

The LINQ (.NET Language Integrated Query) Project is an initiative to standardize data access across data sources and development.

This is an interesting article by Arthur Fuller that explains why he's not convinced that LINQ will revolutionize database application development.

http://articles.techrepublic.com.com/5100-9592-5897968.html

I can't say I completely agree or disagree with all of the comments he makes in the article; nonetheless, I found it an interesting read. Be sure to read the "comments on this article" to see how certain people have reacted to both sides of the argument.

Like Arthur Fuller I've always been a strong proponent of Whatever the back end can do, the back end should do. This certainly has to be looked at on a project by project basis, but typically the projects I'm involved in and architect have benefited from a separate data layer with stored procedures for data access. So naturally I had skepticism as I began reading, researching, and testing LINQ.

With that said - I don't necessarily look at LINQ as having to move all back end code to the front end. I think it ends up being strictly up to how the solution is architected and how the developer wants to utilize it. Unfortunately, with LINQ in its infancy almost every code-snippet, documentation or article has some misleading examples that make it appear that all the data access code must now be moved to the front end of an application. I have to admit with my first several examples it was simple enough for me to drop a DBML file in the web project and write some quick code to test it. But of course after I saw it worked I immediately created a "Data" layer and moved the DBML file there and continued testing. That way the data access layer did just that, access data, and the front end could use the data accordingly.

This comment seemed to sum up exactly what I was thinking as I waded through all the pros and cons of how LINQ will affect the future of database development:

LINQ is a tier neutral technology. On the front end one can use LINQ to query returned datasets, XML files etc and on the back end to query a database. IMHO the back end (data access tier - DAC) is the only tier that is allowed to access data storage like SQL Server. The front end can manipulate returned data from the back end but not retrieve or update it without using the DAC. LINQ is merely a uniform way of accessing different data sources. New functionality like LINQ does not force bad coding style, that is left up to the creativity of the developer.

So although no conclusive agreement has been reached in the development community regarding the future of LINQ, I am optimistic and look forward to learning more about it over the next several weeks and months...

System.Data.Linq.Table Add and Remove Method Name Changes

Matt Pavey — 2008-01-22 21:54:54

If you tested BETA releases of Visual Studio 2008 and the .Net 3.5 Framework you'll find that some minor changes were made for the final release.

In particular one issue I ran into this evening was testing and writing sample code that I found in the MSDN documentation that used the Add and Remove methods of the System.Data.Linq.Table class.

The Add method was changed to InsertOnSubmit and the Remove method was changed to DeleteOnSubmit.

While more wordy, the change was made to make them more explicitly describe their behavior.

Microsoft .NET Framework 3.5

Matt Pavey — 2008-01-22 12:22:46

Microsoft .NET Framework 3.5 contains many new features building incrementally upon .NET Framework 2.0 and 3.0, and includes .NET Framework 2.0 service pack 1 and .NET Framework 3.0 service pack 1.

http://www.microsoft.com/downloads/details.aspx?FamilyID=333325FD-AE52-4E35-B531-508D977D32A6&displaylang=en

.NET Framework 3.5 builds incrementally on the new features added in .NET Framework 3.0. For example, feature sets in Windows Workflow Foundation (WF), Windows Communication Foundation (WCF), Windows Presentation Foundation (WPF) and Windows CardSpace. In addition, .NET Framework 3.5 contains a number of new features in several technology areas which have been added as new assemblies to avoid breaking changes. They include the following:

Deep integration of Language Integrated Query (LINQ) and data awareness. This new feature will let you write code written in LINQ-enabled languages to filter, enumerate, and create projections of several types of SQL data, collections, XML, and DataSets by using the same syntax.
ASP.NET AJAX lets you create more efficient, more interactive, and highly-personalized Web experiences that work across all the most popular browsers.
New Web protocol support for building WCF services including AJAX, JSON, REST, POX, RSS, ATOM, and several new WS-* standards.
Full tooling support in Visual Studio 2008 for WF, WCF, and WPF, including the new workflow-enabled services technology.
New classes in .NET Framework 3.5 base class library (BCL) that address many common customer requests.

Visual Studio 2008 & LINQ

Matt Pavey — 2008-01-22 00:10:54

I started using Visual Studio 2008 today. So far I haven't ran into any problems. In fact, I've tried it on Vista and XP and both installations went very smooth.

I've tested a few different scenarios, inluding working with one of my Visual Studio 2005 .Net 2.0 applications without upgrading to .Net 3.5. I didn't have any noticeable problems in doing so and was able to work with the application as it were 2.0 as expected. And I had all the benefits of Visual Studio 2008. The intellisense for JavaScript was very slick. I don't typically have to write a ton of JavaScript code, but the fact that it's going to be easier to work with and debug is a very nice feature.

I also tested converting an existing 2.0 application to 3.5. Again, I saw no noticeable problems. And once converted to 3.5 I had all the additional benefits of the 3.5 framework at my disposable.

And lastly I created a .Net 3.5 web application from scratch and had no problems. I did some very basic testing initially, and even got involved in writing my first lines of LINQ code. Quite remarkable!

Here's a great link for Visual Studio 2008 samples that was helpful for me to get started, particularly with LINQ.

http://msdn2.microsoft.com/en-us/bb330936.aspx

The "Hands On Labs" are perfect if you are just getting started with the LINQ syntax and LINQ to SQL syntax. The documents are available for VB and C#.

In a matter of minutes I had a connection to a database and fully generated classes by dragging and dropping tables to my "LINQ to SQL Classes" file.

The only code I had to write was to query the data using LINQ and do some basic operations on it.

Definitely looking forward to getting more involved with Visual Studio 2008 and LINQ!

VS 2008 Multi-Targeting Support

Matt Pavey — 2008-01-21 15:06:47

http://weblogs.asp.net/scottgu/archive/2007/06/20/vs-2008-multi-targeting-support.aspx

SCOPE_IDENTITY() vs. @@IDENTITY - Retrieving IDENTITY value for most recently added row in a table

Matt Pavey — 2008-01-11 16:55:23

This one is one of the basics that all SQL programmers learn at some point... Sometimes the hard way.

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You could have a situation where there is a trigger that inserts a new record in a Logs Table, for example, when your Stored Procedure or INSERT SQL Statement inserts a record in the Orders Table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the record added into the Log Table and not the Orders Table, which will create a nasty, nasty bug in your data access layer.

To avoid the potential problems associated with someone adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your INSERT SQL Statement or Stored Procedure.

http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx

Luckily today when some strange behavior occurred during our QA testing this was the first thing that I thought of, and sure enough, the original developer used @@IDENTITY to get the newly inserted identity value, which at the time was perfectly correct to do; however, we've recently added some trigger functionality, which caused this behavior to produce incorrect results. Switching to SCOPE_IDENTITY() fixed the problem. But this one is definitely one you want to remember if you are using triggers in your code.

Setting a variable from dynamic SQL

Matt Pavey — 2008-01-11 16:54:16

I don't have to do this very often, but it definitely comes in handy in certain situations.

-- setting a variable from dynamic sql

DECLARE @MyValue INT

EXEC sp_executesql N'SELECT @MyValue = 999', N'@MyValue INT OUTPUT', @MyValue OUTPUT

SELECT @MyValue

-- setting output parameter from dynamic stored procedure call

DECLARE @OutputParameter VARCHAR(100)

DECLARE @Error INT

DECLARE @SPName VARCHAR(128)

DECLARE @SPCall NVARCHAR(128)

DECLARE @RC INT

SELECT @SPCall = 'EXEC ' + @SPName + ' @OutputParameter OUTPUT'

EXEC @RC = sp_executesql @SPCall, N'@OutputParameter VARCHAR(100) OUTPUT', @OutputParameter OUTPUT

SELECT @Error = @@Error

One example where this was useful for me was for converting a demormalized set of horizontal data to a normalized set of vertical data.

The denormalized data contained a series of column names such as "200701", "200702", "200703", etc. for each month of the year. The file changes month to month and to minimize re-writing code each time a new file comes in I am able to import the data generically, determine the columns that are in the file, and get the value by setting a variable using dynamic SQL.

-- declare variables

DECLARE @Total FLOAT

DECLARE @SqlStatement NVARCHAR(1000)

-- set default values

SET @Total = 0

SET @SqlStatement = 'SELECT @Total = [' + @ColumnName + '] FROM RawData WHERE RecordID = ' + CONVERT(VARCHAR, @RecordID)

-- get the specified column value for the current record

EXEC sp_executesql @SqlStatement, N'@Total FLOAT OUTPUT', @Total OUTPUT

Enjoy!

SQL Server GetAge Function

Matt Pavey — 2008-01-11 16:52:23

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetAge] (@DOB DATETIME, @Today DATETIME)
RETURNS INT
AS
BEGIN
     -- declare variables
     DECLARE @Age INT

     -- set defaults
     SET @Age = Year(@Today) - Year(@DOB)

     -- if it is a previous month subtract one off the age
     IF Month(@Today) < Month(@DOB)
     BEGIN
          SET @Age = @Age -1
     END

     -- if it is the current month but a previous day subtract one off the age
     IF Month(@Today) = Month(@DOB) AND Day(@Today) < Day(@DOB)
     BEGIN
          SET @Age = @Age - 1
     END

-- return value
RETURN @Age
END

Usage

-- declare variables
DECLARE @Today AS DATETIME

-- set default values
SET @Today = GetDate()

-- get data
SELECT   ID,
             DOB,
             'Age' = dbo.GetAge(DOB, @Today)
FROM     MyTable

Mapedit

Matt Pavey — 2007-11-03 14:44:49

"Mapedit is an imagemap-editing program. Mapedit adds client-side imagemaps to web pages, simply by opening the page, selecting the image, and drawing rectangles, polygons and other shapes. There is no easier way to make an imagemap, and zoom support allows easy detail work on large images."

http://www.boutell.com/mapedit/index.html

I used Mapedit for the first time today. It's a very simple application but it saved me alot of time today when doing some HTML image mapping on a regional map of the United States earlier today. In the past I've always just opened my image file up in a standard image editor such as Paint or Photoshop and mapped out the coordinates manually and created the image map manually, which isn't that hard to do; however, in some cases if you have a complex area to map, such as a polygon, it can become very cumbersome to record all of the (x, y) coordinates efficiently and accurately.

Mapedit is a very simple interface that lets you open the image and map out multiple areas and generate the HTML code with little effort.

"Mapedit is shareware. The copy you have downloaded is fully functional for 30 days or 100 saves, whichever comes first. When your time or your saves run out, you will need to unlock the product to continue using it."

Lorem Ipsum: Generating Dummy Text

Matt Pavey — 2007-10-29 11:28:53

Here's a site that many of you are probably familiar with if you've ever had to quickly generate some placeholder text for prototyping, testing, etc.

http://www.lipsum.com

Maximum Capacity Specifications for SQL Server 2005

Matt Pavey — 2007-10-29 11:17:10

The following link contains the maximum sizes and numbers of various objects defined in Microsoft SQL Server 2005 components.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx