November 9, 2014 at 9:48 pm
After a recent application patching on one of our production servers SQL 2008 R2 SP2 Servers users complain that their crystal reports do not show correct info. After initial investigation I have noticed that the problem is related to a views those reports are based on. (Just need to add that in test env all went smooth.)
Looks like columns names and data have been offset by one. To make thing more interesting if I look in SQL statement in View design I can see the following
SELECT
dbo.RAMAPPLICATIONPROCESS.*,
dbo.COR_V_GEN_PLUApplications.RAM_PRIMARY_GROUP AS ASSOCIATION_TYPE,
dbo.COR_V_GEN_PLUApplications.ASSOCIATION_TYPE AS Application_No
FROM dbo.RAMAPPLICATIONPROCESS INNER JOIN
dbo.COR_V_GEN_PLUApplications ON dbo.RAMAPPLICATIONPROCESS.RAM_ID = dbo.COR_V_GEN_PLUApplications.Application_No
If I click on the same view and generate a create script I get the following
SELECT
dbo.RAMAPPLICATIONPROCESS.*,
dbo.COR_V_GEN_PLUApplications.ASSOCIATION_TYPE,
dbo.COR_V_GEN_PLUApplications.Application_No,
FROM dbo.RAMAPPLICATIONPROCESS INNER JOIN
dbo.COR_V_GEN_PLUApplications ON dbo.RAMAPPLICATIONPROCESS.RAM_ID = dbo.COR_V_GEN_PLUApplications.Application_No
Second statement is the correct one.
My Question is how is this possible that the same view shows two different sql statements? Did something got corrupted during application upgrade. If so. How do we fix it?
CC :w00t:
November 9, 2014 at 10:32 pm
Quick thought, what happens if you script the view (modify/script as ALTER) and run the script unchanged?
😎
November 10, 2014 at 12:31 pm
I created a view with an * once, and then when the table that the view referenced got its definition changed, the column names in the view did not correspond with the column in the source table that they originally corresponded with. Is it possible that you've run into this situation that could cause an issue with one or the other designer (design view versus script to function)?
A quick fix would be to resave the view, but really, nobody recommends the * in views and other code meant to be stable because of the issue of moving table definitions ruining the * business.
November 10, 2014 at 12:42 pm
patrickmcginnis59 10839 (11/10/2014)
I created a view with an * once, and then when the table that the view referenced got its definition changed, the column names in the view did not correspond with the column in the source table that they originally corresponded with. Is it possible that you've run into this situation that could cause an issue with one or the other designer (design view versus script to function)?A quick fix would be to resave the view, but really, nobody recommends the * in views and other code meant to be stable because of the issue of moving table definitions ruining the * business.
Yeap, this is yet another reason to stop using select *. The view will continue to work but if the order or columns moves in the base table your view will get scrambled. And if datatypes changes it sort of works, but if the datatypes change you are screwed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply