Accessing columns without the coulmn names

Hi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- PradeepCan you explain how you want to use this information? There are probably ways to get you what you'd like, but there are at least as many really bad answers as really good ones.

-PatP|||Pat, I need to get the value of columns 1 and 2 from the table EMP, while not being bothered as to the names of the columns.|||I'd suggest something like:SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE 'table1' = c.TABLE_NAME
AND 1 = c.ORDINAL_POSITIONSee the BOL regarding INFORMATION_SCHEMA.COLUMNS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_87w3.asp) for more details.

-PatP|||Thanks a million Pat, this is what I was looking for.
Are you a SQL Server DBA?

cheers/- Pradeep|||Heck, I've been called even worse than than SQL DBA!

I answer to just about anything from "Hey you" to "Data Modeler", and everything in between. I tend to refer to myself as a Resident Curmudgeon, which seems about as good a description as any to me!

On a (very slightly) more serious note, yes, SQL DBA is one of the hats that I wear nearly every day.

-PatP|||SQL DataBase Analist|||SQL DataBase AnalistI'm a what-ist ?!?!

-PatP|||i think i can translate ...

ahem.

NEH-HERD.

Scott Davis
"If you are not part of the solution, you are probably 1/5 of the suspension" RDJabarov.|||Now Scott, what the heck does that mean? And when are you planning to have your Margarita? At my funeral?

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 Text of Parameter

I have a report that uses parameters from a query. The query returns two
columns. One for the paramerter value and one for the parameter title. I want
the header of the report to access the same fild that I have set as the Label
Field in my report parameter dropdown.
I can add the actual parameter to the header using:
=Parameters!Property.Value
But I don't want the actual parameter, i want the alias from the second
colum of the parameter query.
I tried
=Parameters!Property.Text
but it didn't work.
Any ideas?
Thanks!try =Parameters!Property.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
>I have a report that uses parameters from a query. The query returns two
> columns. One for the paramerter value and one for the parameter title. I
> want
> the header of the report to access the same fild that I have set as the
> Label
> Field in my report parameter dropdown.
> I can add the actual parameter to the header using:
> =Parameters!Property.Value
> But I don't want the actual parameter, i want the alias from the second
> colum of the parameter query.
> I tried
> =Parameters!Property.Text
> but it didn't work.
> Any ideas?
> Thanks!|||That did it.
Many thanks Lev!
"Lev Semenets [MSFT]" wrote:
> try =Parameters!Property.Label
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
> message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
> >I have a report that uses parameters from a query. The query returns two
> > columns. One for the paramerter value and one for the parameter title. I
> > want
> > the header of the report to access the same fild that I have set as the
> > Label
> > Field in my report parameter dropdown.
> >
> > I can add the actual parameter to the header using:
> >
> > =Parameters!Property.Value
> >
> > But I don't want the actual parameter, i want the alias from the second
> > colum of the parameter query.
> >
> > I tried
> > =Parameters!Property.Text
> > but it didn't work.
> >
> > Any ideas?
> >
> > Thanks!
>
>

ACCESS SQL COLUMN DATA TYPE

HELLO

i have an sql server database, with a table an some columns.

how can i get the data type of each column and the lengh define in the database table?

Are you working with SQL Server 2000 or 2005?

|||

sql 2000

|||

Fire up profiler, filter to your spid, and run sp_help on a table. See what SQL Server does. You can copy the code from what SQL Server does and use it for yourself. To start with, check out syscolumns in books online.

|||

Or, you could use the INFORMATION_SCHEMA views to do it:

SELECT *FROM INFORMATION_SCHEMA.columnsWHERE table_Name =table  

HTH,

Stu

access rights - dynamic reports

hi,
is it possible to make "dynamic reports" for users with different user
rights. depending on their rights one user sees all the columns and
another one with less rights sees just the first column?
thanksthere probably is ... but since i can't tell you i'll give you an
alternative solution - why don't you just create 2 reports
one for general users containing data that general people can see
one for 'special' users containing all of the 'secret' data. because
more than likely once the 'special' users see what they can get,
they're going to want more and it'd be easier to manage the reports on
a group basis rather than a more granular column by column basis.
those are my thoughts anyway ...
hth!

Access Result Set from storede procedure w/in a stored procedure

Hi All
I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.
Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?
Thanks,Two choices

Use multiple output variables
or
Use a table variable.

Access fields in the Fields collection by index

Is there anyway to access the fields collection using an index?
e.g,. =Fileds(0).Value ?
I want to create a calculated field based on two columns (position 1 and 2)
in a matrix report, but their column names change based on the dates as
paramaters.
Any ideas?On May 3, 11:06 am, Eric <E...@.discussions.microsoft.com> wrote:
> Is there anyway to access the fields collection using an index?
> e.g,. =Fileds(0).Value ?
> I want to create a calculated field based on two columns (position 1 and 2)
> in a matrix report, but their column names change based on the dates as
> paramaters.
> Any ideas?
You should be able to access the position 1 column via: =Min(Fields!
FieldThatIsPivoted.Value). The second positioned column will be tricky
to capture though. Otherwise, you should be able to handle this in the
query/stored procedure that sources the report (via while loop or
cursor). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant