Brendan Enrick

Daily Software Development

Change Local SQL Server Used By Visual Studio

A while back I ran into a problem when setting up SQL Server on a computer and using it along with a database project in Visual Studio. It isn’t really a big deal, but I needed to change which locally installed instance of SQL Server I was using by default with Visual Studio. Since I blogged about this previously it is a very nice reference for where to find that setting should the need to change it arise again.

Today I just sat down at a computer which had Visual Studio pointing to a SQL Express instance. Yesterday I installed SQL Server on the computer, so I went to change to the new SQL Server instance. What was my first step? I Googled for my blog post I’d previously written, because I knew it had nice screen shots to show me exactly what I was looking for.

I simply searched for, “change database Brendan Enrick”. The top hit on Google was my previous post explaining how to change the SQL Server Instance for a Visual Studio Database Project. As I mention in that post, I expected the setting to be DB Project specific not VS specific. Now I know better, and I just use the blog post for a quick solution.

Step one is to go to the Tools menu in Visual Studio and choose Options…

Change SQL Server Instance Menu Selection

Now that the Options windows is open in the left side navigation tree select Database Tools and then Design-time Validation Database. Once that screen is visible there is a text box to change the SQL Server Instance Name. Set that to the name of your desired SQL Server Instance. In my case the default one, so I erased the existing value.

Change SQL ServerInstance Options Change




This is one of the big reasons why I am a strong believer in blogging. It is a quick easy way to get this information out for others to use. Posts like this often get a few comments of thanks from people they helped. If helping others in the development community is not enough of a reason, you also always have a record to help you if you run into the same problem again.

Old Blog Favorites

I am somewhat partial to a few of the posts from my previous blog. For some of these posts, I just like the post, some the content about which the post was written, and some were just popular posts.

Installing SQL Server Management Studio with SQL Server: My most popular post is one written about a painful experience installing SQL Server's client tools. Sadly the popular post is out of date. My first post about the topic I found a solution that worked and managed to install the software. I late found out that there was a better way and I updated the post. The problem is that Google continued to send traffic to the old one. For my blog it was a pretty popular post. it has had over 25,000 views.

The original version - Installing SQL Server Management Studio with SQL Server

The current version - SQL Server Client Tools Installation

Visible Whitespace in Visual Studio: Another post written about a traumatic experience was when I accidentally used a keyboard shortcut and enabled visible white space. It caused a blue dot to show for every space character in visual studio. Wow was it hard to read my code with that on. Well I Googled for it, and at the time I didn't receive any results. There are some now my post is one of them. I also didn't notice the option to turn it off. I used an algorithm I like to call brute force to figure out the keyboard shortcut I typed. It was Ctrl + E + S. Comments let me know of a bunch of other ways to solve the problem for different configurations and versions of Visual Studio.

Visible Whitespace in Visual Studio

Return Within a C# Using Statement: IDisposable objects are nice to work with. I prefer not dealing with external resources, which is generally what you're doing with disposable objects, but having this interface makes it a little bit nicer. I wrote a post telling people it was safe to return from within the using statement, because the using statement will make sure that the object is disposed. In the lifetime of the post only one person actually challenged me on it and asked me to provide code showing that what I said is true. I of course responded to that comment and then posted a response on this blog. My response includes sample code showing that using statements make sure that the object is disposed.

If you like the second post make sure you thank Lambros Kaliakatsos for commenting on the first one.

The original post - Return Within a C# using Statement

The follow up including sample code - Returning From Inside a Using Statement

Performance with DropDownLists and ViewState: One thing that it seems a lot of ASP.NET developers still don't understand very well is ViewState. Page lifecycle stuff seems to really be fueling the MVC craze these days. I wrote a post talking about how ViewState can hurt the performance of your applications if you let it get out of hand. The example I used is the drop down list, but it can certainly get a bit crazy from grids, repeaters, etc. I wrote an article basically saying that you should try to avoid ViewState if you're going to have too much of it. I later followed up with a post about how to use a DropDownList without ViewState. Some people are concerned that you will not be able to use the SelectedValue property, but you can if you wire it up correctly. It is all about understanding that painful Page Lifecycle.

Performance with DropDownLists and ViewState

Using a DropDownList without ViewState

There are plenty of other posts I like in there, but I think I've bored my readers enough for one day. I've got a couple of posts lined up which should be a little bit more interesting than this one, so keep reading. As always, have a great day.

Enabling Mixed Mode Authentication in SQL Server

I recently needed to enable Mixed Mode Authentication on a SQL Server instance, so I will demonstrate how to go about setting this up. It is a pretty easy process, but I figure I'll document it nicely here. Perhaps I will come back to this later to reference this. Who knows?

First you will need to connect to the SQL Server instance using SQL Server Management Studio.


Once you've connected to the SQL Server you will need to get to the properties window for the server. To do this you can right click on the SQL Server in the Object Explorer window and it will bring up a context menu.


Now that you've got the properties window open, select the Security section and choose the SQL Server and Windows Authentication Mode radio button.


After clicking OK, you will receive a message informing you of the need to restart the Server before the changes take affect.


Now just right click on the server in the object explorer again and select restart from the context menu and the services for the server will restart.


Congratulations you're done.

Change SQL Server Instance for Visual Studio Database Project

While installing SQL Server 2005 recently I ran into some difficulty. I use database projects from Visual Studio 2005 Team Edition for Database Professionals. I use Visual Studio 2008 with them now, and I recently made a mistake while using one of these projects. When I ran this project my default instance of SQL Server was not listed, so I figured I would connect it to the SQL Express. BIG MISTAKE. After installing SQL Server and getting it working correctly I removed SQL Server Express, and now the real fun begins. The database project no longer loads.

So I try removing and adding the project in my solution figuring it might prompt me for the database instance again. No luck. So I decide to ask around a bit. One forum got me a nice response. It explained how to make the change as well as linking to this MSDN article.

To make this change you just have to alter some settings in Visual Studio. Start by navigating to the Tools menu and choose Options.


Once you've opened the Options menu, navigate to the Database Tools section and pick Design-time Validation Database. This is the information for the database used by Visual Studio for database projects. Change the SQL Server Instance Name to whatever the name of the local database is.


I made the bad assumption of assuming that the project is what connects to the server instead of Visual Studio. I checked everywhere in settings for the project, but it slipped my mind that Visual Studio would be what would contain that setting.

As one final note I will say Googling sucks when you're looking for information about "Visual Studio 2005 Team Edition for Database Professionals". Ah what a great name guys.....

SQL Server Client Tools Installation

A while back I blogged about how to install sql server management studio, and it got a reasonable amount of feedback. More recently, Steve Smith posted a response to it where he highlights a better way of installing Management Studio. The method I outlined doesn't seem to work in every instance, so in his response Steve supplied a method which works more often.

The Solution: Run the SqlRun_Tools.msi file found in this folder.

{drive}\ENGLISH\SQL2005\DEVELOPER\SQL Server x86\Tools\Setup\SqlRun_Tools.msi

{drive}\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Tools\Setup\SqlRun_Tools.msi

Choose the top one for 32-bit systems and the bottom on for 64.

Lots of people keep UAC disabled on Windows, so they'll miss out on nice annoyances like the fact that this method requires you run the msi file as a local administrator. So as I am apt to do, I tried right clicking on it to choose the "Run As Administrator" context menu item. To my surprise it is not there! Below I've supplied the workaround I use in Vista for running as administrator when the context menu item is not present.

One of many ways around running a program as administrator when the context menu item is not present.

  1. In the start menu type "cmd".
  2. When the dos prompt icon is the only remaining icon right click on it and run as administrator.
  3. Copy the file location of the file you wish to run.
  4. Paste the location into the command prompt window.
  5. Click enter.
  6. Watch the program begin with administrator privileges.
  7. Celebrate and enjoy!

Update (9/9/2008): If the software claims that you have nothing to install when you run the SqlRun_Tools.msi then it means that installation has been borked. You will want to try running the Microsoft Installation Cleanup Utility. It will remove the client tools that SqlServer thinks are there, and it will then let you install them.

Copying Data From SQL Server Management Studio

I often write queries in SQL Server Management Studio to find different bits of information about which I am curious. This is quite easy and I discover a lot of useful and interesting things this way. You might ask why I am writing this blog post. I'll first say that I try to share these little tidbits with others. Sure I could bring people over to my computer and show them these nifty collections of data with certain meanings, but that would just be silly. I need to store it elsewhere and email it.

Maybe I'm just going about things the wrong way, but I've tried to copy and paste from SSMS into Outlook and that doesn't usually work well. Outlook doesn't seem to figure out that I would like my data pasted as a table, so I usually throw it into Excel first. Doing this will get my data into a nice tabular format.

Now that I've finally gotten my tabular data into a table everything is great. Right? No. I lose once again. I don't have the column names with my data so I end up with something like this and without column names people will not know what I am trying to tell them.

Brendan McAwesome Enrick 5555555555 12345 9876543
John Michael Smith 1234678910 1337 31337

So then I have to go and type out column names so that the people I send this to know what it is. Now for this example it wouldn't be too bad, but sometimes I'll be sending 10 columns worth of data and that is just annoying. I wish I could just copy out of SQL Server and have it pull column names at least then my Excel trick would at least work. If anyone knows a nice easy way to just copy out of SSMS please let me know it would make things so much easier.

Perhaps you can find some elusive option names similarly to Copy All Data With Column Names, because I sure as hell haven't found it.

In case you are wondering those column names are; FirstName, MiddleName, LastName, Phone, SomeValue, SomeOtherValue.

Installing SQL Server Management Studio with SQL Server

Update (24 March 2008): Steve Smith found a more reliable solution to install the SQL Server Client Tools. Once in the tools folder open the setup folder and in there is a SqlRun_Tools.msi file. If you run that it should actually install SSMS.

When I recently installed SQL Server 2005 and SQL Server Management Studio on my computer it did not install SSMS or any of the other Client Tools. When running the installation of SQL Server 2005 I followed along with the instructions. I individually selected each component to install including the Client Tools for SQL Server. When the installation finished there was no SQL Server Management Studio.

Figuring this might be a difficult thing to Google for, I asked Steve Smith if he knew how to get the client tools. He told me that just about the only way to get SSMS to install was to sacrifice a chicken and hope for luck, because there is something weird which has to be done in order to get the program to install.

Upon scouring the folders on the disc, I discovered that the default setup file is coming from a servers folder. I tried using the setup file from the tools folder. This should have worked, but I had a slight problem. Since I had told SQL Sever to install the client tools from the wrong setup file it now believed I had them installed already and would not install them.

SQL Server 2005 Install Folders

Since the installation defaults to the Servers setup file, I never even saw the tools install. There was also no reason to even suspect one since the primary setup file claimed to be able to install the client tools.

I had to uninstall and reinstall SQL Server without the Client tools and then the setup file from the client tools would install SQL Server Management Studio. This was quite a pain, and I don't understand why the client tools are listed as options in a setup file which cannot install them. I think this is a bit crazy, but at least now when I install it I don't have this problem anymore.