VS 2010 Express can't add SQLEXPRESS Data Source

Question

Hi all,

I've spent a couple of days on this now and have tried everything I can find on the web to fix this issue. I can add Access data sources to VS 2010 Express, but can't find how to add SQL Server 2008 Express data sources. I keep getting the "40" and "26" errors.

 

I am running both VS2010 and SQLServer on a local machine, no network connection involved. I have granted corrected permissions in SQLServer.

I have opened the correct ports, set the correct protocols, ensured my SQLServer is open for remote connections and changed my windows firewall settings.

My question relates to the "Advanced" window in the data source wizard - it refers to .\SQLEXPRESS. I have noticed that this service is not running in my SQL Server Configuration manager and cannot even be started. I also do not have an Express SQL Server Browser at all! (I had one yesterday before I reinstalled SQL Server for the 3rd time!).

Could this be the source of the problem? (the .\SQLEXPRESS) ?

Thanks in advance for any help,

Henry

Answer 1

Hi Henry,

Which data source do you choose in the Add Connection window? From your description, I think you chose Microsoft SQL Server Database File, right? Please change it to "Microsoft SQL Server".

If the database files are attached using the SQL Server Management Studio, we need to use "Microsoft SQL Server" then specify the server name and database name. Please first make sure the connection information is valid.

If we want to connect to a SQL Server database without attaching it first, we need to use "Microsoft SQL Server Database File" and locate the database files. In this case, the database will be attached and connected using user instance.

User instance is a feature of SQL Server Express. It is not supported by other editions of SQL Server (such as Developer or Enterprise). SQL Server Express is usually installed as a named instance called "SQLEXPRESS", so ".\SQLEXPRESS" is specified automatically.

References:

Add/Modify Connection (Microsoft SQL Server)
http://msdn.microsoft.com/en-us/library/dxb6fxah.aspx

Add/Modify Connection (Microsoft SQL Server Database File)
http://msdn.microsoft.com/en-us/library/tk2tbfb5.aspx

Working with User Instances
http://msdn2.microsoft.com/en-us/library/ms254504.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hello Jian,

and thanks for your reply. You mention above that I should select "Microsoft SQL Server" then specify the server name and database name. However, in my "add data source" wizard I do not have the "Microsoft SQL Server" option.

I tried to, but cannot paste a screenshot to illustrate this. The only options I have are:

"Microsoft Access Database File" (this option works) and

"Microsoft SQL Server Database File" (this is the option I cannot make work).

What do I need to do to make the "Microsoft SQL Server" option available to me?

I have installed VS2010 Express and SQL Server 2008 Express on my local machine, so I am not accessing over a network connection.

 

My complete set of actions for replication of the issue is:

1. I have an SQL Server database called crashcourse.mdf which I can access and manipulate in Microsoft SQL Server 2008 R2

2. I open a new project in VS2010 Express (Visual Basic)

3. I click on "add new data source..."

4. I choose database

5. I choose dataset

6. I choose new connection, where I get the 2 options I describe above.

Thanks,

Henry

 

Answer 3

I did not notice that you use Visual Studio 2010 Express. For the Express Edition of Visual Studio, we can only add local database file as data source.

I have installed VS2010 Express and SQL Server 2008 Express on my local machine, so I am not accessing over a network connection. 

My complete set of actions for replication of the issue is:

1. I have an SQL Server database called crashcourse.mdf which I can access and manipulate in Microsoft SQL Server 2008 R2

So now you have a SQL Server 2008 R2 instance (non-express) and Visual Studio 2010 Express on your machine, right?

In this case, we have to install a SQL Server Express instance, detach the database using SQL Server Management Studio and then connect to the database files using "Microsoft SQL Server Database File".

Here is a similar thread for Visual Studio 2008:
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/c48490c8-0b89-4593-8c89-05f893bc5374

We can download the latest SQL Server 2008 R2 Express here: http://www.microsoft.com/express/Database/

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Hello Jian,

and thanks for your further advice. I actually have SQL 2008 Express version and VS2010Express.

I was eventually able to solve my problem by reading another thread which said I had to set the correct SQL instance name in the Tools/Options/Database settings part of the VS2010 menu. This is actually the problem I described at the start of this thread, I just didn't know that I could set that option from VS2010. I had to change the ".\SQLEXPRESS" to default setting (Blank) in the options screen.

It has taken me something like 3-5 days to get this working, but hopefully these are just teething problems caused by my lack of experience.

Answer 5

@ToogleTown

Thanks for posting your solution to this problem.  I too ran into the same issue.  I had to check "Show all settings" in the lower left corner of the Options box in order to see the Data Connections property.  I cleared out the "SQL Server Instance Name (blank for default)" box and am now good to go.

Answer 6

Hello,

Another way is to forbid user instances for your SQl Server Express ( to do when you are installing your instance ).

The common way is to connect is to select the .mdf of your database and put in the textbox for the file name and in the advanced properties tabpage, you set user instance = no ( or false ).

A little remark : the user instance feature should disappear not with Denali ( but maybe ) but officially with the next after Denali. I think that VS Team will have to work to modify VB/VCSharp Express Visual Studio quickly

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.