Accessing columns using fully qualified name from a Join

1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
JDBC, Service Pack 2, Version 2.2.0037
2) The query I'm executing is:
select * from employees, dept, city where dept.dpid=employees.dept and " +
"employees.cityofresidence=city.cid order by dept.dpid, empid
3) city and dept tables have a field called 'name'
4) rs.getString("name") gives dept.name (city.name if city appears before
dept in the FROM clause of the query)
5) rs.getString("city.name") throws exception - 'Invalid column name'
How can I use the fully qualified column name to retrieve correct values
irrespective of the order of the appearance of the table names in the FROM
clause of the query
Note: rs.getString(int col_num) always retrieves correct values.
praskam wrote:

> 1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
> JDBC, Service Pack 2, Version 2.2.0037
> 2) The query I'm executing is:
> select * from employees, dept, city where dept.dpid=employees.dept and " +
> "employees.cityofresidence=city.cid order by dept.dpid, empid
> 3) city and dept tables have a field called 'name'
> 4) rs.getString("name") gives dept.name (city.name if city appears before
> dept in the FROM clause of the query)
That is correct, per JDBC spec.

> 5) rs.getString("city.name") throws exception - 'Invalid column name'
The data/metadata that come back from the DBMS do not contain any reference to the
table from which a column came.

> How can I use the fully qualified column name to retrieve correct values
> irrespective of the order of the appearance of the table names in the FROM
> clause of the query.
You can't. Unless you explicitly define a label for each column in the SQL.
That would involve replacing "select *" with select t1.col1 'table1.col1', t1.col2 'table1.col2' ...
t2.col1 'table2.col1' etc. from table1 t1, table2 t2 etc.
Note that you can use any string for a column label. Using "table1.col1" would suit your
request, but you could tailor it as you see fit.
Joe Weinstein at BEA

> Note: rs.getString(int col_num) always retrieves correct values.
As expected, and is the most reliable.
>

Access Not Executing Stored Procedure correctly

Hi guys I cant seem to get my stored procedure to execute properly through Access Xp. Do you think there is something wrong with my stored procedure??

CREATE PROCEDURE [insert_ConditionalLicense_UpdateFromTerms]
(@.TM_# [int],
@.FirstName [nvarchar](50),
@.LastName [nvarchar](50),
@.SS# [nvarchar](50),
@.Birthdate [nvarchar](50),
@.reasonforconditional [ntext],
@.Notes [ntext])

AS INSERT INTO [GamingCommissiondb].[dbo].[ConditionalLicense_View]
( [TM #],
[FirstName],
[LastName],
[SS#],
[reasonforconditional],
[ConditionalStart Date])


SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATION-COND'
IF @.@.Error <> '0'
RETURN

when I execute it through a command button this is the message I get "paramater" not quite sure why I am getting this messageYou have to provide the parameters on the call you are making with the "on click" action. Otherwise, it will prompt you for them.

?|||Well I have the same kind of stored procedure on another form and it doesnt do that. I have a stored procedure on the Termination form that executes once the users press the command button. Hmm??|||No. There has to be some difference between the two. Look at them closely to find it.|||Ok I will take a closer look at my stored procedure. I'll try again thanks guys|||Ok I will take a closer look at my stored procedure. I'll try again thanks guys

Access is denied when executing xp_cmdshell delete

hi y'all!
here i am again with another problem:

declare @.path varchar (100)
declare @.filename varchar (100)
delcare @.delete_this varchar (100)
declare @.cmd varchar (100)

set @.path = '"\\servername\shared\"'
set @.filename = 'file.txt'
set @.delete_this = @.path+@.filename
set @.cmd = 'del '+@.delete_this

exec master..xp_cmdshell @.cmd

i get this:

Access is denied.

Please Advise
Thnx.I've also heard of "net use"

Can u please enlighten me on this.

Access denied in "Best Practices Analyzer"

Hi.
I get this error on certain results in the compliance report. It only seems
to happen when i use SQL Server authentication
Error while executing the Best Practice Group. See the log file
test_JRSJF0J_0001_(local)_sqlbpa.log. Error message: Access is denied.
Anyone?
Hi Michael
Is the user account used to scan a valid login into SQL Server? Can you
connect from Query Analyzer? Does it have access to all databases specified
in the scan?
If you still have problems, you can use SQL Profiler and log exceptions to
see server side activity and identify the statement/operation that is
failing the access check.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael_Sergel" <MichaelSergel@.discussions.microsoft.com> wrote in message
news:AD22C6B6-CCA9-49A6-B188-18EB8CEAB8BE@.microsoft.com...
> Hi.
> I get this error on certain results in the compliance report. It only
seems
> to happen when i use SQL Server authentication
> Error while executing the Best Practice Group. See the log file
> test_JRSJF0J_0001_(local)_sqlbpa.log. Error message: Access is denied.
> Anyone?
>