Phil Parkin wrote: frederico_fonseca wrote: richardmgreen1 wrote:
Thanks for that. We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.
I'll give it a go with a test database and see how I get on.
it will cause issues - even more if you use different database names depending on the environment (dev/tst/prod)
in order to "correctly" use synonyms with a VS project you will need to use VS variables to use instead of your hardcoded database name on the synonyms.
Interesting. I don't know about this technique. Do you have any good links explaining how best to set it up? The links I have found have not been great.
original links I looked for this (been many many years) I don't have them anymore - but this one explains part of it (e.g. using VS environments to change the contents of the variables dynamically) - https://stackoverflow.com/questions/29969634/how-to-make-schema-compare-of-database-sql-project-respect-sql-cmd-variables
it is a bit of a pain as it requires each user to change THEIR local .user file to contain the required changes - so these should also be kept as a "read me file" within the solution so new users can follow instructions.
one example I did just now to show up how it works.
this example assumes 2 databases on solution - Base (where we will create a synonym to the other db) and Referenced
on Base we add a database reference and we give it name "$(Referenced_db)"
this will create a SQLCMD variable on the project
with this and if we create a synonym on db BASE (create synonym xxx for [referenced].[dbo].
) we would change its content on visual studio to be "create synonym xxx for [$(Referenced_db)].[dbo].
with this, any deploy, publish or schema compare would replace the content of the variable ($(Referenced_db)) with "Referenced".
while the above is fine if your dbnames are the same across environments, this is not always the case - as an example on my shop the db names vary from environment to environment - in some cases it is a single letter at the end of db (D,T,U,P) in others its (_dev, _tst, _uat, none for prod), so we need to do a bit more.
on my sample the database names have a suffix according to environment (_dev, _tst, _uat) - prod does not have this - and we also need to allow for normal Visual Studio environment where variable name must match that of the DB name within the solution.
first create new environments
then we change the SQLCMD variable to have a "local" value - this will add a entry to the projname.sqlproj.user file. and within this one its where the magic happens.
once we add a value to "local" the definition of the project changes - a new sqlcmdvariable is added to the .sqlproj ($(SqlCmdVar_1)) - right image-- and it is also added to the .user file -- left image.
as this is a standard .xml VS file we can manually change it and make it so that that variable is populated based on the configuration name we select in VS (added above).
after changing it my .user file looks like this
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<SqlCmdVar__1 Condition=" '$(Configuration)' == 'Debug' ">Referenced</SqlCmdVar__1>
<SqlCmdVar__1 Condition=" '$(Configuration)' == 'DevServer' ">Referenced_dev</SqlCmdVar__1>
<SqlCmdVar__1 Condition=" '$(Configuration)' == 'TestServer' ">Referenced_tst</SqlCmdVar__1>
<SqlCmdVar__1 Condition=" '$(Configuration)' == 'UATServer' ">Referenced_uat</SqlCmdVar__1>
<SqlCmdVar__1 Condition=" '$(Configuration)' == 'Release' ">Referenced</SqlCmdVar__1>
with the above changes we can now do builds and schema compares - and as long as we select the correct environment BEFORE we do the task, the variable will be replaced with the correct value for that configuration.
sample project attached.