Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpHi Andrew,
I am not sure I understood your issue.
Could you place DDL + Sample Data?
What I did not understand is where are the queries writen? In Access Queries
? In Delphi code? In Stored Procedures?
I hope I can help you if I get a better idea of the issue.
Bye,|||you must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregonsql

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpyou must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregon

Problem with Returning a recordset from a stored procedure in ADO/VBScript

I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.

Here is my code:
<%
.....
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()

if not logRS.EOF then
.....
%>

My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.A good place to start looking is the ADO Connection Error collection. Check to see if Connect.Errors.Count > 0. If so, you will probably find your problem there.

Also, you can try adding SET NOCOUNT ON at the beginning of your SP, and SET NOCOUNT OFF at the end, before you return your recordset. Sometimes the command object stops asking for data when it gets the "X records affected" messages.

Finally, if that doesn't work, try being more explicit with your parameter naming. A good (and more readable) approach would be to use the CreateParameter function.

CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As ADO_LONGPTR], [Value]) As Parameter

Assume your parameter is an INT named @.my_param

cmm.Parameters.Append cmm.CreateParameter("@.my_param",3,1, 4,client_id)

[Note: the values of DataTypeEnum and ParameterDriectionEnum can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumnz_2.asp ]

Hope this helps...|||Ahh. Thank you so much. It was the NOCOUNT property.