Solved

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

Posted on 2013-02-01
13
327 Views
Last Modified: 2013-02-06
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
0
Comment
Question by:rayburnelectric
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 6

Expert Comment

by:esolve
ID: 38843676
You will have to do something like this:

DECLARE @Number varchar(32) = '.00720'
SELECT CONVERT(INT, CONVERT(FLOAT, @Number))
0
 

Author Comment

by:rayburnelectric
ID: 38843748
Its more numbers than just .00720 There is hundreds of rows with Values.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 38843893
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rayburnelectric
ID: 38843909
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
 
LVL 6

Expert Comment

by:esolve
ID: 38844054
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
 

Author Comment

by:rayburnelectric
ID: 38844140
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
 
LVL 6

Expert Comment

by:esolve
ID: 38844215
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
 

Author Comment

by:rayburnelectric
ID: 38844228
SQL2008
ServerName      Collation
RCEC-WWW      SQL_Latin1_General_CP1_CI_AS

SQL2005
ServerName      Collation
QEISCADA      SQL_Latin1_General_CP1_CI_AS
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38844256
can you post the actual query you are performing against the view...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38844265
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
 

Author Comment

by:rayburnelectric
ID: 38844287
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
 

Accepted Solution

by:
rayburnelectric earned 0 total points
ID: 38844339
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
 

Author Closing Comment

by:rayburnelectric
ID: 38858711
Because I found it on my own.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question