Hello again,
I have some poison message detection in place, based on the BOL sample. My problem is that after the 5th message retry my queue goes down - that is the fifth retry on any message. In actuallity, the first message is retried 3 times and it is taken off the queue [for real], the second message comes in and on the second retry - pooof - the queue is down.
I though the poison mechanism should work on a per message basis. It there a setting for the queue I missed? Is my only chance for to fix this: re-enable the queue upon BROKER_QUEUE_DISABLED event notification?
In the "Example: Detecting a Poison Message" section, it reads: This Transact-SQL example shows a simple, stateless service that includes logic for handling poison messages. Before the stored procedure receives a message, the procedure saves the transaction. When the procedure cannot process a message, the procedure rolls the transaction back to the save point. The partial rollback returns the message to the queue while continuing to hold a lock on the conversation group for the message.
Hallo, I have some problems to handle the poison messages.
Messages are exchanged between 2 databases on the same SQL Server instance. I created a Trigger on the insert , that call the sp that begin the conversation(10 minutes Timeout) and send the message to the target queue.
On the target queue I activated a SP and the main code is:
BEGIN TRANSACTION
WAIT FOR (Receive (1) ..) timeout 5000
IF (@ErrorSave <> 0)
BEGIN
INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES ();
END
ELSE
if (@messagetypeName = N'Message')
BEGIN
exec [dbo].[sp_ProcessMessage] @message_body
END
ELSE IF (@messagetypeName = N'EndOfStream')
BEGIN
END CONVERSATION @dialog
END
COMMIT TRANSACTION
My communication has a strange behaviour:
if I type
Begin
insert into TriggerTable values(XMLMessage)
insert into TriggerTable values(XMLMessage)
....
insert into TriggerTable values(XMLMessage)
end
Everything work fine but If I write an insert of 1000 message coming from an another table and I use the
cursor login after I while the transmission stop because the Target queue become inactive. I can see my messages stuck in the Initiator transmission_queue so I think that there is some Poison message that cause 5 rollback and disable the receiver queue.
First I would like to isolate the wrong message and carry on with the insert, my application doesn't have to stop the conversation or return any error, but If I use the Sql server Debugger I'm not able to debug the Target queue's stored procedure.
I suppose that some error happens on the Target queue's stored procedure but how can I first at all find it out?
Maybe it has something to do with the transition wrapped around the RECEIVE command?
I know I have to build in some ability to deal with a posion message. I was thinking that (sort of mentioned in the article http://msdn2.microsoft.com/en-us/library/ms166137.aspx) I would write a special stored procedure that would handle this situation. I don't know how I could activate this. I don't want to poll of course in a waitfor.
Hi! I have crl stored procedure with distributed transaction in it. I really need such transaction. When a poison message occurs 5 time my queue is turned off. I've read about handling poison messages in msdn (save transaction and rollback part of it), but it works only with local transaction. What should I do? An important point is that messages shoold be processed in right order, I can't receive message and put it to the end of queue. I want to try process poison message constantly. I doesn't want to stop receiving messages in that queue. Thanks.
I'm using the Service Broker to parallize my processes (I know that the Service Broker was not designed for that purpose), however it's working quite well.
I use the broker procedure to start procedures which all process all a part of the workload. When the procedure fails because of a lock timeout (or for that concern, for whatever reason), I rollback the transaction (which also roll back my message received on the queue so that it can be retried at a later time.). And this is where my problem lies, if there are 5 sequential rollbacks of messages then the poison message detection kicks in and disables the queue, stopping all the processing. :(
Is there a way to disable poison message detection? I have implemented my own stop-mechanism through a counter system on a per sub-task system so if I could disable poison message detection that would be ideal.
If this is not possible is there a way to turn the queue back on automatically so that it will continue processing the messages on the queue?
I am getting the following Error and I can't find it in the Manual.
Failure to send an event notification instance of type 'BROKER_QUEUE_DISABLED' on conversation handle '{5D273374-E84F-DB11-B3BC-0004239AB15C}'. Error Code = '8429'.
I have checked the service name for the notification and it appears to be okay.
Hi all, i searched everywhere but couldn't find any info on the following error that i'm currently receiving:
"The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'DI'."
I understand that this is due to some problem in my send/receive protocol but how do i fix this problem so that i can continue with my testing? Right now i'm forced to drop my entire test database and reinstall everytime this message shows up because i can't send/receive any messages at that point. Is there anyway to get rid of it?
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
I have set up an sql server which has a column called times. This column is to hold race times however when i enter times such as 22:20:89 with the 89 being milliseconds i am not bale to input this it will only allow me to put times in where milliseconds are below 60. This is a problem especially for the shorter races where half a second is a big difference. Thanks Mike
When trying to connect to a server on a slow connection (satelite), Enterprise Manager times out. I am able to connect with Query Analyzer. Tried to change 'remote login timeout', but that did'nt help, EM seems to timeout long before this value is reached. Any ideas ?
I am trying to add a list of times together retrieved from my database.
They are from seperate records in the same table.
They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?
Below is the SQL statement of getting times from my database if its any help.
SQL StatementSELECT LENGTH_OF_VISIT FROM NOV.MAIN;
Dear All, I need to select records between two datetimes. My Database Structure and sample values are as below Fromdate ToDate 2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000 Query i used select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103))) My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00' Please help Thanks
Hello everyone, I have an odd issue and I can't seem to figure it out. I am using SQL Server 2005 and I am using Transactions in combination with Try/Catch blocks. My query (executing the stored procedure) never seems to finish the query in query analyzer. I have removed all statements that exectute or select, etc upon the database except for a simple Select 'test' as test so I would have something in my Begin/End blocks. Below is my stored proc: ALTER PROCEDURE [dbo].[ar_mainCheckRecon] @tripID int, @payment money, @adjustment money, @serviceType tinyint, @checkTpId int, @checkTlId int, @checkNbr varchar(20) AS BEGINSET NOCOUNT ON; BEGIN TRY BEGIN TRAN select 'test' as test
END TRY BEGIN CATCH RAISERROR('Error commiting transaction', 16,1) ROLLBACK TRAN SELECT '0' as success END CATCH
COMMIT TRANSELECT '0' as errorCode
END I call upon the sproc by running this line in query analyzer: exec ar_mainCheckRecon 433636, 0, 500, 0, 500, 0, 23 Is there something I am overlooking as to why the query keeps running and never stops? My current execution time is at 3 mins accoring to query analyzer! Thanks,John
When using a SQL Server DateTime column to store just times, e.g. '6:00 PM' I've noticed some weird inconsistency. If I insert a time in a SQL statement, such as... INSERT INTO myTable (StartTime) VALUES ('6:00 PM') It winds up going in as '1/1/1900 6:00 PM'. This is okay. This is in fact what I want (unless of course the base day for sql server is in fact 12/30/1899, which is my next question). If, on the other hand, I insert a time value by hand, using SQL Server Management Studio, then after I insert '6:00 PM' it winds up as '<Today's Date> 6:00 PM', i.e. '1/20/2008 6:00 PM'. Also, if I insert it using a SQL Parameter, such as... da.InsertCommand.Parameters.Add("@StartTime", SqlDbType.DateTime, 8, "StartTime") ...Then, if StartTime is 6:00 PM again, it also winds up going in as '<Today's Date> 6:00 PM'. Is there a way to control this behavior (other than to programmatically append a base date to the time I'm inserting)? Finally, what is the base day for SQL Server 2005? Is it 1/1/1900 or 12/30/1899? I think I've seen both. Thanks! Aaron
I have two tables: (results) one with numeric results and one with names (importance_scale)
SELECT * FROM results
name i1 i2 i3 i4 i5 ---- ---- ---- ---- ---- john 2 1 2 2 2 anna 0 2 2 2 2 phil 2 2 2 2 2 dave 1 0 2 2 2
SELECT * FROM importance_scale
imp_value imp_name --------- ------------ 2 very important 1 important 0 not important
i1,i2 etc. value it is a imp_value from table 'importance_scale'
I would like to make report which will show all results in this format (in example only first record): name i1 i2 i3 i4 i5 etc. ----- --- --- --- --- --- john very important important very important very important very important etc.
I was thinking about using CASE I don't want use cursor. What is the best way?
I want to enter times for running event performances (like 10:26.35 for a person's 2-mile time, for instance). I am using sql server 2000. This is an ongoing concern for me since I do a lot of work with running events. Can someone give me some direction as to how I can best deal with this?
The datetime data types do a lot of converting to 'time-of-day' and that is not what I want. For instance, if I enter a 2-mile time of 10:26.35 (a pretty typical 2-mile time) my db converts it to 10:26:35 AM.
If I use varchar data type there is a ton of code to write when I want to sort the data.
Jeff writes "I am trying to write a query to retrieve information that was processed between two times of the day (2:00 pm to 6:00 pm) from a date range... I am perplexed; I am new to SQL and am trying not to become frustrated, but this seems to be beyond my skill level. Any guidence would be greatly appreciated!"
Hi I'm new to SQl..I have some monthly data which is being encoded as Varchar...Say forthe month of January 2005 it wud look like '200501'. For each of mymonth I have a coloums which gives me datetime and volume of the job.This is what I'm doingSelectMonth,Time ,Sum(Volume)[color=blue]>From Table A[/color]Where (( Month like '200501%' ) and (Time between '2005-01-0102:00:00.000' and '2005-01-31 06:00:00.000))group by month, timeorder by monthPRoblem: What I want is all the volume between 2am and 6am on every dayof the month?The above query naturaly will give me everything between first and lastof the month...Any help is appreciated...Balaji
I am using an MS Access front end to input data into a SQL serverdatabase. From Access I am generating an update query utilising aninline function. My problem is that when I update a time value,instead of updating the field with something like 08:00:00 it updatesit to 1/1/1900 08:00:00. Is there a way to force the update to onlyuse the hours minutes and seconds and ignore the date part?Any help would be greatly appreciated.RegardsTrevor
Hi guys,I have a field in my DB called EventDate as a DateTime field,therefore it holds both the date and time together like this:'2004-10-14 08:42:57.000'.I need to add together all the times in this column for a particulardate range (BETWEEN).Any suggestions will be great.ThanksSunny:)
Trying to obtain a full list of SQL 6.5 error messages into a file. There are 2,215 messages present in master.sysmessages, but there is a total of 2,733 messages listed in the dialog box within enterprise manager. Message numbers 17026 thru 19020 don't seem to be stored in the sysmessages table.
Anyone know how I could obtain an extract to obtain a full list of messages ?
After sending a message the message is just gone. Our transmissionand and out our transactionqueue is empty.
This is our CLIENT: CREATE MESSAGE TYPE HelloWorldMessage VALIDATION = WELL_FORMED_XML ; GO CREATE CONTRACT HelloWorldContract ( HelloWorldMessage SENT BY INITIATOR); GO CREATE QUEUE [dbo].[InitiatorQueue] ; GO CREATE SERVICE InitiatorService ON QUEUE [dbo].[InitiatorQueue]; GO drop endpoint SqlEndpoint CREATE ENDPOINT SqlEndpoint STATE = STARTED AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL) FOR service_broker(AUTHENTICATION = WINDOWS) GO use master GRANT CONNECT ON ENDPOINT:qlEndpoint to public GO use test1 DROP ROUTE SqlRoute GO CREATE ROUTE SqlRoute AUTHORIZATION [dbo] WITH SERVICE_NAME = 'TargetService', BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66', ADDRESS = 'TCP://localhost:5900'; GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key encryption by password = 'azertyuiopqsdfghjklm' go
SERVER
CREATE MESSAGE TYPE HelloWorldMessage VALIDATION = WELL_FORMED_XML ; GO CREATE CONTRACT HelloWorldContract ( HelloWorldMessage SENT BY INITIATOR); GO CREATE QUEUE [dbo].[InitiatorQueue] ; GO CREATE SERVICE InitiatorService ON QUEUE [dbo].[InitiatorQueue]; GO drop endpoint SqlEndpoint CREATE ENDPOINT SqlEndpoint STATE = STARTED AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL) FOR service_broker(AUTHENTICATION = WINDOWS) GO use master GRANT CONNECT ON ENDPOINT:qlEndpoint to public GO use test1 DROP ROUTE SqlRoute GO CREATE ROUTE SqlRoute AUTHORIZATION [dbo] WITH SERVICE_NAME = 'TargetService', BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66', ADDRESS = 'TCP://localhost:5900'; GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key encryption by password = 'azertyuiopqsdfghjklm' go Hopefull you have an idea?
Hi, I am developing an app that manages race results and I need some advice on the best way to store the race times in sql server. As far as I know, the best datatype would be datetime. With the data stored, I will need to be able to do things such as:
Hello, I have been working on this stored procedure, it pulls the results that i want, but it takes a very long time to execute and about half the time it times out, i have tried changing the timeout settings on the sql server to 0 (no timeout) but it seems to have made no difference. Could anyone help with making the query a bit more effiecient? SELECT DISTINCT webStats.id, webStats.ip, webStats.useragent, browsers.browsername, os.osname, webStats.datestamp, webStats.hourstamp, webStats.hostname, webStats.refurl, webStats.refhost, webStats.entrypage, webStats.visitcount, webStats.country, searchengines.enginename, IpToCountry.COUNTRY AS CountryName FROM webStats LEFT OUTER JOIN IpToCountry ON webStats.country = IpToCountry.CTRY LEFT OUTER JOIN searchengines ON webStats.useragent LIKE '%' + searchengines.agentstring + '%' LEFT OUTER JOIN browsers AS browsers ON webStats.useragent LIKE '%' + browsers.agentstring + '%' LEFT OUTER JOIN os ON webStats.useragent LIKE '%' + os.agentstring + '%'
WHERE (webStats.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE()) ORDER BY webStats.datestamp DESCThanks for your help. Bart
I have some code that opens a SqlConnection to a MSSQL 2000 database. The connection opens great everywhere except inside my implementation of IPasswordProvider.GetPassword(). It just hangs there, and then throws a timeout. I have tried using both trusted connections, and userid/password connection strings. I notice that within GetPassword, the authenticated user is COMPUTERNAMEASPNET, whereas in the web method that actually is getting called, the authenticated user is my personal user account. The SqlConnection opens fine in the actual web method.
Can anyone tell me what could stop a SqlConnection from opening while inside GetPassword()? I kinda need to look up passwords in the database.
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
I scheduled a Bulk copy operation, and have been getting some inconsistent failures.
Last night the job was set to run every 3 hours, I expect it to take about 1 hour to finish.
The second itteration of the job failed, the first and third ran fine. Here is the error from DTS
Any suggestions? Is there a better/stable Data Provider for SQL?
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSBulkInsertTask_1 DTSRun OnError: DTSStep_DTSBulkInsertTask_1, Error = -2147217900 (80040E14) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040... Process Exit Code 1. The step failed.
