StrataFrame Forum

Use $DbName$ in Deploy Scripts?

http://forum.strataframe.net/Topic21619.aspx

By Derek Price - 1/22/2009

When running the DDT on our package everything works fine. If I change the name of the database in the last step of the deployment wizard, we get package errors. This is due to a pre-deploy script that drops the database if it exists (temporary step during development). However, the database name is hard coded in the script. Do the any of the scripts support using the $DbName$ variable? That would get us around the issue of manually deleting the db first. We're trying to automate the entire process.



Thanks,

Derek
By Trent L. Taylor - 1/22/2009

Actually it technically already does this for you.  When you create a profile script, you can choose which database to execute it on.  So for example, let's assume that my database name is "Trent."  When I deploy, however, I tell it to deploy the Trent Database as Trent_New.  If my profile script is setup to execute the script on the Trent database, the DatabaseMigrator class will execute the script on the Trent_New database for me as it will know how to translate the Trent into Trent_New.
By Derek Price - 1/22/2009

I don't think I'm seeing this. Here's our pre-deploy script:



IF exists(SELECT 1 FROM sys.databases WHERE name = N'BACKUPDB') and exists(SELECT 1 FROM sys.databases WHERE name = N'NEWDB')

BEGIN

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'NEWDB'

DROP DATABASE [NEWDB]

End




When I change the Database Name on Server field in the last screen of the DDT Wizard from "NEWDB" to "NEWDB123", I get a really long delay (maybe a minute?) at:



Ensuring Tables exist on database 'NEWDB123'



Although no errors show up. However, I get a ton of errors when running the Post-Deployment Scripts related to a primary key constraint error trying to insert duplicate keys into the tables. It doesn't seem like "NEWDB123" is being deleted. If I run the DDT again and change the Database Name on Server field back to the original "NEWDB" and run it. I get no delay at the beginning and all scripts run with no errors.



What am I missing here?! Unsure
By Trent L. Taylor - 1/22/2009

Well, the issue here is that the code within the script is not going to be changed.  So you would want to get the current DB name instead of hard coding the database name.  There is actually a discussion on another thread that I am having with Chan about creating profile variables that would allow you to insert variables within the SQL script and then it would get replaced with a real value at the time of deployment making this type of thing much easier.
By Derek Price - 1/22/2009

Being able to use $DbName$ in our pre-deploy script would definitely solve this problem. I hope you can implement this!



Thanks for all your help

Derek
By Derek Price - 1/22/2009

I forgot to ask, how do you get the current DB name in the pre-deploy script?