Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-02-01
13
Medium Priority
?
330 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

877 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