Newbie question on identically named fields in a dataset

This is my first stab at this. I have a simple report that returns a few
columns from a database no sp no expressions just a select statement. There
are two tables that have the same field name and the dataset canâ't seem to
distinguish between them it returns the first field into both columns. When
I run it in Query Analyzer it looks fine. What am I missing here?Query Analyzer seems to be a bit more forgiving about identical names. In
your query, just use Aliases, and you should be ok.
A query like
"Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
Table2 (...)"
should give you fields named Table1Col1 and Table2Col1. A bit more trouble
to write, but you eliminate possible errors.
Kaisa M. Lindahl
"RYF" <RYF@.discussions.microsoft.com> wrote in message
news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> This is my first stab at this. I have a simple report that returns a few
> columns from a database no sp no expressions just a select statement.
> There
> are two tables that have the same field name and the dataset can't seem to
> distinguish between them it returns the first field into both columns.
> When
> I run it in Query Analyzer it looks fine. What am I missing here?|||THX that did the trick!
"Kaisa M. Lindahl" wrote:
> Query Analyzer seems to be a bit more forgiving about identical names. In
> your query, just use Aliases, and you should be ok.
> A query like
> "Select table1.col1 as Table1Col1, table2.Col1 as Table2Col1 from Table1,
> Table2 (...)"
> should give you fields named Table1Col1 and Table2Col1. A bit more trouble
> to write, but you eliminate possible errors.
> Kaisa M. Lindahl
> "RYF" <RYF@.discussions.microsoft.com> wrote in message
> news:BD3D2857-937A-4602-80AD-C472FB295245@.microsoft.com...
> > This is my first stab at this. I have a simple report that returns a few
> > columns from a database no sp no expressions just a select statement.
> > There
> > are two tables that have the same field name and the dataset can't seem to
> > distinguish between them it returns the first field into both columns.
> > When
> > I run it in Query Analyzer it looks fine. What am I missing here?
>
>

Newbie Question

Hi,
I have a procedure that returns 5 different values. I want to run the procedure in another procedure and use those values in that procdure.

How? How? How?

CREATE Procedure get_Averages_EY @.Quality dec OUTPUT, @.Commitment dec OUTPUT,@.Change dec OUTPUT, @.Strategy dec OUTPUT, @.Leadership dec OUTPUT, @.Environment dec OUTPUT, @.id int

I want to use all of the output values in another procedure.

Thanks!!!Try this one.

create procedure proc1(@.id int,@.id2 int output,@.id3 int output)
as
set @.id2=@.id*2
set @.id3=@.id*10
go
create procedure proc2
as
declare @.id2 int,@.id3 int,@.id int
set @.id=3
exec proc1 @.id,@.id2 output,@.id3 output
select @.id,@.id2,@.id3
go
exec proc2

newbie ? about stored procedures

I currently have several sprocs that require a variable which determines
which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
returns fields 4, 5, 6. When I run the sprocs the appropriate fields show up
in the results window but when I refresh the fields, reguardless of what
variable I used it displays X's fields in the fields window. Can I not create
two datasets running the same sproc using different variables. If not is this
so this something that I'll be able to do in the near future? Any help would
be most appreciated.Hey there,
As far as I know Reporting Services scans the Stored Procedure, caches the
field names and uses those.
Why don't you have your stored procedure return generic colum names such as
"Col1", "Col2", "Col3" and then apply some conditional formatting on those
column headers when they're placed on the report. Something like this:
txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
"Postal Code")
I'm pretty sure that this is by design since Reporting Services needs to
know the column names.
Hopefully I'm on the same page as you...
Ben
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> I currently have several sprocs that require a variable which determines
> which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
up
> in the results window but when I refresh the fields, reguardless of what
> variable I used it displays X's fields in the fields window. Can I not
create
> two datasets running the same sproc using different variables. If not is
this
> so this something that I'll be able to do in the near future? Any help
would
> be most appreciated.|||Thanks, Ben but that would mean changing hundreds of sprocs that are being
used by our VB programmers as well. Any other ideas?
"Benjamin Pierce" wrote:
> Hey there,
> As far as I know Reporting Services scans the Stored Procedure, caches the
> field names and uses those.
> Why don't you have your stored procedure return generic colum names such as
> "Col1", "Col2", "Col3" and then apply some conditional formatting on those
> column headers when they're placed on the report. Something like this:
> txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
> "Postal Code")
> I'm pretty sure that this is by design since Reporting Services needs to
> know the column names.
> Hopefully I'm on the same page as you...
>
> Ben
>
> "Arly" <Arly@.discussions.microsoft.com> wrote in message
> news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> > I currently have several sprocs that require a variable which determines
> > which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> > returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
> up
> > in the results window but when I refresh the fields, reguardless of what
> > variable I used it displays X's fields in the fields window. Can I not
> create
> > two datasets running the same sproc using different variables. If not is
> this
> > so this something that I'll be able to do in the near future? Any help
> would
> > be most appreciated.
>
>

newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.