0

When using a SQL Server Database Project in Visual Studio, if you have more than 1000 stored procedures in a database, when you import it Visual Studio will split the procedures into separate directories.

When you attempt to sync any new stored procedures into the project following such an import, via the 'Schema Compare' function, Visual Studio forgets this split folder structure and places all new scripts directly into the 'Stored Procedures' folder. This means that new stored procedures which are scripted do not fit into the existing structure, which forces the user to either live with this detached structure or to manually update project references and copy the files back into the initial structure which Visual Studio created.

Steps

  1. Create a new SQL project.
  2. Right click on the project in solution explorer > select 'Import' > 'Database'.
  3. Bind the project to a SQL2008 database which has more than 1000 stored procedures. Select 1000 for 'Maximum files per folder'.
  4. Allow VS to script out all of the stored procedures. It will automatically break into batches of 1000 and create a new folder 'Proc1', 'Proc2' for each batch as necessary.
  5. Open a new comparison by right clicking on the SQL project > select 'Schema Compare'.
  6. Verify the database synced correctly by comparing the SQL project to your original database. There should be no pending changes.
  7. Create a new stored procedure in the database which was synced.
  8. Run the comparison again, this should now show the new stored procedure under the 'Add' type.
  9. Click 'Update' and allow VS to create a script for the new procedure.
  10. Instead of placing the new script in the original 1000 delimited folder structure, it will instead place the new script in the 'Stored Procedures' folder directly.
Improve this question

1 Answer 1

Reset to default
1

That is an interesting scenario. I will check it out one of these days.

Here is my 2 cents. After the initial load of all the database objects into a database project I(or dev team) stop the practice of adding new SPs or any objects directly to the database through SSMS. Instead, I add them to the Database Project and deploy to all the servers I need them to go to. The database project acts like the true source of code from that point on-wards. On top of that, you can leverage TFS branching feature to keep QA version, Prod version of the Database Projects. I also try to give meaningful names to the folders to categories the SPs under the Stored Proc folder which helps in my future code maintenance.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.