SQL Compare - any difference between generating script vs running the update directly on target?
alayarda
Posts: 1 New member
in SQL Compare
Hi,
I recently ran into an issue when doing production deployment on one of our projects. We use SQL Compare to move the required database objects from the user acceptance testing environment to the production server.
This is what happened on the most recent deployment:
1. Ran SQL Compare and picked the database objects and chose the option to generate a script instead of applying the update directly.
2. Manually ran the script on the target server - encountered errors saying that some DB objects already exists, even though when we checked the target server it does not exist.
3. Created the one table manually, and ran into more issues with other non-existent tables.
4. We then restored a backup copy of the target server that we took before deployment to a different server to try and troubleshoot the issue
5. Ran the same script on the restored server and everything worked fine.
Wondering if anyone has bumped into a similar issue before. The only possibility I could think of is that we have multiple objects with the same object name but different schema (e.g. location1.customerFact and location2.customerFact). Not sure if this is a potential cause of the issue.
I recently ran into an issue when doing production deployment on one of our projects. We use SQL Compare to move the required database objects from the user acceptance testing environment to the production server.
This is what happened on the most recent deployment:
1. Ran SQL Compare and picked the database objects and chose the option to generate a script instead of applying the update directly.
2. Manually ran the script on the target server - encountered errors saying that some DB objects already exists, even though when we checked the target server it does not exist.
3. Created the one table manually, and ran into more issues with other non-existent tables.
4. We then restored a backup copy of the target server that we took before deployment to a different server to try and troubleshoot the issue
5. Ran the same script on the restored server and everything worked fine.
Wondering if anyone has bumped into a similar issue before. The only possibility I could think of is that we have multiple objects with the same object name but different schema (e.g. location1.customerFact and location2.customerFact). Not sure if this is a potential cause of the issue.
Tagged:
Comments
having the same object name is a problem if you don't specify the two part name (ex. Schema.Table). It works by default if you have the objects under the dbo schema (or the default one of the user which executes the script). It depends on how the script has been created. However, this should not work also after the restore. But if I got it, the second time you tried, the script ran successfully.
So, I guess that there was a "open transaction" problem. Something that created objects, not committed, or simply stuck in the middle on the, let's say, "first database". I know, it sounds weird, but I had similar problem. I could not see objects which were created and I had a database file corruption (or a disk failure I don't remember right now actually).
What do you think about this possibility?
CTO @ Engage IT Services s.r.l.
Database Administrator and Team Leader
Microsoft MVP - Data Platform
Staff member of getlatestversion.it community
personal website | blog