Jim's blog | .Net and SQL Server blog

Changing the SQL Server Password

by jbrooks 15. March 2011 11:19

All of these failed except the last one, I was logged in as RMUser:

exec sp_password 'DC9E3622', 'A55BE7!#4', 'RMUser'

Msg 15210, Level 16, State 1, Procedure sp_password, Line 20

Only members of the sysadmin role can use the loginame option. The password was not changed.


Incorrect syntax near 'LOGIN'.

exec sp_password @old = 'DC9E3622', @new = 'A55BE7!#4'

Command(s) completed successfully.


Notify Me When Something Doesn’t Happen

by JBrooks 7. January 2011 14:24

I have 10 clients that each have a process that posts a value to my web service every hour at three minutes after the hour.  I needed to know when a post doesn’t happen for a client. 

The logic of the SQL is that I want to return the last row posted for each Client ID when that last row’s post time is older than 60 minutes (plus 2 minutes for variations).  And I only care if it has happened in the last 4 days.  This is the SQL:

declare @intervalMinutes int,
@variationMinutes int,
@activeDays int
set @intervalMinutes = 60
set @variationMinutes = 2
set @activeDays = 4
select 'For Client '+cast(clientId as varchar)+
    ' Last Posting is too old: '+ 
    convert(varchar(5),p.postTime,1)+ ' '+convert(varchar(5),p.postTime,108) 
    as Message,
'Last status was: '+
    status as Details
from Postings p
where p.postTime = 
    (select max(pIn.postTime) 
    from Postings pIn
    where pIn.clientId = p.clientId)
and p.postTime  between 
    and dateadd(mi,-@intervalMinutes - @variationMinutes,getdate()) 
order by p.postTime

So this could return a table that looks like:


I now only get notified if there last posting is more than 62 minutes old.  I previously blogged about how to have rows like these show up in an RSS Feed HERE.  The good thing about an RSS Feed is that you can have it on your phone, Outlook and web browser.


Having the Result Set of a Stored Proc Sent to You by RSS Feed.

by JBrooks 14. December 2010 12:44

I wanted to monitor one of my system from my desk top and from my phone.  I found a simple solution whereby I can subscribe to the result set of a stored proc by using RSS. So I can have this feed MS Outlook, my phone or my web browser.

First, Visual Studio 2010 makes creating an RSS feed a simple matter that is about 1 page of code.

I simply add an ASPX page to my project and remove most of the markup so it only has 2 lines:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="rss.aspx.cs"  Inherits="RSS.rss"  %>
<%@ OutputCache Duration="60" VaryByParam="none" %>

Next the code behind simply calls the stored proc placing the results into a table and then loading up some of the RSS related collections VS2010 gives you. 

using System;
using System.Data;
using System.ServiceModel.Syndication;
using System.Web;
using System.Collections.Generic;
using System.Xml;
namespace RSS
public partial class rss : System.Web.UI.Page
    protected void Page_Load(object sender, EventArgs e)
        string id = Request.QueryString["id"];
        // I don't want just anyone to subscribe, so you have to know the GUID.
        if (id== null || id != "23F14EA1-1B20-443B-9B94-92C4EA4A8099")
            throw new Exception("Guid not reconized");
        Response.ContentType = "application/atom+xml";
        // this gets the data from the database and populates a table.
        DataTable dt = cDB.getFeed();
        SyndicationFeed myFeed = new SyndicationFeed();
        myFeed.Title = TextSyndicationContent.CreatePlaintextContent("SampleApp Activity");
        myFeed.Description = TextSyndicationContent
            .CreatePlaintextContent(@"A syndication of the most recently 
                    SampleApp activity including exceptions.");
            new Uri(GetFullyQualifiedUrl("/rss.aspx"))));
            new Uri(GetFullyQualifiedUrl(Request.RawUrl))));
        myFeed.Copyright = TextSyndicationContent
                        .CreatePlaintextContent("Copyright SampleApp");
        myFeed.Language = "en-us";
        List<SyndicationItem> feedItems = new List<SyndicationItem>();
        foreach (DataRow dr in dt.Rows)
            SyndicationItem item = new SyndicationItem();
            item.Title = TextSyndicationContent.CreatePlaintextContent(dr["title"].ToString());
            SyndicationPerson authInfo = new SyndicationPerson();
            authInfo.Email = "SampleApp@YourDomain.com";
            // RSS feeds can only have one author.
            // The stored proc returns different categories of data that I am interested in.
            switch (dr["category"].ToString())
                case "WindFarms":
                case "WindFarms ":
                        new Uri(GetFullyQualifiedUrl("/WindFarms.aspx"))));
                    authInfo.Name = "SampleApp WindFarm";
                case "Exceptions":
                        new Uri(GetFullyQualifiedUrl("/ErrorLog.aspx"))));
                    authInfo.Name = "SampleApp Exception";
                    authInfo.Name = "SampleApp";
            item.Summary = TextSyndicationContent.CreatePlaintextContent(
            item.Categories.Add(new SyndicationCategory(dr["category"].ToString()));
            item.PublishDate = DateTime.Parse(dr["pubdate"].ToString());
            item.LastUpdatedTime = item.PublishDate;
            item.Id = item.PublishDate.ToString();
            // Add the item to the feed
        myFeed.Items = feedItems;
        XmlWriter feedWriter = XmlWriter.Create(Response.OutputStream);
        // Use Atom 1.0 
        Atom10FeedFormatter atomFormatter = new Atom10FeedFormatter(myFeed);
    private string GetFullyQualifiedUrl(string s)
        Uri u = new Uri(HttpContext.Current.Request.Url, s);
        return u.ToString();


To have this feed my Outlook RSS folder I just need to right click “RSS Feeds” and select “Add a New RSS Feed…”. 


Then enter the URL of my RSS feed.   Don’t forget to add the GUID at the end with     ?id=23F14EA1-1B20-443B-9B94-92C4EA4A8099


If you site uses authentication in your site you will have to turn it off for the rss.aspx page.  To do this you would add an entry in your web config file:

<location path="rss.aspx">
            <allow users="*"/>

You should now have a folder in Outlook that will get populated by the feed.


ASP.Net | Development

Logging Exceptions and the Commands that Caused Them.

by jbrooks 14. December 2010 12:12

For a long time I’ve been logging all of my exceptions to a table that looks like:


Now for exceptions that were generated by the database I want to also log the exact database call that caused the exception.  It will make debugging go a lot faster.  For this I have a new table:


This will get the exceptionId from the first table and the a string that caused the exception.  Something like:

saveSomething @id=100, @UserName='Jakey',  @IPAddress='',  @editBy='JBrooks'

So the first part of logging the Exception is two methods on my base class.

public void LogException(Exception ex)
    LogException(ex, null);
public void LogExceptionx(Exception ex, SqlCommand objBadCmd)
    int id = 0;
    if (this._WritingErrorLog == true)
        // don't want to get in an infinite loop.
        this._WritingErrorLog = true;
        SqlCommand objCmd = this.GetNewCmd("dbo.insertException");
        string message = "";
        // Get the userId of the user that got the error.
        if (System.Web.HttpContext.Current.Request.Cookies["UID"] != null)
            message = System.Web.HttpContext.Current.Request.Cookies["UID"].Value + "|";
        // Add the command that gave the error.
        if (objBadCmd != null && !string.IsNullOrEmpty(objBadCmd.CommandText))
            message += objBadCmd.CommandText + "|";
        message += ex.ToString();
        if (message.Length > 8000)
            message = message.Substring(0, 7999);
        objCmd.Parameters.Add("@Message", SqlDbType.VarChar, 8000).Value = message;
        objCmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, 8000).Value =
            ((ex.StackTrace == null) ? "no stack trace." : ex.StackTrace);
        //insert the exception and get the new ID.
        id = this.ExecuteScalarInt(objCmd);
        if (id > 0 && objBadCmd != null)
            insertExceptionCmd(id, objBadCmd);
    catch (Exception ExLogException)
    this._WritingErrorLog = false;

Now the part that generates the command string is another method that gets called be the method above:

private string getCmdString(SqlCommand objBadCmd)
    if (objBadCmd == null)
        return string.Empty;
    if (objBadCmd.Parameters == null || objBadCmd.Parameters.Count == 0)
        return objBadCmd.CommandText;
    StringBuilder sb = new StringBuilder();
        sb.Append(objBadCmd.CommandText+" ");
        for (int i = 0; i < objBadCmd.Parameters.Count; i++)
            SqlParameter p = objBadCmd.Parameters[i];
            if (i > 0)
                sb.Append(", ");
            sb.Append(p.ParameterName + "=");
            if (p.Value == null)
                switch (objBadCmd.Parameters[i].SqlDbType)
                    case SqlDbType.BigInt:
                    case SqlDbType.Int:
                    case SqlDbType.Float:
                    case SqlDbType.Decimal:
                    case SqlDbType.SmallInt:
                    case SqlDbType.Money:
                    case SqlDbType.Image:
                    case SqlDbType.Real:
                    case SqlDbType.SmallMoney:
                    case SqlDbType.TinyInt:
                    case SqlDbType.DateTimeOffset:
                        sb.Append("'" + p.Value.ToString() + "'");
    catch (Exception ex)
        return "getCmdString created its own error processing:  " + 
            sb.ToString() + "      +++++" + ex.ToString().Substring(0, 8000);
    return sb.ToString();

So now I have a complete logging of exception in my applications and it is a simple matter to make a page where I can view them, or make an RSS feed where they are sent to me.


Development | SQL | ASP.Net

Change Connection String Password Programmatically

by jbrooks 6. December 2010 13:07

On one of our projects we have a requirement that we have to change our passwords every 90 day.  So I created a page where I can just click a button and it will generate a password, change the password on the database for the user in the connection string and then change the password in the web.config.

The page just has a button that say “Change Password” and a label called lblMessage to show the results.

The code behind just calls the class that does all of the work and then shows a message:

const string CONNSTRINGNAME = "SampleAppConnString";
const string WEBCONFIGFILE = "~/Web.Config";
protected void btnChangePassword_Click(object sender, EventArgs e)
if (cConnStringPasswordChanger.ChangePassword(HttpContext.Current.Server.MapPath(WEBCONFIGFILE), 
    this.lblMessage.Text = "Password has been changed";
    this.lblMessage.Text = "Password has NOT been changed";

Now for the class that does all of the work:

public static class cConnStringPasswordChanger
    public static bool ChangePassword(string webConfig, string ConnStringName)
        return ChangePassword(webConfig, ConnStringName, 
                Guid.NewGuid().ToString().Replace("-", "").Substring(0, 9));
    // 1. Open the web.config.
    // 2. Change the connString to have the new password.
    // 3. Change the password on the database.
    // 4. Commit the web.config change.
    public static bool ChangePassword(string webConfig, string ConnStringName, 
        string newPassword)
        bool changed = false;
        string oldConnString;
        FileInfo fi = new FileInfo(webConfig);
        if (fi.IsReadOnly)
            File.SetAttributes(webConfig, FileAttributes.Normal);
        fi = null;
        XmlDocument cfgDoc = new XmlDocument();
        XmlNode connNode = cfgDoc.SelectSingleNode("//connectionStrings");
        XmlNode myNode = connNode.SelectSingleNode("//add[@name='" + ConnStringName + "']");
        oldConnString = myNode.Attributes["connectionString"].Value;
        string oldPassword = getValue(oldConnString, "Password");
        string newConnString = oldConnString.Replace(oldPassword, newPassword);
        myNode.Attributes["connectionString"].Value = newConnString;
        string userId = getValue(newConnString, "User Id");
        XmlTextWriter writer = new XmlTextWriter(webConfig, null);
        writer.Formatting = Formatting.Indented;
        // last possible second change it on the database.
        changePassword(oldConnString, userId, oldPassword, newPassword);
            changed = true;
        catch (Exception ex)
            // error saving web.config change, so change it back on the database.
            changePassword(newConnString, userId, newPassword, oldPassword);
        writer = null;
        cfgDoc = null;
        return changed;
    // This function is passed a connection string like:
    // "data source=.\SQLEXPRESS;Initial Catalog=MyDb;User Id=McUser;Password=c99c0472e;"
    // and a partName like "User Id" or "Password".
    // it returns the value for that partName.
    private static string getValue(string connString, string partName)
        int partStart = connString.ToLower().IndexOf(partName.ToLower());
        int partEndSemi; 
        int partEndQuote;
        if (partStart > -1)
            partStart += partName.Length + 1;
            throw new Exception(partName + " not found in connection string");
        partEndSemi = connString.Substring(partStart).IndexOf(";");
        partEndQuote = connString.Substring(partStart).IndexOf("\"");
        if (partEndQuote == -1)
            partEndQuote = connString.Length - partStart - 1;
        if (partEndSemi == -1)
            partEndSemi = connString.Length - partStart - 1;
        return connString.Substring(partStart, Math.Min(partEndQuote, partEndSemi));
    // Call the database to change the password.
    private static bool changePassword(string connString, string loginName, 
        string oldPassword, string newPassword)
        bool changed = false;
        using (SqlConnection connection = new SqlConnection(connString))
            SqlCommand command = new SqlCommand("dbo.sp_password", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@loginame", SqlDbType.NVarChar, 128).Value = loginName;
            command.Parameters.Add("@old", SqlDbType.NVarChar, 128).Value = oldPassword;
            command.Parameters.Add("@new", SqlDbType.NVarChar, 128).Value = newPassword;
            changed = true;
        return changed;


ASP.Net | Development | SQL

Get a Reference to MainPage in a Silverlight Application

by JBrooks 30. November 2010 08:57

Once the user was logged in I wanted to change the menu options that are shown.  To do this I had to get a reference to MainPage.  Here is the code for that:

    MainPage m;
    if (Application.Current.RootVisual.GetType().Name == "BusyIndicator")
        m = (MainPage)((BusyIndicator)Application.Current.RootVisual).Content;
        m = (MainPage)Application.Current.RootVisual;

The code below was a PREVIOUS TRY, but didn’t work because sometimes I would get an exception because the first Parent was null.  Didn’t have time to look into this, and thought the code had some value so I left it here for now.

========= PREVIOUS TRY ============================================================

First I created a static method in the App class that walks up the hierarchy of parents until it finds a match.  This can be used for more than just finding MainPage.  Everything in the hierarchy should be derived from the FrameworkElement class.

public static FrameworkElement GetParentByName(FrameworkElement currentPage, string ParentName)
            FrameworkElement fe = (FrameworkElement)currentPage.Parent;
            // Walk your way up the chain of Parents until we get a match
            while(fe.GetType().Name != ParentName)
                fe = (FrameworkElement)fe.Parent;
            return fe;

Then in the page Home I have the following code that calls that method.

private void Page_LayoutUpdated(object sender, System.EventArgs e)
    if (WebContext.Current.User.IsAuthenticated)
        MainPage m = (MainPage)App.GetParentByName(this, "MainPage");
        m.LinkResetPassword.Visibility = System.Windows.Visibility.Collapsed;
        m.LinkChangePassword.Visibility = System.Windows.Visibility.Visible;



Data Access Layer Architecture

by JBrooks 23. November 2010 10:59

I wanted to document how we do our DAL.  First, performance is important so we use ADO.NET instead of the Entity Framework, NHibernate, etc.   Second, in our main project we have 1,743 stored procs  and counting, so a stored proc centered approach is necessary. 

So over the years I have reduced it down so that the following is possible:

protected void Page_Load(object sender, EventArgs e)
        if (!Page.IsPostBack)
            this.GridView1.DataSource = cApp.DB.getIPAddresses();

Normally you would have a business layer in there, but you get the idea from this sample.  Encapsulated in that one call is the logic to get the connection string, open and close connections, log exceptions, etc. 

Here is how access to the method works.  We have a central class called cApp that contains a lot of stuff having to do with the application in general.  One of those items is the database object.

using System.Configuration;
namespace SampleApp
    public sealed class cApp
        static readonly SampleApp.cDB _cDB = new SampleApp.cDB(
        public static cDB DB
                return _cDB;
        static cApp()
        { }


The thing to note here is that there is only every one database object, it is created in a thread safe way, and it stays around for the full length of the application.  Since the applications that I develop are almost always data driven applications I didn’t think it made much sense to create,destroy and garbage collect a data object for every request.  Nothing performs faster than the work that you don’t do.

So what does this cDB class look like?   Here is a sample with only a few methods.

using System;
using System.Data;
using System.Data.SqlClient;
namespace SampleApp
    public class cDB : DBLib.cDBBase
        public cDB(string ConnString)
            : base(ConnString)
        { }
        public cDB()
            : base()
        { }
        public DataTable getIPAddresses()
            SqlCommand cmd = GetNewCmd("dbo.getIPAddresses");
            return GetDataTable(cmd);
        public void saveIPAddresses(Int32 id, String UserName, String IPAddress, String editBy)
            SqlCommand cmd = GetNewCmd("dbo.saveIPAddresses");
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 150).Value = UserName;
            cmd.Parameters.Add("@IPAddress", SqlDbType.VarChar, 50).Value = IPAddress;
            cmd.Parameters.Add("@editBy", SqlDbType.VarChar, 150).Value = editBy;
        public DataTable getExceptions(DateTime exTime)
            SqlCommand cmd = GetNewCmd("dbo.getExceptions");
            cmd.Parameters.Add("@exTime", SqlDbType.DateTime).Value = exTime;
            return GetDataTable(cmd);

We follow the pattern where the method name and parameters are the same as the stored procs.   I had previously blog about how I automatically generate the code for these methods based on the stored procs HERE , which really helps when there are a lot of parameters. 

Most of the work is found in the class that the cDB class inherits from, DBLib.cDBBase.  It is a class I use in every project and it has methods for getting DataTables, Integers, GUIDs, etc.  It will log exceptions to a table and then fail over to a text file if needed.  

You can download a sample application that has the full source code including cDBBase HERE


Development | General

Stripping Out Passwords

by jbrooks 19. November 2010 07:44

I have a page in my ASP.Net application where I show all of my AppSettings, connection strings, etc.  But I didn’t want to show the passwords.  I wanted something like:


So here is the code to strip out the password:

        private string stripPassword(string connString)
            if (string.IsNullOrEmpty(connString))
                return connString;
            int pos = connString.ToLower().IndexOf("pwd");
            if (pos > -1)
                pos += 4;
                pos = connString.ToLower().IndexOf("password");
                if (pos > -1)
                    pos += 9;
                    return connString;
            return connString.Substring(0, pos) + "*******" + 
                connString.Substring(pos + connString.Substring(pos).IndexOf(";"));


ASP.Net | Development

Uploading XML Files to SQL Server

by jbrooks 18. November 2010 13:14

I had a large number of XML files that each contained hourly prices for a lot of different locations.  I needed to get all of the prices for a single location.  I did this by loading all of the files into a table and then querying the table.

First, I needed to change the rights on my database to allow me to run the xp_cmdshell store procedure.

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1

Then I needed to make the temporary table that would hold my file names and load the file names from the directory.

CREATE TABLE #DIR (fileName varchar(100))
EXEC master..xp_CmdShell 'DIR C:\RTHourly\*.xml /B'

A second table is needed hold the content of my XML files,  it is one row for each file. Note the XML data type for the last column called priceData.

create table xmls
(id int identity,
fileName varchar(250),
priceData xml)

Now we are ready to loop thru each file name found in the  #DIR table and load it’s contents into the xmls table.

DECLARE @fileName AS varchar(250)
DECLARE @fullPathName AS varchar(250)
SELECT fileName
WHERE fileName like '%.xml'
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @fileName;
    PRINT @fileName
    set @fullPathName = 'C:\RTHourly\'+ @fileName
    -- this must be dynamic sql b/c file name has to be a literal string for OPENROWSET
    exec (
    'INSERT INTO xmls(fileName, priceData)
    SELECT '''+@fileName+''' AS fileName, A.*
    FROM OPENROWSET( BULK '''+@fullPathName+''',SINGLE_CLOB)
    AS A')
    FETCH NEXT FROM file_cursor INTO @fileName;
CLOSE file_cursor;
DEALLOCATE file_cursor;


At this point all of the files are loaded so I just need to get the data out for my location “AZ”. 

select priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@utcTimepoint)[1]',
        'varchar(150)') as time,
priceData.value('(//PriceGroup/Prices/Price[@location = "AZ"]/Detail/@lmp)[1]',
'real') as price,
from xmls
order by 1 


Now let’s turn off the security hole that we created when we started this little project.

EXEC sp_configure 'xp_cmdshell', 0
EXEC sp_configure 'show advanced options', 0;


Development | SQL

Grid within a GridView Cell

by JBrooks 8. November 2010 11:04


I needed to display some parent data with it’s child data on the same row like shown in the image above. I happen to be combining a few tables together into a new table (called dtPlan) and then binding the GridView to that new table.  So this was my approach while moving the data into the dtPlan table.  dtRampRates is the child table to the data in dtResults.

DataView dvRampRates = new DataView(dtRampRates);
dvRampRates.Sort = "parentId, id";
StringBuilder sb = new StringBuilder();
for (int indexRow = 0; indexRow < dtResults.Rows.Count; indexRow++)
    DataRow drPlan = dtPlan.NewRow();
    drPlan["c0"] = planHour;
    drPlan["c1"] = dtResults.Rows[indexRow]["ResourceStatus"].ToString();
    drPlan["c2"] = dtResults.Rows[indexRow]["MinEconomicMW"].ToString();
    drPlan["c3"] = dtResults.Rows[indexRow]["MaxEconomicMW"].ToString();
    dvRampRates.RowFilter = "parentId=" + dtResults.Rows[indexRow]["id"].ToString();
    sb.Length = 0;
    sb.Append("<table cellspacing='0' class='RampRate'>");
    if (indexRow == 0)
    for (int i = 0; i < dvRampRates.Count; i++)
    drPlan["c4"] = sb.ToString();
gvPlan.DataSource = dtPlan;

The child grid is made in the “for” loop.  Normally you would have this loop in the RowDataBound event for the GridView.  With that approach it is easy to see how most of the code would be the same.


ASP.Net | GridView | Development

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.