Target Sys.conversation_endpoints Not Purged Of Closed Conversations

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


ADVERTISEMENT

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 View Related

Why Is Sys.conversation_endpoints Filling Up Even When Reusing Dialog Conversations

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

Closed Conversation Endpoints Not Being Purged

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

Viewing Closed Conversations

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

Sys.conversation_endpoints

Oct 21, 2005

Is there anyway to figure out which tables/objects this view uses?

View 11 Replies View Related

Sys.Conversation_Endpoints

Oct 12, 2006

I know that if a conversation is normally ended the handle will wind up in the sys.Conversation_endpoints table in a Closed State. I realize they are supposed to stay there for 30 minutes to prevent a reply attack, however the number of rows I have in this table continues to grow with the bulk of the states set to 'Closed'. I am trying to use this table to determine if I have any conversation handle leaks. I see some rows in there with a Disconnected Outbound state while some are conversing. What should I be looking for in this table and how can I know I have a problem (ie. leak). I realize that Disconnected Outbound is probably something i need to look into. Are there any other states I should be concerned with?

Gary

View 9 Replies View Related

More Conversation_endpoints

Oct 26, 2006

So I took the time to build a reproduction of the conversation_endpoint problem that was discussed in another thread. I build two databases, with a send and receive queue. This is essentially the way the code works here at my site. I have a script near the bottom that sends messages every 5 minutes for 2 hours. If there is any logic that removes conversation_endpoints 30 min then the Message record table will show them.

Please let me know what I am doing wrong, so I can change my production code to help eliminate the large buildup in the sys.conversation_endpoints.

Thanks!



use master

go

if exists ( select * from sys.databases where name = 'SBSource' )

drop database SBSource

go

if exists ( select * from sys.databases where name = 'SBTarget' )

drop database SBTarget

go

-- Setup environment for test

create database SBSource

GO

ALTER DATABASE SBSource SET ENABLE_BROKER

ALTER DATABASE SBSource SET TRUSTWORTHY ON

GO

create database SBTarget

GO

ALTER DATABASE SBTarget SET ENABLE_BROKER

ALTER DATABASE SBTarget SET TRUSTWORTHY ON

GO

use SBSource

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[SourceQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBSourceTest] authorization [dbo]

ON QUEUE [dbo].[SourceQueue]

( [Test] );

CREATE ROUTE [ToTarget] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBTargetTest' , ADDRESS = N'LOCAL';

GO

create procedure dbo.ProcessEndDialogMessages

as

begin

set nocount on;

declare @conversation_handle uniqueidentifier,

@message_type sysname,

@message_body xml;

begin transaction;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

while @conversation_handle IS NOT NULL

begin

end conversation @conversation_handle;



commit;

begin transaction;

set @conversation_handle = null;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

end



commit;

end

go

Alter QUEUE dbo.[SourceQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessEndDialogMessages, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go



use SBTarget

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[TargetQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBTargetTest] authorization [dbo]

ON QUEUE [dbo].[TargetQueue]

( [Test] );

CREATE ROUTE [ToSource] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBSourceTest' , ADDRESS = N'LOCAL';

GO

create table dbo.MessageRecord ( Conversation_handle uniqueidentifier, Inserted datetime )

go

create procedure dbo.ProcessTargetQueue

as

begin

set nocount on;

declare @conversation_handle uniqueidentifier,

@message_type sysname,

@message_body xml;

begin transaction;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

while @conversation_handle IS NOT NULL

begin

insert into dbo.MessageRecord ( Conversation_handle, Inserted ) values ( @conversation_handle, getdate() );

end conversation @conversation_handle;



commit;

begin transaction;

set @conversation_handle = null;

WAITFOR (

RECEIVE @conversation_handle = [conversation_handle],

@message_type = [message_type_name],

@message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

end



commit;

end

GO

Alter QUEUE dbo.[TargetQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use sbsource

go



-- start sending messages, check count in conv_endpoints along the way

set xact_abort on

set nocount on

declare @EndAt datetime,

@msg xml,

@ch uniqueidentifier;

set @EndAt = DATEADD( hh, 2, getdate() )

set @msg = '<message>dfsafa</message>';

while getdate() < @EndAt

begin

set @ch = null;

begin transaction;

begin dialog conversation @ch

from service [SBSourceTest]

to service 'SBTargetTest'

on contract [Test]

with

encryption=off;

send on conversation @ch message type [msgTest] (@msg);

--- note the abscence of an end conversation, so no fire and forget!

commit;

waitfor delay '00:05:00'

end

GO

-- check on data after complete.

select state, count(*)

from SBSource.sys.conversation_endpoints

group by state;

select state, count(*)

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

group by state;

select m.*, c.*

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

select *

from SBTarget.dbo.targetqueue

View 1 Replies View Related

Why Are Sys.transmission_queue And Sys.conversation_endpoints Filling Up??

Jul 25, 2007

hi all!



I have 2 instances that communicate via service broker.

The conversations are only one way from initiator server1 db to target server2 db.

I also reuse dialog id's in BEGIN DIALOG @dlgId

i save @dlgId from the first run into a table and then retreive it for each message send

since the messages are constant i don't close the dialog at the target for each message.



i'm just wodering why do both sys.transmission_queue and sys.conversation_endpoints get a row

for each message i send but the transmission_status in sys.transmission_queue is emtpy.



also each conversation_handle and conversation_id is different for each row and

only one row in each sys table has the same conversation_handle as my saved @dlgId.



just wondering what is going on.



this code is done on the initiator



DECLARE @dlgId UNIQUEIDENTIFIER

-- each database has one dialog id

SELECT @dlgId = DialogId

FROM dbo.Dialogs

WHERE DbId = DB_ID()

-- Begin the dialog, either with existing or new Id

BEGIN DIALOG @dlgId

FROM SERVICE [//DataSender] -- service on initiator server

TO SERVICE '//DataWriter', -- service on target server

-- Target's Service Broker Id

'83382A22-2830-4B25-B067-15AAC255EB03'

ON CONTRACT [//Contract1]

WITH ENCRYPTION = OFF;

-- Send data

;SEND ON CONVERSATION @dlgId

MESSAGE TYPE [//Message1] (@msg)



Thanx,

Mladen

View 7 Replies View Related

Placement Of Sys.conversation_endpoints And Sys.transmission_queue

Dec 21, 2006

Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.

View 8 Replies View Related

Conversations

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

Cannot See Data From SYS.CONVERSATION_ENDPOINTS Over A Linked Server

May 21, 2007

Hello,



I have a server that is used to query other servers to ensure that things are functioning correctly. On some of our servers we are running ServiceBroker and some of the monitoring involves querying the SYS.CONVERSATION_ENPOINTS view on these servers.

When I query the SYS.CONVERSATION_ENPOINTS view over a linked server it returns zero rows, though when I run the same query locally it returns data. I initially thought it was a permission issue but it seems I can see data across the linked server to other system views within the sys schema (ie sys.all_objects).



Is there something different/special about SYS.CONVERSATION_ENDPOINTS that prevents me from seeing its data across linked servers or have I simply got permission problems.



Thanks



Ian

View 2 Replies View Related

Do All Conversations Have To Be Bi-directional?

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

Conversations &&amp; Machine Restart

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

Need A Way To Guarantee Message Ordering When Re-using Conversations

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

End 35 Million Conversations Quickly - Production Issue!

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

Recycling Conversations - Locking When Trying To Insert New Conversation Handle To SessionConversations Table

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

Closed

Sep 12, 2007

thx but this code not needed anymore
sry

View 1 Replies View Related

SQLdataReader Getting Closed

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

Object Has Been Closed

May 5, 2004

i am running a java program in tomcat to connect SQL Server,using the Microsoft's jdbc driver ,as the following code :

import java.sql.*;
class Bkjz{
ResultSet rs=null;
String spde;
String condition;
Connection con=null;
Statement sql;
public String getSpde(){
return spde;
}
public void setSpde(String spde){
this.spde=spde;
}
public ResultSet Searchsjk(){
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=zkbm","zkbm","zkbm");
sql=con.createStatement(ResultSet.TYPE_SCROLL_SENS ITIVE,ResultSet.CONCUR_READ_ONLY);
if(spde.equals(""))
condition="select CRCC,CRNM,SPDE,SPNM from SPCR where EMTP='5'group by SPDE,SPNM,CRCC,CRNM";
else
condition="select CRCC,CRNM,SPDE,SPNM from SPCR where SPDE='"+spde+"'"+"and EMTP='5' group by SPDE,SPNM,CRCC,CRNM";
rs=sql.executeQuery(condition);
//con.close();
if(!rs.next())
{
return null;
}
else
{
rs.previous();
return rs;
}



}
catch(Exception e){
message="exception!!! "+e.toString();
System.out.println(e);
return null;
}



}
}
public class Bk{
public static void main(String args[]){
Bkjz bbb=new Bkjz();
bbb.setSpde("1020110");
try{
ResultSet rr=bbb.Searchsjk();

while(rr.next()){
System.out.println(rr.getString("CRCC"));
}
}
catch(Exception e){
System.out.println(e);
}
}
}


without con.close(),it can return Resultset ,but when includeing con.close(),an Exception tell me:Object has been closed, in other programms i've close connection,but it never throws this Exception,why? thanks

View 1 Replies View Related

The Connection Gets Closed Right After It Opens.

Jan 27, 2007

I have the Function, that fires from onLoad even of one of the asp:Label controls on my main page.
Here is it's code:
 SqlConnection conn = new SqlConnection();                conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["UkraineConnectionString"].ToString();                SqlCommand comm = new SqlCommand("SELECT [Greeting] FROM [Misc]", conn);
        try        {            conn.Open();        }        catch        {            Response.Write("Error opening connection in Page_Load of default.aspx to retrieve the greetings");        }
        string MyGreet = (string)comm.ExecuteScalar();
        Greetings.Text = MyGreet;
        try        {            conn.Close();        }        catch        {            Response.Write("Error closing connection in Page_Load of default.aspx after retrieving the greetings");        }        }
When it gests to conn.Open() in the debugging mode I see that the ServerVersion = 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException'.
The most interesting thing is that it used to work before.
Here is the connection string it retrieves fine.
"Data Source=MDM1;AttachDbFilename=|DataDirectory|Ukraine.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"
 As I said it used to work, but one day I tried to access the web site and it said this error that I get, that the connection is closed. Then I was using the SQL Server Express. When in the Visual studio if I would run this same site in debug, everything was working fine. I decided to uninstall the SQL Express and installed the SQL Server.
If I open the SQL Server Managment Studio in the Server name field I see MDM1(this is the name of the PC, but probably it is the same name for the Server. Well, in the MAnagment Studio it conects fine to the MDM1 so it is no probably the naming problem. Ithink it has something to do with permisssions.
If someon can - please help. Thanks.

View 3 Replies View Related

Database Thread Closed?

Jan 30, 2007

Hello,
I have built a system, that uses a .dll file for all SQL operations. So a program looks something like this:
using myDLL;
....
SQLDBCON mSQL = new SQLDBCON();
and here comes the rest of the program.
My question is, the dll file has all stored procedures and when you declare mSQL as shown above, then it opens a thread to the database for that user.
Is that thread properly closed when the site has finished loading?
in my .aspx page i dont have a function like mSQL.CloseDB();
and if i try to add something like this to the dll file
~SQLDBCON {
m_local_con.Close();m_local_con.dispose();
}
i get a error message says something that this is not allowed.
Just want to know if my thread in the dll file is properly closed?

View 9 Replies View Related

Connection ODBC Closed

Jan 13, 2000

That is an app ACCESS 2000 wih Named Pipe ODBC to SQL Server 6.5.

After 10 minutes without use this app, the connection closed !

Have-you idea for correct this probleme ?
Regards
Alain

View 2 Replies View Related

Object Closed Error

May 3, 2006

I'm getting object closed when returning a recordset from a stored procedure. I've tested the select statement in Query Analyzer and a value is getting returned. So, I'm not sure if my code for my stored procedure is incorrect?

Here is the code for the procedure:

CREATE Procedure dbo.GetRepEmailByZip

@sessionid varchar(50),
@zip varchar(5)

AS

Begin Transaction

INSERT INTO dbo.SupportRequests(firstname,lastname,schoolname, address,city,state,zip,phone,email,currentcustomer ,implementationtype,producttype,comment)
(SELECT FirstName,LastName,SchoolName,Address,City,State,Z ip,Phone,Email,CurrentCustomer,ImplementationType, ProductType,Comment
FROM dbo.Temp_ContactInfo
WHERE sessionid = @sessionid)


--If Transacation fails, stop execution of procedure, return error code and Rollback Transaction

IF @@ERROR<>0 OR @@RowCount = 0
BEGIN
ROLLBACK TRANSACTION
--return value
RETURN 1
END

--If Transaction succeeds, commit transaction, continue and process the select statement
COMMIT TRANSACTION

SELECT r.email
FROM PostalCodes p
INNER JOIN TerritoryList z ON p.ZipID = z.ZipID
INNER JOIN RepList r ON r.RepID = z.RepID
WHERE p.ZipCode = @zip
GO



This is the code I'm calling to execute the procedure and return the recordset:


set GetRepEmail = Server.CreateObject("ADODB.Command")
With GetRepEmail
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.GetRepEmailByZip"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4)
.Parameters.Append .CreateParameter("@sessionid", 200, 1,50,usrid)
.Parameters.Append .CreateParameter("@zip", 200, 1,5,zip)
set RepEmail = .Execute()
End With

Dim x, y
x = RepEmail.RecordCount
y = RepEmail.State
Response.Write(x)
Response.Write("<br>")
Response.Write(y)
'Response.Write(RepEmail("email"))
Response.End()


I'm getting "Operation is not allowed when the object is closed", which is occuring on the following line:

x = RepEmail.RecordCount

I'm trying to determine if this is problem within my procedure or in the application code.

Thanks in advance for any help.

View 4 Replies View Related

Ensuring All Connections Are Closed.

Feb 22, 2007

In a previous post I asked the question relating to moving a file assoicated with a connection. It appears I need to close the connection first.

 

On advice, in a script task I created the following:

 

 

Dim dtsConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager

For Each dtsConnection In Dts.Connections

Dim LiveConnection As Object = dtsConnection.AcquireConnection(Nothing)

Dts.Events.FireInformation(0, "", "Connection Name : " + dtsConnection.Name, "", 0, False)

dtsConnection.ReleaseConnection(LiveConnection)

dtsConnection.Dispose()

Next

Dts.TaskResult = Dts.Results.Success

 

RetainSameConnection is set to true. 

 

The dispose line is something I've added just to try - I've tried it without this line as well.

The next component then moves the file and fails complaining that the file is in use.

What can I do?

 

Regards

 

Guy

 

View 2 Replies View Related

Connections Closed But Still Getting Errors On Page

Apr 16, 2007

I have a page that I have 3 connections.  I've made sure that each of these are closed when they are not being used and opened just right before being used.  I keep getting the error "There is already an open DataReader associated with this Command which must be closed first."  This error might show up as being produced by a dataadapter or sqldatareader...I have many.  I've even tried to make separate connections as some have mentioned for each...leaving me with 15+ connections.  I have added "MultipleActiveResultSets=True" to the connection strings as some have mentioned.  I just don't know where to go from here...
Is it possible that the problem lies in multiple instances of this page being opened?  Also, the data refreshes every 15 seconds.  I really need this to work, but I have no clue on how to fix this problem.  The error is easy to reproduce by opening up multiple instances, but some of the times is doesn't give an error at all?!

View 4 Replies View Related

Closed Recordsets From Stored Procedures

Jun 18, 2001

Hello all,

I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.

So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.

Has anyone got any ideas on how to get round this?

Thanks
Dave Sykes

View 2 Replies View Related

There Is Already An Open DataReader Associated With This Command Which Must Be Closed

Oct 15, 2007

I have gathered from reading online that I need to create a 2nd connection to SQL Server if I want to insert records into the database while in a "while (reader.Read())" loop.

I am trying to make my code as generic as possible, and I don't want to have to re-input the connection string at this point in my code. Is there a way to generate a new connection based on the existing open one? Or should I just create 2 connections up front and carry them around with me like I do for the 1 connection now?

Thanks.

View 7 Replies View Related

Finding Cases With All Children Closed

Jan 5, 2013

Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:

CaseInfo Table
CaseID,
CaseNumber,
CaseName

CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate

Party Table
ID,
PartyID,
Firstname,
LastName

SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID

WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName

But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.

View 5 Replies View Related

Can Stored Procs Run After Handle Is Closed?

Jul 23, 2005

I have written a stored proceedure for MSSQL that needs to run for hours ata time. I need to execute it from C++ code. The current code does:nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)followed shortly after by aFree_Stmt_Handle(hstmt) //roughlyThe stored proc currently dies with the statement handle, not fullypopulating the table I need it to.I need to either know when the proc finishes so I can close the handle afterthat, or allow the proc to run independently on the server no matter whatthe program is doing (is exited, etc), either of these is fine.Please Help! Thanks in advance!Joseph

View 2 Replies View Related

ASP Sp Execution Returning Closed Recordset

Jul 20, 2005

Can anybody tell me why a) when running a stored proc from an asp page toreturn a recordset the command succeeds if the sp queries an existing tabledirectly, but b) if the stored proc populates results into a differenttable, temporary table, global temp table, or table variable, then queriesone of these, the asp page reports that the recordset object is closed. Ifusing a table, I have set grant, select, update, delete permissions for theasp page user account, so it doesn't appear to be a permissioning issue. Ifrun in Query Analyser the sp runs fine of course.Abridged asp code is as follows:StoredProc = Request.querystring("SP")oConn.ConnectionString = "Provider=SQLOLEDB etc"oConn.Openset oCmd = Server.CreateObject("ADODB.Command")oCmd.ActiveConnection = oConnoCmd.CommandText = StoredProcoCmd.CommandType = adCmdStoredProcoCmd.Parameters.Refresh'code here that populates the parameters of the oCmd object correctlySet oRs = Server.CreateObject("ADODB.Recordset")With oRS.CursorLocation = adUseClient.CursorType = adOpenStatic.LockType = adLockBatchOptimistic'execute the SP returning the result into a recordset.Open oCmdEnd With' Save data into IIS response objectResponse.ContentType = "text/xml"oRs.Save Response, adPersistXML'the line above fails with stored procs from example B below, reporting "notallowed when object is closed", but works with example ASP Example A - this one works fineCreate Proc spTestA ASSELECT ID FROM FileListGOSP Example B - this one doesn't work from ASP but runs fine in QACreate Proc spTestB ASDECLARE @Results Table (ID TinyInt)INSERT INTO @Results SELECT ID FROM FileListSELECT ID FROM @ResultsGOI can see the SP executing using profiler when the asp page is called forboth sp's above, so it doesn't appear to be a problem with the execution.It's something to do with returning the result set from the table variable.Thanks,Robin Hammond

View 1 Replies View Related

DB Engine :: Connection Closed From Application End

Jun 30, 2015

The application server gets below error while the job is being run intermittently:

An error occurred while performing connection management

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:319)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1839)

[Code] ....

There is no error reported in SQL logs.

View 6 Replies View Related

Operation Not Allowed When Object Is Closed...

Aug 7, 2007

I have this stored procedure on SQL 2005:

USE [Eventlog]

GO

/****** Object: StoredProcedure [dbo].[SelectCustomerSoftwareLicenses] Script Date: 08/07/2007 16:56:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@CustomerID char(8)

)

AS

BEGIN

DECLARE @Temp TABLE (SoftwareID int)

INSERT INTO @Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @CustomerID

DECLARE @SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @Temp

GROUP BY SoftwareID

DECLARE @rowid int

SET @rowid = (SELECT COUNT(*) FROM @SoftwareInstalls)

WHILE @rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @SoftwareInstalls WHERE rowid = @rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @SoftwareInstalls WHERE rowid = @rowid)



DELETE FROM @SoftwareInstalls

WHERE rowid = @rowid

SET @rowid = (SELECT COUNT(*) FROM @SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @CustomerID

ORDER BY Software.Software

END

When i execute it in a Query in SQL Studio it works fine, but when i execute it from an ASP page, i get following error:


ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/administration/licenses_edit.asp, line 56

Here the conection:
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = Session("ConnectionTimeout")
OBJdbConnection.CommandTimeout = Session("CommandTimeout")
OBJdbConnection.Open Session("ConnectionString")
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
SQLStmt.CommandText = "EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
RS.Close


Can anyone help please?
It this because of the variable tables?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved