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


ADVERTISEMENT

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

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

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

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

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

Delete Sys.Transmission_Queue

Oct 17, 2006

I have seen this buried deep with the questions on Service Broker, but I am looking for it again. How do you delete all records from your sys.Transmission_Queue. This is on a test server and I want to clean it before some more test.



View 5 Replies View Related

Messages Stuck In Transmission_queue

Dec 19, 2007

We are having a problem with messages getting out of transmission_queue and into the queues themselves. The queues all are actviated and enabled, and our service broker is enabled at the database level. The dba detached & reattached the db yesterday, which I believe may be the cause of this problem. Everything seems to be in order (sp ownership, activation procs, etc), except when I run:


select [name], count(*)

from sys.dm_broker_queue_monitors qm

inner join sys.service_queues sq on qm.queue_id = sq.object_id

group by [name]


I get 2 entries for each queue. And the last_activated_time is the same date as the detach event. When we reattached we had to do an alter database set enable_broker in order to get it back up and running. When I run the above query on our dev and test environments, I get only one entry per queue.

Does anyone know why this would happen? Is this a valid state for SB?
And to get past it, if we can't figure out the real fix for it, we want to get a copy of all the messages in the transmission queue, do an alter database set new_broker, then replay them all into SB. We hope this fixes the root cause, but it's a guess.

Any ideas? Thanks in advance,
John

View 1 Replies View Related

Messages Stuck In Sys.transmission_queue

Aug 14, 2007

Hi,

At my company, we're trying to use service broker to create a client-server system where there is a head office machine and multiple outlets registered with that head office. My problem is that sometimes when a branch sends a message to the head office, it just seems to sit in the transmission queue and never gets sent. If I run a script that forcibly ends the conversations on the client machine (with cleanup), storing the message bodies and then resend them, they seem to get through fine.

The way that we send messages is by calling a t-sql stored procedure from a c# application using SqlCommand (don't know if this should make any difference).

If I monitor the Head Office machine and one of the Outlets while this is happening, on the HO I get three events in a row:



Broker: Message Classify (1 - Local)
Audit Broker Conversation (2 - No Certificate)
Broker: Message Undeliverable (1 - Sequenced Message)
The TextData contained in the third event is: This message could not be delivered because the security context could not be retrieved.

The RoleName of the server is Initiator, and the TargetUserName is the name of the service on the Outlet.

On the Outlet I get the following event repeatedly (presumably as it continues to try sending the message) - Broker: Remote Message Acknowledgement (1 - Message With Acknowledgement Sent).

On the client the RoleName also appears to be Initiator, and the TargetUserName is blank.

This would make me suspect that certificates were missing or something, except that if I remove messages from the queue and resend them they seem to get through, and also I've checked both databases and they have the correct certificates.

Any ideas?

Thanks in advance,

Adam

View 6 Replies View Related

Messages Stuck In Sys.transmission_queue

Jan 29, 2007

Sorry for the stupid question, but I can't seem to figure it out...

There are 119 messages that are stuck in the transmission queue, all for the same queue. When I check the status of the queue (via sys.service_queues), is_receive_enabled = 1, is_activation_enabled = 1, and max_readers = 3. When I check to see if there is an active queue monitor (via sys.dm_broker_queue_monitors) there is nobody watching this queue. What would cause this queue to be active, enabled but have nobody montioring it? Is there something internal that went wrong that made these (outbound) messages get stuck in the transmission queue, and is not showing up in the views? How can I get these messages "un-stuck" and flow through the system?

A problem I am seeing is the return message to this queue (to signify the target has consumed the message, and to end the conversation) are not being consumed, thus getting stuck in the "DI" state.

Any suggestions would be greatly appreciated.

Thanks in advance,

John Hennesey

View 7 Replies View Related

Locking Semantics On Sys.transmission_queue

Feb 15, 2006

Are lock hints propagated to the underlying tables of system catolog views? I ask because I often query sys.transmission_queue with nolock, and I wanted to know if this was honoured through out the underlying tables.

Secondly, is sys.transmission_queue indexed at all, providing a way to prevent table-scanning?

Thanks

View 1 Replies View Related

Help: Messages Stuck In Sys.transmission_queue

Mar 26, 2007

For some reason the messages are stuck at sys.transmission_queue. The transmission status seems to be null. I verified all the Queues and they hae the values of (is_receive_enables = 1, is_enqueue_enabled = 1, is_activation_enabled = 0)

I have set up the service broker on different domain and have bidirectional ports open to receive messages.

I can send and receive messages, when I run the same service broker setup scripts at the machines that belongs to same domain .

any help appreciated.

View 3 Replies View Related

What Is The Meaning Of 64(error Not Found) In Sys.transmission_queue

Jul 13, 2005

   as Christopher Yager say in "Need distributed service broker sample", I also test sending messages between two SQL Server 2005 instances,and after I setup the test environment with instance1 and instance2,I find queue [q2] in ssb2 can't receive message from ssb1. when I query by "select * from sys.transmission_queue",I get some message records that transmission_status is "64(error not found)".

View 12 Replies View Related

Messages In The Sys.transmission_queue For A Disable Queue

Feb 28, 2007

I may have a misunderstanding of how SB works, but this seems like a problem.

If a queue is disable (i.e. status = off) and a message is sent to the queue the message is placed on the sys.transmission_queue. Once the queue is enabled I thought the messages were sent to the queue in the order they were placed on the sys.tranmission_queue? I have been troubleshooting a problem and this is not the case. Do I have a misunderstanding of how the sys.transmission_queue works?

The queue has retention turned off.

View 4 Replies View Related

How To Log That A Message Has Been Retained In The Transmission_queue When The Queue Is Disabled.

Jun 16, 2007

Hi was wondering whether it is possible to log somewhere outside SB that there are messages in the transmission_queue because the Target queue was disabled.



I was testing this scenario:

try to send messages on a disabled queue and log the problem.



But the transmission_status from the trasmission_queue is always empty.



This is the code that I tried to execute between the send and the commit and after the commit:




WHILE (1=1)

BEGIN

BEGIN DIALOG CONVERSATION .....


SEND ON CONVERSATION ......


if select count(*) from sys.transmission_queue <> 0
BEGIN

set @transmission_status = (select transmission_status from sys.transmission_queue where conversation_handle=@dialog_handle);

if @transmission_status = ''

--Successful send - Exit the LOOP

BEGIN

UPDATE Mytable set isReceivedSuccessfully = 1 where ID = @IDMessageXML;

BREAK;

END

ELSE

raiserror(@transmission_status,1,1) with log;

END

ELSE

BEGIN

UPDATE [dbo].[tblDumpMsg] set isReceivedSuccessfully = 1 where ID = @IDMessageXML;

BREAK;

END

END

COMMIT TRANSACTION;

As I wrote before the @transmission_status variable is always empty and I have the same result even if I put the code after the commit transaction!



Maybe what I'm trying to reach has no sense?


With the event notification I can notify when the queue is disable because the receive rollsback 5 times but what if by mistake the target queue is disabled outside the SB environment? I can I catch it and handle it properly?

Thank you!Marina B

View 3 Replies View Related

Messages In Transmission_queue With No Errors, Status Is Conversing.

Jan 29, 2008

I've got 2 service broker databases on remote servers. I've created my endpoints, my routes and have everything set up. But when i send a test message, the messages set in the transmission_queue. There is no transmission_status. And when i look in at the sys.conversation_endpoints view I see that the conversation status is conversing. One odd thing I wanted to point out though is that the far_broker_instance column of the sys.conversation_enpoints view is null. When i run a trace on both databases, I see activity on the Initiator with things like Started_OutBound and conversing but I don't see any messages such as acknowledgment or any errors. On the Targer side I see no activity at all. Does anyone know what the deal is. Why don't I get some kind of error message. Why are all my messages staying in the transmission_queue?

Thanks in advance.

View 2 Replies View Related

Same Sql Server Instance, 2 Different DB: Messages Are Inserted Into Initiator's Sys.transmission_queue?

Jun 7, 2007

Hello,

I have a question..

I set-up my Service Broker comunication on the same SQL Server Instance beetween 2 different DBs.


One DB behave as Initiator and send messages to the SB service setup in the other DB.

I execute the SEND statement from the Initiator and if I count the messages on the sys.transmission_queue before to commit the transaction the count returns 0.

If I try to send a message not compliant with the message type, the count that runs after the SEND returns 1 - far enought.

I'm confused about the first behaviour because from what I understood the Acknolodgment and the removal of the message from the sys.trasmission queue should happen after the COMMIT.

Some expert can aswer to this direct quection?



Thank you

Marina B.

View 7 Replies View Related

Filling A DDL From A SQL DB??

Nov 1, 2004

Hello All
I am wanting to fill a drop down list in ASP.NET using C# from a SQL database table using a stored procedure. I have my Sproc. But using ASP.NET C# I have no idea how to do this. Can someone give me a good example, and if not too much trouble, place comments in the code, and give an explanation. I am just learning ASP.NET after moving from Classic. Things are alot different.

Thank You in advnace for all your help

Andrew

View 1 Replies View Related

How Can I Keep The Log From Filling Up?

Mar 21, 2008

I have a ton of data to load into a SQL 2005 database.
I just loaded a bunch of data for a number of tables using bcp, and the last table that my script loaded was an 8 million row table. The next table was a 12 million row table, and about 1 million rows into the bcp'ing a log full error was incurred. I have the batch size set to 10000 for all bcp commnads.
Here is the bcp command that failed:

"C:Program FilesMicrosoft SQL Server80ToolsBinncp" billing_data_repository..mtr_rdng_hrly_arc_t in mtr_rdng_hrly_arc_t.dat -c
-b10000 -Sxxxx -T

Here is the last part of the output from the bcp command:

...
10000 rows sent to SQL Server. Total sent: 970000
10000 rows sent to SQL Server. Total sent: 980000
10000 rows sent to SQL Server. Total sent: 990000
SQLState = 37000, NativeError = 9002
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'billing_data_repository' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

BCP copy in failed

I thought that a commit was issued after every 10000 rows and that this would keep the log from filling up.

The log_reuse_wait_desc column in sys.databases is set to 'LOG_BACKUP' for the database being used.

Does a checkpoint need to be done more often?

Besides breaking up the 12 million row data file into something more manageable, does anyone have a solution?

How can I continue to use my same loading script, and keep the log from filling up?

Thank you.

View 9 Replies View Related

Help Filling Dataset

Feb 27, 2007

I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.
This is the code for the Method to return a datareader
 
publicSqlDataReader GetOrgID()
{
 Singleton s1 = Singleton.Instance();
 Guid uuid;
uuid = new Guid(s1.User_id);
 SqlConnection con = new SqlConnection(conString);
 string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
 SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
 
con.Open();
 SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
 
 return dtr;
 
This is the code trying to accomplish the same thing with a Dataset instead.
 
 public DataSet organID(DataSet dataset)
{
 Singleton s1 = Singleton.Instance();
 Guid uuid;
uuid = new Guid(s1.User_id);
 string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
 SqlConnection con = new SqlConnection(conString);
 
 SqlCommand cmd = new SqlCommand(queryString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
 
 SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
 
adapter.Fill(dataset);
 return dataset;
 
 
 
}
 
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.
So assume that the Guid is a valid entry..I should return a valid dataset but its null.
 Additionally if I change the sql query to just be Select * From aspnet_OrgNames I still get a null value...I am assuming I am doing something wrong trying to fill the dataset.
 
Any help would be appreciated.
 

View 2 Replies View Related

TempDb Filling Up!

Jan 14, 2000

MS SQL Enterprise Server, SP5, running under version 6.5.

I have recently been having a problem with the TempDb database
filling up. I originally started the database at 250 Mb but
recently expanded it to 500 Mb.

My last check of the activity on the server during an event
such as this produced the following information.

- Approx. 300 connections to primarily 2 databases.

- 4 active connections:

Connection 1 -SELECT on database 1 with 13,000 records
and a record size of approx. 300 bytes.

Connection 2 -SELECT on database 1 with 13,000 records
and a record size of approx. 300 bytes.

Connection 3 -SELECT on database 2 with 550 records
and a record size of approx. 100 bytes.

Connection 4 -Replication subscriber set at 100 transactions.

My questions are:

1. What processes may cause the TempDb database to fill up?

2. What processes prevent the database from purging?

Any information would be greatly appreciated.

Jim Story

View 2 Replies View Related

Transaction Log Filling

Nov 2, 1999

We are having continual problems with our transaction log filling up on one of our major applications.
Does anyone know of a way or tool to read the transaction log? We want to determine what is causing this problem.

Thanks

View 2 Replies View Related

TEMPDB Filling Up

May 4, 1999

Recently, we converted an Access database to SQL server 6.5. One of the processes that runs against the server is
missing a commit causing temporary stored procedures to fill up TEMPDB in the sysobjects table. The only way to
clear up TEMPDB is to stop and start SQL server when the database fills up. I wrote a quick and dirty stored
procedure to delete the affending rows out of the tempdb..sysobjects table, however, the database still registers as
full after the deletes.
Question:
does anyone know of a process/DBCC I can run against the tempdb..sysobjects table to regain the space in TEMPDB
without having to stop and restart SQL Server? I need a temporary solution while the programmer is debugging the
affending code.
Thanks!
TC

View 1 Replies View Related

Distribution Log Keeps Filling Up!

Dec 5, 1998

hello,

I've got replication set up as a publisher subscriber, to basically sync a primary server with a
backup server. My distribution log keeps filling up, I've got a perf alert for now to truncate it
at 75% full for now, but why does it fill up? it's size is about 1.5 gig
My tran log for my database will also not remove about 500mb of data as well, is there any way to
see what is going on?

Thanks

View 1 Replies View Related

Filling Gridview From Code

Aug 2, 2007

 hello, i'd need a little help with filling GridViewsi browsed over like 10 search pages, but couldnt find any which would solve my problem.so in my ajax project i made a testing page pulled a gridview (GridView1) on it with a fhew buttons and textboxes.i need to fill the gv from code so my websie.asp.cs looks like this 1 protected void Page_Load(object sender, EventArgs e)2 {3 4 5 string connstr = "Data Source=.;database=teszt;user id=user;password=pass";6 SqlConnection conn = new SqlConnection(connstr);7 SqlCommand comm = new SqlCommand("select * from users", conn);8 conn.Open();9 SqlDataReader reader;10 reader = comm.ExecuteReader();11 if (reader.HasRows)12 {13 GridView1.DataSource = reader;14 GridView1.DataBind();15 }16 reader.Close();17 conn.Close();18 comm.Dispose();19 } so i load the page and there's no gridview on the page at all, nor an error msg, the connection and the database/table is fine.any suggestions on what am i doing wrong? and i also like to know if there would be any problem with using this on a tabcontrol/tabthankyou  

View 3 Replies View Related

Filling A Table In Sqlserver2005

Apr 9, 2008

hello everyone
i have created a table in sqlserver2005  named "Departments" - in this table different departments of a telephone ( landline ) company are to be stored,which deals with complaints registered to them by there users.
i want to know the name of these different departments which deals with complaints assigned to them like if i do have complaint from a user who has problem with his handset then that complaint will be assigned to "Maintance dept."
as i was never in indusrty , i need the help in filling the table.
just do write me name of departments and the nature of complaints wh they deal with!!!
thanks for the consideration

View 1 Replies View Related

Filling In Missing Values

Oct 29, 2004

I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9

View 1 Replies View Related

Bad VB Query Filling Up Tempdb

Oct 2, 2000

I have complex VB code that loads millions of records into a SQL 7 database. I see my tempdb slowly growing until system runs out of disk space.

What are typical programming bugs that would cause tempdb to fill up,
and how can I identify the offending statements through profiler?

Any help greatly appreciated!

View 1 Replies View Related

TempDB Log Files Are Filling Up.

Dec 28, 2006

What is the best way to fix issues with your log files in TempDB when you start to see them causing error msgs?

Thansk for your time.

View 2 Replies View Related

Tempdb Transaction Log Filling Up

Aug 2, 2001

We have a stored procedure that uses temp tables and must gather a lot of data. When we stress test this stored procedure, the tempdb transaction log fills up.

We tried using "Select Into" for our tables. That caused us not to write to the transaction log but it caused problems because it locked up sysobjects.

Is there some other way not to write to the transaction log?

View 1 Replies View Related

Delete Statements Without Log Filling Up

Aug 13, 2002

I have a database that I am splitting the data using odd account numbers and even account numbers. The odd acct numbers in one database and the even in the other database.

This database is very large. The problem is when I run the delete statements
it is going to fill up the log files. Can I turn on the "Simple" mode on the database while I am deleteing the data. Will this cause a problem? Then can I turn back on the 'Full' mode when I have finished?

Has anyone ever done this and so how did it work. Or better yet is it possible?

Thanks,
Dianne

View 2 Replies View Related







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