DialogError message type

Hi There

In my testing i have seen the following i just would like confirmation that i am correct.

When something happens to cause a Dialog Error say for example message validation fails.

I have noticed that the actual DialogError message always goes to the initiator queue, is this correct ?

If so then logic to handle the ErrorDialog message type only has to be in the initiator activated SP that logic is not required in the target queue activated SP since the ErrorDialog message always goes to the initiator queue, is that correct ?

Thanx

Both sides need to be prepared to deal with the error message. The same message validation can fail for a message sent on the other direction, or the initiator code can issue an explicit END CONVERSATION ... WITH ERROR (or even an ALTER DATABASE ... SET ERROR_BROKER_CONVERSATIONS), or the initiator can drop the contract used on the conversation, or the service. All these result in an error message being sent to the target, and the list is not complete.

HTH,
~ Remus

Deterministic - column property

Shows whether the data type of the selected column can be determined with certainty. (Applies only to Microsoft SQL Server 2000 or later.)

This is what Microsoft documentation says for this column property. How I can use his feature for database application development? What is the practical use of this property?

SQL Server 2005.

Thank you,

Smith

it is relevant only to computed columns. Indexes can be built on computed columns only if they are determinists. For mroe details refer to BOL.

Determining Trigger Order

I'm not sure if this is the correct forum or not for this question. Basically, i have 6 triggers attached to my table (2 of each type). I used sp_settriggerorder to make sure certian triggers fire first.

Is there any way to go back and determine what order a trigger has been set to (first, none or last)? I've scanned through all of the system tables and don't see anything.

Thanks in advance.

With some digging, you can find that they encode the order into the status fields on sysobjects. However, the way they are exposed is with the ObjectProperty() function -- it takes parameters such as ExecIsFirstUpdateTrigger.

It is not obvious to me how triggers are exposed from the Information_Schema views.

The digging involves using sp_helptext on the sp_settriggerorder and sp_helptrigger stored procedures.

|||That's exactly what I was looking for.

I didn't even think abou tusing sp_helptext to see what the procedure did. I'll definitely use that next time I want to know what SQL is doing.

Thanks!

Determining the most appropriate conversion for a string

Hi,

Is there some kind of code snippet to determine (a) the precise data type (and precision if appropriate) of a given table.columnname in the database, and (b) the most suitable conversion for a varchar that is being made to hold various types of data.

Presumably based on the above one could have some sort of CASE WHEN to cast the data to different types accordingly.

Thanks for any help on this.

You need to use the system tables. Check out sysobjects:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-t_2983.asp

syscolumns:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_5mur.asp

and systypes:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-t_2983.asp

This should get you part of the way:

declare @.tablename varchar(256)
declare @.colname varchar(256)

set @.tablename = .....
set @.colname = .....

SELECT obj.name as 'Table', col.name as 'Column', typ.name as 'DataType', col.length, col.xprec as 'Precision', col.xscale as 'Scale'
FROM sysobjects obj
join syscolumns col
on col.id = obj.id
join systypes typ
on typ.xtype = col.xtype
where obj.type = 'U'
and obj.name = @.tablename
and col.name = @.colname

Also, check out my blog for some information on system views:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

HTH

|||Thanks, that's really helpful...do you have any insight on determining the most appropriate conversion for a string piece of data? I.e. "23/10/04", "1.01F", "0.68", "0.00021", "1", etc.
|||

There are three system functions which would be useful here:

ISNUMERIC

ISDATE

ISNULL

See:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_79f7.asp

This should get you started:

CASE

WHEN ISNUMERIC(column) = 1 THEN ...... Process Number

WHEN ISDATE(COLUMN) = 1 THEN ... Process Date

ELSE ... Process string\

END

Also, you can use the LEN() and DATALENGTH() functions to determine the size of the value passed. If it is a number, you can also use CHARINDEX to look for a decimal point in the string, and return its value or not. That should help you determine whether the number is an integer, decimal or a money field, and help you work out the precision and size of the data.

HTH

|||ISNUMERIC will check for conversion to any of the numeric data types including integer, float, numeric and money. So if you need stricter checks you need to implement yourself. Another option is to use sql_variant but this will complicate your logic due to the conversions. On the other hand you can retain the base type of the value you are specifying.|||Many thanks for your replies. The requirement has actually now been made simpler. In short, I need to have an additional case to logic like the following (I have asterisked the key line):-

if @.stkData like '%+%'
begin
exec dbo.spNeoStackEvaluate @.stkLeft output
exec dbo.spNeoStackEvaluate @.stkRight output

***set @.stkDataOut = cast(@.stkRight as int) + cast(@.stkLeft as int)
return @.stkDataOut
end

Which should handle cases where the strings originated as money datatypes. The problem is that a simple rewrite of the above, casting @.stkRight as money, is not acceptable in T-SQL, but falls over.

So while replies so far have been very helpful in setting up some logic to know what kind of data I am dealing with, I would also like to know if a simple rewrite of the above to handle money-like strings is in some way possible?

-Thanks.
|||I should point out that all the variables here -- @.stkDataOut, @.stkRight, @.stkLeft, are originally declared as varchar(100) type.
|||

I am not quite sure what exacty your problem is here. As I suggested earlier, you can test the varchar value to seeif it is a decimal (or money) or an int by using charindex, and looking for the presence of a '.' character. By getting the position of the decimal point using charindex, you can then determine the size of the decimal or money value to cast it as. This should then be able to handle any case.

HTH

|||The problem is that even assuming a simple case, for test purposes -- e.g. '0.68' as the varchar data, casting it to money throws an error. So for example assuming a value of '20', casting it as an int is fine, but cast(@.somedata as money) where @.somedata is '0.68' fails. Now this piece of data originated in a field of money datatype. Are you saying that my cast statement must be more specific?
|||

I thought the problem was not specifying the size of the data type, but that does not appear to be the issue. Test code works fine:

declare @.char varchar(20)

set @.char = '0.68'

select cast(@.char as money)

-- returns 0.68

So I am not sure what your issue if. Are you sure that it is the value '0.68' that is throwing the error? I suggest you put a select @.somedata value before the cast to get the value of the varchar before the error is thrown, to double check you are not getting any characters in there. Also, what error are you getting? Can you post the code snippet along with the syntax error? What version of SQL Server are you running? You could try casting it as a decimal instead, but make sure you specify the size and precision, otherwise it will default to an integer value.

|||

Though this will work fine (i.e. accessing system tables to get column data types, attributes, etc.), I'd probably recommend you make use of the SQL-92 compliant INFORMATION_SCHEMA views or system provided functions when possible to retrieve the data, given that you may get different results and unpredictable updates across SQL Server versions, SKU's, and hotfixes/service packs.

In this case, the INFORMATION_SCHEMA.COLUMNS view provides you with all the things you are looking for and probably more: name, data type, precision, scale, schema, default, position, nullability, etc., etc.

Additionally, these system functions could help out as well:

COLUMNPROPERTY()
COL_NAME()
COL_LENGTH()

As for the money conversion, the posting above by Shughes should work fine as he mentions...does for me also :-)...

|||Please indicate if Chad and I solved your problem.|||Will do. I am evaluating it at the moment. It's one of a number of problems that I'm working through. I will post in greater detail later on.
|||Just briefly, this is definitely still erroring. The problem seems to consist in going from varchar to money (in order to carry out a valid calculation) and then going back to varchar.

I will post on this in more detail once I get various other problems sorted out.