Reusing Dialogs Causes Blocking
Oct 23, 2006
I was looking at a means of reusing dialogs.
The attempt I tried was looking up an existing dialog in the conversation_endpoints.
However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.
select top 1 conversation_handle
from sys.conversation_endpoints ce
join sys.services s on s.service_id = ce.service_id
join sys.service_contracts c on c.service_contract_id = ce.service_contract_id
where s.name = 'jobStats'
and ce.far_service = 'jobStats'
and (ce.far_broker_instance = @targetBroker OR @targetBroker = 'CURRENT DATABASE')
and ce.state IN ('SO','CO')
and ce.is_initiator = 1
and (ce.conversation_group_id = @conversation_group_id )--or @conversation_group_id is null)
and c.name = @contractName
View 2 Replies
ADVERTISEMENT
Aug 27, 2007
After lots of testing I have found that re-using dialogs improves performance significantly, however I cannot fathom out what is the cost of re-using them. Performance does not come for free hene why I feel there is a cost. Can anyone shed any light on the cost of re-using dialogs? Also why should you re-cycle them? What benefits does recycling dialogs have?
Many thanks
David
View 1 Replies
View Related
Aug 28, 2006
Hi There
SInce day one i have configuring service broker to use transport and dialog security with certificates.
However i am not 100% sure how to setup service broker between 2 instances without security.
Would the following work, or what steps am i missing?
1.Setup the endpoint with WINDOWS authentication instaed fo certificates, the instances are in the same domain, grant necessary permission to the service account for each instance.
2.Create dialogs with the create remote service bindings with the anonymous = ON and grant send to public for the services.
Would this work? Also am i correct in thinking that if encryption is required the use of certificates is mandatory?
Thank YOu
View 5 Replies
View Related
Nov 12, 2003
Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone help
View 1 Replies
View Related
Jan 2, 2006
Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I´ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn´t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They´re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I´ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?
View 1 Replies
View Related
Apr 19, 2008
Hi ,
I want to open and close sql connection only once and want to use in every function without open or close this connection in class file in asp.net 2003 .
how can it possible .
View 1 Replies
View Related
Feb 20, 2008
Okay, so yes, I am new to SQL server...
I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.
REATE Proc CreateMasterSchedule
as
Select
dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate,
(case when dbo.[MOP_Planning Overview].[Order Category]='101' AND CAST (Convert (char(10),[Planning Date], 110)as DateTime)
<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1)then dbo.[MOP_Planning Overview].[Transaction Quantity - Basic U/M] else 0 end)as PriorInProc,
If I try to use DateofPlanningDate in the above case statement, I get the invalid column name error.
Basically, I just need a way to reuse the value returned by this column.
Can anyone help?
View 6 Replies
View Related
Apr 30, 2008
In the Package configurations wizard, I am trying to edit an existing configuration using the edit button. In the Configuration Filter, I get the list of several filters (the filters which were used for other packages). Whe I try to reuse an used filter, it is forcing me to set a new value and when I go back to SQL Server tables , I see the old value has got erased.
Can I not use an existing filter?. Do I need to use new filters for every new package?.
Thanks.
View 1 Replies
View Related
Mar 23, 2004
Hi,
I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?
Thanks,
S.
View 7 Replies
View Related
Aug 30, 2007
Hi
I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks
DECLARE @dialog_handle uniqueidentifier
select @dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'
IF @dialog_handle is NULL
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [initiator]
TO SERVICE 'target'
ON CONTRACT [portcontract];
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [Port] (@msg)
View 1 Replies
View Related
May 10, 2006
Is it possible to reuse a Lookup component which is configured with Full chaching?
My requirement is as follows....
A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".
In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?
View 9 Replies
View Related
Oct 22, 2007
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
View 2 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
Feb 1, 2007
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Sachin
View 4 Replies
View Related
Mar 3, 2004
Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
Eric
View 4 Replies
View Related
Oct 26, 2005
I have 5 packages in a solution.
View 19 Replies
View Related
Aug 15, 2000
One sql transaction is in rollback state and optimization job is trying to restructure index on the same table that this transaction is using. As a result,
transaction is in 'blocking' state and optimization job is in 'blocked' state. 'Kill process' doesn't do anything. How can this situation be resolved?
View 1 Replies
View Related
Nov 26, 2001
Is it possible that blocking can occur between a select and update statements running at the same time on the same object??thanks for the reply!
Sheila.
View 1 Replies
View Related
Nov 1, 2007
Anyone out there having "self blocking" issues since upgrading to SP4 on SQL 2000? Enabing trace flag 9059 has not helped, is this a parallelism issue ?
thanks,
Paul
View 7 Replies
View Related
Jan 6, 2004
Any ideas?
I execute a sproc from QA.
It interogates a folder:
Select @Command_String = 'Dir ' + @FilePath + '*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @Command_String
[/code[
Which I then Parse and get the details...
Set up a CURSOR (Booo hiss....)
[code]
DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]
WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN
Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)
I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)
SET @cmd = 'bcp ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in '
+ @FilePathAndName
+ ' -f' + 'd:DataTax' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt'
+ ' -S' + @@servername + ' -U -P'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' + ', NO_OUTPUT'
INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)
MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH
A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....
I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...
When I do sp_who, it shows the blocking...
I'm so confused....
View 8 Replies
View Related
Aug 31, 2006
Hi just read an article on the internet that states
"A quick way to resolve a blocking problem is to disconnect the connection blocking the rest of the users. All you have to do is ask your database administrator to execute the KILL statement. Obviously, this is not a permanent solution, and won't really help anything except take care of an immediate need."
Now when i run sp_who ... i see that the spid that is blocking is the same as the spid itself....
Now the above article states that killing the one that is blocking the rest of the other spids is ok ...
But what if its the same SPID that is blocking ...what does it signify & whats the solution
Thanks
View 2 Replies
View Related
Nov 13, 2007
Hi Gurus,
I am running a simple SELECT statement on my TempDB
SELECT * FROM TempDB..SysObjects
When I run this , it's getting blocked by some other Process which is not at all relevant to the above Qry. First it gets blocked by one Stored Proc say Proc1 ( The Proc1 is just a SELECT statement on a table which just has four rows ). So I just kill this PROC1 and again its gets blocked by some other stored Proc PROC2 which again is a simple stored Proc and it runs in not time.
Any Suggestions as to where to start.
Thx in Advance
Venu
View 13 Replies
View Related
Jul 23, 2005
hi,I am using SQL SERVER 2000.Problem that i am facing is when ever I check locks in EnterpriseManager I findfollowing blocking - :1) sp_cursoropen;12) sp_cursorclose;13) sp_cursorfetch;1We dont have any control over application(source code).Whateverwe have to do, has to done from database side.can anyone guide me to solve this problem.Thanks
View 4 Replies
View Related
Dec 13, 2007
I have one batch jobs running 4 time a day and web application.
We have hosted the site long time already but this month we are facing the store procedure blocked problem.
The problem is that when user submit the form when during the execution of batch job, two storeprocedure are blocked.
One store procedure that is using from batch job read the table "A" and another store procedure that is using from web app update the table "A".
Then deadlock is occure. but i don't understand it is hosted long time already. Please let me know if you know how to solve this issue.
Thanks,
Aung
View 6 Replies
View Related
Jan 14, 2002
Hi ALL!
Can a sleeping process block another process sleeping/runnable?It's happening in our SQL server.Can someone expalin??
thanks.
Di.
View 1 Replies
View Related
Apr 24, 2002
I have a powerbuilder application that is connecting to SQL Server. We get very frequent DeadLock situations. Is there a way that I can check to see as what SQL is causing the Lock?
Thanks in advance for any suggestions...
-Raj
View 1 Replies
View Related
May 29, 2001
I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.
View 2 Replies
View Related
Jul 10, 2001
Hello,
In the morning at 9:32 one of the spid was blocked and got time out msg for select statement. the statement was sp_execute wht is sp_execute?
can anyone guide me?
Thanks
View 2 Replies
View Related
Jul 13, 2001
Hello,
I've a problems with blocking
simple select * from table1
is blocked
how to know which statement is blocking
select * from table1 is spid 30 blocked by spid 26
and in current activity spid 26 has no select or any statement
how could i know the spid 26 statement ?
And can just select statement be blocked?
wht should we do to monitor locks?
Thanks
Binoy
View 2 Replies
View Related
Nov 15, 2000
I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?
Thanks
View 2 Replies
View Related
Feb 4, 2001
Hi there,
I've a encountering a problem with "Blocking" and would highly appreciatd, if anyone out there please advice how to get to the bottom of this. the problem in as follows:
Every now and then, my SQL Server Freezes and when i look at the process info, i see some user connections in blocked mode and some in blocking. i dont know, where to start looking for. my first question would be, what should I do here? Should i kill the blocked user or blocking user? Also, Whats the difference between this two modes (blocked and blocking) and why does this happening? can i trace the user id which is causing this problem?
Experts please advice, what to do in the near future to aviod this.
I search at microsoft support site and came up nothing good about this problem.
Appreciated, If anyone out there advice on this.
Many thanks in advance.
Sindi
View 1 Replies
View Related
Apr 19, 2000
When an SA attempts to change permission on any object they are shown as blocked as expected. However as new users access the object the SA's process is continuely blocked until the object is free. Can anyone explain this??
Thanks
David
View 3 Replies
View Related
Dec 7, 1999
Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
View 2 Replies
View Related