CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

Catch MSSQL triggers in VB6

Hi
Is there a way to run VB code when a trigger is executed ? Maybe to define a VB event that will occur when a trigger is executed ??
I've tried googling...
Thanks,
Inon.The simplest way to run VB code from a trigger is to create a character mode executable (one with no GUI component). The trigger can start this running on the server using master.dbo.xp_cmdshell calls.

While it is possible to contrive an example where the server launches an application on the client, it is neither easy nor practical. I personally wouldn't try it.

-PatP