View Works in SQL2005 but not SQL2008 with Cast and Round Function

Migrating from SQL2005 to SQL2008 and working on views I have a table called ANALOG in a database called SCADA there is also another table called displayfactors. Values in Analog with the VALUE column comes in as a VARCHAR. In DisplayFactors table the data is  numeric(18,6).

Here is current View in SQL2005:
SELECT     dbo.ANALOG.STATION, dbo.df_table.STATION AS DFSTATION, dbo.ANALOG.VALUE, dbo.df_table.DF, dbo.ANALOG.NAME, ROUND(CAST(dbo.ANALOG.VALUE AS FLOAT)
                      * (dbo.df_table.DF * 1000), 2) AS KVALUE, ROUND(CAST(dbo.ANALOG.VALUE AS FLOAT) * dbo.df_table.DF, 2) AS TVALUE
FROM         dbo.ANALOG CROSS JOIN
                      dbo.df_table

Once again this works in SQL2005.

Error I get is Conversion failed when converting the nvarchar value '.00720' to data type int.

All data in Value field on ANALOG is numbers. Some numbers as 23456
rayburnelectricAsked:
Who is Participating?
 
rayburnelectricConnect With a Mentor Author Commented:
I found solution. My DisplayFactor value needed to be CAST as well in SQL2008

SELECT     dbo.ANALOG.STATION, dbo.df_table.STATION AS DFSTATION, dbo.ANALOG.VALUE, dbo.df_table.DF, dbo.ANALOG.NAME, ROUND(CAST(dbo.ANALOG.VALUE AS FLOAT)
                      * (CAST(dbo.df_table.DF AS FLOAT) * 1000), 3) AS KVALUE, ROUND(CAST(dbo.ANALOG.VALUE AS FLOAT) * CAST(dbo.df_table.DF AS FLOAT), 3) AS TVALUE
FROM         dbo.ANALOG CROSS JOIN
                      dbo.df_table
WHERE     (dbo.ANALOG.STATION = 'CELI') AND (dbo.df_table.STATION = 'CELI1')

Results:
STATION   DFSTATION   VALUE         DF            NAME     KVALUE     TVALUE
CELI      CELI1            45.3351     .00480     AMPA           217.608     0.218
0
 
esolveCommented:
You will have to do something like this:

DECLARE @Number varchar(32) = '.00720'
SELECT CONVERT(INT, CONVERT(FLOAT, @Number))
0
 
rayburnelectricAuthor Commented:
Its more numbers than just .00720 There is hundreds of rows with Values.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mastooCommented:
I'm missing why there is any conversion to Int going on, and is that verbatim the error message you get?  I would have expected something more like 'Error converting data type varchar to int'
0
 
rayburnelectricAuthor Commented:
The bottom part of my question states that. -


Error I get is Conversion failed when converting the nvarchar value '.00720' to data type int.
I don't know why it says nvarchar.
0
 
esolveCommented:
It could be that your SQL2008 has a different collation that your previous server. The nvarchar is used to store unicode characters which allows for eg. chinese characters etc. Which is the reason why I suspect your collation is different between the servers.

The collation is specified during installation but can be changed afterwards. You can also include the collate keyword in your query to cater for this.

http://msdn.microsoft.com/en-us/library/ms184391.aspx
0
 
rayburnelectricAuthor Commented:
No help. Pointing me to a Microsoft article that gives no literal examples of how to apply a fix but leaves it up to user to guess and click on reference that leads to another reference is circular and unhelpful. If you have an idea write it how I should apply. The next person that has this issue or question the link may not work or have moved.
0
 
esolveCommented:
My apologies:

Run this on both servers to find out if the collation is the same:
select @@servername as ServerName, serverproperty('collation') as Collation

If this is not the same you have two choices:
1. Change the code of your view to include the "Collate" keyword which will force the query to use the correct datatypes resulting in the correct conversions (reason why I sent above article)
SELECT email COLLATE SQL_Latin1_General_CP1_CI_AS AS email
2. Change the default collation of the server.

This might not be the problem. Just a suggestion for something to illiminate. Probably easier recreating the view.

Regards
0
 
rayburnelectricAuthor Commented:
SQL2008
ServerName      Collation
RCEC-WWW      SQL_Latin1_General_CP1_CI_AS

SQL2005
ServerName      Collation
QEISCADA      SQL_Latin1_General_CP1_CI_AS
0
 
LowfatspreadCommented:
can you post the actual query you are performing against the view...
0
 
LowfatspreadCommented:
can you show us the result you get for the .00720 value in 2005?

can you confirm what isnumeric(value) returns for the .0720 value as well in 2005/2008
0
 
rayburnelectricAuthor Commented:
SELECT     dbo.ANALOG.STATION, dbo.df_table.STATION AS DFSTATION, dbo.ANALOG.VALUE, dbo.df_table.DF, dbo.ANALOG.NAME, ROUND(CAST(dbo.ANALOG.VALUE AS FLOAT)
                      * (dbo.df_table.DF * 1000), 2) AS KVALUE, ROUND(CAST(dbo.ANALOG.VALUE AS FLOAT) * dbo.df_table.DF, 2) AS TVALUE
FROM         dbo.ANALOG CROSS JOIN
                      dbo.df_table
WHERE     (dbo.ANALOG.STATION = 'CELI') AND (dbo.df_table.STATION = 'CELI1')
This is actually from an MVC 4 page that the query is presented. But I am currently just working in SQL Server Management Studio to find out why it is broke.
0
 
rayburnelectricAuthor Commented:
Because I found it on my own.
0
All Courses

From novice to tech pro — start learning today.