Conversion failed when converting from a character string to uniqueidentifier. - PLEASE HE

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.

Conversion failed when converting from a character string to uniqueidentifier.

My Code is shown below:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

This has worked before but isnt' anymore. Any ideas?

jsmith3465:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

have you tried as...

comSQL.Parameters.AddWithValue("@.PROPERTYID",New Guid(Format("werwerwerwerwerwerwerwerwerwerwe","{0:########-####-####-####-############}")))

|||

I tried adding the New Guid() and that did not solve the problem either. Anymore ideas? I am trying to convert Text to Unique Identifier for storage in SQL Server 2005.

Thanks for all of your help!

Ryan

Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh

Conversion ERROR

This is the error message I get: :(
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the query:
Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code,
dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee,
Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend,
GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B,
Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F,
Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J,
Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N,
Plan_Mas.CR_O) AS PremiumRate
FROM Qual_ins, Plan_Mas
WHERE Qual_Ins.CompanyCode = 'ACME'
AND Qual_ins.ParticipantCode = 4
AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode
AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code
AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code
Order BY Qual_ins.Ins_Code

Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message.
Any help is appreciate!
I'm new to this... "Hello" to all!

ShuviBased on the error message, I'd guess that you are trying to convert a string (CHAR or VARCHAR) to a DATETIME or a SMALLDATETIME. If that is the case, then one or more rows in your data isn't a valid date string.

-PatP

Conversion error

I am getting the error message Error converting data type varchar to float when running the following query:

Code Snippet

select top 175816
AV.intItemID,
AV.intAttrID,
-- AV.vchValue,
CAST(AV.vchValue AS float) AS Test,
0
from tblAttrVals AV
join tblAttributes AA
on AA.intAttributeID = AV.intAttrID
and AA.intDataTypeID in (2, 3)
and (1 = isnumeric (AV.vchValue))
order by AV.intItemID, AV.intAttrID

Here is what is strange. If I bump the top count down by one it succeeds. And even stranger, if I leave the top count the same and uncomment out the line in the select statement that shows the value being converted it succeeds.

Any ideas? This seems like a bug.

Chris:

Can you show us the specific data that is giving you trouble?

|||

I found the issue. It actually had nothing to do with the data that is being returned. It had to do with the data not being returned.

Here is the info from a post that helped me:

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)
2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate
FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.

sqlsql

Conversations

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

This subject is quite intricate and has many facets. It also appears often when discussing Service Broker, so I'll try to address it in a series of blog articles. I've started this today, see http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx

HTH,

~ Remus

|||Thanks Remus. I am starting to notice that I am getting messages such as "There is insufficient system memory to run this query." and "There is insufficient memory available in the buffer pool." when I have a lot of conversations occurring (about 300K records in conversation_endpoints view). I am thinking that this is directly attributable to me creating a new conversation for every audit record(s) created. What is the best way for me to test that this is the case...that Service Broker is really the culprit in tying up all of my system memory?|||look in sys.dm_os_memory_clerks to see how memory is allocated|||Ok, sounds good. I am almost 100% sure it relates to me creating a new dialog for each message I pass.

Do you plan to post another blog anytime soon regarding reusing conversations? The situation I am currently trying to figure out is how to handle closing (or handling) the conversations so that I can reuse them....more specifically:
1. I check a table to see if there are any dialog handles free to use. If they are not, I create a new one and send a message to a queue.
2. The activation proc on the queue gets the message from the queue, but the handle it receives is not the same as the one that was created when I sent the message. It seems that this handle represents the target (from sys.conversation_endpoints). At this point, I can't close that end of the conversation when I have processed the message because if I do, it puts the other end, the initiator, in a disconnected_inbound state, which means I can't reuse it later and send another conversation on it. So, what is the best way to handle that? I want to be able to reuse the handle that I originally created, but not really sure the best way to do it. Thanks in advance.
Tim|||

Yes, I plan a post soon. Here is how I recommend doing it: have a criteria when a dialog should be 'recycled' (ended and a new one started). Good candidate criterias would be 'after N messages sent' or 'X minutes/hours/days after was created'. When this criteria is met, the initiator should sent a special message, something like 'EndOfStream' and removes the handle from the association table (So subsequent usp)Send calls will start a new one). When the target receives this EndOfStream message, it responds with and END CONVERSATION. When the initiator receives the EndDialog message, it ends it side (initiator also must have activation on it's queue). I have arguments why I prefer this pattern, I'll detail in blog.

HTH,

~ Remus

|||Thanks Remus, I eagerly look forward to it. Also, here is a small dump of my dm_os_memory_clerks view when I was receiving the errors: type single_pages_kb multi_pages_kb OBJECTSTORE_SERVICE_BROKER 884584 0 CACHESTORE_BROKERTO 176936 0 MEMORYCLERK_BHF 146696 0 OBJECTSTORE_LOCK_MANAGER 126064 0 OBJECTSTORE_SERVICE_BROKER 101168 0 MEMORYCLERK_SQLSERVICEBROKER 19256 192 MEMORYCLERK_SQLSTORENG 10624 7088 CACHESTORE_OBJCP 6304 32 MEMORYCLERK_SOSNODE 6224 6048 MEMORYCLERK_SQLGENERAL 1832 2016 I also started getting a fun new error in one of my activation procedures: Internal Error: Text manager cannot continue with current statement. Run DBCC CHECKTABLE., which I think is directly related to me creating a new dialog for every message created.|||

This is a procedure I wrote to manage a dialog pool. Basically it creates a number of conversations and then uses them until the number available drops below a certain threshold value. It then selects one at random (so you aren't reusing the same one every time). It works great, but I'd like to hear any comments from the experts.

CREATE PROCEDURE [usp_DialogFactoryCreate]

(

@.minDialogs AS INT,

@.maxDialogs AS INT,

@.fromServiceName AS NVARCHAR(256),

@.toServiceName AS NVARCHAR(256),

@.contractName AS NVARCHAR(256),

@.selectedDialog UNIQUEIDENTIFIER OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.dialogCount INT;

DECLARE @.conversationHandle AS UNIQUEIDENTIFIER;

-- State should be either STARTED_OUTBOUND or CONVERSING

SET @.dialogCount = (SELECT COUNT(*) FROM sys.conversation_endpoints WITH (NOLOCK)

WHERE far_service = @.toServiceName

AND state IN ('SO', 'CO'));

-- Create dialogs until we hit the maximum

-- This will also dictate how many activated procedures will be created for the queue

IF ( @.dialogCount < @.minDialogs)

BEGIN

WHILE (@.dialogCount <= @.maxDialogs)

BEGIN

-- Create dialogs with infinite lifetime for our pool

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE @.fromServiceName

TO SERVICE @.toServiceName

ON CONTRACT @.contractName

WITH ENCRYPTION = OFF;

SET @.dialogCount = @.dialogCount + 1;

END

END

-- Randomly select a dialog conversation

SET @.selectedDialog = (SELECT TOP(1) conversation_handle

FROM sys.conversation_endpoints

WHERE far_service = @.toServiceName AND state IN ('SO', 'CO')

ORDER BY NEWID());

RETURN (0);

END

GO

|||Variuos threads/transaction calling this procedure will conflict for the same conversation and cause contention.|||

Hi Remus,

Ive solved my memory problem by reusing dialogs based upon how long they have been in use. However, now I am running into another tricky problem. What I am noticing when many messages are being passed around is that internal service broker tables are causing a huge number of locks in the database, sometimes over 100,000 of them, which will really lock up other processes on the server. How are these internal tables (QUEUE_MESSAGES_) constructed? Is it a matter of one per message received and processed? I have a feeling that it is being caused by me receiving (RECEIVE TOP(1)) one message at a time and processing that way. I know it isn't a great way to do it, and it is slower, but is it what is causing all of these internal locking in the database? BTW...reusing a dialog based upon how long it has been open was a great idea...thank you very much for it.

Tim

Conversation Timers in ServiceBrokerInterface

Using conversation timers, I would like to send a message to myself. I could then use the self-addressed message to check on the availability of a provider. What would be the recommended approach for doing this using the ServiceBrokerInterface? It seems that I might need to add a method to the Service class. Is it correct? Thanks,

You will need to add a method. I would add it to the Conversation class.

public void BeginTimer(TimeSpan timeout);

Rushi

sqlsql

Conversation Timer versus LIFETIME

I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature.

Thanks,

Eugen F wrote:

If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue?

If the dialog reaches its lifetime before is closed then it will be automatically errored. An errror message will be enqueued into both initiator and target service's queues. Once the dialog has errored, no further messages can be sent on the dialog.

HTH,
~ Remus

|||

Thanks,

is there documentation that describes all the queue columns and how to interpret them?

|||The online documentation seem to be OK, but not quite comprehensive.|||

If there are specific sections of the documentation that need more explanation, please submit feedback to our support website:

http://connect.microsoft.com/site/sitehome.aspx?SiteID=68

Thanks,

Rushi

Conversation Timer problem : Timeout not effective

Hi,

I am using conversation Timer for delaying a message for a few seconds but I can see the message immediately in the queue.

Here is the code i am using. This is a part of a stored procedure I have used.

BEGIN CONVERSATION TIMER ( @.h ) TIMEOUT = @.DelayBySeconds;

SEND ON CONVERSATION @.h

MESSAGE TYPE [sendmsg]

(@.msg);

I am executing this stored procedure with following statements.

exec set_ssb_msg 'test3', 25;

exec set_ssb_msg 'test1', 1;

select * from q1

I was hoping to see just the 'Test1' and see test3 after 25 seconds. But I could see both the messages in a queue as soon as i run the stored proc.

If I execute a receive command on the queue, I am receiving 'test3' first and then 'test1'. This is exactly opposit of what i expected.

Can you please let me know if I am doing anything wrong or missing a step.

Any help is greatly appreciated.

Thanks,

Don.

Conversation timers have no relation whatsoever to sent messages, they affect the local endpoints only. You should expect a DialogTimer message in your sender's queue to show up after 25 and/or 1 seconds. The messages sent are unaffacted by timers. Also, although is not clear in your example, it seems that you're begining a new conversation for each message sent. The message order is only guaranteed within a conversation, and as such your expectations of a certain order on the target queue are not justified.

Conversation that dont end.

Hi There

Message ordering is of utmost importance in our application.

As i found in testing the only way to ensure message ordering is if they are in the same conversation.If you send multiple messages in different conversations there is no garantee which will be processed first.

Therefore i will be creating conversations that last "forever", that is using a single conversation.

I plan on doing a BEGIN DIALOG CONVERSATION when an inititator site is setup and writing the conversation handle guid to a table.

I will them simply SEND ON SONVERSATION using the guid, i will never issue a end conversation from target or initiator.

Is this theory solid, ie: is there a better way or best practice to do this?

I know that conversatons persist with sql server restarts, however what happens if an initiator site db is restored ?

I was thinking of adding logic to first check if a conversation endpoint exists with the specified guid if not , then start another conversation. But is this the best way?

Thanx

If you need ordering, you must use a single conversation (dialog) to send your messages over. You must be aware however, that imposing this requirement means that messages can only be processed serially and you will not have the benefits of parallel processing and scaling out.

Conversations are durable, so they will persist restarting the engine, detaching/attaching the database, etc. However, if you take a backup, let the conversation continue (i.e. more messages are exchanged) and then restore the database, the endpoint's last sent message sequence number will be decreased. If you were to send a message from that endpoint now, it will be assigned a lower message sequence number. The remote endpoint will not ack this message sequence number since it is lower than the one it already has acked and hence the conversation will not be able to continue. At this point, the only option is to error or end the dialog (manually) and start all over.

|||

HI Rushi

Yes i have already factored in the fact that no serial processing can occur.

But as far as the rest goes is the thoery ok, i mean starting a dialog when an inititator is setup and storing and using the conevrsation guid for the life of the conversation.

If the DB is restored and tlogs replayed to the latest point in time messaging should be ok? I also have a transaction audit i mirror at initiator and target so if a restore happens i can set the initiator in sequence. Or like you said worst case scenario end and restart the dialog.

Just want to make sure there is not something else vital i am overseeing when using persistant dialogs.

Thank You for the feedback.

conversation handle

hi all, i am having a hard time getting the conversation handle id to match
..
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:

> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. I
f
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:

> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>sqlsql

conversation handle

hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query?
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tin
The conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query?
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>
|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:

> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>
|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:

> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>

conversation handle

hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> > hi all, i am having a hard time getting the conversation handle id to
> > match ...
> >
> > scripts that create the relevant objects ...
> >
> > create message type QueryMessage validation = none
> > create contract QueryContract (QueryMessage sent by initiator)
> > create queue QueueSender
> > create queue QueueReceiver
> > create service Sender on queue QueueSender
> > create service Receiver on queue QueueReceiver (QueryContract)
> >
> > send with conversation handle id ...
> >
> > begin transaction
> > declare @.conversationhandle uniqueidentifier;
> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> > begin dialog @.conversationhandle
> > from service [Sender]
> > to service 'Receiver'
> > on contract [QueryContract]
> > with encryption = off;
> > send on conversation @.conversationhandle
> > message type [QueryMessage] ('blah blah blah;');
> > commit transaction
> >
> > after i ran the above send query a few times, and do the below select ...
> >
> > select conversation_group_id, conversation_handle, cast(message_body as
> > varchar(1000)) from QueueReceiver
> >
> > i got all kinds of different conversation_group_id and
> > conversation_handle?
> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> > specified
> > in the send query'
> >
> > and when i do the below receive query, it says:
> > Msg 8426, Level 16, State 20, Line 1
> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> > found.
> >
> > receive top(1) convert(varchar(1000),message_body) as message
> > from QueueReceiver
> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >
> > please help! thanks in advance!
> >
> > - tin
> >
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>> The conversation handle is an output parameter for the BEGIN DIALOG
>> statement. Each time you run the statement, a new conversation will be
>> created and the @.conversationhandle will get a new value assigned to it.
>> If
>> the variable had a previous value, it will be overwriten.
>> So the conversation with the handle
>> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> was never created, hence the error on the RECEIVE statement.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "tin" <tin@.discussions.microsoft.com> wrote in message
>> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>> > hi all, i am having a hard time getting the conversation handle id to
>> > match ...
>> >
>> > scripts that create the relevant objects ...
>> >
>> > create message type QueryMessage validation = none
>> > create contract QueryContract (QueryMessage sent by initiator)
>> > create queue QueueSender
>> > create queue QueueReceiver
>> > create service Sender on queue QueueSender
>> > create service Receiver on queue QueueReceiver (QueryContract)
>> >
>> > send with conversation handle id ...
>> >
>> > begin transaction
>> > declare @.conversationhandle uniqueidentifier;
>> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> > begin dialog @.conversationhandle
>> > from service [Sender]
>> > to service 'Receiver'
>> > on contract [QueryContract]
>> > with encryption = off;
>> > send on conversation @.conversationhandle
>> > message type [QueryMessage] ('blah blah blah;');
>> > commit transaction
>> >
>> > after i ran the above send query a few times, and do the below select
>> > ...
>> >
>> > select conversation_group_id, conversation_handle, cast(message_body as
>> > varchar(1000)) from QueueReceiver
>> >
>> > i got all kinds of different conversation_group_id and
>> > conversation_handle?
>> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
>> > specified
>> > in the send query'
>> >
>> > and when i do the below receive query, it says:
>> > Msg 8426, Level 16, State 20, Line 1
>> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
>> > found.
>> >
>> > receive top(1) convert(varchar(1000),message_body) as message
>> > from QueueReceiver
>> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> >
>> > please help! thanks in advance!
>> >
>> > - tin
>> >
>>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> > ah, no wonder. another question:
> >
> > if i want an application to send messages to 2 applications, then i guess
> > i
> > will have to create two queues? i was trying to use one queue with 2
> > conversation handles.
> >
> > many thanks.
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> >> The conversation handle is an output parameter for the BEGIN DIALOG
> >> statement. Each time you run the statement, a new conversation will be
> >> created and the @.conversationhandle will get a new value assigned to it.
> >> If
> >> the variable had a previous value, it will be overwriten.
> >>
> >> So the conversation with the handle
> >> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> was never created, hence the error on the RECEIVE statement.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> HTH,
> >> ~ Remus Rusanu
> >>
> >> SQL Service Broker
> >> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >>
> >>
> >> "tin" <tin@.discussions.microsoft.com> wrote in message
> >> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> >> > hi all, i am having a hard time getting the conversation handle id to
> >> > match ...
> >> >
> >> > scripts that create the relevant objects ...
> >> >
> >> > create message type QueryMessage validation = none
> >> > create contract QueryContract (QueryMessage sent by initiator)
> >> > create queue QueueSender
> >> > create queue QueueReceiver
> >> > create service Sender on queue QueueSender
> >> > create service Receiver on queue QueueReceiver (QueryContract)
> >> >
> >> > send with conversation handle id ...
> >> >
> >> > begin transaction
> >> > declare @.conversationhandle uniqueidentifier;
> >> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> > begin dialog @.conversationhandle
> >> > from service [Sender]
> >> > to service 'Receiver'
> >> > on contract [QueryContract]
> >> > with encryption = off;
> >> > send on conversation @.conversationhandle
> >> > message type [QueryMessage] ('blah blah blah;');
> >> > commit transaction
> >> >
> >> > after i ran the above send query a few times, and do the below select
> >> > ...
> >> >
> >> > select conversation_group_id, conversation_handle, cast(message_body as
> >> > varchar(1000)) from QueueReceiver
> >> >
> >> > i got all kinds of different conversation_group_id and
> >> > conversation_handle?
> >> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> >> > specified
> >> > in the send query'
> >> >
> >> > and when i do the below receive query, it says:
> >> > Msg 8426, Level 16, State 20, Line 1
> >> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> >> > found.
> >> >
> >> > receive top(1) convert(varchar(1000),message_body) as message
> >> > from QueueReceiver
> >> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> >
> >> > please help! thanks in advance!
> >> >
> >> > - tin
> >> >
> >>
> >>
> >>
>
>

Continuos Starting up database 'myDatabase'

Hi group. I have this problem. When I review the Sql Log appears the same
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo

Consuming Multiple Messages In Parallel from Multiple Windows Services

Hi Remus

What if I need multiple clients to read (RECEIVE) the same message?

Would it be possible?

Thanks

No.

A message can only be received once. Normally the first RECEIVE statement removes it from the queue, so no other RECEIVE can find the same message.

Also there is no way for the clients to specify the message to be received. With a WHERE clause the RECEIVE statement at most can restrict the result set to a particular conversation, but not to a particular message.

And finally RECEIVE statement is always executing in READ COMMITED isolation level, so two clients cannot receive messages from the same conversation group in different transactions, since each RECEIVE will attempt to place an exclusive lock on the conversation group and only one transaction can have an exclusive lock at any given moment.

HTH,
~ Remus

|||

If you are looking at a publish/subscribe type scenario, where you want messages to be delivered to multiple services, you could implement a service that maintains a list of subscriber services and upon receiving a message, sends a copy out each of its subscribers. See the sample on Remus' blog:

http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx

|||

Hi Rushi/Remus

I tested that example, setting the same subscription from two different clients. Then I sent a publication, and read messages.

For what I understand in that example when a client subscribes for a particular publication, his conversationID is saved on a table.

When a publication occurs a procedure sends messages to all subscribers, using that conversationID.

BUT, in case there are two subscriptions and a single client is listening for messages, two identical messages are read by the client.

In case there are two clients listening a lot of confusion, sometimes one client gets two messages, sometimes one, sometimes nothing...

I was expecting , since the conversationID seems to address to a single endpoint, only one message...

|||

Assuming that on a publish/subscribe scenario each client must create a unique subscription, I realize that each client have to create its own queue and service.

The problem is sending messages then.

The initiator should send the same message to all queues, but how? The number of queues created is not defined, is there a way to do it?

Is my theory correct? Or am I on the wrong direction?

Thanks for helping

|||

The subscribers are individual conversations. If they are on the same queue, then you must use the RECEIVE ... FROM queue WHERE conversation_handle = ... syntax to retrieve only the notifications for a given client (subscription).

If you use the RECEIVE w/o a WHERE clause, then the clients will mix the notifications, if they are on the same queue.

In the pub/sub sample at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx the initiator doesn't know nor need to how many clients/queues are there. It will iterate through subscriptions and send a message to each one. Clients can be on the same queue or on different queue, it doesn't matter. The subscription notifications are all reply messages (from target to initiator, since is the client that initiates the subscription), so the pub/sub service does not need to know upfront how many clients are there, it just sends replies on the existing dialogs.

HTH,
~ Remus

|||

Thanks for the clarification Remus.

Now it's working good!

|||

Hi,

I have a couple of questions to make.

How can i trigger notifications to my application ( C#)

without hanging in WaitFor Operation?

It's possible for broker service to call some remote

object that my application provide ?

Can Publish/Subscribe using Broker Service be used

for a low latency notifications (150ms ) max with milions of

messages published per second?

Thanks in advance

Srgio

Consuming all Memory

This is a multi-part message in MIME format.
--=_NextPart_000_003B_01C6948D.B1B75700
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
We have an application running in MSSQL 2000 with SP 4 and C#.NET 2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release = it. It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the = memory available in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will = MSSQL stop running when it get to the maximun or what will happned?
I will appreciate your help,
Rafael
--=_NextPart_000_003B_01C6948D.B1B75700
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We have an application running in MSSQL = 2000 with SP 4 and C#.NET 2003.

This server has 8 GB of memory and = enough hard drive.

The MSSQL server keep consuming more = and more memory and never release it. It gets to the point that we have to = reboot or the server crash.

The MSSQL memory start with 278432 kb = of ram until it take all the memory available in the server.

My question is..

What happend If I specify a minimum and = maximun amount of memory. Will MSSQL stop running when it get to the maximun or = what will happned?


I will appreciate your = help,

Rafael
--=_NextPart_000_003B_01C6948D.B1B75700--This is a multi-part message in MIME format.
--=_NextPart_000_005B_01C694C0.9BB01E00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
How to adjust memory usage by using configuration options in SQL Server:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;Q321363=20
-- Mike
This posting is provided "AS IS" with no warranties, and confers no =rights.
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message =news:OnAVm8KlGHA.836@.TK2MSFTNGP02.phx.gbl...
We have an application running in MSSQL 2000 with SP 4 and C#.NET =2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release =it. It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the =memory available in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will =MSSQL stop running when it get to the maximun or what will happned?
I will appreciate your help,
Rafael
--=_NextPart_000_005B_01C694C0.9BB01E00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi
How to adjust memory usage by using configuration options in SQL Server:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us=;Q321363 -- Mike
This posting is provided "AS IS" with no warranties, and confers no =rights.
"Rafael Tejera" = wrote in message news:OnAVm8KlGHA.836@.T=K2MSFTNGP02.phx.gbl...
We have an application running in =MSSQL 2000 with SP 4 and C#.NET 2003.

This server has 8 GB of memory and =enough hard drive.

The MSSQL server keep consuming more =and more memory and never release it. It gets to the point that we have =to reboot or the server crash.

The MSSQL memory start with 278432 kb =of ram until it take all the memory available in the server.

My question is..

What happend If I specify a minimum =and maximun amount of memory. Will MSSQL stop running when it get to the maximun =or what will happned?


I will appreciate your =help,

Rafael

---

--=_NextPart_000_005B_01C694C0.9BB01E00--

Constructing Email message

Hi,

I am constructing a Message (Body) for sending our Emails. It is around
3000 characters long. But for whatever reason, the last line seems to
be broken with a "!" exclamatory mark in it, which results in
displaying the constructed image path as a broken one.

How to resolve this ?. Thanks.

Regards,
KarthickNo idea - what version of SQL Server, how are you building the message
body, which data type are you using for the message body, how are you
going to send the mail, what does "image path" refer to, can you post a
simplified SQL script to show the problem etc.

As a complete guess, you've declared the message body as varchar but
the path has Unicode characters in it, so you need to use nvarchar. But
without more information, that's probably wrong.

Simon|||Simon, thanks for your reply.

I am using SQL Server 2000. The Message body is NTEXT datatype in the
database. And I am constructing the message body like the following:

SELECT @.MessageBody = '<!DOCTYPE HTML PUBLIC "-//IETF//DTD
HTML//EN">'
SELECT @.MessageBody = @.MessageBody + '<html><head><meta
http-equiv="Content-Type" '
SELECT @.MessageBody = @.MessageBody + 'content="text/html;charset=' +
@.CharSet + '">'
SELECT @.MessageBody = @.MessageBody + '<title>Title goes here:
Ticket</title>'
SELECT @.MessageBody = @.MessageBody + '<link rel="stylesheet"
type="text/css" '

-- I have 67 lines of the @.MessageBody construction and at the end of
the stored procedure I am doing a EXEC to insert this @.MessageBody onto
another table and our third party vendor picks up the Emails from the
table and send them out. So I don't send out the emails manually, all I
do is insert the email contents onto a table and the rest is taken care
of.

Please help. Thanks.

Karthick|||It's still a little unclear (at least to me) - @.MessageBody can't be
ntext, because you can't declare an ntext variable. And you don't say
where the invalid data appears - if you SELECT @.MessageBody before
INSERTing it, is the data correct? Or is it only wrong after INSERTing?
And what does "doing an EXEC" mean? Are you using dynamic SQL, or a
stored procedure to do the INSERT?

Rather than describing your problem, I suggest that you try to produce
a (simplified) script to illustrate your problem - code is always
clearer than a description, and if other people can quickly copy and
paste into Query Analyzer, you're more likely to get a useful answer.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon