none
VS 2013 - SQL Server Projects - Can I Change defaults for SQL Servers to use and the paths to DB-files?

    Question

  • Hi all,

    when a create a SQL Server Project using "File > New ...", I find the following settings defaulted:

    1. SQL Server is (local)\Version12.0 and
    2. DefaultDataPath, DefaultFileprefix and the DefaultFilename(s) for MDFs and LDFs point to the Project directory.
    3. Initial settings of file size and increments are also not settable but defaulted.

    Because my projects have to verify performance by testing with large volume of data, we have to use a different SQL Server with files stored on that Server. The Project settings have a button to changes DB properties, but these are not part of that dialog.

    So I spent some fruitless hours in vain to find a way to change the project's connection to my development SQL Servers.

    How to cope with that?

    Thanks in advance.


    Regards Uwe


    • Edited by Jörg Debus Sunday, November 16, 2014 11:05 AM Typos
    • Moved by Caillen Monday, November 17, 2014 7:39 AM
    Saturday, November 15, 2014 2:26 PM

Answers

  • Hi Uwe,

    I don't quite understand you. Maybe you could post a screenshot about those default settings. I don't find those settings in a SQL Server Project, as I don't have much experience on SQL Server Projects. If you want to change the connection, you could go to the Project properties page:

    And you can change all the related settings here. Not sure where "DefaultDataPath, DefaultFileprefix and the DefaultFilename(s)" settings are located.

    By the way, this question is actually not about VS extensions. But i'm not sure where it belongs(maybe TechNet SQL Server forums). So I move it to [where is this forum for...] where the moderator may direct you to the right forum.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jörg Debus Wednesday, December 03, 2014 10:57 AM
    Monday, November 17, 2014 7:39 AM
  • Hi Dave,

    sorry but this is definitively a VS2013 templates issue. Caillen has answered how to manually setup the SQL Server after the SQL Server Project has been created. The defaults for the 4 SQLCMD variables are "System-defined" (See below the screen-shot). They have massive impact on the project because they define DB-filenames and -places.

    These variables are used when the project is created and in the deployment SQLs generated by the build process. To change these after the project has been created is tricky, because you have to mover/rename the DB-files

    I have found the following places where some of these defaults are set:

    1. The connection string is partly defaulted in VS2013 and then Tools>Options>Database Tools>Data Connections
    2. The SQLCMD variables $(DefaultDataPath) and $(DefaulLogPath) are defaulted based on "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SSDT\LocalDbDatabaseFilePaths".
    3. $(DataBaseName) is set to the project name which not a default but a designed setting
    4. I have not found anything on $(DefaultFilePrefix). I suppose, that this is set to the project directory name when the project is created.

    So as of now I can handle the creation of new project data bases at the right places and thanks Caillens answer attached to the correct server.

    BTW: The (localdb)-Server is also started by VS2013. For additional localdb-servers, these must be created and started/stopped with SQLLocaldb command.


    Regards Uwe

    • Marked as answer by Jörg Debus Wednesday, December 03, 2014 11:39 AM
    Wednesday, December 03, 2014 11:39 AM

All replies

  • Hi Uwe,

    I don't quite understand you. Maybe you could post a screenshot about those default settings. I don't find those settings in a SQL Server Project, as I don't have much experience on SQL Server Projects. If you want to change the connection, you could go to the Project properties page:

    And you can change all the related settings here. Not sure where "DefaultDataPath, DefaultFileprefix and the DefaultFilename(s)" settings are located.

    By the way, this question is actually not about VS extensions. But i'm not sure where it belongs(maybe TechNet SQL Server forums). So I move it to [where is this forum for...] where the moderator may direct you to the right forum.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jörg Debus Wednesday, December 03, 2014 10:57 AM
    Monday, November 17, 2014 7:39 AM
  • Hi Caillen,

    thanks for Your response. I have to apologize for being a little late with my answer. I'll try to clarify my case more precisely:

    1. This is surely a topic related to VS2013 handling new "SQL Server Database Projects". When one clicks "OK" the following steps are executed by VS2013 (surely controlled by standard templates or the like):
    • VS2013 creates the new project in a selectable directory and uses the projectname as a subdirectory whre the directory elements (*.sqlproj, bin\, obj\) where  later all the *.sql are placed, too. This is normal and expected.
    • VS2013 creates a new database with its DB-files and attaches it to "(localdb)\ProjectsV12". The name of the database is the name of the project. The path to the DB-files (*.mdf and ldf) and their sizes are also defaulted to small values. The DB-server and the path to the DB-files is taken from the registry (!) HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SSDT\LocalDbDatabaseFilePaths. I don't know when and by what tools these values are set.

    The path to DB-Files is typically also the path to the projects. The (local)-server places all system database files in the User\<user-ID>\Appdata\.. tree. Which is OK for small databases. The templates extracts some data from the DB-file path and places it in the deployment SQLs to populate the SQLCMD variables DefaultDataPath, DefaultFileprefix and the DefaultFilename(s) which are generated when pressing "Executed" or "Debug" the project.

    My problem is that we have to test database performance with larger volumes of data (e.g. two million rows and more). These cannot be stored in the User-path but must be handled by some kind of professional DB-Manager on various storage devices. When everything is tested, the publishing function of VS2013 will extract the structure and you are done incl. all the performance taming things.

    The dialog from the project properties can change to a different Server, but that server does not know your project database. The server and the DB-Files should be specified by the developer before VS2013 creates the database.

    So  the right forum would be a forum, where people developing SQL Server solutions with VS2013 are reading the questions.

    Thanks again for your patience.


    Regards Uwe


    • Edited by Jörg Debus Wednesday, December 03, 2014 11:40 AM
    Wednesday, November 19, 2014 2:31 PM
  • Might try them over here.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

     

     

     


    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows]

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights.

    Thursday, November 20, 2014 2:10 AM
    Moderator
  • Hi Dave,

    sorry but this is definitively a VS2013 templates issue. Caillen has answered how to manually setup the SQL Server after the SQL Server Project has been created. The defaults for the 4 SQLCMD variables are "System-defined" (See below the screen-shot). They have massive impact on the project because they define DB-filenames and -places.

    These variables are used when the project is created and in the deployment SQLs generated by the build process. To change these after the project has been created is tricky, because you have to mover/rename the DB-files

    I have found the following places where some of these defaults are set:

    1. The connection string is partly defaulted in VS2013 and then Tools>Options>Database Tools>Data Connections
    2. The SQLCMD variables $(DefaultDataPath) and $(DefaulLogPath) are defaulted based on "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SSDT\LocalDbDatabaseFilePaths".
    3. $(DataBaseName) is set to the project name which not a default but a designed setting
    4. I have not found anything on $(DefaultFilePrefix). I suppose, that this is set to the project directory name when the project is created.

    So as of now I can handle the creation of new project data bases at the right places and thanks Caillens answer attached to the correct server.

    BTW: The (localdb)-Server is also started by VS2013. For additional localdb-servers, these must be created and started/stopped with SQLLocaldb command.


    Regards Uwe

    • Marked as answer by Jörg Debus Wednesday, December 03, 2014 11:39 AM
    Wednesday, December 03, 2014 11:39 AM