none
unable to connect to sql server 2008 r2 through vs2010 express

    Question

  • You cannot vote on your own post
    0

    I dunno if this is the right forum to ask this question.

    I am running on windows 7 and I have installed sql 2008 R2 with sql management studio. Then I installed vs2010 express.  I am able to connect to the db through windows and sql authentication through sql server management studio (SSMS) and create a db. But in C# , I am unable to connect to the db through the database explorer.

    when I goto Database explorer, right click on data connection, I can see only add connection which is not grayed out. From here no matter what I do, I cannot connect to the server. Infact I get  I get a dialog box which contains a Datasource listbox, Data provider combo box. The datasource listbox has 3 options

    1. Acess database file,
    2. sql server compact 3.5 ,
    3. sql server database file

    while the combobox changes according to the content of the listbox


    I selected sql server database file in the list box and then in the combo box .net framework data provider for sql server get's selected. I click on continue and I get a dialog window which has

    1. edit box with a browse button called as "Database filename (new or existing)"
    2. A group box "Log on to Server" with two radio buttons "Window Authentication" , "Sql authentication"
    3. Advanced button
    4. Test connection button

    I then typed the  name of my db into the database file edit box "emp" . As windows authentication was selected, I clicked on test connection and I got

    "This connection cannot be tested because the specified database file does not exist."

    I checked it I have selected the correct instance in the Advanced button dialog.
    So I browsed to the mdf file which contains the data under the programs file/mssql server/.../data folder and selected the mdf file. I got a message that I don't have the permsssion to open this file. contact the file ownder or an administrator tobtain permission.

    Then I right clicked on C# and selected run as administrator.  Then I was able to select the file from the data directory folder and open the connection and the database was visible in the Database Explorer. As I was trying to work on Linq on SQl, I added a linq to sql class and I clicked on the dbml file. Onto this I dragged and dropped the table from the database explorer. I got a message,

    The connection you selected uses a local data file that is not in the current project. Would you like to copy the file to your project and modify the connection?If you copy the data file to your project, it will be copied to the project's output directory each time you run the application. Press F1 for information on controlling this behavior.

    I clicked on Yes.

    Now I got

    Connections to SQL Server database files (.mdf) require SQL Server 2005 Express or SQL Server 2008 Express to be installed and running on the local computer.  The current version of SQL Server Express can be downloaded at the following URL: http://go.microsoft.com/fwlink/?LinkId=125883

    I went to link mentioned above and I saw that I have the same thing installed.

    Please tell me how to connect to the db from database explorer without doing all this circus and what's with the error message above ?

    Tuesday, January 04, 2011 4:11 AM

All replies

  • Tuesday, January 04, 2011 6:24 AM
  • Hi Chaitanya,

     

    Thanks for the reply. I had gone through those links earlier too. But I did not find any solution to my problem...

    Infact  it is fine in Visual Web Developer 2010 express. In web developer, I was able to go to database explorer and connect to the db through add connection" option.

    I had installed a user instance of sql server 2008 r2 after downloading the exe SQLEXPRWT_x86_ENU.exe. Then only thing of particular interest which I found in the links above was gregor' comment on how he went to  "Visual Studio / Tools/Options/Database Tools/Data Connections/ in SQL Server Instance ..." here i added my server instance name. But it was of no use. The strangest thing is that ,I am having this problem in  Visual C# 2010 express .

     The difference I noticed in webdeveloper and C#2010 express is that in that database explorer of web developer,in the add connection dialog, the DataSource list box had options such as Microsoft sql Sever, Microsoft Sql Server database file, Microsoft ODBC data source, Microsoft Acces DAtabase file,etc   .Where as in C#2010, the Add Connection of Databse Explorer did not have Microsoft Sql Server as an optin in list box.

    Pls suggest ....

     

    Tuesday, January 04, 2011 10:02 AM
  • By the way, I should also point out that I had the same problem in another system which had vs2008 and sqlserver 2008 r2. I did not check the web developer part in vs2008 but in C# developer it was the same problem. Then after i installed vs2008 service pack 1, the problem with C# connection to db through explorer was solved, alhtough I haven't checked with webdeveloper.
    Tuesday, January 04, 2011 10:12 AM
  • when I try to create a new item of service based database type in visual c# 2010 express, I get

     

    Connections to SQL Server database files (.mdf) require SQL Server 2005 Express or SQL Server 2008 Express to be installed and running on the local computer.  The current version of SQL Server Express can be downloaded at the following URL: http://go.microsoft.com/fwlink/?LinkId=125883

    Tuesday, January 04, 2011 11:10 AM
  • I could use some help here on how to connect to sql server 2008 r2 using database explorer in Visual C# 2010 express as I am having the problems mentioned above only for C#2010 express and not in web developer 2010 express. I have tried the suggestions above. I have a user instance of sql server 2008 r2 , with named pipes, tcpip,... enabled except for via. I have enabled fullstream,..

    I have now uninstalled vs2010 and sql 2008 r2 with management studio. one of the links above said to install vs2010 express first with the sql express that is bundled in it and then install sql 2008 r2. Is that the right order? Also should I delete any registry entries after all that uninstall and restarts?

    Wednesday, January 05, 2011 5:32 AM
  • Is there any service pack for sql 2008 r2 or vs2010 express?
    Wednesday, January 05, 2011 5:39 AM
  • Did you install the Microsoft SQL Server2008 R2 RTM - Express with Advanced Services (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e08766ce-fc9d-448f-9e98-fe84ad61f135&displaylang=en).

    This edition will have SQL Server management studio to check the connectivity to SQL Server databases.

    I am not sure of any suggested order to install VS2010 and SQL 2008R2 Express editions. But you can certainly try that.

    For SQL Server 2008 R2 we have CU5 released. Check this: http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/12/20/cumulative-update-5-for-sql-server-2008-r2-rtm.aspx, this is the latest one..

    Refer: http://msdn.microsoft.com/en-us/library/bb655891(VS.90).aspx, to identify different ways to connect to Databases and Database files and display data. Hope you are following the similar pattern.

    Hope this helps..


    Chaitanya
    Wednesday, January 05, 2011 5:49 AM
  • I went to http://www.microsoft.com/express/Database/InstallOptions.aspx and I clicked on database with management tools options. I did not want those extra advanced services.... present in the link which you posted..(500mb extra).

    I think the CU5 is applicable for R2 and not for R2 express if I am not mistaken.

    Thanks for the link on ".... different ways to connect to Databases and Database files and display data"

    I will try to install vs2010 and then sql 2008 r2 and then post here....

    Wednesday, January 05, 2011 6:35 AM
  • I went through the link that you gave about  identify different ways to connect to Databases and Database files .As I was trying out linq to sql, I had followed all the steps in http://msdn.microsoft.com/en-us/library/bb531271(v=VS.90).aspx. but when I

    Goto database Explorer,Add Connection dialog box ,made sure that the data source is Microsoft SQL Server Database File (SqlClient),
    Clicked Browse and then located and select the emp.mdf database and then Clicked Open, I got the error

    that I don't have the permsssion to open this file. contact the file ownder or an administrator tobtain permission.

    I am using Windows 7 and I have not logged in under admin account /username.

    Then I right clicked on C# and selected run as administrator...... what happened next is explained above in detail in my first post

    Wednesday, January 05, 2011 6:51 AM
  • Did you try with DataBase instead of Database file? The MDF file you are trying to access is it deploy on some SQL instance? If yes, then try detaching and try again.


    Chaitanya (http://twitter.com/chmediko)
    Wednesday, January 05, 2011 8:45 AM
  • Hi chaitanya,

    Sorry I had unistalled it completly and I did not see your post in the meantime. I didn't understand your questions/suggestions which you have made recently. I had connected to sql instance which is present on my system through the management studio and created a db and I was trying to connect to this through C# 2010 express. My installation of C#2010 express had the option of only connecting through database file and not to database wheras the web developer had the option of connecting to both the database file and the database.

    Ok. Now I installed first ONLY vs2010 web developer express with the sql 2008 service pack that was bundled along with it. I was able to connect, with the bundled sql server .

    Again I must remind that in database explorer of web developer,in the add connection dialog, the DataSource list box had options such as Microsoft sql Sever, Microsoft Sql Server database file, Microsoft ODBC data source, Microsoft Acces DAtabase file,etc .

    Now I installed VS2010 C# express. But I was not able to connect to the bundled sql server which I have mentioned above. Here in Add Connection of Databse Explorer did not have Microsoft Sql Server as an optin in list box. Ihad access database file, sql server compact 3.5 and sql server database file.

    what should I do? I am sure that now even if I install the sql server 2008 r2 with the management studio, I won't be able to connect through C#2010 express.

    Wednesday, January 05, 2011 10:20 AM
  • Did you try with DataBase instead of Database file? The MDF file you are trying to access is it deploy on some SQL instance? If yes, then try detaching and try again.


    Chaitanya (http://twitter.com/chmediko)

    Ok. I give up. I installed SQLEXPRWT_x86_ENU.exe. This time, I made an assumption.

    I assumed that since VS2010 express installed SQL 2008 service pack, when I install SQLEXPRWT_x86_ENU.exe, I only need to upgrade the installed express edition. In the installation of SQLEXPRWT_x86_ENU.exe, there was an option which said "upgrade from SQL2008". I clicked that and upgraded my express edtion to R2. As management studio was not installed, i again ran the setup and I clicked on "Add new features to existing ......" and selected management studio. Even after doing all this, I am still not able to connect to the db through C#2010 express.

    I give up. This is bulls***.

    Thursday, January 06, 2011 8:55 AM
  • I have the same issue (VS2010 express, sql server 2008r2). 

    I am going to guess the problem is in some of "hidden configuration" that happens in the registry, or VS 2010 [Express] doesn't auto discover newer revisions (I wouldn't expect it to discover newer major versions, but minor revisions should go, but probably aren't)..

    The three registry keys point to what is presumably an unpatched release assembly of 10.0.0.0

    • .NET Framework Data Provider for SQL Server:

    HKEY_CURRENT_USER\Software\Microsoft\VWDExpress\10.0_Config\DataProviders\{91510608-8809-4020-8897-FBA057E22D54}\Assembly

    Value: Microsoft.VisualStudio.Data.Providers.SqlServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

    • Microsoft SQL Server:

    HKEY_CURRENT_USER\Software\Microsoft\VWDExpress\10.0_Config\DataSources\{067EA0D9-BA62-43f7-9106-34930C60C528}\SupportingProviders\{91510608-8809-4020-8897-FBA057E22D54}\UsingDescription

    Value: DataProvider_Sql_DataSource_Description, SR, Microsoft.Data.ConnectionUI.Dialog, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

    • Microsoft SQL Server Database File:

    HKEY_CURRENT_USER\Software\Microsoft\VWDExpress\10.0_Config\DataSources\{485C80D5-BC85-46db-9E6D-4238A0AD7B6B}\SupportingProviders\{91510608-8809-4020-8897-FBA057E22D54}\UsingDescription

    Value: DataProvider_Sql_FileDataSource_Description, SR, Microsoft.Data.ConnectionUI.Dialog, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

    Just browsing around the VS Express SQL server installations, I wasn't able to locate that assembly, so it's likely hidden elsewhere.  I did find one that is possibly related:

    C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.VisualStudio.Data.Providers.SqlEverywhere.dll

    It has a 3/18/2010 create date, and a version of 10.0.30319.1

    I would presume these are the same for the other Express SKUs

    • Edited by JustinC Thursday, January 13, 2011 8:13 AM Emphasis on assemblies of concern
    Thursday, January 13, 2011 8:09 AM
  • I have the exact same problem.  At least now I know I am not insane; there is some bug where we cannot connect to SQL DB via Visual Studio 2010.

    I am learning all this stuff so this bug has been pretty confusing.  I am wondering what is the best workaround for me to do in the mean time.

    Basically what I am wanting to do is make an application where I can simulate 3 users that can all update/view/delete records in a database... i guess I can create a db file.. or xml file.. sigh.

    Monday, March 14, 2011 8:14 AM
  • Hello folks,

    Thought I should chime in, I'm sure most of you have probably resolved your issues on this matter. However, I had the exact same problem but with VS 2010 Professional. I had installed Sql Server 2008 R2. When I clicked on the server explorer and tried to add a database connection through visual studio 2010, it would never find my database that I had just created in sql server 2008 R2. Then I realized that visual studio 2010 was not picking up the database server name(SQLEXPRESS), so I basically added it. Here are the steps:

     

    right clicked on data connection in VS 2010 and added a new connection.

     

    The screen popped up.

    The Datasource I picked was Microsoft sql server(SQLclient)

    The server name(here is where I made the change), it defaulted to my computer name

    so I added sqlexpress to it.

    for example:

    pc-name\SQLEXPRESS

     

    by adding SQLEXPRESS to it, I was able to use Windows Authentication

    and select the database that I had created under microsoft sql server 2008 R2.

     

    I clicked on Test connection and it worked fine.

     

    so basically, the only change I made was added the "\SQLEXPRESS" to the default value in the server name box.

     

    Hope this help.

     

    thanks,

     

    Ranjan 

    • Proposed as answer by ASPNETnewbie Thursday, December 01, 2011 11:11 AM
    Wednesday, June 29, 2011 3:52 AM
  • Folks,

    I'm having exactly the same problem. I posted a question and was referred to this thread as a solution. It looks like the problem was never resolved really as I can see no solution posted.

    seafarer007 has posted some information, however this appears to be relevant to VS 2010 Professional only, as he refers to picking Datasource 'Microsoft SQL Server (SQL client)' from the popup panel. My problem is that this option does not appear on the panel.

    I'll try again on my own question's thread.

    Mick

     

     

    Wednesday, August 31, 2011 11:30 PM
  • Hello folks,

    Thought I should chime in, I'm sure most of you have probably resolved your issues on this matter. However, I had the exact same problem but with VS 2010 Professional. I had installed Sql Server 2008 R2. When I clicked on the server explorer and tried to add a database connection through visual studio 2010, it would never find my database that I had just created in sql server 2008 R2. Then I realized that visual studio 2010 was not picking up the database server name(SQLEXPRESS), so I basically added it. Here are the steps:

     

     

    right clicked on data connection in VS 2010 and added a new connection.

     

    The screen popped up.

    The Datasource I picked was Microsoft sql server(SQLclient)

    The server name(here is where I made the change), it defaulted to my computer name

    so I added sqlexpress to it.

    for example:

    pc-name\SQLEXPRESS

     

    by adding SQLEXPRESS to it, I was able to use Windows Authentication

    and select the database that I had created under microsoft sql server 2008 R2.

     

    I clicked on Test connection and it worked fine.

     

    so basically, the only change I made was added the "\SQLEXPRESS" to the default value in the server name box.

     

    Hope this help.

     

    thanks,

     

    Ranjan 

     

    This information also applies to the Express edition. The method needs correction. For VC# 2010 Express

    Go to View>Other Windows > Database Explorer to open the pane.

    In the Database Explorer pane, right click on Data Connections, select Add connections. The Add Connection dialog box appears. Note that the top field may say " Microsoft SQL Server Database File (SqlClient)". This is in fact the correct name.

    Click the "Advanced" button, a 2nd dialog box named "Advanced Properties" appears. Scroll to the Data Source property. For SQL Server 2008 R2 Express, the data Source is/should be set to ".SQLEXPRESS". The "." denotes the local computer, and "SQLEXPRESS" is the name of the (usually sole) instance of SQL Server Express.

     

    Note that all my entries are correct, that database IS in fact opened (it was locked when I tried to open it via code), but is not listed under the "Data Connections" label; the "+" sign that was there is now a minus sign.

    Note that I deleted a whole pile of stuff (SQL CE V3.5, and V4.0), plus alot of "tools and so on. I discovered I had broken VS2010. I un-installed VS 2010 SP1 completely and then repaired/re-installed VC# 2010. I then re-installed VS 2010 SP1. VS now appears to be fixed except for the Database Explorer. My point: all this uninstall re-install/repair didn't help, so don't un-install anything to "clean-up" once things work. Everything is so integrated that removing one item can affect others.

    Sunday, December 11, 2011 3:16 PM
  • I realize this is very old, but I am also having this problem. The last two suggestions are irrelevant to the stated problem. I'll restate the problem to clarify. I have Visual Studio C# 2010 Express installed. I want to connect to a SQL Server instance that is not local. I have no problem connecting to the server in SSMS (2008 R2) or BIDS 2008 or BIDS 2005. The problem is when trying to create a new connection in Visual Studio C# 2010 Express, Microsoft SQL Server is not an option. I have only three data source options:

    Microsoft Access Database File

    Microsoft SQL Server Compact 3.5

    Microsoft SQL Server Database File.

    From reading the documentation online, all of it assumes Microsoft SQL Server is an option (as well as Oracle). I can't find any documentation on how to add new providers. I guess at this point I will just try Visual Studio 2012 instead.

    Friday, November 02, 2012 8:50 PM