none
Accessing SQL Server Express in Visual Studio 2010 Express

    Question

  • I started working through the book MIcrosoft ADO.NET 4 Step by Step by Tim Patrick.  I'm using Visual Basic 2010 Express, SQL Server 2008 R2 Express, and SQL Server Management Studio 2008 R2 Express.  According to Patrick, the Express editions of these programs will work with his tutorials.

    Here are the instructions I followed:

    I started SQL Server Management Studio and opened a new Object Explorer connection.  I connected to the Database Engine named (my computer name)\SQLEXPRESS.  Within the Databases branch of the connection tree, I added a new database named StepSample.  I then opened a file provided by the author named DB Script.sql.  I then executed this script and verified that the specified tables and objects were created within the database.

    I then started Visual Basic and created a new project.  I then selected Data | Add New Data Source.  Within the Data Source Configuration Wizard, I selected Database as the Data Source Type.  For the Database Model, I selected Dataset.  For the Data Connection, I selected New Connection.

    This is where I have my problem.

    Within the Choose/Change Data Source dialogue box, under the data source options, Patrick dictates selecting Microsoft SQL Server.  But my only data source options are Microsoft Access Database File, Microsoft SQL Server Compact 3.5, and Microsoft SQL Server Database File.

    I need to know how I can connect to Microsoft SQL Server so that I can connect to StepSample.  I've tried several variations on these steps and alternative methods trying to access StepSample. If connecting to Microsoft SQL Server in Visual Basic is not possible, then I'm OK with some alternative option. I just need to be able to use a database defined by DB Script.sql in Visual Basic.

    Any help would be appreciated.

    Wednesday, March 28, 2012 6:59 PM

Answers

  • Then another possibility would be that this file is already registered in the temporary instance of SQL-Server Express but under another name.  Try repeating the test but with a brand new database file.  You don't need to fill it, just create it with SQL-Server Express, detach it and then try to connect to it from Visual Studio.

    Another solution would be to create the database file from VS and then, use SSMS to connect to the living, (temporary) second instance of SQL-Server Express that is created in memory to serve this user file by using its pipe name.  From there, you can manipulate the database file from SSMS like any other ordinary database file, including running your sql scripts.

    To find the pipe name of your in-memory instance, see http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

    You must be careful when working with the memory instance.  Usually, the name of the database inside the instance will be the same as the full path of the database file.  However, if you're not careful and try to attach a database yourself, you might end up with the database file attached under another name.  In this occasion, VS will be unable to connect to the database because it will use the full path of the database file as the name of the database to try to connect with it but the same file cannot be connected under two different names; therefore the connection will fail.

    Sunday, April 01, 2012 9:20 PM

All replies

  • check this link this might help you

    http://msdn.microsoft.com/en-us/library/ms233763.aspx

    Thursday, March 29, 2012 3:16 PM
  • Hi Oanea1988,

    Regarding to your description, in VB you can only add a data source to an. mdf or access database. The feature which allows you to connect to an existing SQL server database is not available.

    For more information, how to connect to SQL Server in VB please refer to these articles and specially SqlConnection code for VB
    1. Using SQL Server 2005 Express from Visual Basic 6

    2. Creating Connections to SQL Server

    3. How to Connect to SQL Server Through VB.net


    Regards, Amber zhang

    Friday, March 30, 2012 2:55 AM
    Moderator
  • This method does work for me making and using a new database in Visual Basic.  But I was hoping for someway to create a database from a sql file without having to manually create all of the tables and objects.
    Friday, March 30, 2012 6:25 AM
  • Take a look at this:

    http://www.youtube.com/watch?v=wBAB7DX9IcM

    Friday, March 30, 2012 5:34 PM
  • One possible solution would be to detach the database StepSample from SQL-Server Express and then connect to it as a Microsoft SQL Server Database File.  I don't know if you will have to copy it first to your project folder.

    You can also choose to simply edit the connection string directly in your app.config file.  Shouldn't be a problem.  You connection string should looks like:

    connectionString="Data Source=LocalHost/SQLEXPRESS\;Initial Catalog=StepSample;Integrated Security=True"

    Don't forget that when a database is attached to an instance of SQL-Server, you cannot attach it to another instance whithout first closing it and detaching it.

    Also, when connecting using a Microsoft SQL Server Database File, a temporary instance of SQL-Server Express is created in memory and your database will be connecting to it until one hour of inactivity has passed. Therefore, you won't be able to reattach it to your primary instance of SQL-Server Express until that laps of time.

    You can also use SSMS to connect to this temporary instance using its pipe name.  Search Goggle to know how to find the pipe name of a temporary instance (or User Instance) of SQL-Server Express.

    For other ideas, see http://stackoverflow.com/questions/188963/connecting-to-sql-server-with-visual-studio-express-editions

    Saturday, March 31, 2012 11:20 AM
  • I detached the database as per these instructions.  But when I attempted to connect to the database as a Microsoft SQL Server Database File, I received this error:

    A network-related or instance-specific error occured 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    Saturday, March 31, 2012 4:05 PM
  • did you following the instruction on the following MSDN sites ?

    http://msdn.microsoft.com/en-us/library/ms165673%28v=sql.105%29.aspx

    http://msdn.microsoft.com/en-us/library/ms190209%28v=sql.105%29.aspx

    are you sure that the instance is running and it allows remote connections?

    I would recommand that you use SSMS to connect to the SQL Server instance and than follow the instruction in "How to: Attach a Database (SQL Server Management Studio)"

    Saturday, March 31, 2012 6:52 PM
  • I've just tested it here and this works correctly.

    However, in an attempt to reproduce the error, I noticed that the connection will fail if the SQL-Server Express instance is not already running; so this new datasource connection is unable to start the SQL-Server Express if it's not running.

    Another possibility might be a permission issue; so you should make a copy in your project directory.

    BTW, you need both the .MDF and the .LDF: the log file.  However, if the log file is not already in the same repertory, a new logfile will be created.

    Sunday, April 01, 2012 9:13 AM
  • To the best of my knowledge, these factors are not causing my error.  I verified in SQL Server Configuration Manager that SQL Server (SQLEXPRESS) is running.

    Sunday, April 01, 2012 5:45 PM
  • Then another possibility would be that this file is already registered in the temporary instance of SQL-Server Express but under another name.  Try repeating the test but with a brand new database file.  You don't need to fill it, just create it with SQL-Server Express, detach it and then try to connect to it from Visual Studio.

    Another solution would be to create the database file from VS and then, use SSMS to connect to the living, (temporary) second instance of SQL-Server Express that is created in memory to serve this user file by using its pipe name.  From there, you can manipulate the database file from SSMS like any other ordinary database file, including running your sql scripts.

    To find the pipe name of your in-memory instance, see http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

    You must be careful when working with the memory instance.  Usually, the name of the database inside the instance will be the same as the full path of the database file.  However, if you're not careful and try to attach a database yourself, you might end up with the database file attached under another name.  In this occasion, VS will be unable to connect to the database because it will use the full path of the database file as the name of the database to try to connect with it but the same file cannot be connected under two different names; therefore the connection will fail.

    Sunday, April 01, 2012 9:20 PM
  • This method makes sense, but for some reason it's not working for me.  Though I've been able to create databases in Visual Basic in the past, for some reason, when I try to now, I receive the same error I described above.

    Would reinstalling the programs possibly help this?

    Tuesday, April 03, 2012 3:40 AM
  • Sorry but I'm out of ideas here.  At this point, anything is possible; included a possible corrupted installation or a total mix-up on your part.

    If you can set up a virtual machine, it would be a good idea to do so and test different configurations/installations.  Othewise, you can try reinstalling everything but from my personal experience, this has often - but not always - the result of making things even worse.

    If you do this, prepare a good backup before starting the reinstallation procedure.

    Tuesday, April 03, 2012 8:34 AM