Solved

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

Posted on 2013-02-01
13
316 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now