How to force SQL Server to handle all numeric types as doubles?

Hi, is it possible to force SQL Server to handle all numeric data types as
doubles? Eg. I have an very simple query:
SELECT 1+1 AS Result
I need SQL Server to work with number 1 as with double and also to return a
result as a double (however it doesn't make any sence in this situation).
Thanks.Hi
I don't think there is an automatic way, but you can use the CAST/CONVERT
functions. Also check out the topic "Data Type Precedence" in Books Online.
John
"Tomas Machala" <t.machala@.tiscali.cz> wrote in message
news:%23X$A60tDGHA.3988@.TK2MSFTNGP12.phx.gbl...
> Hi, is it possible to force SQL Server to handle all numeric data types as
> doubles? Eg. I have an very simple query:
> SELECT 1+1 AS Result
> I need SQL Server to work with number 1 as with double and also to return
> a result as a double (however it doesn't make any sence in this
> situation).
> Thanks.
>|||Tomas Machala (t.machala@.tiscali.cz) writes:
> Hi, is it possible to force SQL Server to handle all numeric data types as
> doubles? Eg. I have an very simple query:
> SELECT 1+1 AS Result
> I need SQL Server to work with number 1 as with double and also to
> return a result as a double (however it doesn't make any sence in this
> situation).
The one way, is to express the numbers as floats. So in your example:
SELECT 1E0+1E0 AS Result
Note that in an expression like:
SELECT 1E0 + 1 Result
The integer one will be automatically converted to float.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to find out what it the the poison message.

Hallo,
I have some problems to handle the poison messages.

Messages are exchanged between 2 databases on the same SQL Server instance.
I created a Trigger on the insert , that call the sp that begin the conversation(10 minutes Timeout) and send the message to the target queue.

On the target queue I activated a SP and the main code is:

BEGIN TRANSACTION

WAIT FOR (Receive (1) ..) timeout 5000

IF (@.ErrorSave <> 0)

BEGIN

INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES ();

END

ELSE

if (@.messagetypeName = N'Message')

BEGIN

exec [dbo].[sp_ProcessMessage] @.message_body

END

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

BEGIN

END CONVERSATION @.dialog

END

COMMIT TRANSACTION

My communication has a strange behaviour:

if I type

Begin

insert into TriggerTable values(XMLMessage)

insert into TriggerTable values(XMLMessage)

....

insert into TriggerTable values(XMLMessage)

end

Everything work fine but If I write an insert of 1000 message coming from an another table and I use the

cursor login after I while the transmission stop because the Target queue become inactive.
I can see my messages stuck in the Initiator transmission_queue so I think that there is some Poison message that cause 5 rollback and disable the receiver queue.

First I would like to isolate the wrong message and carry on with the insert, my application doesn't have to stop the conversation or return any error, but If I use the Sql server Debugger I'm not able to debug the Target queue's stored procedure.

I suppose that some error happens on the Target queue's stored procedure but how can I first at all find it out?

Maybe it has something to do with the transition wrapped around the RECEIVE command?

thank you for any helps!

Marina B.

If an activated procedure hits and error it will report it to the ERRORLOG file as well as to the systen event log (EventVwr.exe). check there to see if any error is present.

You can determine if poison message happens by either monitoring with Profiler the Broker/Broker:Activation events or subscribying to an event notification (see CREATE EVENT NOTIFICATION in BOL) for the QUEUE_DISABLED event on the queue. But I cannot say how to determine which is the poisson message, this is application specific. You can try to turn off activation and manually run the procedure to see if it works as expected.

|||

Hi,
there was not error on the Event Log so after further investigation I found out that the error was raised by the sp that process the message.

Now I have anyway another small problem because I'm not able to avoid the rolleback on the receiving queue and the disabling of the Target queue.

The SP that process the message raise an Error to the caller (the Target queue's activation SP).

In the BEGIN catch I log the error on a table and I want to continue to process all the messages till the queue is empty.
But it seems that the the Target queue's activation SP always rollback because I can see the the Error is inserted 5 times into the log table and then the Target's queues is disabled.

This are the main parts of the code:

Target's queue activation SP:

Begin try

Begin Transaction

WAITFOR (

RECEIVE top(1) ....)

if (@.@.ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION

BREAK

END

SET @.ErrorSave = @.@.ERROR ;

IF (@.ErrorSave <> 0)

INSERT INTO tblLOG VALUES(...)
ELSE
exec sp_ProcessMessage @.Message
COMMIT
END TRANSACTION
END TRY

BEGIN CATCH

BEGIN

INSERT INTO tblLOG values(...)
COMMIT

END

END CATCH

This is the exec sp_ProcessMessage @.Message
BEGIN TRY

BEGIN TRANSACTION
... Do something that cause an error
COMMIT
END TRY
BEGIN CATCH

IF XACT_STATE() <> 0

BEGIN

ROLLBACK TRANSACTION ;

END

exec [dbo].usp_RethrowError;

END CATCH

the usp_RethrowError stored procedure raise the error to the caller.

How can I avoid the Rollback in case something goes wrong in the SP that Process the message?
I suppose that in the moment I use a try...catch block in the Target's queue activation SP I have also to delete the statements:

SET @.ErrorSave = @.@.ERROR ;

IF (@.ErrorSave <> 0)

INSERT INTO tblLOG VALUES(...)

because is case of error on the receive command the sp will execute the code in the BEGIN CATCH..END CATCH..isn't?

Thank you

Marina B.

How to find out what it the the poison message.

Hallo,
I have some problems to handle the poison messages.

Messages are exchanged between 2 databases on the same SQL Server instance.
I created a Trigger on the insert , that call the sp that begin the conversation(10 minutes Timeout) and send the message to the target queue.

On the target queue I activated a SP and the main code is:

BEGIN TRANSACTION

WAIT FOR (Receive (1) ..) timeout 5000

IF (@.ErrorSave <> 0)

BEGIN

INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES ();

END

ELSE

if (@.messagetypeName = N'Message')

BEGIN

exec [dbo].[sp_ProcessMessage] @.message_body

END

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

BEGIN

END CONVERSATION @.dialog

END

COMMIT TRANSACTION

My communication has a strange behaviour:

if I type

Begin

insert into TriggerTable values(XMLMessage)

insert into TriggerTable values(XMLMessage)

....

insert into TriggerTable values(XMLMessage)

end

Everything work fine but If I write an insert of 1000 message coming from an another table and I use the

cursor login after I while the transmission stop because the Target queue become inactive.
I can see my messages stuck in the Initiator transmission_queue so I think that there is some Poison message that cause 5 rollback and disable the receiver queue.

First I would like to isolate the wrong message and carry on with the insert, my application doesn't have to stop the conversation or return any error, but If I use the Sql server Debugger I'm not able to debug the Target queue's stored procedure.

I suppose that some error happens on the Target queue's stored procedure but how can I first at all find it out?

Maybe it has something to do with the transition wrapped around the RECEIVE command?

thank you for any helps!

Marina B.

If an activated procedure hits and error it will report it to the ERRORLOG file as well as to the systen event log (EventVwr.exe). check there to see if any error is present.

You can determine if poison message happens by either monitoring with Profiler the Broker/Broker:Activation events or subscribying to an event notification (see CREATE EVENT NOTIFICATION in BOL) for the QUEUE_DISABLED event on the queue. But I cannot say how to determine which is the poisson message, this is application specific. You can try to turn off activation and manually run the procedure to see if it works as expected.

|||

Hi,
there was not error on the Event Log so after further investigation I found out that the error was raised by the sp that process the message.

Now I have anyway another small problem because I'm not able to avoid the rolleback on the receiving queue and the disabling of the Target queue.

The SP that process the message raise an Error to the caller (the Target queue's activation SP).

In the BEGIN catch I log the error on a table and I want to continue to process all the messages till the queue is empty.
But it seems that the the Target queue's activation SP always rollback because I can see the the Error is inserted 5 times into the log table and then the Target's queues is disabled.

This are the main parts of the code:

Target's queue activation SP:

Begin try

Begin Transaction

WAITFOR (

RECEIVE top(1) ....)

if (@.@.ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION

BREAK

END

SET @.ErrorSave = @.@.ERROR ;

IF (@.ErrorSave <> 0)

INSERT INTO tblLOG VALUES(...)
ELSE
exec sp_ProcessMessage @.Message
COMMIT
END TRANSACTION
END TRY

BEGIN CATCH

BEGIN

INSERT INTO tblLOG values(...)
COMMIT

END

END CATCH

This is the exec sp_ProcessMessage @.Message
BEGIN TRY

BEGIN TRANSACTION
... Do something that cause an error
COMMIT
END TRY
BEGIN CATCH

IF XACT_STATE() <> 0

BEGIN

ROLLBACK TRANSACTION ;

END

exec [dbo].usp_RethrowError;

END CATCH

the usp_RethrowError stored procedure raise the error to the caller.

How can I avoid the Rollback in case something goes wrong in the SP that Process the message?
I suppose that in the moment I use a try...catch block in the Target's queue activation SP I have also to delete the statements:

SET @.ErrorSave = @.@.ERROR ;

IF (@.ErrorSave <> 0)

INSERT INTO tblLOG VALUES(...)

because is case of error on the receive command the sp will execute the code in the BEGIN CATCH..END CATCH..isn't?

Thank you

Marina B.