Different behaviour of query on 64 bit and 32 bit

Question

Different behaviour of query on 64 bit and 32 bit

viduvs

Ten Centuries

Points: 1026
More actions
June 17, 2010 at 1:39 am

#222207

Hi,
Follwing is the proceduere I am trying to create on SQL Server 2005 64 bit - Enterprise Edition RTM.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc1]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Proc1
WITH ENCRYPTION --on
As
if @@trancount > 0 rollback tran
select [ID] ,[Name],[Name] ,ParentGID
from LUser
where [ID] > 0 and DeleteRequest = 0
order by [Name]
return 0
GO
Its giving me following error:
Ambiguous column name 'Name'
If I run the same code on SQL Server 2005 32 bit, it is creating procedure successfully.
In the query, [Name] column is being fetched twice. So if I remove either of the columns and keep only 1, then procedure is getting created successfully on 64 bit machine. i.e.
select [ID], [Name] ,ParentGID
from LUser
where [ID] > 0 and DeleteRequest = 0
order by [Name]
Another solution I tried is keeping the same query but using table alias, creates procedure successfully. i.e.
select U.[ID] , U.[Name], U.[Name] , U.ParentGID
from LUser U
where U.[ID] > 0 and U.DeleteRequest = 0
order by U.[Name]
So can anybody please tell me why is the different behaviour on 32 bit and 64 bit?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

shield_21 Ten Centuries Points: 1033 More actions · Answer 1

Hi there,

I'm running a 32-bit version of SQL Server.. Your query also produces the same error on my machine.. But, without ORDER BY clause, the query runs fine.. I think it is unclear on what column it will sort ([Name] from second or third column)..

My suggestion is to give alias on the other [Name] column..

select [ID] ,[Name] ,[Name] AS Name2,ParentGID

from LUser

where [ID] > 0 and DeleteRequest = 0

order by [Name]

Cheers,:-D

shield_21

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can you run the following on both the 32 bit and 64 bit versions please

SELECT @@version

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

viduvs Ten Centuries Points: 1026 More actions · Answer 3

Thanks. I got the answer - its only because of conflict order by clause is having. If we give alias, yes it works.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

That's not an answer. 32-bit and 64-bit are supposed to be feature equivalent. I know the one that you say runs fine works on SQL 2000, I've never seen it work on SQL 2005, 32 bit or 64 bit.

Can you post the output of SELECT @@Version on both servers please. Also check the compatability level of the databases on the two servers, make sure they're the same.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

p.s. You should patch that server up to at least SP3. SQL 2005 should not be left at RTM.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass