Comments
Sort by recent activity
This can't quite be correct. The SubProduct table has a single PK (SubProductCode). When setting the FK for ForSale, you should get the error:
Msg 1776, Level 16, State 0, Line 23
There are no primary or candidate keys in the referenced table 'dbo.SubProduct' that match the referencing column list in the foreign key 'fk_ForSale_SubProductCode_within_ProductCode'.
Msg 1750, Level 16, State 1, Line 23
Could not create constraint or index. See previous errors.
The FK in ForSale needs to match the PK in SubProductCode. Both SubProductCode and ProductCode should be in the PK. The ordering needs to be the same.
However, if you have this DRI setup, then Data Generator will recognize the FK relationship and choose that. Since the FK will be on product and subproduct, the data will match. [image] / comments
This can't quite be correct. The SubProduct table has a single PK (SubProductCode). When setting the FK for ForSale, you should get the error:
Msg 1776, Level 16, State 0, Line 23
There are no prim...
Can you provide more schema DDL and sample data that explains this? Is this a compound FK? / comments
Can you provide more schema DDL and sample data that explains this? Is this a compound FK?
Can I ask if this is SSMS 16, 17 or something else? / comments
Can I ask if this is SSMS 16, 17 or something else?
This is a hack, but what I'd do for this case, which is a bit of an edge case, is cause production drift. I'd test this, but have the script executed in production first manually. Then I'd ensure my migration script is idempotent, so [image] if not exists( select fk_actionid from RoleAction where fk_roleid = 1000 and fk_actionid = 2028 and fk_permissionid = 3340 and enabled = 1)
INSERT INTO dbo.RoleAction
( FK_RoleId ,
FK_ActionId ,
FK_PermissionId ,
Enabled
)
VALUES ( 1000 , -- FK_RoleId - int
2028 , -- FK_ActionId - int
3340 , -- FK_PermissionId - int
1 -- Enabled - bit
)
Note, I'd be careful here with using static values. This is a place where the db development can go south. Ideally you'd insert using some other tokens, like the text or action, and let the ID values fall into place. If you're sure they're always in sync, then this can work, but usually this bites you later.
I realize this is a hack, and gets away from automation, but is this something you do often with migration scripts? / comments
This is a hack, but what I'd do for this case, which is a bit of an edge case, is cause production drift. I'd test this, but have the script executed in production first manually. Then I'd ensure m...
There isn't a good way to do this now. The ordering of scripts isn't something you can control. The only good way to do this is schedule two deployments, where you deploy static data changes ahead of the migration script.
May I ask what the migration script is doing? / comments
There isn't a good way to do this now. The ordering of scripts isn't something you can control. The only good way to do this is schedule two deployments, where you deploy static data changes ahead ...
DLM automation is the replacement for the SDK. What do you mean by the incremental update doesn't work? Do you not get an error report from DLM that helps? Or are you wanting the entire schema of the target (client) db? / comments
DLM automation is the replacement for the SDK. What do you mean by the incremental update doesn't work? Do you not get an error report from DLM that helps? Or are you wanting the entire schema of t...
The DLM cmdlets will verify the schema is as expected after deployment or they will return an error. If you really need to use a comparison, talk to sales/support about the SQL Compare command line. / comments
The DLM cmdlets will verify the schema is as expected after deployment or they will return an error. If you really need to use a comparison, talk to sales/support about the SQL Compare command line.
If you have named pipes for the instance, then you'd connect that way to each node. The listener is what won't receive (or acknowledge) a named pipes connection. You can connect to SQL02 with named pipes, since it's responding to that protocol.
If you use Windows Auth, then I'm guessing it either defaults to TCP or falls back to TCP. Was looking for documentation on this, but didn't see it. / comments
If you have named pipes for the instance, then you'd connect that way to each node. The listener is what won't receive (or acknowledge) a named pipes connection. You can connect to SQL02 with named...
Protocol. Only TCP works for the listener: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover#AGlisteners / comments
Protocol. Only TCP works for the listener: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover#AGlisteners
The trick with a view works. We hide system tables because there are so many they can clutter up the view, and because we can't deploy to them, this hasn''t been something we included in the product. / comments
The trick with a view works. We hide system tables because there are so many they can clutter up the view, and because we can't deploy to them, this hasn''t been something we included in the product.