Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved DataBase Error: 42000 Microsoft SQL Server Native Client 10.0

  1. Issue
0
Votes
Undo
  1. Anton van der Post
    Anton van der Post
  2. PowerBuilder
  3. Tuesday, 27 March 2018 16:38 PM UTC
Running in following error MicroSoft SQLServer 2008 R2: DataBase Error: (209=SQLSTATE = 42000 Microsoft SQL Server Native Client 10.0 Ambiguous column name 'OrderKey' - checked all datastores and datawindows for duplication of column OrderKey

- checked related stored stored procedures for duplication of column OrderKey in final result set (NO COUNT ON) The same code runs fine when using a copy of the database in SQL2000 Compatible Mode. Any pointers are appreciated Thanks, Anton
  1. Error
Slow Startup?
Windows 10 Environment object....PB17 R2
Responses (3)
Anton van der Post
Anton van der Post Accepted Answer Pending Moderation
  1. Thursday, 29 March 2018 14:47 PM UTC
  2. PowerBuilder
  3. # 1
All - thanks for your replies. Using the suggestions I was able to pinpoint the "non-compliant" stored procedure. To test the problem: I took the SQL Server2000 stored procedure and loaded that in SQL Server 2008 R2 native database mode and there was the error: Msg 209, Level 16, State 1, Procedure nspCartonization, Line 1154 Ambiguous column name OrderKey Msg 209, Level 16, State 1, Procedure nspCartonization, Line 1154 Ambiguous column name CartonGroup SQL SERVER 2008 NATIVE MODE - NO LONGER ALLOWED syntax: SELECT OrderKey, CartonGroup, * FROM TABLE ==>> This has duplicated column names and execution / compile of the procedure fails. Problem solved with: SELECT OrderKey, CartonGroup FROM TABLE or SELECT * FROM TABLE The application Runtime has no longer this error. Best Regards, Anton
Comment
There are no comments made yet.
Lars Mosegaard
Lars Mosegaard Accepted Answer Pending Moderation
  1. Wednesday, 28 March 2018 04:30 AM UTC
  2. PowerBuilder
  3. # 2

I have seen that error when using a column name in the ORDER BY clause even where there is no other column possible. 

Work around is to order the relative column number of the columns.  for example ....ORDER BY 2 , 1 desc 

Comment
Markus Eckert
  1. Markus Eckert
  2. Thursday, 29 March 2018 12:02 PM UTC
It's enough to use . syntax in the ORDER BY.



SQL Server 2008 is stricter than 2005 and preceding as far as what's acceptable ambiguity in the order by column.



This isn't allowed in SQL Server 2008 and higher:



SELECT col1, col2, col1

FROM table1

ORDER BY col1



This is:



SELECT col1, col2, col1

FROM table1

ORDER BY table1.col1

  1. Helpful
Anton van der Post
  1. Anton van der Post
  2. Thursday, 29 March 2018 14:33 PM UTC
.

  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 27 March 2018 21:04 PM UTC
  2. PowerBuilder
  3. # 3

It is possible that the 2008 server has tighter rules than that of the 2000 server.

Try pre-pending the table name to the columns names and see if that helps.

Sometimes using an alias instead of the actual table name is easier to do.

 

Example:

SELECT  column1  FROM  table1;

becomes

SELECT  T1.column1 FROM  table 1  T1;

 

Olan

​

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.