Today, I read the SQL Server Bible online help to design the following problems during SQL Server 2000 replication. Finally, I came to the conclusion that the SQL database engine cannot correctly parse or not parse the SQL Server database. 1. Create a copy table: -- Create a table sales on the Publishing Server:
Create Table Sales
(Sale_id Int Identity ( 1 , 1 )
Not For Replication,
Sales_region Varchar ( 20 ),
Constraint Id_pk Primary Key (Sale_id)
)
-- Create a table sales on the subscription Server:
Create Table Sales
(Sale_id Int Identity ( 100001 , 1 )
Not For Replication ,
Sales_region Varchar ( 2 ),
Constraint Id_pk Primary Key (Sale_id)
)
2. create a transaction replication and non-updatable subscription. All others use the default settings. Only change the publishing attribute "Name Conflict" to "delete data that matches the existing table and row filter statement ", to prevent deletion of manually created tables and identity column rules. An error occurred while modifying the server when inserting new data or deleting old data: A. error message: {call sp_msupd_sales (null, 'afg2342342', 2,
0x02 )}
The transaction serial numbers and command IDs of the last batch processing were 0x0000006100000082000500000000 and 1, respectively. B. Last life: the 'sale _ id' Column cannot be updated '. C. Error details: the 'sale _ id' Column cannot be updated '.
(Source: dev1 (data source); Error Code : 8102)
3. Copy the storage process of the automatically created subscription server as follows. I commented on the error:
Create Procedure "Sp_msupd_sales"
@ C1 Int , @ C2 Varchar ( 20 ), @ Pkc1 Int
, @ Bitmap Binary ( 1 )
As
If Substring ( @ Bitmap , 1 , 1 ) & 1 = 1 -- When the condition is no, the else statement block should be executed
Begin
Update "Sales" Set
"Sale_id" = Case Substring ( @ Bitmap , 1 , 1 ) & 1 When 1 Then @ C1 Else "Sale_id" End -- Its syntax is completely correct. The stored procedure has been created and the ID column cannot be updated. Because @ bitmap = 0x02, this statement block should not be executed, however, since the statement is correctly analyzed and not executed, it is still parsed during execution ,???
, "Sales_region" = Case Substring ( @ Bitmap , 1 , 1 ) & 2 When 2 Then @ C2 Else "Sales_region" End
Where "Sale_id" = @ Pkc1
If @ Rowcount = 0
If @ Microsoftversion > Zero X 07320000
Exec Sp_msreplraiserror 20598
End
Else
Begin
Update "Sales" Set
"Sales_region" = Case Substring ( @ Bitmap , 1 , 1 ) & 2 When 2 Then @ C2 Else "Sales_region" End
Where "Sale_id" = @ Pkc1
If @ Rowcount = 0
If @ Microsoftversion > Zero X 07320000
Exec Sp_msreplraiserror 20598
End
Go
Finally, we come to the conclusion that it is not a problem of copying components. The problem is that the database engine cannot correctly parse or not parse the component at the right time. Simply try the following code (first analyze and then execute ):
Create Table Test
(
ID Int Identity ( 1 , 1 ) Not For Replication ,
Content Varchar ( 20 ),
Constraint Id_pk Primary Key (ID)
)
Go
If 1 = 0
Begin
Print ' It can not be. '
Update Test Set ID = ID, content = Content -- where 1 = 0
End
Else
Begin
Print ' Right. '
Update Test Set Content = Content
End
