November 13, 2007 at 2:05 am
Hello,
I am really confused now. I have the following query:
SELECT CONVERT(char(4), P.name) Name
,CONVERT(char(19), p.starttime ,121) StartTime
,CONVERT(char(19), s.starttime ,121) starttime
,CONVERT(char(19), s.endtime ,121) endtime
,p.errorcode
,p.errordescription
FROMMSDB..sysdtspackagelog P
,MSDB..sysdtssteplog S
WHERES.lineagefull = P.lineagefull
ANDP.name LIKE 'A101%'
ANDP.lineagefull = '4DD8ACB9-7C6A-458E-A051-5985643BFFEA'
ORDER BY S.starttime
for some reason it orders sometimes by the column "StartTime" (which is p.starttime) instead of column "S.starttime". I guess theres a simple solution for this, so would anyone be able to provide me a clue? Even more confusing is the fact, that around every 4th execution, the query returns the resullts in the correct order.
Incorrect order:
Name StartTime starttime endtime errorcode
---- ------------------- ------------------- ------------------- -----------
A101 2007-11-06 15:55:53 2007-11-06 16:08:22 NULL NULL
A101 2007-11-06 15:55:53 2007-11-06 15:55:53 2007-11-06 15:55:53 NULL
A101 2007-11-06 15:55:53 2007-11-06 15:55:53 2007-11-06 15:57:23 NULL
A101 2007-11-06 15:55:53 2007-11-06 15:57:23 2007-11-06 16:08:22 NULL
(4 row(s) affected)
Correct order:
Name StartTime starttime endtime errorcode
---- ------------------- ------------------- ------------------- -----------
A101 2007-11-06 15:55:53 2007-11-06 15:55:53 2007-11-06 15:55:53 NULL
A101 2007-11-06 15:55:53 2007-11-06 15:55:53 2007-11-06 15:57:23 NULL
A101 2007-11-06 15:55:53 2007-11-06 15:57:23 2007-11-06 16:08:22 NULL
A101 2007-11-06 15:55:53 2007-11-06 16:08:22 NULL NULL
(4 row(s) affected)
Many Thanks!
P.S.: This is just an ad-hoc query. In a "real-life scenario" i would not use the same alias for 2 or more columns.
Best Regards,
Chris Büttner
November 13, 2007 at 2:16 am
Try to use CASE SENSITIVE COLLATION.
N 56°04'39.16"
E 12°55'05.25"
November 13, 2007 at 2:32 am
Hi Peter,
Could you please elaborate a little more on what you are trying to show me?
I cannot use a collation for the datetime column that I want to sort.
I have tried a case sensitive collation for the column I dont want to sort:
SELECT CONVERT(char(4), P.name) Name
,CONVERT(char(19), p.starttime ,121) COLLATE SQL_Latin1_General_Cp1_CS_AS StartTime
,CONVERT(char(19), s.starttime ,121) starttime
,CONVERT(char(19), s.endtime ,121) endtime
,p.errorcode
--,p.errordescription
FROM MSDB..sysdtspackagelog P
,MSDB..sysdtssteplog S
WHERE S.lineagefull = P.lineagefull
AND P.name LIKE 'A101%'
AND P.lineagefull = '4DD8ACB9-7C6A-458E-A051-5985643BFFEA'
ORDER BY S.starttime
Name StartTime starttime endtime errorcode
---- ------------------- ------------------- ------------------- -----------
A101 2007-11-06 15:55:53 2007-11-06 16:08:22 NULL NULL
A101 2007-11-06 15:55:53 2007-11-06 15:55:53 2007-11-06 15:55:53 NULL
A101 2007-11-06 15:55:53 2007-11-06 15:55:53 2007-11-06 15:57:23 NULL
A101 2007-11-06 15:55:53 2007-11-06 15:57:23 2007-11-06 16:08:22 NULL
(4 row(s) affected)
but that doesnt change the behaviour of the query. It still randomly sorts from time to time.
Where exactly do you want me to change the collation?
Thanks!
Best Regards,
Chris Büttner
November 13, 2007 at 3:08 am
Is The Table is a Stable Table or a Transaction Table ie The table is Growing/ getting deleted as Time changes?????????
November 13, 2007 at 3:15 am
Hello vinuraj,
these are system tables.
If you enable logging of DTS packages to SQL Server,
the logs will be stored in these two tables.
The table does grow, but this specific data set is not updated / deleted / re-inserted anymore.
Thanks!
Best Regards,
Chris Büttner
November 13, 2007 at 3:20 am
You are Right.
Is There any posibility of flushing out the data from the table?
November 13, 2007 at 3:26 am
I don't think the data is the issue.
And I dont like the idea of flushing the data out. It "might" solve this issue, but it doesn't help in identifying the cause of this.
Since this is an ad-hoc query, I can live with the incorrect ordering (or use distinct column aliases to solve the problem).
But I am more or less just curious where this specific statement fails and whether this is due to
a coding issue on my side or some well-defined SQL Server behaviour.
Thanks for sharing your thoughts!
Best Regards,
Chris Büttner
November 13, 2007 at 4:42 am
The order by clause runs after the aliases in the select have been applied. This is most likely undefined behaviour deriving from technically incorrect SQL. Most likely whichever column gets aliased first gets the StartTime alias and that changes on each exec plan recompile
In SQL 2005, if you run that query you'll get an error 'ambiguous column name'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2007 at 5:22 am
Hello Gail,
Did you try this query in SQL2K5?
Because when I run it from either QA or SSMS against a SQL2K5 machine, I get no errors.
(remember, this is an ad-hoc query where multiple columns with the same name actually are allowed).
Even though the order by is the last logical step in the process, it should correctly order by S.starttime since I specified the table alias as well. If I wrote "ORDER BY starttime" instead of "ORDER BY S.starttime", I would understand the ambiguity. But with the table alias "S.", the case should be clear on which column to use for sorting.
Or did I miss something?
Here's something from BOL on which my assumption is based:
order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL Server 2005 (90) compatibility mode the expression cannot resolve to a constant. Column names and aliases can be qualified by the table or view name. In SQL Server 2005, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.
Thanks!
Best Regards,
Chris Büttner
November 13, 2007 at 12:06 pm
The behaviour you watch follows ANSI standard.
According to it ORDER BY looks for a column(s) with name(s) specified in outgoing resultset. If it can find specified column it sorts by it. No prefixes are taken into consideration.
Effectively "ORDER BY S.starttime" is equivalent to "ORDER BY starttime" in your case because you've got column "starttime" in the output.
SQL2000 takes the first "starttime" it can find in the query, SQL2005 cannot resolve the query because it finds 2 "starttime" columns and cannot decide which one to take for ordering.
_____________
Code for TallyGenerator
November 13, 2007 at 1:01 pm
I'm pretty new to SQL but have you tried aliasing the second starttime?
November 13, 2007 at 2:55 pm
Hello Sergiy,
thanks for your response.
Do you have a web or book resource where I could find more of the information you gave me?
Especially the thing with ignoring the table alias in the order by.
Besides that, you are the second person that tells me SQL2K5 cannot resolve the query.
What do you mean by this? I ran the query with SQL2k5 at home and it threw no error.
Thanks!
--------------------------------------
Hello siboyd07,
yes, aliasing the column helps. But I was more interested in an explanation of the behaviour when I have exactly this column name that I specified originally.
Thanks for your help!
Best Regards,
Chris Büttner
November 13, 2007 at 3:02 pm
I don't have your tables handy, so I can't run that query. Maybe this will suffice. Against a SQL 2005 SP2 server
select name, object_id, type_desc, create_date, create_date
from sys.objects
-- Runs fine. 66 rows returned in the master database.
select name, object_id, type_desc, create_date, create_date
from sys.objects
order by create_date
-- Msg 209, Level 16, State 1, Line 1
-- Ambiguous column name 'create_date'.
Duplicate column names are always allowed. An order by referencing a duplicated column name gives an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2007 at 3:26 pm
November 14, 2007 at 12:32 am
Hello Gail,
you should be able to run the query I posted since it uses only system objects in MSDB.
There is no table I created myself.
Regarding the query you provided, it indeed throws an error, but thats because you dont use the table alias or name in the order by clause. If you replace your query with this one, it should work:
select name, object_id, type_desc, create_date, create_date
from sys.objects
order by sys.objects.create_date -- Prefix order by col with tablename or -alias
Thanks!
Best Regards,
Chris Büttner
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply