# Monday, 07 May 2012

Today I was trying to run a sample application for VS11 Beta that I found online: Download Hands-on Lab Source Files. However when trying to run the pages I encountered the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Looking into the inner exception:

ANetworkRelatedErrorOccured

gave me the following:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

Ouch!

Well, what I could’ve done is trying to fiddle around with settings of SQL Server, trying to get it right and working. However I noticed that Entity Framework was being used and in the code I found this little piece:

public class ProductsContext : DbContext
{
    public ProductsContext() : base("WebFormsLab-Products")
    {
    }

    public DbSet<Category> Categories { get; set; }
        
    public DbSet<Product> Products { get; set; }
        
    public DbSet<Customer> Customers { get; set; }
}

Inherits from DbContext? Aha, that’s interesting. Now, let me see if we can get SQL Server Compact into play. Well, we can. There’s a nice Nuget package available:

PM> Install-Package EntityFramework.SqlServerCompact

This will update the web.config as well as besides downloading the package. Now running the same page again it shows the information nicely instead of giving that nasty error. Also it generated a new database file in the App_Data subfolder:

sdfcreated

Super!

Conclusion: SQL Server Compact to the rescue together with Entity Framework Code First. It’s easy to integrate via Nuget and it simply works (disclaimer: on this machine).

Grz, Kris.

Monday, 07 May 2012 20:12:44 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [1]  | 
# Sunday, 12 April 2009

Taken from the download site:

SQL Server 2008 Service Pack 1 (SP1) is now available. You can use these packages to upgrade any SQL Server 2008 edition.
Note:We remain committed to our plans to keep service packs contained, focusing on essential updates only, primarily a Roll-up of Cumulative Update 1 to 3, Quick Fix Engineering (QFE) updates, as well as fixes to issues reported through the SQL Server community. While keeping product changes contained, we have made significant investments to ease deployment and management of Service Packs:

  • Slipstream – You are now able to integrate the base installation with service packs (or Hotfixes) and install in a single step.
  • Service Pack Uninstall – You are now able to uninstall only the Service Pack (without removing the whole instance)
  • Report Builder 2.0 Click Once capability

Download it here.

Grz, Kris.

Sunday, 12 April 2009 19:05:17 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  | 
# Thursday, 07 August 2008

Yes, you read it correctly, SQL Server 2008 hit the RTM status. You can download a trial here or the real stuff at the MSDN subscription pages.

Hopefully SP1 for .NET 3.5 and Visual Studio 2008 will arrive soon also?

Grz, Kris.

Thursday, 07 August 2008 07:52:11 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  | 
# Sunday, 02 December 2007

Setting up a new environment sometimes gets you into troubles. On my new laptop I didn't want to install SQL Server Express but only the developer edition of SQL Server 2005. Since I know there's a tool that ships with the .NET framework called aspnet_regsql, with which you can install the needed database for membership etc, I wanted to create such a database. Running the tool however I got this error message:

System.Web.HttpException: Unable to connect to SQL Server database. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

So ok, I set on remote connections for TCP/IP only but still got the same error. Strange, but true. So I searched for the error and found more information about it. Apparently the instance name of my database didn't get filled in automatically when using the wizard. Quickly inserting the correct instance name and all worked out like a charm.

ASP.NET SQL Server Setup Wizard

Grz, Kris.

Sunday, 02 December 2007 14:39:06 (GMT Standard Time, UTC+00:00)  #    Disclaimer  |  Comments [0]  | 
# Sunday, 27 May 2007

When you start creating a new ASP.NET 2.0 site with Visual Studio 2005 or Visual Web Developer Express (VWD) and want to start using it you'll notice that a new file in the App_Data folder gets created besides your own database, namely the aspnetdb.mdf file. This extra database holds all the tables and stored procedures to let Membership, Roles, Profile etc run smoothly.

However a problem arises when you don't want to use that dedicated new database when you want to deploy to your live webserver, certainly not when you use a host that only offers one database and charges you extra for another database. Luckely you can control things more when using the dedicated aspnet_regsql tool that ships with the .NET 2.0 framework.

What I'm about to describe in this article is how to use that tool to generate a SQL script that you can use to run on your other database with a tool like SQL Server Management Studio (SSMS). In this example I'll be using the installed Northwind database on my localhost developer machine.

Just start up a new DOS box by going to Start | Run and type in cmd followed by enter. In Windows Vista you push the blue windows logo button and in the field with the text Start Search you type in cmd followed by ctrl + shift + enter. The reason for that combination is that you must run it under Admin privileges or else the to be generated file doesn't get writed to disk.
A new DOS box will appear and you just navigate to the following directory/folder:

Windows\Microsoft.NET\Framework\v2.0.50727\

If you're not used to using DOS you can navigate to it by typing this in the DOS box: cd \windows\Microsoft.net\framework\v2.0.50727 followed by enter.

Then you type in this line: aspnet_regsql.exe -E -S localhost -d Northwind -A all -sqlexportonly c:\membership.sql again followed by enter. At the location c:\ a new file gets generated: membership.sql.

The Northwind name in the parameter list is later on used to set the db name in the generated sql file: SET @dbname = N'Northwind'

Once generated you can use/tweak this file to be used in SSMS to get executed and to install everything needed in the database.

Ok, up untill now we focussed on getting everything ready on the database side but we also have to let our ASP.NET 2.0 application know that we're pointing out to another database than the default one. The solution for this is to override the default settings for the LocalSqlServer connectionstring which can be found in the machine.config file.

<add name="LocalSqlServer"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

providerName="System.Data.SqlClient" />

To override that you open the web.config file in your application which can be normally found in the root of the application. Go to the <connectionStrings> element.

<connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="The connection string to your
                         (new) database"
providerName="System.Data.SqlClient" />
</connectionStrings>

Notice the second line where you call the remove statement. This is needed in order to be able to override the LocalSqlServer connection string!

If you're in need of a little help to get your connection string right there's a dedicated site: http://www.connectionstrings.com/.

If you're interested in creating one dedicated database for multiple applications you can also check out Scott Guthrie's post: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005.

Grz, Kris.

Sunday, 27 May 2007 09:46:38 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  | 
# Wednesday, 02 May 2007

I just recently found out about a custom Split function for SQL Server 2000/2005 while I was reading this article: Designing Reports with SQL Server Reporting Services 2005. It mentioned a custom Split function made in T-SQL that could take in a delimited string with id's.

Today I had the need of such a functionality in my current project and luckely remembered where I saw it in the first place. Since I like it I thought I would create a small example for it. What it does is retrieve, from the Northwind database, some data of the Employees table and binds it to a GridView control. If you check several checkboxes and press the button you get, for those selected emloyees, to see everything that's in the database for them.

Grab the Split function from the article or get it here:

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'Split' 
)
   DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split
(
    @ItemList NVARCHAR(4000), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  
GO

All credits go the respective author of the formentioned article for making the function.

I created the following stored procedure that makes use of the Split function:

CREATE PROCEDURE USP_RetrieveInformationForSelectedEmployees
    @p_selectedEmployees    NVARCHAR(50)
AS
BEGIN
    SELECT * 
    FROM Employees
    WHERE EmployeeID in (SELECT Item FROM split(@p_selectedEmployees, ','))
END

And this is the ASP.NET webform:

 

   1:  <%@ Page Language="C#" %>
   2:  <%@ Import Namespace="System.Data.SqlClient" %>
   3:  <%@ Import Namespace="System.Web.Configuration" %>
   4:  <%@ Import Namespace="System.Data" %>
   5:   
   6:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
   7:      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   8:   
   9:  <script runat="server">
  10:   
  11:      protected void Button1_Click(object sender, EventArgs e)
  12:      {
  13:          RetrieveInformationForSelectedEmployees();
  14:      }
  15:   
  16:      private void RetrieveInformationForSelectedEmployees()
  17:      {
  18:          StringBuilder sb = new StringBuilder();
  19:   
  20:          // First loop through the GridView and see which
  21:          // employees were selected. I use the StringBuilder
  22:          // since the list could be a very long list.
  23:          foreach (GridViewRow row in GridView1.Rows)
  24:          {
  25:              if (((CheckBox)row.FindControl("CheckBox1")).Checked)
  26:              {
  27:                  sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
  28:              }
  29:          }
  30:   
  31:          using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
  32:              ["NorthwindConnectionString"].ConnectionString))
  33:          {
  34:              using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
  35:              {
  36:                  cmd.CommandType = CommandType.StoredProcedure;
  37:                  cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
  38:   
  39:                  DataSet ds = new DataSet();
  40:                  SqlDataAdapter da = new SqlDataAdapter(cmd);
  41:                  da.Fill(ds);
  42:   
  43:                  GridViewResult.DataSource = ds;
  44:                  GridViewResult.DataBind();
  45:              }
  46:          }
  47:      }
  48:      
  49:  </script>
  50:   
  51:  <html xmlns="http://www.w3.org/1999/xhtml" >
  52:  <head runat="server">
  53:      <title>Untitled Page</title>
  54:  </head>
  55:  <body>
  56:      <form id="form1" runat="server">
  57:      <div>
  58:          <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
  59:              DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
  60:              <Columns>
  61:                  <asp:TemplateField>
  62:                      <ItemTemplate>
  63:                          <asp:CheckBox ID="CheckBox1" runat="server" />
  64:                      </ItemTemplate>
  65:                  </asp:TemplateField>
  66:                  <asp:BoundField DataField="LastName" HeaderText="LastName" 
  67:                      SortExpression="LastName" />
  68:                  <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
  69:                      SortExpression="FirstName" />
  70:                  <asp:BoundField DataField="Title" HeaderText="Title" 
  71:                      SortExpression="Title" />
  72:              </Columns>
  73:          </asp:GridView>
  74:          <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  75:              ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
  76:              SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title] 
  77:              FROM [Employees] ORDER BY [LastName], [FirstName]">
  78:          </asp:SqlDataSource>
  79:          
  80:          <asp:Button ID="Button1" runat="server" Text="Retrieve data" OnClick="Button1_Click" />
  81:          <p>
  82:              <asp:GridView runat="server" ID="GridViewResult" />
  83:          </p>
  84:      </div>
  85:      </form>
  86:  </body>
  87:  </html>

In lines 23 upto 29 I loop over the rows in the GridView and see if the checkbox in the first column was checked. If it was, I append the DataKey value of it to the StringBuilder instance. On line 37 I pass the string of all the selected employee IDs. The stored procedure uses the Split function in the IN clause.

Have fun!

Grz, Kris.

kick it on DotNetKicks.com

Wednesday, 02 May 2007 19:06:40 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [4]  | 
# Saturday, 03 March 2007

Microsoft provides since recently a new learning center for the beginning developer. Totally dedicated to the freely available Expression editions of Visual Studio 2005 people who are interested in beginning to learn to program can take advantage of.

You can find the Learning Center here.

Another interesting piece of information are the SQL Server 2005 Express For Beginners screencasts.

Grz, Kris.

kick it on DotNetKicks.com

 

Saturday, 03 March 2007 09:50:15 (GMT Standard Time, UTC+00:00)  #    Disclaimer  |  Comments [0]  | 
# Sunday, 22 October 2006

I was unaware of this at first, which gave me some frowns on my face when I found out the hard way, but results in SQL Server Management studio are truncated to a certain amount of characters. Normally you won't notice this but when a certain line exceeds that amount you'll notice the truncated text. I experienced it while concatenating several columns casted as varchars.

By default the maximum amount of characters is 256 but you can easily edit this setting by going to Tools | Options. There you set a higher number of maximum displayed characters in the right lower corner of the screen when you navigate to Query results |SQL Server | Results to Text (or to Grid):

 

Grz, Kris.Recenet

kick it on DotNetKicks.com

Sunday, 22 October 2006 14:22:30 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  | 
# Saturday, 03 June 2006

Like most ASP.NET developers I also first installed SQL Express 2005 together with visual studio.net 2005. After a while however I decided to get it off my dev laptop and install the full blown SQL Server 2005 instead because I wanted to test against this database and because I also needed it for an internal course at work. Things went fine, I also played around with the aspnet_regsql tool to make a dedicated database for my ASP.NET 2.0 services like Membership. I even changed the default setting for the LocalSqlServer connection string in my machine.config file in order to not be forced to override that setting in each new webproject I created.

After a while I felt the urge to reïnstall SQL Express 2005 because I downloaded several example sites and starter kits. Although it's quite easy to attach a .mdf file to SQL Server 2005, you can read my blogpost about it here: Attaching a .mdf file when you don't have the .ldf file available.

The installation went smoothly, I could see the extra database in SQL Server Management Studio but when I wanted to add a new local database, ie SQL Express database, in the App_Data subfolder of a web project I got this error:

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

Huh?

Well I search around, gained some knowledge about the problem and thought I share it with you:

First you'll need to open up SQL Server Configuration Manager. Navigate to that in the menu like Microsoft SQL Server 2005 > Configuration tools > SQL Server Configuration Manager. Take a look at figure 1:


Figure 1: The SQL Server Configuration Manager tool.

Double click, or right click and choose Properties, of the selected line and you'll get the properties window which you can see in Figure 2:


Figure 2: The properties window

You'll need to make sure that the Local system is selected.

The second part of the solution's to delete the following folder on your hard drive: c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS(1). This folder's used to store information and apparently it messes up the proper working of SQL Express.

Grz, Kris.

(1): http://forums.microsoft.com/msdn/showpost.aspx?postid=98346&siteid=1

Saturday, 03 June 2006 19:23:25 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [2]  | 

Microsoft launched the download site for SQL Express with advanced services.

Overview:

Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express) is a new, free, easy-to-use version of SQL Server Express that includes a new graphical management tool and powerful features for reporting and advanced text-based searches. SQL Server Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for embedded application clients, light Web applications, and local data stores.

SQL Server Express with Advanced Services has all of the features in SQL Server 2005 Express Edition, plus you can:

  • Easily manage and administer SQL Server Express with a new easy-to-use graphical management tool -- SQL Server 2005 Management Studio Express (SSMSE).
  • Issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.
  • Run SQL Server Reporting Services reports on local relational data.

Free to download, free to deploy, and free to redistribute as an embedded part of an application, SQL Server Express with Advanced Services is the fast and easy way to develop and manage data-driven applications with powerful built-in reporting and full-text search functionality.

Grz, Kris.

Saturday, 03 June 2006 15:47:28 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  | 
# Monday, 29 May 2006

Hi,

ASP.NET 2.0 is promoted quite heavily by Microsoft and other companies or dedicated sites. What most people seem to love are the starter kits which have several dedicated members that like to extend these starter kits and expose these on the internet. If you would be interested in extending some of the starter kits, or just have one deployed as your own personal website, you can download them here.

Most of these starter kits ship with a sample database which normally consists of a .mdf and a .ldf file. Not all people have SQL Server 2005 Express edition installed but rather only SQL Server 2005. It's quite easy to attach such an Express database to SQL Server 2005 when you have both files available but as I found out in the past sometimes only the .mdf file is provided which prohibits one to attach the database the normal way.

Luckely there's a special procedure available: sp_attach_single_file_db.

An example on how to use it:
EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

The @dbname = and the @physname can be omitted if you like.

Grz, Kris.

Monday, 29 May 2006 20:25:24 (GMT Daylight Time, UTC+01:00)  #    Disclaimer  |  Comments [0]  |