 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Service Broker / March 2007Pls Help: SP 2 broke my Service Broker
Hello Please help. It's a major this code produces two different results on SP1 and SP2 DECLARE @ConversationHandle uniqueidentifier , @TargetConversationHandle uniqueidentifier ,@ConversationID uniqueidentifier , @message_type_id int BEGIN TRANSACTION ; -- Begin the dialog. BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//2020technologies.com/SSB/Services/inResponseConsoleQueueService] TO SERVICE N'//2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService', 'CURRENT DATABASE' ON CONTRACT [//2020technologies.com/SSB/Contracts/inResponseConsoleContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle; SET @ConversationID = ( select ce2.Conversation_ID FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_handle = @ConversationHandle AND is_initiator = 1 ); select @ConversationID as ConversationID select * FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_id = @ConversationID rollback On SP 1, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 55ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 0 65537 54ABFD1D-0EC9-DB11-B25E-00137284088D 65539 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 2007-03-02 23:04:32.743 1900-01-01 00:00:00.000 0 0x000000000000 -1 1 0 0 -1 0 0 0 52ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 1 65537 53ABFD1D-0EC9-DB11-B25E-00137284088D 65538 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x38F50F000000 -1 0 0 0 -1 0 0 0 on SP 2, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 0E9A6126-0EC9-DB11-966E-000F1F0C79EB FA109B1D-E00A-48B7-9B83-DFC4938B1A86 1 65537 0F9A6126-0EC9-DB11-966E-000F1F0C79EB 65538 2075-03-21 01:48:22.820 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService A167AE0D-E63E-4EF8-AF21-ECC64ABE680D 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x89280F000000 -1 0 0 0 -1 0 0 0 Why? Please help! Sp2 trace, if it helps. I will send Sp1 too in the next message Hello Please help. It's a major this code produces two different results on SP1 and SP2 DECLARE @ConversationHandle uniqueidentifier , @TargetConversationHandle uniqueidentifier ,@ConversationID uniqueidentifier , @message_type_id int BEGIN TRANSACTION ; -- Begin the dialog. BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//2020technologies.com/SSB/Services/inResponseConsoleQueueService] TO SERVICE N'//2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService', 'CURRENT DATABASE' ON CONTRACT [//2020technologies.com/SSB/Contracts/inResponseConsoleContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle; SET @ConversationID = ( select ce2.Conversation_ID FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_handle = @ConversationHandle AND is_initiator = 1 ); select @ConversationID as ConversationID select * FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_id = @ConversationID rollback On SP 1, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 55ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 0 65537 54ABFD1D-0EC9-DB11-B25E-00137284088D 65539 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 2007-03-02 23:04:32.743 1900-01-01 00:00:00.000 0 0x000000000000 -1 1 0 0 -1 0 0 0 52ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 1 65537 53ABFD1D-0EC9-DB11-B25E-00137284088D 65538 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x38F50F000000 -1 0 0 0 -1 0 0 0 on SP 2, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 0E9A6126-0EC9-DB11-966E-000F1F0C79EB FA109B1D-E00A-48B7-9B83-DFC4938B1A86 1 65537 0F9A6126-0EC9-DB11-966E-000F1F0C79EB 65538 2075-03-21 01:48:22.820 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService A167AE0D-E63E-4EF8-AF21-ECC64ABE680D 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x89280F000000 -1 0 0 0 -1 0 0 0 Why? Please help! Hello Please help. It's a major this code produces two different results on SP1 and SP2 DECLARE @ConversationHandle uniqueidentifier , @TargetConversationHandle uniqueidentifier ,@ConversationID uniqueidentifier , @message_type_id int BEGIN TRANSACTION ; -- Begin the dialog. BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//2020technologies.com/SSB/Services/inResponseConsoleQueueService] TO SERVICE N'//2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService', 'CURRENT DATABASE' ON CONTRACT [//2020technologies.com/SSB/Contracts/inResponseConsoleContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle; SET @ConversationID = ( select ce2.Conversation_ID FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_handle = @ConversationHandle AND is_initiator = 1 ); select @ConversationID as ConversationID select * FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_id = @ConversationID rollback On SP 1, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 55ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 0 65537 54ABFD1D-0EC9-DB11-B25E-00137284088D 65539 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 2007-03-02 23:04:32.743 1900-01-01 00:00:00.000 0 0x000000000000 -1 1 0 0 -1 0 0 0 52ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 1 65537 53ABFD1D-0EC9-DB11-B25E-00137284088D 65538 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x38F50F000000 -1 0 0 0 -1 0 0 0 on SP 2, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 0E9A6126-0EC9-DB11-966E-000F1F0C79EB FA109B1D-E00A-48B7-9B83-DFC4938B1A86 1 65537 0F9A6126-0EC9-DB11-966E-000F1F0C79EB 65538 2075-03-21 01:48:22.820 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService A167AE0D-E63E-4EF8-AF21-ECC64ABE680D 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x89280F000000 -1 0 0 0 -1 0 0 0 Why? Please help! You are looking for the target endpoint before you commited the SEND transaction. This is now allowed, as it will return random results depending on wheter the SEND has succeeded in-line or had defered the message trough the transmission_queue. Never do this in an application.  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. HTH, ~ Remus Rusanu SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx Hello Please help. It's a major this code produces two different results on SP1 and SP2 DECLARE @ConversationHandle uniqueidentifier , @TargetConversationHandle uniqueidentifier ,@ConversationID uniqueidentifier , @message_type_id int BEGIN TRANSACTION ; -- Begin the dialog. BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//2020technologies.com/SSB/Services/inResponseConsoleQueueService] TO SERVICE N'//2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService', 'CURRENT DATABASE' ON CONTRACT [//2020technologies.com/SSB/Contracts/inResponseConsoleContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle; SET @ConversationID = ( select ce2.Conversation_ID FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_handle = @ConversationHandle AND is_initiator = 1 ); select @ConversationID as ConversationID select * FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_id = @ConversationID rollback On SP 1, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 55ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 0 65537 54ABFD1D-0EC9-DB11-B25E-00137284088D 65539 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 2007-03-02 23:04:32.743 1900-01-01 00:00:00.000 0 0x000000000000 -1 1 0 0 -1 0 0 0 52ABFD1D-0EC9-DB11-B25E-00137284088D D04BA85D-0464-4D12-9DC9-EBB1D702EB74 1 65537 53ABFD1D-0EC9-DB11-B25E-00137284088D 65538 2075-03-21 01:48:08.743 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService 04FCCD56-1222-42AC-9B33-1E7A23306715 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x38F50F000000 -1 0 0 0 -1 0 0 0 on SP 2, it produces conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system 0E9A6126-0EC9-DB11-966E-000F1F0C79EB FA109B1D-E00A-48B7-9B83-DFC4938B1A86 1 65537 0F9A6126-0EC9-DB11-966E-000F1F0C79EB 65538 2075-03-21 01:48:22.820 CO CONVERSING //2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService A167AE0D-E63E-4EF8-AF21-ECC64ABE680D 1 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1 0x89280F000000 -1 0 0 0 -1 0 0 0 Why? Please help! this does not work either. Now I commit. DECLARE @ConversationHandle uniqueidentifier , @TargetConversationHandle uniqueidentifier ,@ConversationID uniqueidentifier , @message_type_id int BEGIN TRANSACTION ; -- Begin the dialog. BEGIN DIALOG CONVERSATION @ConversationHandle FROM SERVICE [//2020technologies.com/SSB/Services/inResponseConsoleQueueService] TO SERVICE N'//2020technologies.com/SSB/Services/inResponseWorkerConsoleQueueService', 'CURRENT DATABASE' ON CONTRACT [//2020technologies.com/SSB/Contracts/inResponseConsoleContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [//2020technologies.com/SSB/Messages/inResponseConsoleAdmin] ('boo'); COMMIT SET @ConversationID = ( select ce2.Conversation_ID FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_handle = @ConversationHandle AND is_initiator = 1 ); select @ConversationID as ConversationID select * FROM sys.conversation_endpoints ce2 WITH (NOLOCK) WHERE ce2.conversation_id = @ConversationID --rollback > You are looking for the target endpoint before you commited the SEND > transaction. This is now allowed, as it will return random results [quoted text clipped - 109 lines] > > Why? Please help! I would expect it to function as in SP1. Why is such change in behavior? > You are looking for the target endpoint before you commited the SEND > transaction. This is now allowed, as it will return random results [quoted text clipped - 109 lines] > > Why? Please help! Now you can see the difference. On one of your machines, the message is being delivered and the target is being created. On the other, it is not. This has nothing to do with SP1 vs. SP2, you need to investigate why the message is not being delivered in one of the cases. Start by looking into sys.transmission_queue.transmission_status and see why the messages are being delayed. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. HTH, ~ Remus Rusanu SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx >I would expect it to function as in SP1. > Why is such change in behavior? [quoted text clipped - 112 lines] >> >> Why? Please help! THANK YOU! THANK YOU! THANK YOU! You are very smart! You lead me to the answer! the error was An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'inSight_Demo_4_1_Dev'. You should correct this situation by resetting the owner of database 'inSight_Demo_4_1_Dev' using the ALTER AUTHORIZATION statement. so I entered , for now, ALTER AUTHORIZATION ON DATABASE::inSight_Demo_4_1_Dev TO TAZ\SQLService] -- service account then I tried the service account and it worked too. ALTER AUTHORIZATION ON DATABASE::inSight_Demo_4_1_Dev TO [NT AUTHORITY\SYSTEM] and it works too. ALTER AUTHORIZATION ON DATABASE::inSight_Demo_4_1_Dev TO [SA] this works too. Which is better in your opinion? I am not sure. Please share your opinion. Thank you for your help. I am very greatful. > Now you can see the difference. On one of your machines, the message is > being delivered and the target is being created. On the other, it is not. [quoted text clipped - 122 lines] >>> >>> Why? Please help! I prefer to have sa as database owners because if you move databases the owner will still exist as a login in master. Sa has the same SID in all SQL Servers. I'd be interested to hear, though, whether this can be considered a security risk... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > THANK YOU! THANK YOU! THANK YOU! > [quoted text clipped - 141 lines] >>>> >>>> Why? Please help! I wouldn't see it as a security risk. If they have a copy of your database and enough permissions to attach it. they can get access to it no matter who owns it just by change who the dbo is. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm >I prefer to have sa as database owners because if you move databases the >owner will still exist as a login in master. Sa has the same SID in all SQL [quoted text clipped - 170 lines] >>>>> >>>>> Why? Please help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.