July 19, 2013 at 11:58 am
On a query running from SQL 2012, reading a view on a 2005 server, I am unable to figure out how to evaluate null.
select * from table where field is null returns 0 rows.
select * from table where NOT field is null returns 1163 rows.
select * from table returns 1174 rows.
There are 11 rows with NULL in the field. Running a select ISNULL(field, 'xxxxx') returns NULL instead of the string value.
The same queries run directly on the 2005 server work properly. I can't find any documentation of difference between 2005 and 2012 that would cause this. Does anyone know how to resolve it? Running the query from the 2005 server is not an option at this time.
thank you -
July 19, 2013 at 1:25 pm
Sarah Johnston (7/19/2013)
On a query running from SQL 2012, reading a view on a 2005 server, I am unable to figure out how to evaluate null.select * from table where field is null returns 0 rows.
select * from table where NOT field is null returns 1163 rows.
select * from table returns 1174 rows.
There are 11 rows with NULL in the field. Running a select ISNULL(field, 'xxxxx') returns NULL instead of the string value.
The same queries run directly on the 2005 server work properly. I can't find any documentation of difference between 2005 and 2012 that would cause this. Does anyone know how to resolve it? Running the query from the 2005 server is not an option at this time.
thank you -
Is the value in these rows the string literal "NULL" or are they actually NULL? The default colors in SSMS will fill the cell yellow for actual nulls where a string NULL will be white.
select 'NULL' union all
select null union all
select 'Notice how the middle one here is yellow?'
_______________________________________________________________
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/
July 19, 2013 at 1:26 pm
Try these 3 queries and see what the results are.
select COUNT(*)
from table
where field is null
select COUNT(*)
from table
where field is not null
select COUNT(*)
from table
_______________________________________________________________
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/
July 19, 2013 at 1:33 pm
The value is actually null, not a string. The 3 queries suggested are what I ran to get the counts in my first post. Run from the 2005 server, they behave as expected. from 2012, they don't. All I can think of is that maybe something is different in the encoding types? But I don't know how to test for that.
July 19, 2013 at 1:51 pm
Sarah Johnston (7/19/2013)
The value is actually null, not a string. The 3 queries suggested are what I ran to get the counts in my first post. Run from the 2005 server, they behave as expected. from 2012, they don't. All I can think of is that maybe something is different in the encoding types? But I don't know how to test for that.
Are you doing this across a link server or did you connect directly to the 2005 instance?
_______________________________________________________________
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/
July 20, 2013 at 3:32 pm
Can you post the actual query and the view definition as a start?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 22, 2013 at 7:17 am
The view, on 2005. The orgName from the left outer join is the field I'm currently looking at, and that is used for the following queries.
SELECT LName, FName, AName, orgName, PosNum, [Title Code 1], [Faculty Advisory Group], [EEO Category], Research, Teaching, Extension, [Acad. Support], [Stud. Support], [Admin. Support], [Maint. Support]
FROM
(
/*query that produces data*/
SELECT P.PosNum, P.[Title Code 1], P.[Work Location], LName, FName, AName, [Percent],
CASE
WHEN Branch = 0 THEN 'Teaching'
WHEN Branch = 1 THEN 'Research'
WHEN Branch = 2 THEN 'Extension'
WHEN Branch = 3 THEN 'Acad. Support'
WHEN Branch = 4 THEN 'Stud. Support'
WHEN Branch = 5 THEN 'Admin. Support'
WHEN Branch = 6 THEN 'Maint. Support'
ELSE Branch
END AS Branch
FROM DB.Position P INNER JOIN
(SELECT PosNum, SUM([Acct %]) AS [Percent], substring([Account#], 7, 1) AS Branch
FROM DB.Account
WHERE substring([Account#],1,1)='9'
GROUP BY PosNum, substring([Account#], 7, 1)) A ON P.PosNum = A.PosNum
WHERE LName <> 'VACANT') AS Appts
INNER JOIN DB.Titles T on [Title code 1] = T.[title code]
LEFT OUTER JOIN (select distinct parentID, orgName, unitCode, isHRManaged FROM [SERVER].[DB1].[SCHEMA].[vwOrgsPublic]) O ON Appts.[Work Location] = O.unitCode --AND O.isHRManaged = 1
PIVOT (SUM([Percent]) /*aggregate column*/
FOR [Branch] /*column whose values will become headers */
IN ([Research], [Teaching], [Extension], [Acad. Support], [Stud. Support], [Admin. Support], [Maint. Support])
)
AS PAppts
The queries, from 2005:
select count (*) from DB.SCHEMA.[vwAppt%]
select count (*) from DB.SCHEMA.[vwAppt%] where orgName is null
select count (*) from DB.SCHEMA.[vwAppt%] where not orgName is null
1174
11
1163
The queries, from 2012:
select count (*) from SERVER.DB.SCHEMA.[vwAppt%]
select count (*) from SERVER.DB.SCHEMA.[vwAppt%] where orgName is null
select count (*) from SERVER.DB.SCHEMA.[vwAppt%] where not orgName is null
1174
0
1163
And finally, from 2012 as well:
SELECT distinct ISNULL(HR.orgName,'xxxxxxxxxxx') as orgName,
case when RTRIM(LTRIM(LOWER(HR.orgName)))='null' then 'StringNull' else HR.orgName end as NullTest
FROM SERVER.DB.SCHEMA.[vwAppt%] AS HR
RESULTS:
orgName NullTest
---------------------------------------------------------------------------------------------------- -----------
NULL ActualValue
4-H Youth and Family Development Department ActualValue
Note that the test for the field being a string with the string value null is false, and that the ISNULL function is still ignored.
Thanks,
July 22, 2013 at 3:09 pm
If I understand this correctly, the view definition refers to a table on a third server, and the column you are having problem with comes from that server?
From what you say and show, it smells like a bug in SQL Server. What you can try on your own is to use Profiler against the SQL 2005 instance see what queries SQL 2012 sends to it. You may also have to trace against the third server.
In the end, you will probably have to open a case with Microsoft to get the issue resolved.
What does "SELECT @@version" report for three servers?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 22, 2013 at 3:14 pm
Yes, the view 2005 queries a 2012 db successfully (including nulls). The three versions are:
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86)
Mar 25 2011 13:50:04
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Thanks. Unless there are any other ideas of what to try, I'll pursue opening a case with Microsoft.
July 22, 2013 at 3:33 pm
Beside the tracing I suggested, things you could try is what happens when you move the SQL 2005 database to an SQL 2012 instance etc. But all that is time-consuming, so I am not sure what I want to recommend it.
I like to point out that bug is necessarily not in SQL 2012, but it could exist in SQL 2005, but is exposed now, because SQL 2012 sends different queries to SQL 2005.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply