Jim's blog | .Net and SQL Server blog

Silverlight 3 for Business Applications

by JBrooks 28. July 2009 10:49

A few weeks ago I saw a demo of Silverlight 3 at the PhillyDotNet User Group - WOW. Wow for business applications, not graphic applications. There is a learning curve, but you get a lot for it. For example, the demo showed a grid being bound to a table without needing to write any code.

Right out of the box you had sorting, editing, paging, etc. But it wasn't the lame stuff you normally get and then have to rework. For example the paging was smart enough to write the sql that would only bring back the 20 rows you needed for the page.

The demo continued with him putting a detail form on the page for editing. Again no code, but it was smart enough to know that it had the same datasource as the grid on the page. So as you were moving row to row on the grid - the detail form was showing the current row (and it was very fast).

Both the grid and the detail form were editable and as you changed a field in one the other would reflect the new value. The editing was smart enough to validate the field on its own. So you couldn't put a letter in a field that was an integer type, etc. It also limited the number of characters that could be entered based on the column size found in the database. All the date fields on the detail form automatically had a calendar next to them. You get the idea - no coding for any of this.

If this weren't enough, it can be used to build occasionally connected applications. So he showed how he updated a few records on a few different pages, had the option to revert back a field later (ctrl-Z), and then at the end submitted all the changed records to be saved.

Also, they said it works with Linq2SQL and the entity framework.

Here is a link to a demo (not the one I saw.)  http://videos.visitmix.com/MIX09/T40F




How to Work With a GridView Row in JavaScript

by JBrooks 14. July 2009 16:40

The question came up asking how to change something in a GridView row based on something else in that row without doing a postback.   So I created a simple example where if you check a CheckBox then another column in that row would have it's background color changed.  I did this in a generic way by sending the checkbox to the function by using the keywork "this" and then getting a reference to the row.  Here is what it looks like:

So when you check the CheckBox for a given row, it's "Name" column's background changes color to Aqua.

Here is the code behind to bind the table:

using System;
using System.Data;

namespace BlogTests
    public partial class _Default : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
               DataTable dt = new DataTable();

                dt.Columns.Add("Include", typeof(Boolean));
                dt.Columns.Add("Name", typeof(String));
                dt.Rows.Add(new Object[] { 0, "Jim" });
                dt.Rows.Add(new Object[] { 0, "Jen" });
                dt.Rows.Add(new Object[] { 0, "Kylie" });
                GridView1.DataSource = dt;



and then the .aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="BlogTests._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Row Test</title>
    <style type="text/css">
            background-color: Aqua;
            font-weight: bold;

    <script type="text/javascript">
        function toggleSelected(sender) {
            var row = GetParentElementByTagName(sender, "TR");

            if (sender.checked)
                row.cells[1].className = "selected";
                row.cells[1].className = '';


        function GetParentElementByTagName(element, tagName) {
            var element = element;
            while (element.tagName != tagName)
                element = element.parentNode;
            return element;

    <form id="form1" runat="server">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <asp:TemplateField HeaderText="Include" SortExpression="Include">
                    <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("Include") %>' onclick="toggleSelected(this)" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name">
                <ItemStyle HorizontalAlign="Left" />



ASP.Net | Development | GridView

Attaching upload files to a record.

by JBrooks 2. July 2009 11:31

One of our applications has a page for our users to report bugs.  We wanted to allow them to “attach” one or more files to the bug record.  The files could be screen shots, spreadsheets, etc.    So the bug entry page would have the normal data entry part and have the following box at the bottom of the page:


If you click the FileName it will download the file if IIS is setup to send that extention.  All this should be made into a user control or a server control, but for now I have the code in the page.  Here are the key parts:

// the page has a property that returns the bug's record id.

private int id

{ get { return Convert.ToInt32(Request.QueryString["id"]); } }



private string uploadDir

{ get { return System.AppDomain.CurrentDomain.BaseDirectory+ "\\Uploads\\"; } }


protected void btnUpload_Click(object sender, EventArgs e)

   if (this.FileUpload1.HasFile)

   {  //we rename the file to start with the id and an underscore.

   string fileName = uploadDir + id.ToString() + "_" + FileUpload1.FileName;





   } else { setErrorMessage("You have not specified a file."); }



protected void bindFiles()


// make a table to hold the file's information.

    DataTable dt = new DataTable();

    dt.Columns.Add("FileName", typeof(String));

    dt.Columns.Add("Created", typeof(DateTime));

    dt.Columns.Add("Size", typeof(Int32));

    dt.Columns.Add("id", typeof(Int32));


         // get a list of files that start with our id.

    string[] files = Directory.GetFiles(uploadDir,id.ToString()+"_*.*");


//populate the table.

    foreach (string fileName in files)


        DataRow dr = dt.NewRow();

        FileInfo fi = new FileInfo(fileName);

        // at this point we need to strip off the id.

        dr["FileName"] = fileName.Substring(fileName.LastIndexOf("\\") + 2 + id.ToString().Length);

        dr["Created"] = fi.CreationTime;

        dr["Size"] = fi.Length;

        dr["id"] = this.id;





// now bind the table to our gridview

    this.gvFiles.DataSource = dt;





protected void lbDelete_Click(object sender, EventArgs e)


         //Don't like how fragile this is, but good enough for now.

    string fileName = uploadDir + id.ToString() + "_" + ((HyperLink)((GridViewRow)((LinkButton)sender).Parent.Parent).Cells[1].Controls[0]).Text;








 Here is the related mark-up from the aspx page:

<div  id="divFiles" style="border: solid 1px black">

<span id="Span1" style="font-size:X-Large;font-weight:bold;">FILES</span>

<br />

    <asp:FileUpload ID="FileUpload1" runat="server" />&nbsp;&nbsp;&nbsp;

    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" /><br />

    <br />

    <asp:GridView ID="gvFiles" runat="server" AutoGenerateColumns="False" Caption="<strong>FILES</strong>"

        CaptionAlign="Top" CellPadding="4" ForeColor="#333333" GridLines="None">

        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />


            <asp:TemplateField InsertVisible="False" ShowHeader="False">


                    <asp:LinkButton ID="lbDelete" runat="server" CausesValidation="False" CommandName="Delete"

                        Text="Delete" OnClientClick="confirm('Are you sure you want to delete this file?');" OnClick="lbDelete_Click"></asp:LinkButton>



            <asp:HyperLinkField DataTextField="FileName" SortExpression="FileName" Text="FileName"

                HeaderText="FileName" DataNavigateUrlFormatString="~/Uploads/{0}_{1}"

                DataNavigateUrlFields="id,FileName" >

                <ItemStyle HorizontalAlign="Left" />


            <asp:BoundField DataField="Created" DataFormatString="{0:M/d/yy H:mm}" HeaderText="Created"

                SortExpression="Created"  />

            <asp:BoundField DataField="Size" HeaderText="Size" SortExpression="Size" DataFormatString="{0:#,###,###}" >

                <ItemStyle HorizontalAlign="Right" />



        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

        <AlternatingRowStyle BackColor="White" />



<br />




ASP.Net | GridView | Development

Formatting Dates

by JBrooks 2. July 2009 09:37

I was having a lot of code that looked like the following:

         if(dr["StartTime"] != DBNull.Value)   
                tbStartTime.Text = dr["StartTime"].ToString("M/d/yy H:mm");

              tbStartTime.Text = "";


         tbStartTime.Text = DateTime.Now.ToString("M/d/yy H:mm");


You usually use the same formatting throughout an application so I simplified this by using the following function:

        public string formatDate(object date)
            if (date != DBNull.Value)
                return Convert.ToDateTime(date.ToString()).ToString("M/d/yy H:mm");
                return "";

 So now I can have my formatting in one place and use this like so:

       tbStartTime.Text = formatDate(dr["StartTime"]);

 and using the same function:

     tbStartTime.Text = formatDate(DateTime.Now);






ASP.Net | Development

Release Manager for Subversion

by JBrooks 27. June 2009 06:36

Getting close to being done my release manager.

I got the video done, it can be seen here: http://www.ReleaseManager.com

Any comments would be appreciated!


Development | General

Another Place to Put the Connection String

by JBrooks 18. June 2009 09:44

Another way to store your connection string in a way that it doesn't get in the way when you promote your web site from Test to QA to Prod is the following:

Have your web site under a given directory, say d:\WebSites\MyWebsite.
And you also have another directory to hold your connection strings that isn't under that directory.   So I could have my connection string as an appSetting in the following file:

The section looks like:
    <add key="Env" value="Dev"/>
    <add key="DefaultConnection" value="server=MyServer;uid=MyUser;pwd=P@ssw0rd;database=MyDB;" />

Then you have the following in your d:\WebSites\MyWebsite\web.config file

<appSettings file="../Support/config/WebEnvironment.config">

You would retreive the string by:

-Things to note are that this now allows you to delete everything under d:\WebSites\MyWebsite before you redeploy your site.  
-If you change the connection string you need to do an IIS reset for the site to pick up the change.  
-You can't do the file="..." part with a connection


ASP.Net | Development

Generate Save Stored Proc from Table

by JBrooks 15. June 2009 11:18

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data

 This second step is tedious and lends itself to automation.  So I created the stored proc createSPFromTable that takes the name of a table and creates a simple save store proc.  So the example:

createSPFromTable SourceTypes


create proc saveSourceTypes(@id int, @Name varchar(50), @DisplayOrder float, @Status char(1))



set nocount on



if @id > 0

    update SourceTypes

    set Name = @Name,

    DisplayOrder = @DisplayOrder,

    Status = @Status

    where id = @id



    insert into SourceTypes

    (Name, DisplayOrder, Status)

    values (@Name, @DisplayOrder, @Status)


 set @id = scope_identity()



select @id


return @id






Below is the stored proc that generates the basic save proc.   Not that you have to pass in the primary key column if it is not named "id".

create proc [dbo].[createSPFromTable](@TableName varchar(250), @idColumn varchar(25)=null)
set nocount on

declare @crlf char(2)
set @crlf = char(13)+char(10)

declare @parameterList varchar(8000)
set @parameterList='create proc save'+@TableName+'('

-- This converts sql type to c# type.
SELECT @parameterList=  @parameterList + case when lower(c.COLUMN_NAME) = isnull(@idColumn,'id') then '' else '@'+c.COLUMN_NAME+' ' + c.DATA_TYPE+
WHEN 'binary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'char' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'decimal' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'numeric' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nvarchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varbinary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
ELSE '' END+', ' end,
@idColumn = case when @idColumn is null and lower(c.COLUMN_NAME) = 'id' then c.COLUMN_NAME else @idColumn end
where TABLE_NAME = @TableName

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+'as'+@crlf+'begin'+@crlf+'set nocount on'+@crlf+@crlf+@crlf

set @parameterList=@parameterList+'if @'+@idColumn +' > 0 ' + @crlf

set @parameterList=@parameterList+' update '+@TableName+ @crlf +' set'

SELECT @parameterList=@parameterList+' '+ c.COLUMN_NAME+' = @'+c.COLUMN_NAME+', '+ @crlf
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn

set @parameterList=substring(@parameterList,1,len(@parameterList)-4)+ @crlf+ ' where '+@idColumn +' = @'+@idColumn+ @crlf

set @parameterList=@parameterList+'else '+ @crlf +'begin'+@crlf+ ' insert into '+@TableName+@crlf +' ('

SELECT @parameterList=@parameterList+ c.COLUMN_NAME+', '
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf

set @parameterList=@parameterList+' values ('

SELECT @parameterList=@parameterList+'@'+ c.COLUMN_NAME+', '
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+@crlf

set @parameterList=@parameterList+' set @'+@idColumn+' = scope_identity()'+@crlf

set @parameterList=@parameterList+'end'+@crlf

set @parameterList=@parameterList+'select @'+@idColumn+@crlf

set @parameterList=@parameterList+'return @'+@idColumn+@crlf

set @parameterList=@parameterList+'end'+@crlf
print @parameterList




Development | SQL

Generate Data Access Layer Methods From Stored Procs

by JBrooks 3. June 2009 10:42

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data
    3. create the data access layer methods that call the stored procs.

This is a little tedious so I created SQL stored proc called createDALFromSP to generate a data access method from a stored proc.  

To create a DAL method for a stored proc called SaveRequest I would run this from SQL Analyzer:

      createDALFromSP 'SaveRequest'

An what it generates looks like the following:

public void SaveRequest(Int32 ID, String Name, Guid UserID, String Comments, Boolean IsSpecial, Double BuildOrder, String status, Boolean clear)
    SqlCommand cmd = GetNewCmd("dbo.SaveRequest");

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Name;
    cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserID;
    cmd.Parameters.Add("@Comments", SqlDbType.VarChar, 8000).Value = Comments;
    cmd.Parameters.Add("@IsSpecial", SqlDbType.Bit).Value = IsSpecial;
    cmd.Parameters.Add("@BuildOrder", SqlDbType.Float).Value = BuildOrder;
    cmd.Parameters.Add("@status", SqlDbType.Char, 1).Value = status;
    cmd.Parameters.Add("@clear", SqlDbType.Bit).Value = clear;

    cmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;


Below is the stored proc createDALFromSP, it looks like a mess but it is basic stuff.  This works on SQL Server 2000 and 2005.

create  proc dbo.createDALFromSP(@procName varchar(250))

declare @parameterList varchar(8000)
declare @isGet bit

select @isGet = case when lower(@procName) like 'get%' then 1 else 0 end

if @isGet = 1
    set @parameterList='public DataTable '+@procName+'('   
    set @parameterList='public void '+@procName+'('

-- get the signiture of the method itelf - so convert SQL Server data types to C# datatypes
SELECT @parameterList=@parameterList+(SELECT CASE DATA_TYPE
        WHEN 'bigint' THEN 'Int64'
        WHEN 'binary' THEN 'Binary'
        WHEN 'bit' THEN 'Boolean'
        WHEN 'char' THEN 'String'
        WHEN 'datetime' THEN 'DateTime'
        WHEN 'decimal' THEN 'Decimal'
        WHEN 'float' THEN 'Double'
        WHEN 'image' THEN 'Binary'
        WHEN 'int' THEN 'Int32'
        WHEN 'money' THEN 'Decimal'
        WHEN 'nchar' THEN 'String'
        WHEN 'ntext' THEN 'String'
        WHEN 'numeric' THEN 'Decimal'
        WHEN 'nvarchar' THEN 'String'
        WHEN 'real' THEN 'Single'
        WHEN 'smalldatetime' THEN 'DateTime'
        WHEN 'smallint' THEN 'Int16'
        WHEN 'smallmoney' THEN 'Currency'
        WHEN 'sql_variant' THEN 'String'
        WHEN 'sysname' THEN 'String'
        WHEN 'text' THEN 'String'
        WHEN 'timestamp' THEN 'Binary'
        WHEN 'tinyint' THEN 'Byte'
        WHEN 'uniqueidentifier' THEN 'Guid'
        WHEN 'varbinary' THEN 'Binary'
        WHEN 'varchar' THEN 'String'
                ELSE '!MISSING' END)+' '+substring(PARAMETER_NAME,2,300)+', ' 
where SPECIFIC_NAME = @procName

if @@rowcount > 0
       set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'

-- This is the body of the method
-- Most of the work is to convert sql data types to SqlDbType while listing out the parameters.
select Line
from (
select -99 SortOrder, @parameterList Line
union all
select -98, '{'
union all
select -97, '    SqlCommand cmd = GetNewCmd("dbo.'+@procName+'");'
union all
select -96,''
union all
select ORDINAL_POSITION, '    cmd.Parameters.Add("'+PARAMETER_NAME+'", SqlDbType.'+
    WHEN 'bigint' THEN 'BigInt'
    WHEN 'binary' THEN 'Binary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'bit' THEN 'Bit'
    WHEN 'char' THEN 'Char, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'datetime' THEN 'DateTime'
    WHEN 'decimal' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'float' THEN 'Float'
    WHEN 'image' THEN 'Image'
    WHEN 'int' THEN 'Int'
    WHEN 'money' THEN 'Money'
    WHEN 'nchar' THEN 'NChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'ntext' THEN 'NText'
    WHEN 'numeric' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'nvarchar' THEN 'NVarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'real' THEN 'Real'
    WHEN 'smalldatetime' THEN 'SmallDateTime'
    WHEN 'smallint' THEN 'SmallInt'
    WHEN 'smallmoney' THEN 'SmallMoney'
    WHEN 'sql_variant' THEN 'Variant'
    WHEN 'text' THEN 'Text'
    WHEN 'timestamp' THEN 'Timestamp'
    WHEN 'tinyint' THEN 'TinyInt'
    WHEN 'uniqueidentifier' THEN 'UniqueIdentifier'
    WHEN 'varbinary' THEN 'VarBinary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'varchar' THEN 'VarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
                         END)+').Value = '+substring(PARAMETER_NAME,2,300)+';'
where SPECIFIC_NAME = @procName
union all
select 901,''
union all
SELECT 902,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.InputOutput;'
where SPECIFIC_NAME = @procName
union all
SELECT 903,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.Output;'
where SPECIFIC_NAME = @procName
union all
select 904,case when @isGet = 1 then '    return GetTable(cmd);' else '    ExecuteNonQuery(cmd);' end
union all
select 905, '}'
 ) x
order by SortOrder









Development | SQL

Reporting when Logging only the start and end of an outage condition

by JBrooks 3. June 2009 09:33

In a previous post I had a system to only log the start and end of an outage condition.  To report on the outages we need to first look at the table where the exceptions get logged:

    CREATE TABLE [dbo].[Exceptions](
        [ExTime] [datetime] NULL CONSTRAINT [DF_Exceptions_ExTime]  DEFAULT (getdate()),
        [Message] [varchar](8000) NULL,
        [StackTrace] [varchar](8000) NULL
      ) ON [PRIMARY]

And a sample of the data:

        SELECT TOP 12 ExTime, Substring(Message,1,60) Message
        FROM Exceptions

                  ExTime Message
5/5/09 13:21 Outage start of error, ErrorGuid: 595f890d-c337-49
5/5/09 13:28 Outage end of error, ErrorGuid: 595f890d-c337-493f
5/11/09 18:08 dbo.processRequestAll|Thread was being aborted.
5/12/09 14:13 The actual number of records in insert did not match the exp
5/15/09 13:00 Data Feed FAILED at 5/15/2009 1:00:04 PM
5/15/09 13:00 Data Feed Error occurred at 5/15/2009 1:00:04 PM
5/22/09 18:10 dbo.saveXML|The DELETE statement conflicted 
5/26/09 11:14 The actual number of records in insert did not match the exp
6/3/09 6:39 Outage start of error, ErrorGuid: c1c430f0-9a8f-4f
6/3/09 6:41 Outage end of error, ErrorGuid: c1c430f0-9a8f-4fca
6/3/09 12:36 Outage start of error, ErrorGuid: 06de0545-d152-44
6/3/09 12:39 Outage end of error, ErrorGuid: 06de0545-d152-4438


 Now to do the reporting we create a temp table with just the records we are interested in:

           select ExTime,
           case when charindex('start of',Message) > 0 then 'S'
           else 'E' end a,
           substring(Message,charindex('Guid',Message)+5,40) g  into #t
           from Exceptions
           where Message like 'Outage%'

The first column (badly named "a") is just a flag to say if it is a Start or End record.  The second column (named "g") is the Guid for the outage event.    

Now create another temp table where the guid is summarized:

        select g, max(case when a = 'S' then ExTime end) startTime,
        max(case when a = 'E' then ExTime end) endTime into #t2
        from #t
        group by g
        having count(1) = 2

Now we can report on this.

           select startTime,
           CASE DATEPART(weekday,startTime)
           WHEN 1 THEN 'Sunday'
           WHEN 2 THEN 'Monday'
           WHEN 3 THEN 'Tuesday'
           WHEN 4 THEN 'Wednesday'
           WHEN 5 THEN 'Thursday'
           WHEN 6 THEN 'Friday'
           WHEN 7 THEN 'Saturday'
           round((datediff(ss,startTime,endTime)+0.0)/60,2) Minutes
           from  #t2 t
           where not exists
           (     select 1
                 from #t2 tin
                 where t.startTime between tin.startTime and tin.endTime
                 and t.g <> tin.g)
          and startTime > '05/13/09'
          order by startTime

  This gives us the following report that shows us the start time of the outage and how many minutes it lasted:


               StartTime Day     Minutes
5/27/09 14:38 Wednesday 9.53
5/28/09 10:06 Thursday 9.68
5/28/09 13:21 Thursday 4.52
5/28/09 23:18 Thursday 9.64
5/30/09 13:21 Saturday 3.85





ASP.Net | Development


by JBrooks 3. June 2009 09:10

We found an issue with some of the older scripts having SET ANSI_NULLS OFF and this causes problems.  The ANSI_NULLS ON setting is a database option that determines how NULL comparisons are handled. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

So if I have:

        Select *
        from A
        inner join B
        on A.name = B.name

With ANSI_NULLS OFF every rows where NAME is null in A will match every row in B where NAME is null.   I can’t see when you would ever want this.  And SET ANSI_NULLS OFF in SQL 2008 and after will actually throw an error.

The second part of this problem is that for stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect.   

The third part of this problem is that when we combine our sql scripts into 1 big text file and if one of the scripts has SET ANSI_NULLS OFF then every subsequent stored proc and SQL statement is affected if it doesn’t define this setting itself.

So going forward I'm going to make sure my store proc scripts start with:

This is generated for you automatically when you right click a stored proc and select “Modify” or “Script Stored Proc As…”


Development | SQL