Datatype conversion strangeness

Hi all,

After more headbanging and cursing than an entire Metallica audience, I have finally deduced how to store and calculate the values of a stack storing RPN calculations using a recursive stored procedure. But there is a new conundrum. Originally, to test the above, I was using fairly simple values -- 10, 20, +, 3, /, etc. But the real data is likely to include values with at least a couple of decimal places -- these have been configured using the money data type. Nevertheless, in my actual stack table the value needs to be specified as a varchar. However, as soon as I start sending in 0.68 as a varchar to my stored procedure, it gives an error.

Can anyone offer any light on this?

(The error message number is 245, data type conversion error. But this is inexplicable as I have commented out any conversion code in order to get to the root of the problem. As far as I am aware this value "0.68" is always being passed around as a varchar.)

Thanks.

But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors. It is hard to tell what is wrong without looking at some repro. You should however avoid doing these type of operations since the potential for misinterpretation or run-time error is large. If you are dealing with numeric values then specify the data type of the variables accordingly.|||Umachandar,

Many thanks for your reply. The reason I'm doing it this way is that I'm modelling a stack. Also some values are actually the tablename.objectname id of values in a read-only section of the database, so the <value> part of this stack has to be flexible -- hence varchar. Surely the potential for runtime error is not that great, as I only want to be able to handle values such as the following:-

table1.id1 (this gets parsed by the stored procedure into values such as follow...)
0.68
0.43
10
20
+
*

|||Again, it is hard to tell what is wrong without looking at some piece of code that reproes the error. Alternatively, you could run profiler trace with statement level events to see which statement is failing (you can even get this from the error message header which will point to the line that generated the error).|||OK let me try to explain as fully as possible.

I have a table NEO_FORMULA_STACK (f_id(int), position(int), val(varchar(100)), valname(varchar(100)), units (nvarchar(12).

I have a stored proc, which accepts f_id (as above) as a parameter. Based on this f_id it then loads the 'stack' of values into table TEMP_STACK (pos(int),val(varchar(100).

What I mean is that there are multiple formulas in the original table each with their own 'stack' of values (arranged in Reverse Polish Notation format, i.e. 10,20,+,3,/ [a way of writing ((10+20)/3).

Now, in the course of the load from NEO_FORMULA_STACK TO TEMP_STACK conversions such as tablename.objectname being evaluated to e.g. 0.098 might very well occur. But my impression is that if NEO_FORMULA_STACK has a val column of VARCHAR(100) datatype, and so does TEMP_STACK, then there should be absolutely no problem going from 'tablename.objectname' to '0.000918'. These are BOTH completely valid strings. Bear in mind that NO calculation has taken place at this point, I have simply loaded NEO_FORMULA_STACK for a particular f_id into TEMP_STACK. Attempting to pop off a value like '0.0098' into a temporary variable of varchar(100) is throwing error: 245, converting from varchar to int at line 77 of the stored proc:-

I can't give you the exact line, because one sp is calling another so the line it cites as being in the wrong is a comment.

Please let me know if you need more detail, or if you have any clues based on this information. Many thanks!
|||"But you might be doing some operation that involves say concatenation of numeric value with varchar or vice versa. This will result in implicit conversion of the value to the data type with highest precedence and depending on the value you can get run-time errors."

I assure you I am doing no concatenation or anything like that. I removed all such code in order to try and attack the problem. I am loading one varchar into another varchar, then popping that varchar off into a temporary variable of type varchar -- and it is that pop which is throwing the error, saying illegal conversion, when as far as I can see they are ALL varchar(100).
|||Your explanation doesn't really help to clarify the exact operations being done in the SP. As I said, this error happens due to implicit conversions of values of different types or initialization of variables/parameters using value of different data type. Add additional debug statements to see the results before the input so you can identify the issue. Or create a simpler repro that will help you identify the problem due to implicit conversion.

Datatime order by Problem

Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:

F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------

To do this Im using the follow syntax :

SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))

But it gives the follow message erro trying to select data :

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?

Thansk for attention.

Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql

DataSet Bug?

Hi,
###Problem 1###
I've encountered serveral times on this problem Every time when I used store
procedure to create a datatset. In the store procedure, it cannot have more
then 2 select statement even though the first select state is only shown when
a flag is turned on. For example :
...
set @.debug=0
if @.debug=1
select * from ABC
Select X, Y, Z from WWW
...
The dataset will shows the 1st select statement instead of the 2nd. I've to
deleted the those unwanted lines before my dataset is correct. Is this a bug?
###Problem 2###
Currently, I'm facing another problem with DataSet ...:(
This time when i wrote a intensive coding in the store procedure with
consists of quite a number of insert Temp tables. When I run the store
procedure using the Query Analyzer, my dataset is correct. However when I
used Reporting Services, I'm unable to get the correct dataset. Why is this
so?
Any help is definitely appreciated... ThanksPls ignore the 2nd problem. I've found the root cause. my Applogies...;P
But the 1st problem, i've encountered several times.
"Samie" wrote:
> Hi,
> ###Problem 1###
> I've encountered serveral times on this problem Every time when I used store
> procedure to create a datatset. In the store procedure, it cannot have more
> then 2 select statement even though the first select state is only shown when
> a flag is turned on. For example :
> ...
> set @.debug=0
> if @.debug=1
> select * from ABC
> Select X, Y, Z from WWW
> ...
> The dataset will shows the 1st select statement instead of the 2nd. I've to
> deleted the those unwanted lines before my dataset is correct. Is this a bug?
>
> ###Problem 2###
> Currently, I'm facing another problem with DataSet ...:(
> This time when i wrote a intensive coding in the store procedure with
> consists of quite a number of insert Temp tables. When I run the store
> procedure using the Query Analyzer, my dataset is correct. However when I
> used Reporting Services, I'm unable to get the correct dataset. Why is this
> so?
> Any help is definitely appreciated... Thanks

DataDir Property - Is it possible to store files on more than 1 drive?

The "DataDir" property for an AS 2005 instance seems to only allow for one path to be specified. I am working with a very large data set that is being updated frequently and I would like to be able to reference more than on LUN on my disk array.

Yes, you can.

Take a look at the StorageLocation property of your partition.

Edward Melomed (MSFT)

--

This posting is provided "AS IS" with no warranties, and confers no rights.

Database/table help

Almost embarassed to ask this but here it goes...

Need to create a database, which will store employee, manager and department. Could someone please help me in creating these tables and their relationships. Looking for something efficient and simple...where this employee works in this department, managed by manager.

Thank you.

May be you should use a simpler product like MS Access to see how you can create these tables and establish relations between the tables.|||

hi,

perhaps you can have a look at http://www.databaseanswers.org/data_models/hr_intro.htm..

the Microsoft database sample AdventureWorks include a human resource implementation as well.. you can see/download the diagram at http://www.microsoft.com/downloads/details.aspx?FamilyID=0f6e0bcf-a1b5-4760-8d79-67970f93d5ff&DisplayLang=en

regards

database trigger question

Hi

I am trying to setup a trigger on a database where the trigger fires off a store proc when there is an insert. For some reason, its working on a database in Dev and not on a database in QA.

the trigger is on an insert to a table, the trigger looks something like this

create trigger XXX

after Insert

SET XACT_ABORT OFF -- this so that when the proc attached to the trigger fails, insert it anyway

exec updatesomething

if @.@.error <> 0

exec createAudit

In dev, the row is inserted, but in QA the row is not. I did a trace, both have the SQL:BatchCompleted event of the insert sql statement, but in QA environment, the trace does not have the sql statement after exec updatesomething. it just stops at exec updatesomething.

I check the database settings to make sure there were the same, looks like they are, I do not know how to find out what is causing it to work in 1 database and not the other

thanks

Pauli

A few things to check for -

Make sure that the trigger exists and is enabled -- select objectproperty(object_id('dbo.XXX'), 'ExecIsTriggerDisabled') -- should return 0.

Your description above seems to imply that the trigger actually fired in the QA environment. If so, then it could be that there was an error causing the 'exec createAudit' to be skipped. You can check for this by looking for error events in the trace output.

Hope that helps you track it down.