We're seeing a lot of stuff showing up as "differences" when we do a build from either Team Build or VS2005. There don't appear to be any differences, but it creates the script anyway. When we use Schema compare, it doesn't flag the same tables as changes. This sounds like maybe the options are different for the compare. Is there a way to check that and get them behaving the same way?
Do you have the build option 'Always re-create database' checked? This option is on the project properties build page. If you have that option checked, the build will output script for creating the entire database, not just changes. If you don't have that option checked, can you post examples of an object that the build is generating script for that you think it shouldn't? If you can post the source from your project and the source from the target generated by SSMS, maybe we can figure out what is causing the problem.
Thanks,
Chris,
I posted a similar problem I was having in this forum many months ago and never got a satisfactory reply. My problem was that when I did a Build, the resulting synchrnoization script was much larger than if I did a Schema Compare using the same source and target objects. One thing I did learn is that when a Build is done, DBPro doesn't necessarily respect the Compare options you have set, unlike what happens when a schema compare is done. For some reason, Microsoft feels like they know better than us how a synchronization script should be generated when a Build is done and will ignore some Compare options we have set. However, this doesn't account for all the differences in changes I was seeing.
Amos.
Chris - Do you still have questions about this issue? If so, could you provide us with more information about what is in your sync script that you don't think should be there?
Thanks,
Chuck
Chris -
We are still waiting on information from you. If we don’t hear from you in the next seven days, we will delete the thread. We do this to keep the system full of useful information for customers searching for answers and focus expert attention on active unanswered questions.
I'm having this same issue. The SchemaCompare script correctly identifies the changes and creates a much smaller script than the build script that is created when pointing to the same target database.
In addition, the build script is altering one of our largest tables unnecessarily which will prevent us from using the build script for production deployment.
Please let me know what info you need to debug. I can repro the problem on demand.
We may have to go so far as getting your project and database so we can look at exactly what is happening to you. But let's try something a little easier first.
Can you give a couple of examples of things that exist in the build script that aren't in the schema compare script? That might help narrow the problem. Also, can you be a little more specific about what it is doing to the large table unnecessarily? Were there any changes to the table that need deployed or should it remain completely unchanged?
Thanks,
Chuck
There are tables, procs, and UDFs in the build script that don't exist in the schema compare script. I suspect this may be due to the fact that the settings for ANSI NULLS and QUOTED IDENTIFIER are different in the project than for some of the stored procedures and UDFs on the server. In the project we have both ANSI NULLS and QUOTED IDENTIFIER ON and all the objects set to use the "Project Default". However on the server these 2 options tend to differ by stored procedure and UDF.
Could this be causing the build to create alter statements for those objects that have different settings for these two options and also causing the build to include dependent objects that don't have changes like the tables?
I've tried setting the options in Visual Studio to be similiar to those that are implicitly set during the schema compare (as mentioned this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1191819&SiteID=1). However those settings don't seem to have any effect on the build script.
Ok, it looks like we had 2 problems combined to make the build script different than the schema compare script:
1.
A table in the project had a different column order than the production database (one column was transposed). This didn't show as a table needing an update in Schema Compare because we had the option "Force table column order to be identical" turned off. However, the table is being dropped and recreated in the build script. I fixed the project to have the same column order as the target database and the build script is no longer dropping and recreating the table. I take it those options under Database Tools=>Schema Compare only apply to the Schema Compare and do not affect the build process?
2.
The additional procs and UDFs in the build script that are not in Schema Compare are there because our settings for ANSI NULLS and QUOTED IDENTIFIER are different in the project than on the server. Is this correct? I can't find any other reason why the procs/udfs would need to be included in the build script.
I am having the same issue.
I start from a new project, import the database schema and force the setting of my imoprted database then immediately I set my target to be the same database I imported. Then I build the script and I am seeing what you are seeing.
This is a link to my thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2184557&SiteID=1
I am hopping to get this resolved I don't want to open a ticket.
Amos: I referenced your thread Gert gave me an answer regarding your thread.
Elie
We don't provide as many options for the build as we do in Schema Compare because the build is supposed to produce a script that will make the target database look exactly like your project. We know that doesn't provide as much flexibility as some would like, but for most people it works. We are investigating ways to make the build/deploy process more flexible in future releases.
As for the quoted identifiers and ansi null settings, you can set those on individual objects in the project. We don't import those settings automatically, but we do allow you to change them per object after you import a database. You can change those properties by accessing the properties of a schema object in solution explorer. Just right click the file and choose properties or select the file and choose F4.
I haven't had time to try to reproduce this scenario myself yet, so I'm not certain this will solve your problem. Let me know what you find and I'll try to investigate sometime in the next few days.
Chuck,
Thanks for the reply.
We decided to leave the settings for ansi nulls and quoted identifier as "Project Default" because of the number of objects we're managing. This forced the procs and udfs to be redeployed in the build script, however now all objects use the same settings. We were oblivious to these settings before DBPro so this exercise helped us to clean up our databases.
