Refresh fails due to incorrect compatibility level in shadow DB

PeterDanielsCRB

SCA: 4.2.20023.15120
VS 2019: 16.4.3

I have the project set for target platform "SQL Server 2008", which should be compatibility level 100.  However, when I "Refresh" the project, it builds a shadow DB at compatibility level 150.  This causes a failed table creation due to:

"Msg 402, Level 16, State 1, Server E000PDANIELS0L1, Line 34
The data types datetime and time are incompatible in the add operator."

Is there some other place where I'm supposed to configure the compatibility level?

PeterDanielsCRB

After resolving the installation issue , which required a second update to vs 2019 ent (v 16.4.4) and uninstalling and reinstalling sca, I am up and able to "refresh" the project in VS. I was able to use a pre-deployment script to set the compatibility level of the shadow DB to 100. It still seems that this is a bug , though, unless I'm misunderstanding the concept of setting the target platform to "sql server 2008" in the project settings. 

Sergio R

Hi Peter,

In this case, the best option would be to configure the project to host the Shadow Database in a SQL Server 2008 server, you can do this by following the instructions on this documentation page:
https://documentation.red-gate.com/sca/developing-databases/concepts/shadow-database

PeterDanielsCRB

Thanks, @Sergio R.  I think that is probably a valid solution, although somewhat heavy.  I was going to try to use a pre-deployment script to just set the compatibility level if the '$(DatabaseName)' LIKE N'%_shadow', but my VS SCA is now broken - I believe from an VS Ent 2019 update.  I submitted a support ticket for that one.  The journey continues.

Sergio R

That's a viable option as well.

PeterDanielsCRB

I also had to do some adjustments in the pre-deployment script for the project validation using the powershell cmdlets.  Apparently that doesn't use '%_shadow', so I brute-forced the check to look to see if the DB was on my local host.  I imagine that will break if I move the build to  CI server, but it's working for now.  I wasn't able to actually see a build validation DB on my local host - curious as to where (which instance) that is being built on and how the DB is named? 

Sergio R

By default the Shadow Database will be on the same server as the development database and its name will be suffixed with _(your_user_name)_SHADOW