Solved

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

Posted on 2013-02-01
13
326 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Disable TLS1.0 on Win 2012 server 7 116
SQL- GROUP BY 4 53
Database Owner 3 45
Importing from CSV to SSMS 2 10
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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