mosessaur

How can I generate 1 file that contains SQL scripts for my database using VSTS for DB Pro

Also I've imported my database project from existing SQL Server 2000 Database schema. The scripts contains collation scripts! I want to prevent that. I want the scripts without collation. I set the Deployment option to not script collation, but the scripts still contains collation.

I want the generate file to have no collation script !

Is that possible !




Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

Chuck Weininger - MSFT

When importing your database schema into a project, we don't give you the option to not script collations. But when you build the project, you can choose to ignore collations. In the project properties dialog box on the build options tab, there is an option you can set to ignore collations. When you select that option, then build your project, the resulting build script should not have any collation information in it. The script can be found in the 'sql' folder in your project folder. In the project propertyes build options, you can specify that the database always be recreated when building. If you do this, the build will create the complete script to create your database. If you don't check this, the build will build an incremental script to make your target database match your project.

Please let me know if that answered your questions.






Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

mosessaur

Thanks, but I have a problem with this "In the project propertyes build options, you can specify that the database always be recreated when building. If you do this, the build will create the complete script to create your database" I'm afraid that one of my team by mistake click on Deploy, does this re create the database on the target database !

And what about Re-Build does it do the same ! kindly clarify these things!

Regards,






Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

Chuck Weininger - MSFT

Yes, deploy will run the build script against the database that you have specified in the build properties page. If you selected to always recreate the database, it will drop and recreate your target database. Re-Build does not do a do deploy, it just rebuilds the build script.




Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

mosessaur

So, Is there is a security option to disable deploy for all developers and enable it for certain user !, Also I set the option to back up the database before apply any update, where is the database is backed up




Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

Chuck Weininger - MSFT

No, there isn't. I think we are thinking about security a little differently than you are. We are thinking that the security should come from SQL Server, not our product. Developers should not have permissions to databases on production servers, so there shouldn't be any danger of a developer deploying a project and overwriting an important database. Developers should only have access to test SQL servers or SQL servers on their own machine. Here is a link to a security whitepaper that our team wrote:

http://msdn2.microsoft.com/en-us/library/bb264457(vs.80).aspx

Hopefully that can answer most of your questions, but let me know if you have more.






Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

Paul Burton

Hi Chuck,

We are trying to create a script without collations, and have the settings you state above, but we find that collations are always included in the script.

The build script sets the database collation (in this instance to Latin1_General_CS_AS), but it then adds this same collation to all (n)varchar & (n)text columns. We don't want our script to impose a collation on the columns - we want to simply inherit the database collation. Our customers may need a different collation - they can easily reset the database collation, but this will have no effect if the columns have their own collations explicitly set!

How can we create a script that does not set column collations (and we really do not want to be editing our build script manually!)

paul





Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

Chuck Weininger - MSFT

I just wanted to let you know that I don't have an answer yet, but I'm looking into it. Just to be sure, you are running our RTM version correct

Thanks,






Re: Visual Studio Team System - Database Professionals How can I generate 1 file that contains SQL scripts for my database?

Chuck Weininger - MSFT

I suspected this might be the case, but wanted to verify before I said anything. The behavior you are seeing is a bug in RTM, but we have fixed it in our first service release. It has not been released yet, but we do have a CTP available for you to try. It can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1561182&SiteID=1

If you can try the CTP, let me know how it goes.

Thanks,