Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in SQL-Server 2005 with varchar 8000

Hello,

We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).

To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.

Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.

We use the following .Net Code to execute the stored procedure this stored procedure:

aParams[2] = sSerDatos.GetParameter("@.DominiosMenu", DbType.String, 8000);

aParams[2].Direction = ParameterDirection.Output;

sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)

When we invoque the sqlcommand we get this sqlexception:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@.DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.

If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.

Any help would be appreciated.

Thanks,

Sam

Hello Sam,

You are seeing this error because DbType.String is always in Unicode format, so its maximum allowed size is 4000 characters (8000 bytes). If you specify size 8000, this would mean 8000 characters, which would be 16,000 bytes, which is not a valid data length.

You have 2 options - either use size up to 4000 for DbType.String, or change DbType.String to DbType.AnsiString which is not Unicode and allows size of 8000 characters (which would also be 8000 bytes).

I hope you find this information helpful. Please let me know if I can be of any other help.

Thanks

Stoyko Kostov

|||

Hello,

I also met that problem - db field was nvarchar(max), and in application form one text field was changed from 2000 to 5000 characters. And the same error sill occured. This solution helped me exactly.

Thanks, Arek

|||

If the db field is nvarchar(max), I can also suggest using SqlParameter and SqlDbType instead of DbParameter and DbType. If you need this parameter to be of type nvarchar(max), simply set its SqlDbType to NVarChar and its size to -1. For example,

System.Data.SqlClient.SqlConnection conn;

System.Data.SqlClient.SqlCommand cmd;

System.Data.SqlClient.SqlParameter p1 = cmd.CreateParameter();

p1.SqlDbType = SqlDbType.NVarChar;

p1.Size = -1;

p1.ParameterName = "name";

or

System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("name", SqlDbType.VarChar, -1);

Please let me know if you have any questions about this example.

|||

Hi,

I am using .net 2.0 but I do not get

DbType.nvarchar or .varchar
Any thoughts please?

Error in MS Stored Procedure

I am current creating a Oracle membership provider in dotnet based on the MS membership provider. One of the stored procedures that need converting is shown below. The variable UserId (highlighted blue) is declare and never assigned to but is used within the update query also highlighted in blue. If this is an oversight then I presume that this part of the stored procedure is never actually executed. Can somebody put an eye over this code to confirm.

Thanks.

ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.CurrentTimeUtc datetime,
@.UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @.UserId uniqueidentifier

IF (@.UpdateLastActivity = 1)
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, @.CurrentTimeUtc, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1

UPDATE dbo.aspnet_Users
SET LastActivityDate = @.CurrentTimeUtc
WHERE @.UserId = UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@.ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@.UserName) = u.LoweredUserName AND u.UserId = m.UserId

IF (@.@.ROWCOUNT = 0) -- Username not found
RETURN -1
END

RETURN 0
END

It is a bug in the code. The code is wrong and there are other problems with the logic. You should file a bug at http://connect.microsoft.com.

Error Handling/ Stored Procs

Hi,
I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @.@.rowcount and @.@.error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?
Thanks.As far as dealing with both @.@.ROWCOUNT and @.@.ERROR is concerned, this is what I do:

SELECT @.lError = @.@.ERROR, @.lRowCount = @.@.ROWCOUNT

I select the values into local variables in the stored procedure andthen do whatever is needed based on those values. Note that youmust SELECT both values in the same statement, and I *believe* @.@.ERRORmust appear first because it will be reset when @.@.ROWCOUNT is accessed.
In your case, @.lError should be 0 and @.lRowCount should be 1 when a record is inserted correctly.


|||Thanks, I can now get my two values back!

Error handling problem

I am running SQL 2000.
I have several procedures where I do general validation on entered values.
User procedures typically call these "checkprocedures" to verify all entered
values.
I thought I grasped the idea of error handling but must have done something
wrong.
When a wrong value is found I throw an exception in the checking procedure
and return an arbitrary return code other than 0, typically 1.
In the calling procedure I check the @.@.ERROR value and then take action
based on that. The problem is that excution does not stop after the exceptio
n
is thrown even if I explicitly end with a Return in the calling procedure.
What is wrong?
Why does not the execution in the main procedure stop?
Maybe it is overkill to use both exceptions and return values:
Should I skip the exception throwing and rely on the Return values?
Should I skip the Return values and rely on the exception throwing?
My idea was to avoid defining the validation and exception messages on many
places.
Code example enclosed below:
CREATE PROCEDURE dbo.CheckLanguage
@.LanguageKey char(5),
@.LanguageID int OUT
AS
SET @.LanguageID = NULL
-- Check if given language exists
SELECT @.LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @.LanguageKey
IF @.LanguageID IS NULL
BEGIN
RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @.LanguageKey)
RETURN 1
END
GO
Calling procedure contains this code:
....
-- Make sure the given language key is valid
EXEC CheckLanguage @.LanguageKey, @.LanguageID OUT
SET @.ErrorNumber = @.@.ERROR
IF @.ErrorNumber <> 0
BEGIN
SET @.Response = 'Error_LanguageKey'
RETURN 0
END
...It seems it is the RETURN statement after the RAISERROR that resets the
@.@.ERROR to 0 again, making the calling procedure ....
What would you recommend?
Should I use RAISERROR or RETURN in the check procedure to inform the
calling procedure about the error?
I tend to the RAISERROR ....|||Since the procedure executes OK even if @.LanguageID is NULL, @.@.ERROR in your
calling statement will always be 0. @.@.ERROR traps the error number for the
most recently executed statement.
You need something like this
CREATE PROCEDURE dbo.CheckLanguage
@.LanguageKey char(5),
@.LanguageID int OUT
AS
SET @.LanguageID = NULL
-- Check if given language exists
SELECT @.LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @.LanguageKey
IF @.LanguageID IS NULL
BEGIN
RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @.LanguageKey)
RETURN 1
END
Calling procedure contains this code:
....
-- Make sure the given language key is valid
DECLARE @.return_status int
EXEC @.return_status = CheckLanguage @.LanguageKey, @.LanguageID OUT
IF @.return_status = 1
BEGIN
SET @.Response = 'Error_LanguageKey'
RETURN 0
END
...
"Jakob Lithner" wrote:

> I am running SQL 2000.
> I have several procedures where I do general validation on entered values.
> User procedures typically call these "checkprocedures" to verify all enter
ed
> values.
> I thought I grasped the idea of error handling but must have done somethin
g
> wrong.
> When a wrong value is found I throw an exception in the checking procedure
> and return an arbitrary return code other than 0, typically 1.
> In the calling procedure I check the @.@.ERROR value and then take action
> based on that. The problem is that excution does not stop after the except
ion
> is thrown even if I explicitly end with a Return in the calling procedure.
> What is wrong?
> Why does not the execution in the main procedure stop?
> Maybe it is overkill to use both exceptions and return values:
> Should I skip the exception throwing and rely on the Return values?
> Should I skip the Return values and rely on the exception throwing?
> My idea was to avoid defining the validation and exception messages on man
y
> places.
>
> Code example enclosed below:
> CREATE PROCEDURE dbo.CheckLanguage
> @.LanguageKey char(5),
> @.LanguageID int OUT
> AS
> SET @.LanguageID = NULL
> -- Check if given language exists
> SELECT @.LanguageID = LanguageID
> FROM Language
> WHERE LanguageKey = @.LanguageKey
>
> IF @.LanguageID IS NULL
> BEGIN
> RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
> 16, 1, @.LanguageKey)
> RETURN 1
> END
> GO
>
> Calling procedure contains this code:
> ....
> -- Make sure the given language key is valid
> EXEC CheckLanguage @.LanguageKey, @.LanguageID OUT
> SET @.ErrorNumber = @.@.ERROR
> IF @.ErrorNumber <> 0
> BEGIN
> SET @.Response = 'Error_LanguageKey'
> RETURN 0
> END
> ...|||Thanks, I guess that is the best solution!

Error handling in nested stored procedures

I have a problem with my error handling in nested stored procedures (SQL
Server 2000).
Below is an example of stored procedures that are causing this problem. If
you install the scripts and run sp1, you'll get 2 errors:
(1) Divide by zero error encountered. -- This is expected due to : select 1/
0
(2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
This 2nd error is the problem and I'm not sure what is it.
If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')",
where Col1 is an int, I get : Syntax error converting the varchar value
'hello' to a column of data type int. BUT I don't get that 2nd error about
the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
error, my error handling works perfectly.
Does anyone know what is causing this problem, and how I should change my
error handling to avoid it.
Thanks,
Craig
CREATE proc sp2
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
select 1/0
select @.err=@.@.error if (@.err<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SP
GO
CREATE proc sp1
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
exec @.retvalue = sp2
select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount<>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SPIn SQL Server, you get this error if you, for instance, begin a transaction
in one procedure and end
it in another procedure. In short, if you exist a procedure with a different
@.@.TRANCOUNT compared to
when you entered the procedure, you get this error. The reason why it seems
to work when you assign
'hello' to an int is that SQL Server will stop execution in such case. In sh
ort, have the
transaction handling in the outermost procedure. Or, you can in the inner pr
ocedure check
@.@.TRANCOUNT when you enter and SAVE TRAN if it was > 0 and in such case do t
he rollback to the
savepoint, but you still have to communicate to the caller that you didn't p
erform the work so the
caller can ROLLBACK its work. Check out the error handling articles at www.sommark
og.se for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:8F9E1557-B00E-48BB-AA9F-1C7436C1B547@.microsoft.com...
>I have a problem with my error handling in nested stored procedures (SQL
> Server 2000).
> Below is an example of stored procedures that are causing this problem. If
> you install the scripts and run sp1, you'll get 2 errors:
> (1) Divide by zero error encountered. -- This is expected due to : select
1/0
> (2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> This 2nd error is the problem and I'm not sure what is it.
> If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')"
,
> where Col1 is an int, I get : Syntax error converting the varchar value
> 'hello' to a column of data type int. BUT I don't get that 2nd error about
> the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
> error, my error handling works perfectly.
> Does anyone know what is causing this problem, and how I should change my
> error handling to avoid it.
> Thanks,
> Craig
>
> CREATE proc sp2
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> select 1/0
> select @.err=@.@.error if (@.err<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
> GO
> CREATE proc sp1
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> exec @.retvalue = sp2
> select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount<>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
>

Error Handling in MS SQL Stored Procedures

My problem is about error handling in SQL stored procedures.
Please see my two model procedure below and the output of sp_Two.
CREATE PROCEDURE sp_One
AS
declare @.x float, @.y float, @.z float
set @.x = 1
set @.y = 0
set @.z = @.x/@.y
create procedure sp_Two
as
declare @.err int
exec sp_One
set @.err = @.@.error
if @.err <> 0 goto EH
EH:
if @.err <> 0
print @.err
else
print 'Correct'
Output
Server: Msg 8134, Level 16, State 1, Procedure sp_One, Line 7
Divide by zero error encountered.
8134
How do I get only the error number. I do not want the error description and
other texts. I am linking with Actuate. The above output causes the report to
fail.
Thanks in advance,
Prasanth
Hi
Give the SQL Server to raise the error and catch the number on the client
side.
Also ,please read this article
http://www.sommarskog.se/error-handling-I.html
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:DF1C119E-06DA-49C3-A738-0B52328FAC14@.microsoft.com...
> My problem is about error handling in SQL stored procedures.
> Please see my two model procedure below and the output of sp_Two.
> CREATE PROCEDURE sp_One
> AS
> declare @.x float, @.y float, @.z float
> set @.x = 1
> set @.y = 0
> set @.z = @.x/@.y
> create procedure sp_Two
> as
> declare @.err int
> exec sp_One
> set @.err = @.@.error
> if @.err <> 0 goto EH
> EH:
> if @.err <> 0
> print @.err
> else
> print 'Correct'
> Output
> --
> Server: Msg 8134, Level 16, State 1, Procedure sp_One, Line 7
> Divide by zero error encountered.
> 8134
> How do I get only the error number. I do not want the error description
and
> other texts. I am linking with Actuate. The above output causes the report
to
> fail.
> Thanks in advance,
> Prasanth

Error Handling in MS SQL Stored Procedures

My problem is about error handling in SQL stored procedures.
Please see my two model procedure below and the output of sp_Two.
CREATE PROCEDURE sp_One
AS
declare @.x float, @.y float, @.z float
set @.x = 1
set @.y = 0
set @.z = @.x/@.y
create procedure sp_Two
as
declare @.err int
exec sp_One
set @.err = @.@.error
if @.err <> 0 goto EH
EH:
if @.err <> 0
print @.err
else
print 'Correct'
Output
--
Server: Msg 8134, Level 16, State 1, Procedure sp_One, Line 7
Divide by zero error encountered.
8134
How do I get only the error number. I do not want the error description and
other texts. I am linking with Actuate. The above output causes the report t
o
fail.
Thanks in advance,
PrasanthHi
Give the SQL Server to raise the error and catch the number on the client
side.
Also ,please read this article
http://www.sommarskog.se/error-handling-I.html
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:DF1C119E-06DA-49C3-A738-0B52328FAC14@.microsoft.com...
> My problem is about error handling in SQL stored procedures.
> Please see my two model procedure below and the output of sp_Two.
> CREATE PROCEDURE sp_One
> AS
> declare @.x float, @.y float, @.z float
> set @.x = 1
> set @.y = 0
> set @.z = @.x/@.y
> create procedure sp_Two
> as
> declare @.err int
> exec sp_One
> set @.err = @.@.error
> if @.err <> 0 goto EH
> EH:
> if @.err <> 0
> print @.err
> else
> print 'Correct'
> Output
> --
> Server: Msg 8134, Level 16, State 1, Procedure sp_One, Line 7
> Divide by zero error encountered.
> 8134
> How do I get only the error number. I do not want the error description
and
> other texts. I am linking with Actuate. The above output causes the report
to
> fail.
> Thanks in advance,
> Prasanth

Error Handling in MS SQL Stored Procedures

My problem is about error handling in SQL stored procedures.
Please see my two model procedure below and the output of sp_Two.
CREATE PROCEDURE sp_One
AS
declare @.x float, @.y float, @.z float
set @.x = 1
set @.y = 0
set @.z = @.x/@.y
create procedure sp_Two
as
declare @.err int
exec sp_One
set @.err = @.@.error
if @.err <> 0 goto EH
EH:
if @.err <> 0
print @.err
else
print 'Correct'
Output
--
Server: Msg 8134, Level 16, State 1, Procedure sp_One, Line 7
Divide by zero error encountered.
8134
How do I get only the error number. I do not want the error description and
other texts. I am linking with Actuate. The above output causes the report to
fail.
Thanks in advance,
PrasanthHi
Give the SQL Server to raise the error and catch the number on the client
side.
Also ,please read this article
http://www.sommarskog.se/error-handling-I.html
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:DF1C119E-06DA-49C3-A738-0B52328FAC14@.microsoft.com...
> My problem is about error handling in SQL stored procedures.
> Please see my two model procedure below and the output of sp_Two.
> CREATE PROCEDURE sp_One
> AS
> declare @.x float, @.y float, @.z float
> set @.x = 1
> set @.y = 0
> set @.z = @.x/@.y
> create procedure sp_Two
> as
> declare @.err int
> exec sp_One
> set @.err = @.@.error
> if @.err <> 0 goto EH
> EH:
> if @.err <> 0
> print @.err
> else
> print 'Correct'
> Output
> --
> Server: Msg 8134, Level 16, State 1, Procedure sp_One, Line 7
> Divide by zero error encountered.
> 8134
> How do I get only the error number. I do not want the error description
and
> other texts. I am linking with Actuate. The above output causes the report
to
> fail.
> Thanks in advance,
> Prasanth

Error Handling in Activation Procs

I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.

I tried using @.@.Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.

Can other people on this forum using service broker give me an idea of how you are getting around this issue?

Hmmm...interesting. Where did you read the best practice about not rolling back the part of your code that receives the record off of the queue. I am not sure that makes sense to me. I would want to put the message back onto the queue if I know I have ran into an error.|||The idea is to avoid Poison Messaging where possible by accounting for whatever errors you know you will encounter (foreign key errors, Invalid Data types)...these types of errors will never be able to run and will therefore, bring down your queue every time if you rollback. The idea is to never roll back the receive, unless there is some type of fatal error that causes the batch to abort. In that case, as long as your receive is in a transaction, it will be rolled back automatically. If it is a handled error, you need to do something else with that message to move it off the queue.|||

On one of our systems we use seperate transactions for recieving the message and processing the message. The transaction around the receive simply removes the message from the queue and logs it to an audit table. If this step fails then we rollback and put the message back onto the queue. If we successfully receive and log the message we then begin another transaction (if applicable) for the processing of the message. Therefore if this fails for some reason (ie constraints) then we just rollback the processing and not the receive from the queue - we can then get the message from the audit table to re-process if required. Our idea is to keep the code in the transaction that includes the receive from the queue as simple as possible to avoid failures and prevent rollbacks - therefore avoiding poison messages. I'm not sure if this would be applicable in your case.