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

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

Does A Status Message Box Exist?

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

Table Design Issue - Message Failure And New Status

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

Backup Failing Message: Tape0 Failed, Status=1117

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

Lock/block Retained After Commit

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

Can't Find Or Load Message DLL. Message DLL Must Be In Path Or In Current Directory.

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

This Message Could Not Be Delivered Because Its Message Timestamp Has Expired Or Is Invalid

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

This Message Could Not Be Delivered Because Its Message Timestamp Has Expired Or Is Invalid.

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

Data Conversion Failed. The Data Conversion For Column Value Returned Status Value 4 And Status Text Text Was Truncated Or On

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

Message Type And Message Body..............

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

Returning Completed When Status = 1 And Not Completed When Status = 0

May 3, 2005

Returning "completed" when status = 1 and "not completed when status = 0

View 3 Replies View Related

Can&#39;t See Job Status In EM?

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

How To Get The Status Of A Sql Job

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

How To Get The Status?

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

Job Running Status

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

Order Status

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

SQL Server&#39;s Status

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

DTS Package Status

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

Status Bit In Sysdatabases

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

Updating The Status Bar

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

Status Querys

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

Checking Job Status Using SQL-DMO

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

Status Tables

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

Server Status

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

Get The Current Status

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

Exclude Status Appear More Than Once In Same ID

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

How To Get The Status Count

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

Update Status?

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

Replication Status

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

How To Find Status Of SQL Job - Please Help

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

Suspended Status

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

Status Column

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







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