October 24, 2008 at 7:37 am
I put this in Newbies because it is something simple and I don't understand it. I have personally test in both SQL Server 2000 and SQL Server 2005 using SQL Server Management Studio 2005. It only occurs on some databases in an instance but not all.
if object_id('dbo.dps') is not null
drop table dbo.dps
go
create table dbo.dps (
instr_type char(1),
company char(10),
issue_id char(8),
rec_payable char(1),
sched_date datetime,
pay_type char(1),
effect_date datetime,
pay_amount float,
status char(1)
)
go
INSERT INTO dps (instr_type,company,issue_id,rec_payable,sched_date,pay_type,effect_date,pay_amount,status)
SELECT 'L','CMP','ISSUE','R',GETDATE(),'I',DATEADD(DAY,1,GETDATE()),1000,'C' UNION ALL
SELECT 'L','CMP','ISSUE','R',DATEADD(MONTH,1,GETDATE()),'I',DATEADD(MONTH,1,DATEADD(DAY,1,GETDATE())),1000,'C' UNION ALL
SELECT 'L','CMP','ISSUE','R',DATEADD(MONTH,2,GETDATE()),'I',DATEADD(MONTH,2,DATEADD(DAY,1,GETDATE())),1000,'C' UNION ALL
SELECT 'L','CMP','ISSUE','R',DATEADD(MONTH,3,GETDATE()),'P',DATEADD(MONTH,3,DATEADD(DAY,1,GETDATE())),1000,'C'
GO
SELECT pay_type, effect_date, pay_amount, effect_date FROM dps ORDER BY effect_date
GO
The reason effect_date is there twice is because it was a code change and someone didn't take the time to replace variables, etc... but why on some databases is there an 'Ambiguous column name' error and other times not. I was able to reproduce it on master but not the production database. Maybe there's some setting or something?
October 24, 2008 at 9:17 am
When I run it I get an error against DB's in 2005 mode, but it runs fine if I change the compatibility to 2000. So it is a change in how SQL Server processes the order by from 2000 to 2005. You could change it by aliasing one of the effect_date columns or by using the position in the order by (ORDER BY 2 or 4).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2008 at 9:25 am
Thank you, I would have been searching forever for that :hehe: I've since updated the code to work properly but still thought it was a bit strange.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply