User profile for Kendra_Little

Hi Peter, Thanks for reporting this. I've seen this issue before, and I was able to resolve it by manually deleting the shadow database (with the option to close existing connections when I do so). That is a workaround and not something we want users to have to do regularly, so if this issue persists after yesterday's release, we should look into it. I'll keep an eye out for it as well. Kendra / comments

Hi Peter,Thanks for reporting this. I've seen this issue before, and I was able to resolve it by manually deleting the shadow database (with the option to close existing connections when I do so). ...

0 votes

Update: I did a quick livestream here which I hope answers your question: https://youtu.be/Y56X9H4XG08 The demo there is for working with an existing project in SSMS, but the workflow is quite similar to what you would do in visual studio. The TSQL I used to create simple objects to test the filter is: CREATE ROLE DoYouSeeThisRole; GO CREATE USER DoYouSeeThisUser WITHOUT LOGIN; GO --DROP ROLE IF EXISTS DoYouSeeThisRole; --DROP USER IF EXISTS DoYouSeeThisUser; / comments

Update: I did a quick livestream here which I hope answers your question: https://youtu.be/Y56X9H4XG08The demo there is for working with an existing project in SSMS, but the workflow is quite simil...

0 votes

Hi @CAT_Keith, Couple of questions for clarification -- Are you using SQL Change Automation in SSMS, or Visual Studio? And is this a new project you are setting up, or an existing one where you have already baselined, and you want to add the filter later? (I am thinking perhaps the second from the context of your question but I'm not 100% sure.) Thanks, Kendra / comments

Hi @CAT_Keith,Couple of questions for clarification -- Are you using SQL Change Automation in SSMS, or Visual Studio? And is this a new project you are setting up, or an existing one where you have...

0 votes

Hi fforgoso , The best way to do this is to create multiple deployments. Each deployment can contain the exact combination of changes that you would like. There are a couple of different techniques you could use to selectively split migrations (of any kind) into multiple deployments. Are you currently using a practice of branching and merging? If so, that type of strategy helps you control the flow of changes into your release pipeline. An example is described here: https://littlekendra.com/2020/01/10/why-i-like-the-release-flow-branching-strategy-with-git-for-database-devops/ Hope this helps, Kendra / comments

Hi fforgoso ,The best way to do this is to create multiple deployments. Each deployment can contain the exact combination of changes that you would like.There are a couple of different techniques y...

0 votes

Hi Ben, Some first places I would look... Is it possible some code was accidentally deployed to the model database and created that schema there? If you're using a dynamically named database to build (you're not specifying the name and it automatically creates one with a guid-like name), when SQL Server creates the db it will automatically have what's in model before any code is deployed and it could cause this issue. If that's not it, could some code have been added to a pre-deployment script that is doing this? Hope this helps, Kendra / comments

Hi Ben,Some first places I would look...Is it possible some code was accidentally deployed to the model database and created that schema there? If you're using a dynamically named database to build...

0 votes

Hi @Melissa7 , If you upgrade the plugin past version 4.0.19288, you get more control over this behavior. Prior to this version the SCA extension would always open in SSMS. On this version and in later versions, the SCA extension will only open by default if you had it open when you last closed SSMS -- so if you close the tab when you are done using it, it won't automatically load when you next open SSMS. Hope this helps! Kendra / comments

Hi @Melissa7 ,If you upgrade the plugin past version 4.0.19288, you get more control over this behavior. Prior to this version the SCA extension would always open in SSMS. On this version and in la...

0 votes

Hi @sgtwickool, Thanks for getting in touch with your concerns. I've emailed you some options for proceeding outside of this thread. Cheers, Kendra / comments

Hi @sgtwickool,Thanks for getting in touch with your concerns. I've emailed you some options for proceeding outside of this thread. Cheers,Kendra

0 votes

Hi @Ajeet , Just to be clear for other readers, this question about external databases is quite different from the topic of "External Tables" which started this thread. SQL Compare gives you a variety of options for comparing -- you can compare databases to other databases, to scripts, to snapshots of databases, etc. Depending on how your access to these databases works, there should be some way to do the comparison. For instance if you can connect via a VPN you might simply be able to compare the databases directly in the SQL Compare GUI. If there are more restrictions on how you connection, you might do something like write out one database to a scripts folder and then compare that scripts folder to the database on the other end-- but you do have a variety of options. Hope this helps, Kendra / comments

Hi @Ajeet ,Just to be clear for other readers, this question about external databases is quite different from the topic of "External Tables" which started this thread. SQL Compare gives you a varie...

0 votes

Hi Steve, I actually recommend starting with SCA with version control -- I think you'll find that things like this are simpler, because you can choose to ignore things like fillfactor in the project settings in either SQL Source Control (if you prefer a state-first approach) or SQL Change Automation (if you prefer a migrations-based approach). If you start this way, you'll need to re-adjust how you handle things like this when you add in the version control bit, so it may not end up reducing overall complexity by much. That being said, I believe you should be able to specify things like IgnoreFillFactor and other SQLCompare options as additional -SQLCompareOptions when you create the release artifact. I believe the reason this isn't called out in the documentation is that most people don't need to do it (as it's handled by the source control project settings). Hope this helps! Kendra / comments

Hi Steve,I actually recommend starting with SCA with version control -- I think you'll find that things like this are simpler, because you can choose to ignore things like fillfactor in the project...

0 votes

Hello, Thanks for your question. For stored procedures, they should all import/baseline successfully, but they will print a warning. This is because SQL Server has a feature called "deferred name resolution" for stored procedures: since stored procedures can create a table and then reference it in the same procedure, they don't require that objects exist when the stored procedure is created. Instead, name resolution in deferred until the first time the stored procedure is executed. Views and some functions are a different story, however. If a view references a table, SQL Server requires validation that the dependency exists, and if it can't find it (in the current database or another), it will give that error. Apologies if this comes off as pedantic! I know it may sound that way. I mention it only because it may mean that the scope of your issue is smaller than it might seem at first. Although the scope really will depend on how many views you have which have the cross-db references. [image] To get going, there are a few approaches you can take. I'm putting the simplest first. The final two options are more work but are great longer term. 1) If it turns out to be just a couple of views/functions and it's not important for you to have them in version control, you may simply filter them out and proceed without them. 2) You can create a schema-only copy of the dependent databases on your localdb instance -- potentially using SQL Compare to generate the script and deploy it. No data need be in dependent databases, SQL Server only needs to validate the schema. 3) You can restore databases or use clones, as described in this article. For the large data warehouse, you'd want something like our SQL Clone tool, an on-demand snapshot using SAN technology, or similar other data virtualization solution to make it fast -- OR if you have a scaled down copy of the DW which is used for development purposes, that could work, too. While this approach may seem like overkill, the reason it's wonderful in the long run is that you can immediately identify if indexes exist in the dependent database to support your queries, and if you've got a regular process to refresh the clones of dependent databases then you find out if there have been schema changes in them which impact you (even if that that depdendent database is not in version control or is managed by a team which is totally different than yours). 4) If you are also going to be adding the dependent databases into version control in the same solution, this article describe a "stubbing" approach to solving it. Hope this info helps! Kendra Edited to fix my weird formatting problem, then edited a second time to fix a bad link / comments

Hello,Thanks for your question. For stored procedures, they should all import/baseline successfully, but they will print a warning. This is because SQL Server has a feature called "deferred name r...

0 votes

How can we help you today?

Kendra_Little

Comments