StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Use $DbName$ in Deploy Scripts?

Use $DbName$ in Deploy Scripts?

Derek Price — Thu, 22 Jan 2009 10:13:51 GMT

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 [b]$DbName$[/b] variable? That would get us around the issue of manually deleting the db first. We're trying to automate the entire process.

Thanks,
Derek

RE: Use $DbName$ in Deploy Scripts?

Derek Price — Thu, 22 Jan 2009 10:13:51 GMT

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

RE: Use $DbName$ in Deploy Scripts?

Derek Price — Thu, 22 Jan 2009 10:12:15 GMT

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

RE: Use $DbName$ in Deploy Scripts?

Trent L. Taylor — Thu, 22 Jan 2009 10:00:39 GMT

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.

RE: Use $DbName$ in Deploy Scripts?

Derek Price — Thu, 22 Jan 2009 09:57:43 GMT

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

[codesnippet]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[/codesnippet]

When I change the [i]Database Name on Server[/i] 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 [i]Database Name on Server[/i] 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:

RE: Use $DbName$ in Deploy Scripts?

Trent L. Taylor — Thu, 22 Jan 2009 09:14:10 GMT

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.