Data-Tier Applications Post-Deployment scripts database context

Question

I have been using a DAC for a new application i'm working on for a little while now, with the assumption that the advertised "Post-Deployment script" feature would work.  What I have noticed is that if you deploy from VS2010, it is executing the post deployment script in the context of the master database, and not my configured database name.   Since none of my script statements explicity state the database name, this obviously causes an exception when trying to the deploy from VS2010 

Msg 4121

 

Cannot find either column "utils" or the user-defined function or aggregate "utils.GetErrorMessage", or the name is ambiguous.

This isn't an issue when you deploy from SSMS, as it deploys in the context of the configured database name.  Surely this has to be a bug in VS2010.  Avenues of deployment, should, deploy the exact same way.

I figured I could do a workaround using the "use" statement, but i'm not sure if there is a way of getting the value of the configured database name.  Is there a variable avaliable to the post deployment script to get this name?  Or perhaps is there a better way of putting the context that the post deployment script into the configured database?

Happy New Year to all!

Answer 1

Hi Savstar,

 

This is the default behavior.

Please refer to “Adding a post-deployment script to the project” section in this documentation:

http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/Data-tierAppsInSQLServer2008R2.docx

 

However if you think post-deployment script should be executed against specific database, you may provide Microsoft feedback at https://connect.microsoft.com/SQLServer and our product team will consider it in the future.

 

If anything is unclear, please let me know.

---

Regards,
Tom Li

Answer 2

Thanks for the reply Tom.

Since it is the default behaviour to run the scripts in the context of the master database, are you able to suggest how to get the name of the database that the DACPAC is being deployed as? 

Answer 3

Hi Savstar,

 

As said in the above link, Post-Deployment script is executed in a separate context. It is hard to get the name of created database name. However I would like to recommend that try checking the sys.databases system view with the create_date column. Please see the command below:

SELECT TOP 1 * FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'msdb', 'model') ORDER BY create_date DESC

It might provide us the name of the database created by Data-Tier Application. However it might be incorrect.

 

Then we could use dynamic SQL to switch database and execute script.

 

If anything is unclear, please let me know.

---

Regards,
Tom Li

Answer 4

Thanks Tom,

I totally forgot about the system tables.  I do agree with you though, it might be incorrect. 

For my implementation I will use the sysdac_instance table, just to be different.  I do realise that it too has the same flaw as using master.sys.databases

I will make a suggestion at https://connect.microsoft.com/SQLServer  for a variable (or something else) to be avalible when the pre/post scripts run 

Answer 5

I understand using the sysdac_instance table. I cannot get the syntax for the dynamic sql to use that

DECLARE @DatabaseName NVARCHAR(256)
SET @DatabaseName = N'select top 1 database_name from [msdb].[dbo].[sysdac_instances] order by date_created desc'

How do I convert that to a statement that will change the context.

Answer 6

Unfortunately Jeffrey, there is only one way to do this.  Dynamic SQL.  It is really bad I know, since this isn't really a solution, but a crappy workaround.  Hence, I have added a suggestion to the SQL Server dev teams list of suggestions.  Feel free to vote for it.  What I have suggested is to have the database name available as a SQLCMD environment variable.  It should a quick and easy enhancement for the team to implement.

https://connect.microsoft.com/SQLServer/feedback/details/737079/data-tier-application-dacpac-deployment-database-name-avaliable-as-a-pre-processor-variable