Real Time DSS: message

Showing posts with label message. Show all posts

Friday, March 30, 2012

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.Ric,
Try going to Query Analyzer and editing the sp. You should be able to see
the settings of "QUOTED_IDENTIFIER" when the sp was created. chnage it to
accomodate your needs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.SZ_ProcessToCPInvoices
...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
AMB
"Ric" wrote:

> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Lin
e
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.
Ric,
Try going to Query Analyzer and editing the sp. You should be able to see
the settings of "QUOTED_IDENTIFIER" when the sp was created. chnage it to
accomodate your needs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.SZ_ProcessToCPInvoices
...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
AMB
"Ric" wrote:

> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>

Wednesday, March 28, 2012

Receiving a message with a specific contract

If the service is defined with multiple contracts is there a way to receive a message with a specific contract?

No. I'm curious to understand why do you want this, can you give a bit of detail?

Thanks,
~ Remus

|||

I am implementing a queue processing system. You can get some of the info here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446600&SiteID=1. The queue can contain messages of different contracts/types. When I receive a message I instantiate a different processing component based on the contract/type of the message. There are processing servers that process different kind of messages. The single processing server might only have a subset of processing components. This is why I need a way to retrieve messages based on the contract/type.

Alex

|||

You can try having one service as front end that simply forwards each dialog to specialized services on the back end, similar to the approach described here: https://blogs.msdn.com/remusrusanu/archive/2006/03/28/563571.aspx

Each supported contract would be forwarded to a different service.

HTH,
~ Remus

|||

I have a working implementation without Service Broker which handles message types and message priority (another one Service Broker does not handle directly). It does not make sence to switch to Service Broker which would also require to increse the complexity of the architecture.

Alex

Received message constantly processed.

Hello,

when is seemd that everything works some weird behaviours comes out.

I try to summarize the problem without to post the complete code.

Service Broker is set to have a dialog between two databases on the same SQL Server instance.

The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.

The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).

The conversation has a Timeout Dialog to end the dialog after a while.

The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.

n.b.the Receive is within a Transation that I commit at the end.

some other informations that in the meanwhile I found out :

This was my complete WAIT FOR(RECEIVE :

WAITFOR ( RECEIVE top(1) -- just handle one message at a time
@.message_type=message_type_id, --the type of message received
@.messagetypename=message_type_name,
@.message_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [TargetQueue]
), timeout 1000;

if (@.@.ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END

IF I delet TIMEOUT 1000, everything works as expected ...
Inside if (@.@.ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout)
I'm happy that it works what if this is the solution, it make no sense to me!

Any ideas?
Thank you!

M.B.

Thank you very much

M.B.

This sounds like there are messages constantly comming and causing the WAITOFR(RECEIVE...) to get another message while is waiting. Is this the case?|||

Hi,

it shouldn't be!!

I suppose that the only way to understand what is going on is to use the SQL Profiler.

Isn't?

Thank you

|||

Hi,

I tried to use the SQL server Profiler to understand what is going on when I send/receive the message.

First at all on both queues(initiator and target) I have RETENTION = OFF so the check if @.@.ROWCOUNT = 0 will be never true because the message stays in the queue till the END of the CONVERSATION and in my case the conversation will end when there is an error or when the conversation timeout expires; otherwise I send/receive using the same conversation handle.

If I write:

WAITFOR (

RECEIVE top(1) ...

), TIMEOUT 5000;

In the SQL Server Profiler I seen that the SP that is called after the receive statement and that processes the message is constantly executed.

If I write:

WAITFOR (

RECEIVE top(1) ...

)

In the SQL Server Profiler I seen that the SP that is called after the receive statement and that processes the message is executed only ONCE.

I'm quite confuse ... I thought that is better to use a timeout but if I use I have that strange behaviour!!

Any helps/advices?
Thankx

Marina B.

|||

@.@.ROWCOUNT is set by RECEIVE to the number of rows (messages) actually returned, so it doesn't matter if RETENTION is ON or OFF.

From your description of the problem is clear that your procedured does not correctly detect when RECEIVE returned no rows, so it continues to spin on empty RECEIVEs that timeout. Can you post the actual code of the procedure?

|||

Hi Remus,

thank you very much for your answer...

Maybe it is not necessarely but this is the SP wuch start the conversation (activated by a trigger on a insert )

PROCEDURE [dbo].[sp_sendInserted]

@.MessageXML nvarchar(max)

BEGIN

DECLARE @.dialog_handle uniqueidentifier;

DECLARE @.dialog_id uniqueidentifier;

DECLARE @.msg XML;

DECLARE @.Error INT;

SET NOCOUNT OFF;

BEGIN TRY

BEGIN TRANSACTION;

set @.msg = Convert(xml,@.MessageXML);

WHILE (1=1)

BEGIN

set @.dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceivedService');

if @.dialog_handle is null

--Begin new dialog

BEGIN

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE [SendService]

TO SERVICE 'ReceivedService'

ON CONTRACT [MainContract]

WITH ENCRYPTION = OFF;

BEGIN CONVERSATION TIMER (@.dialog_handle) TIMEOUT = 600;

END;

SEND ON CONVERSATION @.dialog_handle

MESSAGE TYPE MyMessage (@.msg); END

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() = -1

BEGIN

ROLLBACK TRANSACTION;

END

Insert into [dbo].[tblErrorXMLMessages] values(@.MessageXML,@.dialog_handle,@.dialog_id,null,null,ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_PROCEDURE(),getdate());

END CATCH

END

And this is the SP activated on the ReceiveQueue:

PROCEDURE [dbo].[OnReceivedMessage]

DECLARE @.message_type INT;

DECLARE @.messagetypename NVARCHAR(1000);

DECLARE @.XMLmessage_body XML;

DECLARE @.dialog UNIQUEIDENTIFIER;

DECLARE @.dialog_id UNIQUEIDENTIFIER;

DECLARE @.ErrorSave INT;

DECLARE @.ErrorDesc NVARCHAR(100);

SET NOCOUNT ON;

WHILE (1 = 1)

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

WAITFOR (

RECEIVE top(1) -- just handle one message at a time

@.message_type=message_type_id, --the type of message received

@.messagetypename=message_type_name,

@.XMLmessage_body=message_body, -- the message contents

@.dialog = conversation_handle -- the identifier of the dialog this message was received on

FROM [ReceivedQueue]

)--, timeout 5000; -- if the queue is empty for three second

--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages

set @.dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @.dialog);

-- If we didn't get anything, bail out

if (@.@.ROWCOUNT = 0)

BEGIN

INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');

COMMIT;

BREAK;

END

If (@.messagetypeName = N'MyMessage')

BEGIN

DECLARE @.ret integer;

EXECUTE @.ret = [TestReceiver].[dbo].[sp_ShredXMLMessageToRelationalData] @.XMLmessage_body; --SP that PROCESSES THE MESSAGE RECEIVED ON THE QUEUE

if (@.ret <> 0)

INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES

(cast(@.XMLmessage_body as nvarchar(max)),@.dialog,@.dialog_id,null,null,0,null,null,getdate());

END

ELSE IF (@.messagetypeName = N'EndOfStream')

BEGIN

END CONVERSATION @.dialog;

END

-- Check for the Error Dialog message.

ELSE IF(@.messagetypeName=N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')

BEGIN

DECLARE @.WrongXMLMessage NVARCHAR(max);

set @.WrongXMLMessage = (Select message_body FROM dbo.TradeReceivedQueue WITH (NOLOCK) where Message_type_name = 'MyMessage' and status = 3 and conversation_handle=@.dialog);

DECLARE @.Error int;

DECLARE @.ErrorDescription nvarchar(4000);

WITH XMLNAMESPACES

('http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb)

SELECT @.Error = cast(@.XMLMessage_Body as XML).value('(//ssb:Error/ssb:Code)[1]','INT'),

@.ErrorDescription = cast(@.XMLMessage_Body as XML).value('(//ssb:Error/ssbescription)[1]','nvarchar(4000)')

IF @.WrongXMLMessage is null

BEGIN

SET @.WrongXMLMessage = N'<Error>Error retrieving the wrong XML message from the sender queue</Error>';

END

Insert into [TestSender].[dbo].[tblErrorXMLMessages] values(@.WrongXMLMessage,@.dialog,@.dialog_id,'MArina','ReceivedQueue',@.Error,@.ErrorDescription,ERROR_PROCEDURE(),getdate());

--After the End Conversation all messages from the queue are deleted

END CONVERSATION @.dialog;

END

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

insert into [testReceiver].[dbo].[tblReceived] values('Error','<Marina>Error in SP Traget QUEUE</Marina>');

END CATCH

END

Let me know whether you need more info regarding contract, queues etc..etc..

Thank you very much!

Marina B.

|||

marina B. wrote:

WAITFOR (

RECEIVE top(1) -- just handle one message at a time

@.message_type=message_type_id, --the type of message received

@.messagetypename=message_type_name,

@.XMLmessage_body=message_body, -- the message contents

@.dialog = conversation_handle -- the identifier of the dialog this message was received on

FROM [ReceivedQueue]

)--, timeout 5000; -- if the queue is empty for three second

--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages

set @.dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @.dialog);

-- If we didn't get anything, bail out

if (@.@.ROWCOUNT = 0)

BEGIN

INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');

COMMIT;

BREAK;

END

SQL Server 2005 Books Online wrote:

@.@.ROWCOUNT (Transact-SQL)

Returns the number of rows affected by the last statement

RECEIVE is not the last statement before you check @.@.ROWCOUNT.

|||

As always,

my stupid mistake.

I thought that I was making a mess with the transaction!!

I moved the @.@.Rowcount check after the RECEIVE statement and it worked..

I should read more CAREFULLY the BOL !!

Thankx!!

Marina B.

Receive Error: 17805 Invalid buffer received from client

Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error and what kind of filter if any do i need to apply?
Please advise..
Cheers!
Brett
You should be able to track it down quickly if it's
happening every hour. You get capture exceptions using the
Errors and Warnings class. You can then see the spid that
generated this error. If you also capture the T-SQL
statements and stored procedures at the same time, you will
see what the spid that generated the exception was
executing.
What you filter on depends more on your system. You can at
least filter out the applications SQL Profiler and SQLAgent%
-Sue
On Fri, 21 Oct 2005 15:33:20 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:

>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
> State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error and what kind of filter if any do i need to apply?
>Please advise..
>Cheers!
>Brett
>

Receive Error: 17805 Invalid buffer received from client

Tuesday, March 20, 2012

Rebuild index failed

I scheduled a maint plan to rebuild index. All table's indexes are successfully built except for one table. The error message I got:

Failed:(-1073548784) Executing the query "ALTER INDEX [Index_name] ON [dbo].[table] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON )
" failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can someone tell me what the problem is and how to fix it? Thanks.

I am assuming you are using the Enterprise Edition of SQL 2005?|||

We are using Enterprise Edition. All other tables can be reindexed. This is the only table has problem. And this is not a big table. The indexed column is nvarchar(50).

Any ideas?

Friday, March 30, 2012

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Wednesday, March 28, 2012

Receiving a message with a specific contract

Received message constantly processed.

Receive Error: 17805 Invalid buffer received from client

Receive Error: 17805 Invalid buffer received from client

Receive Error: 17805 Invalid buffer received from client

Tuesday, March 20, 2012

Rebuild index failed

Real Time DSS

Blog Archive

About Me