Retained Message Status And Deletetion ?
Sep 27, 2007
Hi There
We do not use message retention in our application.
However today i dicovered messages still sitting in a queue, on further investigation i found that the messages had been processed, the problem was that i had mistakenly altered a queue with rentention ON earlier.
So the messages on the queue were simply retatined messages.
But i have 2 questions:
1.Is there a way to "delete" these retained messages without ending the conversation, we use permanant conversations so ending the conversation is a last resort.
2.Why is the message status of the retaitned messages 0 ?
BOL : From the topic "Message Retention"
"For queues that specify retention, the RECEIVE command sets the status of received messages to 1 rather than deleting the message."
However my retained messages have a status of 0 ? Also i have confirmed that new not yet received messages have a status of 1.
BOL : From topic "RECEIVE (Transact-SQL) "
" Message Status
0=Ready1=Received message2=Not yet complete3=Retained sent message
"
But from my testing retained = 0 and Ready = 1, is this a mistake in BOL or am i missing something ?
Thanx
View 3 Replies
ADVERTISEMENT
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
May 31, 2001
As part of a DTS job, I run an ActiveX script that loops through each file in a directory and imports the data into a single table. I'd like to display each filename as it is being imported, but I don't want to use the msgbox command for obvious reasons. Is there a status message box I can fill to display whatever text I want? If so, what command would I use?
View 2 Replies
View Related
Mar 13, 2008
Hello,
I have a table which i use to queue messages in. in this table there is a status column along with all the other message colums (ID, body, phone, time, etc). I use tinyint to express the diffrent status values (my frist question is, should i continue using tinyint, or is there some sort of enum, or another suggestion?). the value 1 means its a new message. An external process polls this table and pics up messages that are new.
Some messages fail, and so their status is changed to two. An external post process comes along and does what it needs to with messages that have status' which it handles, for example message failures, it might erase it, or change its status back to 1.
I expect to handle thousands of messages per hour. Should I create another table, which has only id and status as columns. And when a message fails I will place it in here instead of placing it in my original table. then the post process only polls this table and joins with my main table. instead of having both processes poll the single table. Would this be more efficient for sql internal seraching? since it has a smaller table to search in? or does it not make a diffrence.
Thank you.
View 5 Replies
View Related
Jul 4, 2000
In SQL Server 7.0 I right click on the database, select Tasks, Select backup, then indicate backup of database (complete) to tape. Overwrite (I put tape into NT Server), OK, then it says backup in progress and then I get the message shown in the subject...
Any advice please? I have backed up NT files on this tape before and have tried a second tape...
Thanks much,
Steve Brown
View 1 Replies
View Related
Jul 20, 2005
I am using SQL server 2000 with Uniface (4GL).I am running a process which commits every 30 seconds or so on onemachine and another user is trying to perform a small update at thesame time on the one of the tables whose data is modified by the firstprocess.I accept that the two second process may get blocked at times by thefirst but expect that to only happen rarely due to the likelihood ofboth processes trying to update the very same row in a large table.Other users are also on line at the same time performing updatesacross the database.What I have witnessed though was the second process being blocked bythe first (confirmed by looking in enterprise manager) and REMAININGBLOCKED EVEN AFTER A COMMIT in the first process!!My big problem is that I have only seen this happen once on a customersite and have not been able to reproduce it myself to provide anyfurther information.The application is set to use row level locking and is also in readuncommited mode.Any help or suggestions would be greatly appreciated.Bob.
View 3 Replies
View Related
Jul 23, 2007
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
View 3 Replies
View Related
Aug 8, 2007
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
View 5 Replies
View Related
Aug 8, 2007
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
View 1 Replies
View Related
Jan 7, 2008
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
View 11 Replies
View Related
Nov 17, 2006
Hello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
View 1 Replies
View Related
May 3, 2005
Returning "completed" when status = 1 and "not completed when status = 0
View 3 Replies
View Related
Aug 28, 2002
SQL7, sp3, NT4...
Unless I grant a developer "system admin" role, they are unable to see whether or not any job *not* owned by themselves is running?
EX: Developer A can't see whether Developer B's job is executing? Why? I've granted them both db_owner in msdb, and all permissions possible in msdb. Is there an extended sp I can look for?
Thanks,
View 1 Replies
View Related
Mar 4, 2004
Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advance
View 1 Replies
View Related
Jul 23, 2005
I have a table with columns c1, c2, c3, c4.if all nulls or blanks. Status = 0if c1 assigned but no c2, c3, and c4, then status = 1if c2 assigned but no c3 and c4, then status = 2if c3 .. then ..if c4 .. then ..I want to have one SQL to get the status like (ignored checking forblanks here for demo)SELECT Status = (if not c4 is null then 4else not c3 is null then 3else not c2 is null then 2else not c1 is null then 1else 0)FROM mytable.Thought of using CASE ... WHEN ... but it is only on one colum.Any better idea.ThanksJohn
View 1 Replies
View Related
Sep 25, 2006
In SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.Thanks a lot!Penn
View 1 Replies
View Related
Sep 1, 2007
Hi all - am having trouble working out what would be the best way to do this (most efficient)I have written an order, despatch and accounts system. To get a list of orders ready for despatch, I have written a query which calculates the amount of items ordered against the amount of items despatched from that order. If there are any outstanding items, then this order is in the list of orders which are outstanding for despatch.
I am having trouble working out how to go about creating a list of orders ready do be invoiced... Orders that are ready to be invoiced can be completely despatched orders or part despatched orders. That is to say that if an order is only half despatched, my client still wants to be able to raise an invoice against that part of the order. I envisage creating a query which checks my orderline table to see if the item has been invoiced and creating the list from there. But as the application is used more and more, this query will take longer to run? ANy ideas?
Thanks.
Steve.
View 1 Replies
View Related
Jul 9, 2001
Hello,
Can anyone guide me to monitor whether Production Sql server is up and running and would e-mail or page me if by chance it has stopped.
Any help is appreciated.
Thanks.
Priti
View 5 Replies
View Related
Jun 24, 2000
is there any way to make the status of a package execution to be success without caring about failure of certain steps in the package?
View 1 Replies
View Related
Sep 26, 2002
I need to know the meaning of the status bit in sysdatabases because BOL only reports some of them.
For SQL 7.0 SP1 I have some database with a status bit equal to 0 (zero) and equal to 12.
For SQL 2000 SP2 I have some database with a status bit equal to 0 (zero),24,20 and 1073741840.
So I would really appreciate if someone can explain the meaning of these status bits.
Thank you very much.
Franco
View 2 Replies
View Related
Apr 14, 2003
Hi,
Is there any way to update the status bar in Query Analyzer.
Suppose I am running a script which updates data to a file. I want to know the status showing the percentage it completed.
Thanks
:confused:
View 1 Replies
View Related
Jun 8, 2007
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
Any recommendations?
View 5 Replies
View Related
Jun 27, 2004
I have a question I hope someone can help me with.
My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
Thanks for any help,
Andrew
View 2 Replies
View Related
Mar 27, 2008
Hi
My boss and I have recently started designing a new database, and we've got into a bit of a debate about creating status tables (e.g. order statuses).
It was my intial instinct to create a separate table for each kind of status (e.g. an OrderStatuses table, a JobStatuses table, etc), however my boss wanted to create one table containing all the statuses, with an extra field for defining what kind of status it is.
I was just wondering what others thought was the best approach.
Thanks
Tom
View 5 Replies
View Related
Jun 3, 2008
Hi Gurus,
While iam connecting to servers through management studio iam facing
iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
Is there any comand to view server status ?
Thanks,
ServerTeam
View 3 Replies
View Related
Jun 5, 2008
Hello All,
Can someone help me with this query?
I have the following table and data structure
Table1: Orders
Field1: OrderID
Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate
Sample Data:
Table1:
11
12
Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008
Wanted Results:
11,4,6/4/2008
12,2,6/2/2008
View 3 Replies
View Related
Jul 16, 2013
How can i exclude those with more than one status? i tried using rows but it seems doesn't work.
ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close
the result should appear as:
ID status
2 new
2 close
4 new
4 close
View 4 Replies
View Related
Mar 7, 2015
I have a table of status_Master where the details are maintained by street name with status but I need the result of total count by each status against the street name.
Status_Master
Sl NoStreetNameStatus
1 Delhi Pending
2 Delhi Working
3 Delhi Working
4 Mumbai Pending
5 Mumbai Pending
6 Delhi Working
7 Delhi Problem
8 Mumbai Working
9 Mumbai Problem
50 . .
100 . .
200 . .
Result as below
StreetNameWorkingPendingProblem
Delhi 3 1 1
Mumbai 1 2 1
View 2 Replies
View Related
May 12, 2006
table1 has 3 fields:
bookNumber
123
234
345
status -- status defaul value is 0
partnerNumber
1
2
3
table 2 has 2 fields
bookNumber
234
345
567
789
partnerNumber
1
2
3
4
A stored procedure named remove_books, which remove books if the conditions are true.
If stored procedure is executed. That means, it removes the bookNumbers from table2 if the conditions are true. Lets say it removes 234 from table2
then
do update in table1 status = 1 for BookNumber 234.
View 19 Replies
View Related
Aug 6, 2007
I need to know how can i see the replication status. I have 4 servers with ms sql 200 standard server and hundreds of commnad to replicate. Sometime is important to know how mush of this commnads is replicated.
Thank You.
View 10 Replies
View Related
Sep 9, 2007
T-SQL for checking which jobs are currently running ?
And
what happen if index rebuild job overlap with shrink log file job
Is database wont allow to insert or update for other application
Thanks in Advance
Dina Satam
View 1 Replies
View Related
Sep 25, 2007
Hi All,
Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?
regards
Bill
View 3 Replies
View Related
Jun 28, 2006
Hi everyone,
while executing sp_who sub directory, I see a column which makes me totally confused. This column is status column.
I understood nothing about its usage. So would you please inform me ?
Thanks
View 1 Replies
View Related