Closed Conversations Are Not Purged From The Receiver Endpoints Table
Nov 30, 2007
Hi,
I implemented the pattern suggested in the 'Recycling Conversations' article that Remus Resanu presented. Everything works great except ended conversations on the receiver remain in the sys.conversation_endpoints table forever in the 'CLOSED' state.
Is there some setting I am missing to have those conversations purged from the endpoints table. I am concerned that in the production environment this table will grow very large.
Thanks
View 2 Replies
ADVERTISEMENT
Dec 7, 2006
I hope someone can help me with this as we plan on using Service Broker in a high volume production environment. The script that builds everything is available if it's needed to diagnose the problem I'm having.
I'm having an issue where sys.conversation_endpoints on the target side of a conversation is never getting purged of closed conversations even after the 30 minute delay. The view is filled with closed conversations and database size is growing every day. I'm aware I can end conversation with cleanup on these conversations, but I would prefer that Service Broker behaves as expected. I'm also aware of the problems with the fire and forget model, but my model is request/response/end between 2 databases on the same server instance. Here's the typical series of events:
Initiator sends request
Target receives request
Target processes request
Target sends response
Initiator receives response
Initiator processes response
Initiator ends conversation
Target receives EndDialog message
Target ends conversation
Occasionally during the target's processing of a request, an exception is caught and the Target ends the conversation with an error:
Initiator sends request
Target receives request
Target processes request and recognizes error
Target ends conversation with error
Initiator receives EndDialog message
Initiator ends conversation
Here's the trace where Database ID 23 is initiator and 24 is target, no error:
EventClass
DatabaseID
TextData
EventSubClass
Broker:Conversation Group
23
1 - Create
Broker:Conversation
23
STARTED_OUTBOUND
11 - BEGIN DIALOG
Broker:Conversation
23
CONVERSING
1 - SEND Message
Broker:Message Classify
23
1 - Local
Broker:Conversation Group
24
1 - Create
Broker:Conversation
24
STARTED_INBOUND
12 - Dialog Created
Broker:Conversation
24
CONVERSING
6 - Received Sequenced Message
Broker:Activation
24
1 - Start
Broker:Conversation
24
CONVERSING
1 - SEND Message
Broker:Message Classify
24
1 - Local
Broker:Conversation
23
CONVERSING
6 - Received Sequenced Message
Broker:Activation
23
1 - Start
Broker:Conversation
23
DISCONNECTED_OUTBOUND
2 - END CONVERSATION
Broker:Conversation Group
23
2 - Drop
Broker:Message Classify
23
1 - Local
Broker:Conversation
24
DISCONNECTED_INBOUND
7 - Received END CONVERSATION
Broker:Conversation
23
CLOSED
10 - Received END CONVERSATION Ack
Broker:Conversation
24
CLOSED
2 - END CONVERSATION
Broker:Conversation Group
24
2 - Drop
Broker:Activation
23
2 - Ended
Broker:Activation
24
2 - Ended
Here are the typical records in the target sys.conversation_endpoints. These records never disappear:
Normal
With Error
conversation_handle
3FE27EE5-1E86-DB11-B009-000BDB714730
53E17EE5-1E86-DB11-B009-000BDB714730
conversation_id
0A432392-55F5-461B-87D5-0058795BC3AE
BCCDFA85-86A3-43B8-9648-24FFE5C0ED3F
is_initiator
0
0
service_contract_id
0
0
conversation_group_id
00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000
service_id
0
0
lifetime
2074-12-25 21:29:28.640
2074-12-25 21:29:28.000
state
CD
CD
state_desc
CLOSED
CLOSED
far_service
http://my.domain.com/schemas/test/Initiator/2006-12-07
http://my.domain.com/schemas/test/Initiator/2006-12-07
far_broker_instance
227D0898-0399-40E0-954B-C8B685EE415A
227D0898-0399-40E0-954B-C8B685EE415A
principal_id
5
5
far_principal_id
6
6
outbound_session_key_identifier
DEBEB4DB-D186-410B-9555-A34F8F5C9FE2
B82BB074-5AE5-4164-9D0B-53E364B0B52B
inbound_session_key_identifier
1DBAE307-5DFF-4050-9D94-71003D8BD058
57B5C7D8-9E8B-4614-9325-5AA30AED3670
security_timestamp
2006-12-07 18:45:52.763
1900-01-01 00:00:00.000
dialog_timer
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
send_sequence
1
1
last_send_tran_id
0x550800000000
0x700700000000
end_dialog_sequence
-1
1
receive_sequence
2
1
receive_sequence_frag
0
0
system_sequence
0
0
first_out_of_order_sequence
-1
-1
last_out_of_order_sequence
0
0
last_out_of_order_frag
0
0
is_system
0
0
View 9 Replies
View Related
Sep 25, 2006
We have been having a problem with service broker for quite a while now and searching on these forums and more generally on the web has not yielded any kind of answer...
Our application utilises service broker within a single database (there is no cross database, cross instance or cross server communication). There are approximately 12 queues which are used to varying degrees. A few of the queues have a throughput rate in the order of up to 100s per minute at peak periods.
In some scenarios we are able to make use of persistent conversations but the majority of messages are sent on their own conversation or in small batches on their own conversation.
Some time ago we found that the database was growing in size more than expected due to a build up of data in the service broker meta data internal tables, exposed via the sys.conversation_endpoints system view.
We identified a problem in the application that was preventing some conversations from being closed and have now fixed this.
However, we are still experiencing a build up in the service broker tables and sys.conversation_endpoints shows this is now due to a very large number of conversations in the "CLOSED" state.
I know that conversations are kept around to prevent replay attacks but thought they were supposed to be cleared after about 30 minutes. This is certainly not happening as we currently have conversations that were opened on the 18th September, a full week ago. We currently have about 350,000 closed conversations and this figure is increasing!
We have tried restarting the SQL Server instance with no effect.
I have been using a script to loop through all closed conversations and get rid of them using the "WITH CLEANUP" clause but I'm loath to create a scheduled task that does this in the background when service broker should be doing the job itself.
Has anybody experience this problem and, even better, have any idea how to solve it?
Daniel
View 12 Replies
View Related
Jan 5, 2006
Wierd problem here
As one user, when i select * from sys.conversation_endpoints I can see all (I assume) conversations in all states specifically DO, DI and CD
However when I change to another user I see only DI
Why is this?
If it is a permissions issue what permission do I have to grant to a user to see all conversations in sys.conversation.endpoints?
View 1 Replies
View Related
Feb 8, 2008
Hi,
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
Thanks
View 6 Replies
View Related
Jan 30, 2006
I am trying to test load balancing between multiple broker service instances. I have set up one sender and two receivers. When I tried sending a lot of messages from the sender, I noticed that all messages were being received by receiver 1 alone. While I am able to communicate between sender and receiver 1, I am not able to send message to the second receiver (I stopped the first receiver instance to find this out). I receive the "certificate not found" error in the Profiler for the second receiver. The code for my second receiver is very similar to the first one.
I am dumping in the full code down here. I appreciate if someone can figure out what is wrong. Thanks
Sender:
use [master];
go
create master key encryption by password = 'masterhello1';
go
create certificate TrpCertServ1
with subject = 'TrpCertServer1',
start_date = '06/01/2005';
go
--make sure the cert exist
select * from sys.certificates;
go
--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 2
BACKUP CERTIFICATE TrpCertServ1
TO FILE = 'c:amitOfficialService BrokercertsTrpCertServ1Pub.cer';
go
--you need to create an endpoint in order to enable communication
--outside of this instance
CREATE ENDPOINT SSB1
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4021
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ1,
ENCRYPTION = REQUIRED
);
go
USE master ;
GO
ALTER ENDPOINT SSB1
FOR SERVICE_BROKER ( MESSAGE_FORWARDING = ENABLED,
MESSAGE_FORWARD_SIZE = 10 ) ;
GO
--check that the endpoint has been created
select * from sys.endpoints;
go
--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin1
with password = 'remserver@1';
go
create user remconnuser1
from login remconnlogin1;
go
--grant connect to the endpoint to the login
grant connect on endpoint::ssb1 to remconnlogin1
go
--now is time to go over to server 2 and do similar tasks
--but first make sure that the cert you dumped out above can be
--available for server 2
--Step 2
-- you have now done the similar setup in server 2,
--and you should now do the final setup in master
--where you create a certificate from server 2's public
--cert and assigns it to the user created above
--uncomment from here to go and execute
create certificate TrpCertServ2Pub
authorization remconnuser1
from file = 'C:amitOfficialService BrokercertsTrpCertServ2Pub.cer';
go
--adding for new receiver
create certificate TrpCertServ3Pub
authorization remconnuser1
from file = 'C:amitOfficialService BrokercertsTrpCertServ3Pub.cer';
go
-- end of addition
use master;
go
--create the database
create database [rem_ssb1];
go
use [rem_ssb1];
go
--set master key
create master key
encryption by password = 'hellodb1';
create certificate DlgCertServ1Db1
with subject = 'DlgCertServ1Db1',
start_date = '06/01/2005'
active for begin_dialog = on;
go
--make sure the cert exist
select * from sys.certificates;
go
--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 2
BACKUP CERTIFICATE DlgCertServ1Db1
TO FILE = 'c:amitOfficialService BrokercertsDlgCertServ1Db1Pub.cer';
go
--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser1
without login;
go
--Step 2
create certificate DlgCertServ2Db2Pub
authorization remdlguser1
from file = 'c:amitOfficialService BrokercertsDlgCertServ2Db2Pub.cer';
go
-- adding content for new receiver
create certificate DlgCertServ3Db3Pub
authorization remdlguser1
from file = 'c:amitOfficialService BrokercertsDlgCertServ3Db3Pub.cer';
go
-- end of addition
use [rem_ssb1];
go
-- we need two message types
CREATE MESSAGE TYPE [sendmsg]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [recmsg]
VALIDATION = WELL_FORMED_XML;
go
--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go
--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q1
with status = ON;
go
--we need a service
CREATE SERVICE [rem_s1]
on queue q1
(
[Ctract]
);
go
--create a route to the remote service, we know it'll be called rem_s2
create route [rem_s2_route]
with
service_name = 'rem_s2',
address = 'TCP://127.0.0.1:4022';
go
--drop route [rem_s2_route]
-- adding for new route
create route [rem_s3_route]
with
service_name = 'rem_s2',
address = 'TCP://127.0.0.1:4023';
go
-- end of addition
select * from sys.routes
--as we'll be doing encrypted dialogs we need a remote service bindin
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s2'
WITH USER = remdlguser1,
ANONYMOUS=Off
--give the user send rights on the service
grant send on service::rem_s1 to remdlguser1;
go
use [rem_ssb1];
go
----start the dialog and send a message
----uncomment from here until the following go statement and run
DECLARE @h uniqueidentifier --conversation handle
DECLARE @msg xml; --will hold the message
BEGIN DIALOG CONVERSATION @h
FROM SERVICE rem_s1
TO SERVICE 'rem_s2'
ON CONTRACT [Ctract];
SET @msg = '<hello00/>';
SEND ON CONVERSATION @h
MESSAGE TYPE [sendmsg]
(@msg);
RECEIVER 1:
--Step 1
use [master];
go
create master key encryption by password = 'masterhello2';
go
create certificate TrpCertServ2
with subject = 'Transport Certificate for Server2',
start_date = '06/01/2005';
go
--make sure the cert exist
select * from sys.certificates;
go
--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 1
BACKUP CERTIFICATE TrpCertServ2
TO FILE = 'c:amitOfficialService Brokercerts2TrpCertServ2Pub.cer';
go
--you need to create an endpoint in order to enable communication
--outside of this instance
CREATE ENDPOINT SSB2
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ2,
ENCRYPTION = REQUIRED
);
go
--check that the endpoint has been created
select * from sys.endpoints;
go
--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin2
with password = 'pass1234$';
go
create user remconnuser2
from login remconnlogin2;
go
--grant connect on the endpoint to the login
grant connect on endpoint::SSB2 to remconnlogin2
go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser2
from file = 'c:amitOfficialService Brokercerts2TrpCertServ1Pub.cer';
--go back to server 1 and step 2 in the script 1_setup_sec_master_server1.sql
--make sure the public cert 'TrpCertServ2Pub.cer' is available
--from server 1
use master;
go
--create the database
create database [rem_ssb2];
go
use [rem_ssb2];
go
--set master key
create master key
encryption by password = 'hellodb2';
go
create certificate DlgCertServ2Db2
with subject = 'DlgCertServ2Db2',
start_date = '06/01/2005'
active for begin_dialog = on;
go
--make sure the cert exist
select * from sys.certificates;
go
BACKUP CERTIFICATE DlgCertServ2Db2
TO FILE = 'c:amitOfficialService Brokercerts2DlgCertServ2Db2Pub.cer';
go
create user remdlguser2
without login;
go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1
create certificate DlgCertServ1Db1Pub
authorization remdlguser2
from file = 'c:amitOfficialService Brokercerts2DlgCertServ1Db1Pub.cer';
use [rem_ssb2];
go
-- we need two message types
CREATE MESSAGE TYPE [sendmsg]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [recmsg]
VALIDATION = WELL_FORMED_XML;
go
--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go
--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q2
with status = ON;
go
--we need a service
CREATE SERVICE [rem_s2]
on queue q2
(
[Ctract]
);
go
--create a route to the remote service, we know it'll be called rem_s1
create route [rem_s1_route]
with
service_name = 'rem_s1',
--broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083',
address = 'TCP://127.0.0.1:4021';
go
--as we'll be doing encrypted dialogs we need a remote service binding
--and the user is the user we created in the 2_setup_objects_server2_db.sql scripts
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s1'
WITH USER = remdlguser2,
ANONYMOUS=Off
go
--give the user send rights on the service
grant send on service::rem_s2 to remdlguser2;
go
SELECT * from q2;
RECEIVER 2:
use [master];
go
--make sure master had master key
create master key encryption by password = 'masterhello2';
go
create certificate TrpCertServ3
with subject = 'Transport Certificate for Server3',
start_date = '06/01/2005';
go
--make sure the cert exist
select * from sys.certificates;
go
BACKUP CERTIFICATE TrpCertServ3
TO FILE = 'c:amitOfficialService Brokercerts3TrpCertServ3Pub.cer';
go
--you need to create an endpoint in order to enable communication
--outside of this instance
CREATE ENDPOINT SSB3
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4023
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ3,
ENCRYPTION = REQUIRED
);
go
--check that the endpoint has been created
select * from sys.endpoints;
go
--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin3
with password = 'pass1234$';
go
create user remconnuser3
from login remconnlogin3;
go
--grant connect on the endpoint to the login
grant connect on endpoint::SSB3 to remconnlogin3
go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser3
from file = 'c:amitOfficialService Brokercerts3TrpCertServ1Pub.cer';
use master;
go
--create the database
create database [rem_ssb3];
go
use [rem_ssb3];
go
--set master key
create master key
encryption by password = 'hellodb3';
go
create certificate DlgCertServ3Db3
with subject = 'DlgCertServ3Db3',
start_date = '06/01/2005'
active for begin_dialog = on;
go
--make sure the cert exist
select * from sys.certificates;
go
BACKUP CERTIFICATE DlgCertServ3Db3
TO FILE = 'c:amitOfficialService Brokercerts3DlgCertServ3Db3Pub.cer';
go
--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser3
without login;
go
--copy in the public cert from server 1 to somewhere on this server
--create a certificate from the public cert from server 1
create certificate DlgCertServ1Db1Pub
authorization remdlguser3
from file = 'c:amitOfficialService Brokercerts3DlgCertServ1Db1Pub.cer';
--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go
--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q2
with status = ON;
go
--we need a service
CREATE SERVICE [rem_s2]
on queue q2
(
[Ctract]
);
go
--create a route to the remote service, we know it'll be called rem_s1
create route [rem_s1_route]
with
service_name = 'rem_s1',
--broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083',
address = 'TCP://127.0.0.1:4021';
go
--as we'll be doing encrypted dialogs we need a remote service binding
--and the user is the user we created in the 2_setup_objects_server2_db.sql scripts
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s1'
WITH USER = remdlguser3,
ANONYMOUS=Off
go
--give the user send rights on the service
grant send on service::rem_s2 to remdlguser3;
go
select * from q2
View 7 Replies
View Related
Sep 27, 2006
How can my receiver's queue procedure execute a procedure on another server?
Everytime it gets to that remote procedure statement, I get DISCONNECTED_INBOUND status.
The procedure I want to call is on SQL 2000.
View 9 Replies
View Related
Apr 24, 2007
I am currently designing an auditing application using Service Broker. Right now, when I send a message from a trigger, I start a conversation, and later on when the message has been processed, the conversation has ended. One thing I am concerned with is that when a lot of updates are occurring on the system, if the amount of conversations being created will eat up system resources. Does it make sense to create them and end them later, or should I try to reuse them?
Tim
View 10 Replies
View Related
Mar 20, 2008
From a service broker newbie...
Most of the examples I've found and played with demonstrate two way conversation. A sender initiates a call, and gets a message back.
My Requirements doesn't really need two way communication. I have a scenario where triggers on two different tables result in modifications to a third table, and I don't want the triggers to deadlock each other, so an asynchronous queueing mechanism seems like the perfect solution...
But I can't seem to make it work one way.
I can get one message through, and then all subsequent messages hang up in the transmission queue with the very informative "One or more messages could not be delivered to the local service targeted by this dialog."
I'm thinking all the examples work the way they do because you have to notify the transmitter that the message was
received by sending a message back... and by not doing this I'm stuck in the first conversation. I was thinking that by doing END CONVERSATION <Msg Handle> in the stored procedure bound to the receiver's queue was doing that.
Do I have to communicate bi-directionally always? I guess this is a safety feature but I trust MSMQ to deliver messages...
Thx
View 3 Replies
View Related
Aug 26, 2006
Say I have a conversation established and the initiator server needs to reboot. Will the conversation automatically restart when the server comes back up? If not, can I get it to with some setting? If not, what is the best way to handle this?
Thanks - Amos
View 1 Replies
View Related
Sep 17, 2007
Hi -
In my application, I need to be able to guarantee that processing for a re-used conversation is completed prior to starting processing the next (re-used) conversation. My application is based on the concepts from the sample posted on Remus's blog: http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx#comments). Essentially (in this sample), we create a new conversation for each SPID and re-use the conversation, so that messages are sent through the queue (and processed in order) for each SPID. SPID was used in the sample code as an example of some application-specific "thing" that you care about message ordering for. To prevent a conversation from living forever (using up log/resources), they are ended after 1 hour using DialogTimer and a customer message type.
My conundrum is this:
Assume conversation 1 (on SPID 1) is flooded with a large number of messages just before the conversation timer expires. The DialogTimer then expires before the target queue is drained. The sample code (mentioned above) then creates a new conversation for the same SPID (with a DialogTimer of 1 hour). Until the queue for conversation1 is drained, we have 2 conversations being processed for the same SPID. This same problem would occur in any application where you re-use conversations for a period of time (using DialogTimer), and then start a new conversation when the DialogTimer expires.
So although I like having the idea fof being able to re-use conversations, I would need to guarantee that conversation 1 is finished processing before conversation 2 starts processing (for the same SPID, to be consistent with the sample above). If I could get these 2 conversations into the same conversation group on the target queue, the CG locking would solve the problem. But because conversation groups only apply to the initiator queue (when you begin dialog with related conversation), I have no "out of the box" way to control how the conversation groups are associated on the target queue. Remus posted an idea here (bottom of thread): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=182646&SiteID=1, which was to just send a special message at the beginning of each new conversation (containing the conversation group to use), and then doing a move conversation to conversationgroupid on the target queue. I've tried this solution, and the problem is 1.) if the set convo message fails for some reason, the conversation group is not set and 2.) if the target queue seems to reject most of my move conversation commands with the error "The destination conversation group '<conversation guid>' is invalid." - which I am guessing is due to the fact that this convo group id is being used on the initiator as well.
Any ideas?
Thanks!
Terryc
View 6 Replies
View Related
Aug 5, 2007
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations?
when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation.
But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle?
this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases.
Neither queues have any error messages in them.
Thanx
View 1 Replies
View Related
Dec 15, 2006
We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(
Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?
Thank you so much in advance,
John Hennesey
View 5 Replies
View Related
Mar 2, 2007
When I constructed my endpoint:
CREATE ENDPOINT MyNewEndPoint
STATE = STARTED
AS HTTP
(
PATH = '/MyEndpoint',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'arkansas'
)
FOR SOAP
(
WEBMETHOD 'GetSession'
(NAME='MyDB.dbo.spGetSession'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'MyDB',
NAMESPACE = 'http://MyDB/MyEndpoint'
)
the site is hardcoded. But, when I'm getting ready to deploy I'll need the site to be my production site. Is there an easier way to accomplish this then to edit the script each time before delpoyment?
TIA,
Dave C.
View 1 Replies
View Related
Jun 9, 2006
Are HTTP Endpoints able to be reached by another application that does not run on the same network?
For example, we have a partner that has a Web Application that would like to retrieve data from our database. We would like them to call a SOAP method through the Endpoint and in essence let their Web Application consume the Web Service. Is this possible?
View 1 Replies
View Related
Mar 5, 2007
I tried asking a similar question over at the asp.net, but I'm not getting any replies.
I created an endpoint in SS 2005 using DIGEST authentication, and I was successful in adding the web service to my project and getting results from a call to it.
However, the production environment does not exist in a domain environment, which eliminates even DIGEST (which requires a valid windows domain logon).
But, when I create the endpoint using BASIC authentication, I can no longer "find" the service. SS says the command(s) completed successfully after the Create Endpoint command. As a test, the documentation says that you can enter the http site into IE and the WSDL will display. And that works in digest mode. However, I've tried both:
http://<server>/path?WSDL and
https://<server>/path?WSDL
And neither returns the WSDL in IE (nor can it be added to my project as a web service).
I'm hoping someone has some ideas on how I can resolve this problem.
TIA,
Dave
View 1 Replies
View Related
Dec 13, 2007
Hi All,
I have mirroring set up in a test environment and it works great however I need to know/understand how the endpoints are encrypted. I have them set to use the AES algorithm; however I can not tell from BOL what keys they are using. I know the service master key is the root encryption key and endpoints sit at the server level (same level as service masterkey) but I am not sure if the endpoints use it to perform the encryption or not; do they and if not what do they use?
Thanks in advance,
Coleman
View 3 Replies
View Related
Mar 18, 2008
hi,
I am new to sql so i created this http endpoint int sql 2005.
now how to use it?
for example i create stored procedure and i can use it by "execute procedurename"
how it works on endpoint side?
View 1 Replies
View Related
Jul 3, 2007
Hi,
I am trying to use a SQL Endpoint in my SQL 2005 DB to retrieve information from my C#.Net program. It works fine for me when I run it, but I am set as an administrator in SQL Server. When I try to run it as a normal/test user, I get the error message: "There was an error in the incoming SOAP request packet: Client, Endpoint, UnmappedSoapMethod."
Now, I have set up the endpoint in SQL and I can see the method I am trying to call when I do a select on the sys.endpoint_webmethods table. I have also allowed the domain group which the test user is a member of execute permission to the stored procedures and Access permissions to the endpoint.
What have I missed in the set up for me to be receiving this error message?
Cheers and thanks in advance.
Sam.
View 7 Replies
View Related
May 18, 2007
Is it possible to return typed data in an Endpoint for an ASP.Net Web Reference to Proxy? If so, is there any specific terminology I should be aware of to target my search?
I realize there is a choice between Object (returns a dataset or error) or dataset, but the automatically generated WebReference Proxy in ASP.Net (2.0) is untyped and we can't change the typing there as you have to remove the entire WebReference to pick up new WebMethods (or changes to signitures I'd assume).
I'm able to create my own typed proxy as a psuedo-DAL assembly which takes the WebReference and cast rows/objects into typed rows/objects one at a time, but this seems like a lot of work and probobly not the best practice.
Any help would be greatly appreciated
View 1 Replies
View Related
Dec 20, 2006
I am trying to expose a stored procedure on SQL Server 2005 as a web service. So I just have an end point created with the procedure as my web method. Now when I try to reference this web service in my C# windows application, I am only able to see the procedure definition by requesting for the WSDL http://servername/path?wsdl. But when I try to reference it, I get the error 501/505 - Not Implemented or not supported. Any ideas what the issue here could be? Would really appreciate the help here as I am kinda stuck! - Thanks, Manmeet
View 2 Replies
View Related
Mar 20, 2007
Hi,
I'm looking into hosting SQL Server endpoints on Vista. Unfortunately they don't work nicely because, as far as I know, they require http.sys which is part of Windows Server 2003. Hence, I can create my endpoint but i can't browse to it over http.
So, siple question (not so simple answer) how can I access SQL Server endpoints on Vista?
Thanks
Jamie
View 4 Replies
View Related
Jun 20, 2007
i have posted here a few times on my endpoint issues but i cant find a resolution so im approaching the issue from a different angle. can you guys tell me how you create and consume endpoints, preferably with the endpoint code, the url you use to see the wsdl, and any network/pc settings you may have altered to get endpoints to work. im looking for tips or steps that may not be included in basic endpoint tutorials. I really am stuck and have no idea what else to try. thanks all
View 3 Replies
View Related
Mar 11, 2008
I have application who like to use HTTP Endpoints in our cosolidate enviornment. How I can measure the performance on HTTP Endpoints? I would like to know how much CPU, Memory and IO used by HTTP Endpoints?
Any suggestion? Thanks in advance.
View 1 Replies
View Related
Mar 23, 2008
Has anyone worked out how to trace SOAP/HTTP Endpoints in SQL 2005 SP 2?
I'm not having a particular problem, just want to know how things work, such as which layer is responsible for the HTTP Authentication. I've traced the HTTP.sys stack using logman.exe (see http://www.thelastpickle.com/2008/03/22/tracing-sql-soap-endpoints-through-httpsys/) .
I cannot see any SQL Profiler events, and I've tried setting trace 7801 and 7803 as described in http://www.sqljunkies.com/Article/5CCAC423-1407-4A36-AF71-ED6A67D9646A.scuk#_Toc52964195 but they do not result in any logging.
thanks
Aaron
View 3 Replies
View Related
Mar 8, 2008
All -
I have a SQL Server 2005 HTTP endpoint. I create a transaction scope in a client application and call two methods on that endpoint. Will the operations performed in the two methods, really, be in a transaction?
In other words, will SQL Server enlist in the ambient transaction even when I am accessing it through a HTTP endpoint?
Thanks.
View 4 Replies
View Related
May 23, 2007
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
CallWebService.Credentials = System.Net.CredentialCache.DefaultCredentials
Call CallWebService.IntelisisService(ArchivoOriginal)
My class rperez.IntelisisServiceEndPoint is referenced (hardcoded) in the dll project I need it dynamic
Do I need to change my code to PROXY object ?
View 1 Replies
View Related
Aug 23, 2006
We have tested mirroring with success.
Now we want to completly disable mirroring, and have done this on all mirrored database.
It seems though that the server stille maintains the mirror endpoints, hence you can do a "telnet localhost 5022".
How can we disable the mirroring endpoints ?
Martin Tan
SQL DBA
View 1 Replies
View Related
Sep 1, 2015
I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "
Same problem facing on different servers.
Note : Environment is SQL 2012,SQL 2008 including cluster servers .
View 2 Replies
View Related
Feb 17, 2008
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I look forward to any response. Thanks!
View 5 Replies
View Related
Sep 12, 2007
thx but this code not needed anymore
sry
View 1 Replies
View Related
Jun 7, 2008
I have an sp, which has 2 select statements, so iam using a sqldatareader and binding the data to a dropdown.
the first binding is fine, but when i say dataReader.NextResult(), It is null.It says the reader is closed. Can any one tell a work around for this.
thanx in advance,
Anil Kumar.
View 6 Replies
View Related
