none
Can't find databases

    Question

  • I am using VS 2012 ultimate.

    I have installed SQL Server 2012 Management Studio and SQL Server 2012 Data Tools. Both connect to my database engine.

    When working with my solutions using the VS 2012 IDE, I can find and connect to my databases (stored in the solution folder for each of my solutions), using Server Explorer.

    However, when using Management Studio or Data Tools, I can connect to the server but none of the solution-specific databases are visible. I can't attach them or locate them in any way.

    Thoughts and suggestions?

    Thanks

    Doug

    PS. I have just upgraded to update 3 of VS 2012 but this problem existed before and after the upgrade. I have checked SQL for upgrades and am told I have the latest version installed.

      


    VBDataStarter

    Wednesday, July 10, 2013 6:56 PM

Answers

  • Hi  VBdataStarter ,

    Could you help us check some information as we can do the further analysis, how the solution-specific databases stored in VS AND which instance these databases belong? We also need know which login you use to login to the SSMS.

    Please try the following steps to troubleshoot the issue:

    1. Please run the following attach the database with SQL Server Management Studio

    use master

      go

      -- please modify the path of dbname.mdf file

    sp_attach_db [ @dbname= ] 'dbname'

            , [ @filename1= ] 'filename_n' [ ,...16 ]   go

    [ @dbname= ] 'dbnam '

    Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.

    [ @filename1= ] 'filename_n'

    Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. Up to 16 file names can be specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file. The primary file contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.

    2. Open Visual Studio Server Explorer, connect to the SQL Server 2012 Edition the specific database, and then right click the connection, Properties, to get the connection string. The connection string will like following:

    Data Source=servername; Initial Catalog=databasename; Integrated Security=True

    Thanks,
    Candy Zhou
    Tuesday, July 16, 2013 4:01 AM
  • PS. I have just upgraded to update 3 of VS 2012 but this problem existed before and after the upgrade.

    Hello Doug,

    It's not a "problem" nor an issue; it's by design.

    By default VS utilizes a "user instance" of SQL Server and this mean, the database will be attached to SQL Server only during runtime and then will be only available for the process, which started the user instance.

    As Candy suggested, you could copy (move) the MDF+LDF file from the AppData folder of your project to the DATA folder of your SQL Server and the attaching the database file as a permant database. Afterwards you have to modify the connection string in your app.config file (removing "user instance=true" and "AttachDatabase....")


    Olaf Helper

    Blog Xing

    Tuesday, July 16, 2013 6:09 AM
    Moderator

All replies

  • Hi  VBdataStarter ,

    Could you help us check some information as we can do the further analysis, how the solution-specific databases stored in VS AND which instance these databases belong? We also need know which login you use to login to the SSMS.

    Please try the following steps to troubleshoot the issue:

    1. Please run the following attach the database with SQL Server Management Studio

    use master

      go

      -- please modify the path of dbname.mdf file

    sp_attach_db [ @dbname= ] 'dbname'

            , [ @filename1= ] 'filename_n' [ ,...16 ]   go

    [ @dbname= ] 'dbnam '

    Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.

    [ @filename1= ] 'filename_n'

    Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. Up to 16 file names can be specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file. The primary file contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.

    2. Open Visual Studio Server Explorer, connect to the SQL Server 2012 Edition the specific database, and then right click the connection, Properties, to get the connection string. The connection string will like following:

    Data Source=servername; Initial Catalog=databasename; Integrated Security=True

    Thanks,
    Candy Zhou
    Tuesday, July 16, 2013 4:01 AM
  • PS. I have just upgraded to update 3 of VS 2012 but this problem existed before and after the upgrade.

    Hello Doug,

    It's not a "problem" nor an issue; it's by design.

    By default VS utilizes a "user instance" of SQL Server and this mean, the database will be attached to SQL Server only during runtime and then will be only available for the process, which started the user instance.

    As Candy suggested, you could copy (move) the MDF+LDF file from the AppData folder of your project to the DATA folder of your SQL Server and the attaching the database file as a permant database. Afterwards you have to modify the connection string in your app.config file (removing "user instance=true" and "AttachDatabase....")


    Olaf Helper

    Blog Xing

    Tuesday, July 16, 2013 6:09 AM
    Moderator
  • Thanks Olaf

    Your message provides a workable solution for me. When I need to make significant changes to the database, I can simply copy it to the DATA folder as you suggest and then copy it back when done. This is quite easy and effective.

    When I recently upgraded my SQL Server Management Studio and SQL Server Data Tools, I am pretty sure that I got a message saying that these tools no longer used "User Instances" and that this reference would no longer be found in the connection strings. Sure enough I just checked all the connection strings in my solution and none of them include "user instance=". Plus the user instance property no longer shows up when adding or modifying a connection within VS. 

    Not sure if that suggests other ways of addressing my access issue beyond the copy-paste approach - which is a perfectly good method for my purposes. 

    Thanks for your help.


    VBDataStarter

    Thursday, July 18, 2013 7:49 PM
  • Hi Candy

    thanks for your reply on this. To be honest, your response is beyond my technical grasp and I was not able to implement it. Olaf suggested a simple (for me) copy and paste approach that will suffice. 

    Thanks

    Doug


    VBDataStarter

    Thursday, July 18, 2013 7:56 PM