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
When I re-boot the machine, if the sql server is set to "automatic", will the service re-start when re-booting the machine?
View 1 Replies View Relatedi am using Asp.net , C# , sql server 2005.
i made one rdl report and called it from server (win2003). it worked successfully . but when i give print for the same report(through reportviewer control print option). Client machine restart. Same thing happened on four machine that were in win2000.
Please guide me.
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
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
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?
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
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
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
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
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
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
Help!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
Hi All,
I have enterprise version software CD1 and CD2 of SQL server 2005
when i try to run setup.exe..the following error pops -up
The image file Setup.exe is valid, but is for a machine type other than the current machine
MY OS version is windows 2003 32bit Operating system...
I am suspecting the binarie may be of 64bit.. How do we check the binaries are 32 bit and 64 bit software
Thanks for your Help
Mohan
Hi to everyone,
I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.- Thanks to everyone for any help.
Hullo all
I have two machines,
One is a standalone machine and the other is on a domain network. How can I run a stored procedure/job on the standalone machine from the domain machine ?
running the procedure as a Domain user results in a failed job/stored proc.
also creating an sql login and attempting to run it as that user also fails. How can I solve this problem ?
please e-mail me at wayde@sunnygrp.com if you have any thoughts...
Hi All;We are going to change our application server. We will copy all of ourDatabase from Mic 2000 Server OS to Mic 2003 Server OS. I found anarticle that how to move all the folders from same OS. My question isthat Would the 2003 OS be a problem when we copy all of the MC SQL 2000folders to New OS ? Has anybody done this before? Could you gimme yoursuggestions please?ThanksASA
View 1 Replies View RelatedHi,
here goes the question:
i have 1 sqlserver 2k5 machine (machine A) and sqlserver 2k (machine b)
how do i build a query to select data from 2k machine in a query of 2k5?
i mean something like
select *
from login:passw@machineb.dabatase.dbo.customers
thanks!
Hi,
here goes the question:
i have 1 sqlserver 2k5 machine (machine A) and sqlserver 2k (machine b)
how do i build a query to select data from 2k machine in a query of 2k5?
i mean something like
select *
from loginassw@machineb.dabatase.dbo.customers
thanks!
Hi,
If I use GetDate() function from client machine (using "\ServerMachineSQLExpress" instance from client machine "ClientMachine"), then from where I will get the DateTime value ?
From "ServerMachine" or from "ClientMachine"
Thanks
Hi Guys,
I was wondering if there was any white papers on the migration part from microsoft?
Apparently the situation is like this
Currently installed MOSS 2007 in a testing pc as application server. This application server will refer another testing database server which running SQL Server 2005.
Now, wanted to move this 2 temporary server into another 2 new servers.
Correct me if I'm wrong, it cannot be done by using image like Norton Ghost due to difference hardware spec.
So, if I install another fresh copy of MOSS2007 & SQL server 2005 in that 2 new servers, how can I migrate the contents, documents, etc from the testing database server?
It would be great if someone can show me the links to kb article. Thanks
Dear All:
When i try to debug the northwindoledb example with VS2005 i get the foolwing error message.
Is there a way to configure the solution to run witn WM5 emulator.
The application run with wm5 emulator when i set the plataform to Windows Mobile 2003, but i want to use the wm5 platform to debug my application with wm5 emulator.
------ Rebuild All started: Project: northwindoledb, Configuration: Debug Windows Mobile 5.0 Pocket PC SDK (ARMV4I) ------
Deleting intermediate and output files for project 'northwindoledb', configuration 'Debug|Windows Mobile 5.0 Pocket PC SDK (ARMV4I)'
Compiling...
Employees.cpp
northwindoledb.cpp
stdafx.cpp
Generating Code...
Compiling resources...
Linking...
coredll.lib(COREDLL.dll) : fatal error LNK1112: module machine type 'THUMB' conflicts with target machine type 'ARM'
Build log was saved at "file://d:Proyectos_VS2005Northwindoledb_WM2003_SQLEW_Windows Mobile 5.0 Pocket PC SDK (ARMV4I)DebugBuildLog.htm"
northwindoledb - 1 error(s), 0 warning(s)
========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========
This started as an annoyance that has start to become a real problem. I am accessing a file that can be read with OLE DB that I am interrogating and inserting its records into a SQL Server database. The problem is that sometimes this will stop working, and requires a server reboot. The error that comes back is Microsoft.Jet... an error has occurred... Has anyone else had this similar problem, and how were you able to solve it?
Thanks in advance...
After finished a unit test of an ASP.net program, I deleted the data from 3 tables to get ready for a system test. Then during the system test, the PK columns of 2 tables continued the integer number from the unit test. How can I let PKs restart from 1 for the system test and production test, respectively?
TIA, Jeffrey
Before I re-install, anybody got a clue?
I changed a few parameters in the Enterprise Manger config box, then restarted, but Sql Server did not
come back up. REbooted, Sql Server would not come up.
Tried to start from command line using "sqlservr -c -dc:mssqldatamaster.dat -f", and it TRIED to come
up, but then I got error messages about login for SA.
first message said login was successful, but the next message says "login failed"
Whats up?
I am using windows Vista that comes with IIS 7. I am trying to figure out how to restart IIS, I went to services & tried to stop IIS Admin but was given a msg saying that you cannot stop this service.
Does anyone know how to successfully restart IIS 7?
Maybe a 'dumb' question, but I can't figure it out. I can't tell from the logs since they are periodically truncated, so how can I tell when the system was last restarted?
I would like to know the period of time that a dmv has been updating a particular view.
Your help is most appreciated.
-- M
I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
Can we have Reporting Services having its database on a server and distribution server (http://IISserver/Reports)
on another server?We dont want our database server to have IIS and dont want SqlServer installed on our IIS server
kind regards
how can I configure the SQL server to automatically restart selected services on a regular basis?
View 1 Replies View RelatedHey all.. I am wanting my SQL Server installation to restart its services at a time I define in the Scheduled Tasks feature of the server its on
I need help with command line commands to:- Stop SQL Server Agent service- Stop and Start SQL Server service- Restart SQL Server Agent service
Thanks in advance
Has anyone ever experienced the SQL Server unexpectedly rebooting?
Yesterday my SQL Server 7 installation rebooted itself and the error log seemed fine as did the restart. The only evidence I can find was a message in the event viewer's application log stating the the MSSQLServer service terminated unexpectedly. Any ideas are appreciated.
Thanks,
- D
We are using NT 4.0 + 6.5 + Microfocus Netexpress
After the server is booted, our application flies.
After some days ( 1 to 3), the performance starts to degrade until it reaches an unbearable sluggishness. Teh degradation is not continuous, but it shows with time spans (which vary from 1 to 5 hours) during which everything is unacceptably slow. This time spans are usually longer each time they present themselves. There are no errors and if given the time (unacceptable) every workstation finishes its tasks properly.
If we stop the server and reboot it, everything comes back to the initial excellent performance and the cycle of degradation starts over again.
The hardware is excellent and we have checked almost everything that came into our minds, with no results.
Does anyone have any previous similar experiences ?
Does anyone have any ideas on where we should focus our search ?
We are migrating the application into 7.0 but I am afraid that our nightmares might continue
I would appreciate all the advice you could give me. Thank you
Elias