Returning fully qualified field names

Hi,
I am querying SQL Server 2000 using ADO. I have a join on two tables each
having fields with the same names. MS Access will include the table name
where there is a confilct. Is there a way in SQL of forcing SQL Server to
return the fully qualified field names to the ADO collections for data
retrieval?
i.e. [table].[field] format
Thanks,
Cliveafaik, you have to alias them yourself
select table1.column1 as table1_column1, table2.column1 as
table2_column1 ...
Clive Taylor wrote:

>Hi,
>I am querying SQL Server 2000 using ADO. I have a join on two tables each
>having fields with the same names. MS Access will include the table name
>where there is a confilct. Is there a way in SQL of forcing SQL Server to
>return the fully qualified field names to the ADO collections for data
>retrieval?
>i.e. [table].[field] format
>Thanks,
>Clive
>|||You will have to give them an alias.
AMB
"Clive Taylor" wrote:

> Hi,
> I am querying SQL Server 2000 using ADO. I have a join on two tables each
> having fields with the same names. MS Access will include the table name
> where there is a confilct. Is there a way in SQL of forcing SQL Server to
> return the fully qualified field names to the ADO collections for data
> retrieval?
> i.e. [table].[field] format
> Thanks,
> Clive|||#1. Don't use SELECT *, now you don't have to return the common column
name(s) from both tables. If you are joining Customers and Orders, for
example, what is the point of returning customerID twice?
#2. Anyway, ADO is still going to have a problem, I don't think you can
refer to "table.column" format because it is a much simpler construct once
it gets to ADO, and it doesn't understand what a table is, or which base
table any element came from... all it has a recordset. Typically this is
avoided by using ALIASes, e.g.
SELECT oCustomerID = o.CustomerID, cCustomerID = c.CustomerID
FROM Orders o
INNER JOIN Customers c
ON o.CustomerID = c.CustomerID
Now, in ADO, you refer to rs("oCustomerID") and rs("cCustomerID")...
But see point #1 first. This is not something SQL Server is going to be
able to "automagically." You need to write your queries to take this into
account. And to remove this artificial linkage you have placed between SQL
Server and ADO (and for dozens of other reasons), I suggest you place the
query into a stored procedure.
"Clive Taylor" <CliveTaylor@.discussions.microsoft.com> wrote in message
news:1B8B4ED6-EFD3-4148-ADAA-35C85B524BE5@.microsoft.com...
> Hi,
> I am querying SQL Server 2000 using ADO. I have a join on two tables each
> having fields with the same names. MS Access will include the table name
> where there is a confilct. Is there a way in SQL of forcing SQL Server to
> return the fully qualified field names to the ADO collections for data
> retrieval?
> i.e. [table].[field] format
> Thanks,
> Clive|||Thanks everyone, I was hoping for a short cut but you've confirmed what I wa
s
thinking.
Clive
"Aaron Bertrand [SQL Server MVP]" wrote:

> #1. Don't use SELECT *, now you don't have to return the common column
> name(s) from both tables. If you are joining Customers and Orders, for
> example, what is the point of returning customerID twice?
> #2. Anyway, ADO is still going to have a problem, I don't think you can
> refer to "table.column" format because it is a much simpler construct once
> it gets to ADO, and it doesn't understand what a table is, or which base
> table any element came from... all it has a recordset. Typically this is
> avoided by using ALIASes, e.g.
> SELECT oCustomerID = o.CustomerID, cCustomerID = c.CustomerID
> FROM Orders o
> INNER JOIN Customers c
> ON o.CustomerID = c.CustomerID
> Now, in ADO, you refer to rs("oCustomerID") and rs("cCustomerID")...
> But see point #1 first. This is not something SQL Server is going to be
> able to "automagically." You need to write your queries to take this into
> account. And to remove this artificial linkage you have placed between SQ
L
> Server and ADO (and for dozens of other reasons), I suggest you place the
> query into a stored procedure.
>
> "Clive Taylor" <CliveTaylor@.discussions.microsoft.com> wrote in message
> news:1B8B4ED6-EFD3-4148-ADAA-35C85B524BE5@.microsoft.com...
>
>