error when same key and column name in 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

error when same key and column name in 2 tables

Author

Topic

Stoffelke
Starting Member

2 Posts

Posted - 2011-01-14 : 05:31:20

Problem:

We are using 2 times the same primary and the same column name, ie.

- ORD_KEY on ord_nr in table A

- ORD_Key on ord_nr in table B

This results that the first table is working fine but when accessing the second table it gives a system error. Then we tried to put the tables in 2 different databases but this gives the same result.

Anyone knows what the problem can be?

Otherwise we have to change our source code as we are migrating from Topspeed to MSSQL. In Topspeed a prefix before the column name resolves this problem. Is there a possibility to use prefixes or namespaces in MSSQL?

We are using Server 2008 Enterprise x64.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 06:33:00

What is the error? what is the query that throws it?

Poor planning on your part does not constitute an emergency on my part.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 08:43:39

Yes. Alias the tables, then prefix the columns with the alias.

SELECT	t1.columnName, t2.columnName
FROM	table1 as t1
JOIN	table2 as t2
On	t1.pk = t2.pk
-- WHERE columnName is the same name in both tables

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 08:45:34

or, if the error is because of the same name in the result set, you can alias the fields in the select statement


SELECT	t1.columnName as apples, t2.columnName as oranges
FROM	table1 as t1
JOIN	table2 as t2
On	t1.pk = t2.pk
-- WHERE the columnName is the same name in both tables

Stoffelke
Starting Member

2 Posts

Posted - 2011-01-14 : 10:07:55

quote:
Originally posted by dataguru1971

What is the error? what is the query that throws it?

Poor planning on your part does not constitute an emergency on my part.

Adding the first record to the db is ok. For the second the Clarion-MSSQL-driver returns me an empty error-string and code 0. When I'm ignoring the error, nothing is added anymore.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources