can calculation be defined in SQL Server Management Studio?

hi!can somenboby hlep me,i want to know that can calculations be defined in SQL Server Management Studio?And how to do it?Thank you very much!

Calculations are added to a new or existing cube using "Business Intelligence Development Studion" (BIDS). You could generate the script for a cube and then add the calculation by hand, but it is much easier to do using BIDS. If you simply want to create and test a calculation using SQL Server Management Studio you can add a query scoped member using a WITH clause in the MDX editor. Here is a simple example that can be run against Adventure Works:

WITH MEMBER Measures.[Internet Sales x 2]

AS

[Measures].[Internet Sales Amount] * 2

SELECT

{[Measures].[Internet Sales Amount],

[Measures].[Internet Sales x 2] } ON COLUMNS

FROM

[Adventure Works]

HTH,

Steve

|||thank you ,steve.this is a usefulness way.if i just want to creat calculation using SQL Server Management Studio ,like create calculation using BIDS that the calculation can show in measures,does it achieve?|||

If I understand your question correctly the answer is no. The WITH MEMBER calculation is only good for the life of the query. If you want to add a measure to a cube that can be used by others accessing the cube then I would use BIDS.

Steve

|||except using BIDS,does it exist another way to resolve the problem,for example,some script?|||

If your only alternative is SQL Server Management Studio, you can add a calculated member by running an ALTER cube script. In SSMS connect to the AS server where your cube is located. Expand the database and cube folders until you find the cube you want to add a calculated member to. Right click on the cube and then choose:

Script Cube as -> ALTER to -> New Query Editor Window

Then do a search on the script for MdxScripts. You should now see the calculation script for the cube. Edit the script to add in your CREATE MEMBER statement and then run the script. As long as your MDX is error free, you should now be able to see your new calculated member when you access the cube.

HTH,

Steve

Can AS-Actions be used in RS 2005?

Hello,
we defined a drillthrough action in AS to display some detail-information:
If we browse a cube inside Management Studio and do a right click on a
corresponding value, the defined action shows up in the context menu, so we
can display a list with the details. Now we want to have the same thing
inside reporting services: Just click on a value and show the corresponding
details. Is it possible to use the "resultset" of the AS-Action inside a
report? Where can we find detailed information on how to do that?
Regards,
R. Watermannshort answer: no
in RS you have to create your own navigation which is independent from the
cube definition.
RS is a reporting tool not a data navigation tool (like OWC proclarity
etc...).
maybe using some code you can retrieve these actions, but by default RS
can't do this.
"Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
news:OIaULjrGGHA.3624@.TK2MSFTNGP09.phx.gbl...
> Hello,
> we defined a drillthrough action in AS to display some detail-information:
> If we browse a cube inside Management Studio and do a right click on a
> corresponding value, the defined action shows up in the context menu, so
> we can display a list with the details. Now we want to have the same thing
> inside reporting services: Just click on a value and show the
> corresponding details. Is it possible to use the "resultset" of the
> AS-Action inside a report? Where can we find detailed information on how
> to do that?
> Regards,
> R. Watermann
>

Can any body help me for creating user datatype

I am new to sql2005... how can i create user defined datatype containing different types
ex : address with
name as varchar.
id as integeryou can find the details regarding your question here or here
Hope that solves your problem.

Can a trigger determine INSERT/UPDATE context?

(SQL Server 2000, SP3a)
Hello all!
I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it determine
whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
need to have two separate triggers, and then potentially call out to a third SP that has
the "guts" of the current trigger?
Thanks!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_04DA_01C3B8E9.27FD2C60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
If your trigger handles only inserts and updates, then you will have rows in
inserted but not in deleted when there is an insert. However, if there is
an update, then both will be populated:
if @.@.ROWCOUNT = 0
return
if exists (select * from deleted)
begin
-- do the update processing
end
else
begin
-- do the insert processing
end
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
(SQL Server 2000, SP3a)
Hello all!
I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can
it determine
whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
need to have two separate triggers, and then potentially call out to a third
SP that has
the "guts" of the current trigger?
Thanks!
John Peterson
--=_NextPart_000_04DA_01C3B8E9.27FD2C60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If your trigger handles only inserts =and updates, then you will have rows in inserted but not in deleted when there is an insert. However, if there is an update, then both will be populated:
if @.@.ROWCOUNT =3D =0
=return
if exists (select * from deleted)
begin
-- do the =update processing
end
else
begin
-- do the =insert processing
end
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...(SQL Server 2000, SP3a)Hello all!I was wondering if, within a =trigger that's defined FOR INSERT, UPDATE, can it determinewhether the =action that fired the trigger was an INSERT versus an UPDATE? I've got =alittle "extra" logic to do for an UPDATE that I want to avoid with an =INSERT. Or do Ineed to have two separate triggers, and then potentially call =out to a third SP that hasthe "guts" of the current trigger?Thanks!John Peterson

---

--=_NextPart_000_04DA_01C3B8E9.27FD2C60--|||Sure, compare (a) the count(*) from inserted with (b) the count(*) from
deleted.
if (a) = 0 and (b) > 0, it's a delete
if (a) > 0 and (b) = 0, it's an insert
if (a) > 0 and (b) > 0, it's an update
(Not sure if (a)=0 and (b)=0 is possible, but this would mean that the
trigger was fired for nothing, e.g. 0 row(s) affected.)
See http://www.aspfaq.com/2496 for an example of control flow in a trigger,
based on the event.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a
third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>|||Ah, thanks guys -- I hadn't considered using those tables! I was trying to fiddle with
the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an INSERT is invoked,
that function will return TRUE. However, I'm getting a syntax error when I try to use it
like that:
...
if ((not update(MySpecificCol)) or (columns_updated())) begin
...
end
...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0510_01C3B8EB.34932630
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
The columns_updated() function requires an argument.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
Ah, thanks guys -- I hadn't considered using those tables! I was trying to
fiddle with
the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is invoked,
that function will return TRUE. However, I'm getting a syntax error when I
try to use it
like that:
...
if ((not update(MySpecificCol)) or (columns_updated())) begin
...
end
...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a
third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>
--=_NextPart_000_0510_01C3B8EB.34932630
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The columns_updated() function =requires an argument.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle withthe COLUMNS_UPDATED() function, as BOL seemed to indicate =that when an INSERT is invoked,that function will return TRUE. =However, I'm getting a syntax error when I try to use itlike =that:...if ((not update(MySpecificCol)) or (columns_updated())) begin ...end..."John Peterson" wrote in =messagenews:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> (SQL Server 2000, SP3a)>> Hello all!>> I was =wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it =determine> whether the action that fired the trigger was an =INSERT versus an UPDATE? I've got a> little "extra" logic to do for an =UPDATE that I want to avoid with an INSERT. Or do I> need to have =two separate triggers, and then potentially call out to a third SP that =has> the "guts" of the current trigger?>> =Thanks!>> John Peterson>>

---

--=_NextPart_000_0510_01C3B8EB.34932630--|||I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error when I try to use
it
> like that:
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
What's wrong with the method I posted?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions
because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context.
Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context?
I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying
to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error when
I try to use
it
> like that:
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it
determine
> > whether the action that fired the trigger was an INSERT versus an
UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a
third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

What's wrong with the method I posted?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get that =function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I wonder ifthere's a quick/easy way to determine that?"John =Peterson" wrote =in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with> the COLUMNS_UPDATED() function, as BOL seemed to =indicate that when an INSERT isinvoked,> that function will return =TRUE. However, I'm getting a syntax error when I try to useit> like =that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) begin> ...> =end> ...>>> "John Peterson" wrote in message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's defined =FOR INSERT, UPDATE, can itdetermine> > whether the action that =fired the trigger was an INSERT versus an UPDATE? I've got a> => little "extra" logic to do for an UPDATE that I want to avoid with an =INSERT. Or do I> > need to have two separate triggers, and then =potentially call out to a third SP thathas> > the "guts" of the current trigger?> >> > Thanks!> >> > =John Peterson> >> >>>

---

--=_NextPart_000_0537_01C3B8EC.A1EC5AC0--|||This is a multi-part message in MIME format.
--=_NextPart_000_00D6_01C3B8DC.C739DD80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom! There's nothing wrong with the method that you posted -- I =guess I was just using this as an exercise to learn more about the =COLUMNS_UPDATED() function, and whether using that might be more =performant. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT =actions because the
columns have either explicit values or implicit (NULL) values =inserted.
</Quote>
But, as I say, I can't quite get that function to work in this =context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT =context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when =an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error =when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, =UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an =UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with =an INSERT. Or do I
> > need to have two separate triggers, and then potentially call out =to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_00D6_01C3B8DC.C739DD80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an exercise =to learn more about the COLUMNS_UPDATED() function, and whether using that might =be more performant. :-)
"Tom Moreau" = wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with> the COLUMNS_UPDATED() function, as BOL seemed =to indicate that when an INSERT isinvoked,> that function will =return TRUE. However, I'm getting a syntax error when I try to useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) =begin> ...> end> ...>>> "John Peterson" = wrote in message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's defined =FOR INSERT, UPDATE, can itdetermine> > whether the action =that fired the trigger was an INSERT versus an UPDATE? I've got a> => little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I> > need to have two separate triggers, =and then potentially call out to a third SP thathas> > the ="guts" of the current trigger?> >> > Thanks!> =>> > John Peterson> >> >>>

---

--=_NextPart_000_00D6_01C3B8DC.C739DD80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0579_01C3B8EE.E25FEB10
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the
ordinal position of various columns, then your trigger code will have to
change. Often, developers forget to update the trigger when that happens.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uONX2dRuDHA.2448@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing wrong with the method that you posted -- I
guess I was just using this as an exercise to learn more about the
COLUMNS_UPDATED() function, and whether using that might be more performant.
:-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT
actions because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context.
Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context?
I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying
to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error
when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT,
UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an
UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a
third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_0579_01C3B8EE.E25FEB10
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the ordinal position of =various columns, then your trigger code will have to change. Often, =developers forget to update the trigger when that happens.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:uONX2dRuDHA.2448=@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an exercise =to learn more about the COLUMNS_UPDATED() function, and whether using that might =be more performant. :-)
"Tom Moreau" = wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with> the COLUMNS_UPDATED() function, as BOL seemed =to indicate that when an INSERT isinvoked,> that function will =return TRUE. However, I'm getting a syntax error when I try to useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) =begin> ...> end> ...>>> "John Peterson" = wrote in message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's defined =FOR INSERT, UPDATE, can itdetermine> > whether the action =that fired the trigger was an INSERT versus an UPDATE? I've got a> => little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I> > need to have two separate triggers, =and then potentially call out to a third SP thathas> > the ="guts" of the current trigger?> >> > Thanks!> =>> > John Peterson> >> >>>

---

--=_NextPart_000_0579_01C3B8EE.E25FEB10--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Understood -- I had hoped there would be a quick and easy mechanism to =essentially create a bitmask that was representative of all the columns =in the table (without regard to the position of the columns). Then, =simply compare this value with the COLUMNS_UPDATED() value. But, I =think something like that would be far more onerous than your (and =Aaron's) recommendation. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebVwVjRuDHA.2308@.TK2MSFTNGP09.phx.gbl...
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change =the ordinal position of various columns, then your trigger code will =have to change. Often, developers forget to update the trigger when =that happens.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uONX2dRuDHA.2448@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing wrong with the method that you posted -- =I guess I was just using this as an exercise to learn more about the =COLUMNS_UPDATED() function, and whether using that might be more =performant. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT =actions because the
columns have either explicit values or implicit (NULL) values =inserted.
</Quote>
But, as I say, I can't quite get that function to work in this =context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT =context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that =when an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax =error when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, =UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus =an UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid =with an INSERT. Or do I
> > need to have two separate triggers, and then potentially call =out to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Understood -- I had hoped there =would be a quick and easy mechanism to essentially create a bitmask that was =representative of all the columns in the table (without regard to the position of the columns). Then, simply compare this value with the =COLUMNS_UPDATED() value. But, I think something like that would be far more onerous =than your (and Aaron's) recommendation. :-)
"Tom Moreau" = wrote in message news:ebVwVjRuDHA.2308=@.TK2MSFTNGP09.phx.gbl...
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the ordinal position =of various columns, then your trigger code will have to change. =Often, developers forget to update the trigger when that =happens.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:uONX2dRuDHA.2448=@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an =exercise to learn more about the COLUMNS_UPDATED() function, and whether using =that might be more performant. :-)
"Tom Moreau" = wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because = thecolumns have either explicit values or implicit (NULL) values = inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I =was trying to fiddle with> the COLUMNS_UPDATED() function, as BOL =seemed to indicate that when an INSERT isinvoked,> that function =will return TRUE. However, I'm getting a syntax error when I try to = useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) begin> ...> end> ...>>> "John Peterson" wrote in = message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's =defined FOR INSERT, UPDATE, can itdetermine> > whether the action =that fired the trigger was an INSERT versus an UPDATE? I've got =a> > little "extra" logic to do for an UPDATE that I want to avoid =with an INSERT. Or do I> > need to have two separate =triggers, and then potentially call out to a third SP thathas> > the ="guts" of the current trigger?> >> > Thanks!> >> > John Peterson> >> >>>

---

--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0--

can a query be written to do this...

I have the following table defined: Not my design/idea and I can't change
it)
CREATE TABLE [dbo].[Task] (
[TaskID] [ROWIDENTIFIER] NOT NULL ,
[Name] [SHORTNAME] NULL ,
[Description] [SHORTDESCRIPTION] NULL ,
[PreviousTaskID] [ROWIDENTIFIER] NULL ,
[NextTaskID] [ROWIDENTIFIER] NULL ,
[ProcedureID] [ROWIDENTIFIER] NOT NULL ,
[IsActive] [WFBOOL] NULL
) ON [PRIMARY]
PreviousTaskID and NextTaskID form, what amounts to a linked list where
PreviousTaskID points to the TaskID that comes before the current task and
NextTaskID points to the TaskID of the task that follows the current task.
A PreviousTaskID equal to null signifies the first task in a list and a
NextTaskID equal to null signifies it is the last task in the list.
With all of that in mind: Is there any way to write a query that will return
a single set of rows ordered from first to last?
TIA
Brian WBW -
It seems unnecessary to have a Next and Previous so long as the chain is
always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways, here'
s
something that should get you started:
create table #Task (
TaskID int not null
, [Name] varchar(50) null
, PreviousTaskID int null
, NextTaskID int null
)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
'T1', null, 2)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
'T1', 1, 3)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
'T1', 2, 4)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
'T1', 3, 5)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
'T1', 4, 6)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
'T1', 5, null)
declare @.parent_level int
set @.parent_level = 0
declare @.hierarchy table (parent int, item int, [level] int)
insert into @.hierarchy (parent, item, [level])
select null, taskid, 0
from #Task
where previoustaskid is null
while 1 = 1
begin
insert into @.hierarchy(parent, item, [level])
select nexttaskid, taskid, @.parent_level + 1
from #Task
where previoustaskid in (select item from @.hierarchy where [level] =
@.parent_level)
if @.@.rowcount = 0
break
set @.parent_level = @.parent_level + 1
end
select t.TaskID, h.[level] as Ordering
from #Task t
join @.hierarchy h on t.TaskID = h.item
order by 2 asc|||Perfect!
muchos gracias!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:C3096324-4209-4868-80AB-FA4FA7DB9B97@.microsoft.com...
> BW -
> It seems unnecessary to have a Next and Previous so long as the chain is
> always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways,
here's
> something that should get you started:
> create table #Task (
> TaskID int not null
> , [Name] varchar(50) null
> , PreviousTaskID int null
> , NextTaskID int null
> )
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
> 'T1', null, 2)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
> 'T1', 1, 3)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
> 'T1', 2, 4)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
> 'T1', 3, 5)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
> 'T1', 4, 6)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
> 'T1', 5, null)
> declare @.parent_level int
> set @.parent_level = 0
> declare @.hierarchy table (parent int, item int, [level] int)
> insert into @.hierarchy (parent, item, [level])
> select null, taskid, 0
> from #Task
> where previoustaskid is null
> while 1 = 1
> begin
> insert into @.hierarchy(parent, item, [level])
> select nexttaskid, taskid, @.parent_level + 1
> from #Task
> where previoustaskid in (select item from @.hierarchy where [level] =
> @.parent_level)
> if @.@.rowcount = 0
> break
> set @.parent_level = @.parent_level + 1
> end
> select t.TaskID, h.[level] as Ordering
> from #Task t
> join @.hierarchy h on t.TaskID = h.item
> order by 2 asc
>

Calulated Fields vs UDF's

Does anyone have any suggestions on the use of Calculated fields over scalar
User Defined functions? I would think that the calulated field may be faster
than a scalar UDF but I don't know much about the mechanisms SQL uses to run
each method. Also, where might Table-valued UDF's possibly fit into this
scenario?
thank you for your time.Scalar UDFs (in SQL Server 2000) can be fairly slow, depending on the
implementation. If the same code can be placed in-line in a Select, for
example, the select will run faster than the version calling the UDF. Often
much, much faster.
I have not done a performance comparison, but with that in mind I would
expect a calculated field to run faster than a scalar UDF.
Table-valued UDFs return tables, so they are not a good fit for
high-performance operations on a column. The performance of table-valued
UDFs can be summed up this way:
In-line table valued UDF = View with parameters, therefore the UDF is
'compiled into' the plan if used in a join.
Multi-statement table valued UDF = Stored Procedure that returns a table.
If included in a join, it executes first and returns a result set.
RLF
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:44F255E8-8DE8-4449-9104-C3463EEFD3F4@.microsoft.com...
> Does anyone have any suggestions on the use of Calculated fields over
> scalar
> User Defined functions? I would think that the calulated field may be
> faster
> than a scalar UDF but I don't know much about the mechanisms SQL uses to
> run
> each method. Also, where might Table-valued UDF's possibly fit into this
> scenario?
>
> thank you for your time.|||Scalar UDFs are slow when they must do a lookup (i.e., they contain a
SELECT statement). When this happens it serializes your reads such that
your queries that contain the scalar UDF behave like cursor operations.
If your UDF simply performs a calculation without any lookups, the
performance should be similar to computed columns. I think you can
create indexes on computed columns though, but I'm not totally certain.
-Alan|||Thank you RLF and Alan for your explanations. My column is indeed a general
computation on other columns in the table so it sounds like they may be
similar in performance between both methods. The one advantage of using a
scalar UDF that I have figured out is that if I have multiple tables using
this similar computation, then I simply have to make the change in one place
to effect both table queries. I suppose I could simply call the UDF in the
calculated field as well to centralize the definition.
I typically would have created a veiw with a call tothe UDF with in it and
on top of the base table columns but I wanted to careful not to spread my
data access out all over the place for columns that might naturally seem to
be contained in the base table.
Maybe I will do a little playing with this today and post some varied
results using these different approaches. It might be interesting.
"Alan Samet" wrote:

> Scalar UDFs are slow when they must do a lookup (i.e., they contain a
> SELECT statement). When this happens it serializes your reads such that
> your queries that contain the scalar UDF behave like cursor operations.
> If your UDF simply performs a calculation without any lookups, the
> performance should be similar to computed columns. I think you can
> create indexes on computed columns though, but I'm not totally certain.
>
> -Alan
>|||A few more details.
SQL Server's Query Optimizer does not understand the output distributions of
UDFs. As such, it can cause cardinality estimates in query plan generation
to be sub-optimal.
(Eventually, we may be able to improve this story in a future release)
What I tell customers now - if you can write it as scalar logic, please do
so. If you need to use a UDF, please consider a computed column over the
result of this as well so that the optimzier can create the statistics it
needs to do a good job in plan generation.
I would specifically *not* recommend the use of UDFs to perform "singleton
(index) lookups" into another table. I've seen this pattern used in a
number of deployments. While it "works", it is very hard for the query
optimizer to pick a proper join order (which means that sometimes it picks a
poor order and plan performance can become very slow). If you can represent
these as joins, I recommend that you do so.
Best of luck to you,
Conor Cunningham
SQL Server Query Optimization Development Lead
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:0940C18A-9E28-4F12-B926-95B00B336D42@.microsoft.com...
> Thank you RLF and Alan for your explanations. My column is indeed a
> general
> computation on other columns in the table so it sounds like they may be
> similar in performance between both methods. The one advantage of using a
> scalar UDF that I have figured out is that if I have multiple tables using
> this similar computation, then I simply have to make the change in one
> place
> to effect both table queries. I suppose I could simply call the UDF in the
> calculated field as well to centralize the definition.
> I typically would have created a veiw with a call tothe UDF with in it and
> on top of the base table columns but I wanted to careful not to spread my
> data access out all over the place for columns that might naturally seem
> to
> be contained in the base table.
> Maybe I will do a little playing with this today and post some varied
> results using these different approaches. It might be interesting.
> "Alan Samet" wrote:
>

Calling Webservice

Hi Scott,
Thanks for the input, but this user has been made a member of all of
the roles defined within Reporting services. i.e "Browser",
"Publisher","My Reports", "Content Manager". I've tried all
combinations of these. No joy. The user has to be a member of the
"Administrators Group" on the machine itself (it appears?).
Scott Allen wrote:
> Hi Amy:
>
> Reporting services uses roles based authorization, and the default
> setup only places the local administrator into a role.
>
> While RSAdminUser might have rights to access all the files and
> folders in the reporting services installation, you still have to put
> the user into a "role". Roles are how RS determines who can browse a
> folder, who can view a report, who can manage a data source etc.
>
> From your reports home page you can go to Site Settings -> Configure
> site wide security (link is at the bottom). Perhaps you should define
> a new role for RSAdminUser and grant just enough authorization for
the
> user to get the things done you need.
>
> Hope this helps,
>
> --
> Scott
> http://www.OdeToCode.com
>
> On 3 Aug 2004 05:04:48 -0700, amydudley@.webmail.co.za (Amy) wrote:
>
> >Hi,
> >
> >I have set the Reporting WebServices WebService Directory Security
in
> >IIS to Windows Authentication. The authentication setting in the
> >config file is set to "Windows" with Impersonation set to "true".
> >
> >I created an account called "RSAdminUser" with which to call the
> >webservice.
> >When calling the "ListReportsUsingDataSource" method, after setting
> >the credentials to this "RSAdminUser", I get the following error:
> >
> >"System.Web.Services.Protocols.SoapException: The permissions
granted
> >to user 'SERVER\RSAdminUser' are insufficient for performing this
> >operation. -->
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> >The permissions granted to user 'SERVER\RSAdminUser' are
insufficient
> >for performing this operation. at
>
>Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(String
> >path) at
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
> >DataSource, CatalogItem[]& Reports) -- End of inner exception stack
> >trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataSource(String
> >DataSource, CatalogItem[]& Reports)"
> >
> >I have methodically given this user permissions to all of the
relevant
> >folders and files that I can think of, but the only thing that makes
> >this work is making "RSAdminUser" a member of the "Administrators"
> >group. Is it possible that only members of the "Administrators"
group
> >are allowed to execute this method?
> >
> >Is there an alternative solution? What permissions does this
account
> >need to execute this method?
> >
> >Thanks in advance,
> >
> >AmyWhat is the path of the data source in the namespace. Is it nested several
folders deep and have you given the user rights to all items in the path to
the data source?
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"ALD" <amydudley@.webmail.co.za> wrote in message
news:cesoec$hlm@.odak26.prod.google.com...
> Hi Scott,
> Thanks for the input, but this user has been made a member of all of
> the roles defined within Reporting services. i.e "Browser",
> "Publisher","My Reports", "Content Manager". I've tried all
> combinations of these. No joy. The user has to be a member of the
> "Administrators Group" on the machine itself (it appears?).
> Scott Allen wrote:
> > Hi Amy:
> >
> > Reporting services uses roles based authorization, and the default
> > setup only places the local administrator into a role.
> >
> > While RSAdminUser might have rights to access all the files and
> > folders in the reporting services installation, you still have to put
> > the user into a "role". Roles are how RS determines who can browse a
> > folder, who can view a report, who can manage a data source etc.
> >
> > From your reports home page you can go to Site Settings -> Configure
> > site wide security (link is at the bottom). Perhaps you should define
> > a new role for RSAdminUser and grant just enough authorization for
> the
> > user to get the things done you need.
> >
> > Hope this helps,
> >
> > --
> > Scott
> > http://www.OdeToCode.com
> >
> > On 3 Aug 2004 05:04:48 -0700, amydudley@.webmail.co.za (Amy) wrote:
> >
> > >Hi,
> > >
> > >I have set the Reporting WebServices WebService Directory Security
> in
> > >IIS to Windows Authentication. The authentication setting in the
> > >config file is set to "Windows" with Impersonation set to "true".
> > >
> > >I created an account called "RSAdminUser" with which to call the
> > >webservice.
> > >When calling the "ListReportsUsingDataSource" method, after setting
> > >the credentials to this "RSAdminUser", I get the following error:
> > >
> > >"System.Web.Services.Protocols.SoapException: The permissions
> granted
> > >to user 'SERVER\RSAdminUser' are insufficient for performing this
> > >operation. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> > >The permissions granted to user 'SERVER\RSAdminUser' are
> insufficient
> > >for performing this operation. at
> >
>Microsoft.ReportingServices.Library.RSService.ListReportsUsingDataSource(St
ring
> > >path) at
>
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataS
ource(String
> > >DataSource, CatalogItem[]& Reports) -- End of inner exception stack
> > >trace -- at
>
Microsoft.ReportingServices.WebServer.ReportingService.ListReportsUsingDataS
ource(String
> > >DataSource, CatalogItem[]& Reports)"
> > >
> > >I have methodically given this user permissions to all of the
> relevant
> > >folders and files that I can think of, but the only thing that makes
> > >this work is making "RSAdminUser" a member of the "Administrators"
> > >group. Is it possible that only members of the "Administrators"
> group
> > >are allowed to execute this method?
> > >
> > >Is there an alternative solution? What permissions does this
> account
> > >need to execute this method?
> > >
> > >Thanks in advance,
> > >
> > >Amy
>

Calling User Defined Function in VC++ using SQLDMO

Hi all,
I had a user defined function by name dbo.GetContact (dbo is
owner),the function contains a select statement and returns a value and
i am using this function in a View. when i calling this function in
Query Analyzer it is working fine... but when i call this i use View
programtically,it is telling that "InValid Object dbo.GetContact".
View :
select dbo.getcontact('Address', '_332', 'Email')
Can anyone provide me the solution...
Looking forward for ur reply..
Thanz in advance...
Regards,
PrinceHi
Are you calling it in the view the same way or as part of a select statement
against a table?
Please post DDL and DML.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Prince" wrote:

> Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
> owner),the function contains a select statement and returns a value and
> i am using this function in a View. when i calling this function in
> Query Analyzer it is working fine... but when i call this i use View
> programtically,it is telling that "InValid Object dbo.GetContact".
> View :
> select dbo.getcontact('Address', '_332', 'Email')
> Can anyone provide me the solution...
> Looking forward for ur reply..
> Thanz in advance...
> Regards,
> Prince
>|||hai mike..
i am calling it in a view like this :
pDatabase->ExecuteWithResults("that view",&pResults))
Mike Epprecht (SQL MVP) wrote:
> Hi
> Are you calling it in the view the same way or as part of a select stateme
nt
> against a table?
> Please post DDL and DML.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Prince" wrote:
>|||On 23 Aug 2005 02:48:47 -0700, Prince wrote:

>Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
>owner),the function contains a select statement and returns a value and
>i am using this function in a View. when i calling this function in
>Query Analyzer it is working fine... but when i call this i use View
>programtically,it is telling that "InValid Object dbo.GetContact".
>View :
> select dbo.getcontact('Address', '_332', 'Email')
Hi Prince,
Try:
SELECT Column1, Column2, ...
FROM dbo.getcontact('Address', '_332', 'Email')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Prince, Please try this Visual C++ code. It runs okay for me;
_ConnectionPtr pConn = NULL;
_bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=Enterprise;Data Source=(local)");
HRESULT hr = S_OK;
CoInitialize(NULL);
try
{
hr = pConn.CreateInstance((__uuidof(Connection)));
if (FAILED(hr))
{
cout << "Error instantiating Connection object" << endl;
CoUninitialize();
return 0;
}
//Open the SQL Server connection
hr = pConn->Open(strCon,"","",0);
if (FAILED(hr))
{
cout << "Error opening Database Object using ADO _ConnectionPtr" << endl;
CoUninitialize();
return 0;
}
}
catch (_com_error& ce)
{
cout << "Error= " << ce.ErrorInfo << endl;
}
_RecordsetPtr pRst = NULL;
pRst.CreateInstance(__uuidof(Recordset));
_bstr_t strSQL("select dbo.DBCreationDate('Enterprise')");
VARIANT* ptr = NULL;
IADORecordBinding *picRs = NULL; // Interface Pointer declared
CCustomRs rs;
try {
pRst->Open(strSQL, variant_t((IDispatch *)pConn,true), adOpenStatic,
adLockOptimistic, adCmdText);
if(SUCCEEDED(pRst-> QueryInterface(__uuidof(IADORecordBindin
g), (void
**)&picRs)))
{
TESTHR(picRs->BindToRecordset(&rs));
}
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
// Print Com errors.
cout << "Error" <<endl;
cout << "Code = " << e.Error();
cout << "Code meaning = " <<
e.ErrorMessage();
cout << "Source = " << (LPCSTR) bstrSource;
cout << "Description = " << (LPCSTR)
bstrDescription;
}
Please email me at frankchang91@.gmail.com if you have any questions.
"Prince" wrote:

> Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
> owner),the function contains a select statement and returns a value and
> i am using this function in a View. when i calling this function in
> Query Analyzer it is working fine... but when i call this i use View
> programtically,it is telling that "InValid Object dbo.GetContact".
> View :
> select dbo.getcontact('Address', '_332', 'Email')
> Can anyone provide me the solution...
> Looking forward for ur reply..
> Thanz in advance...
> Regards,
> Prince
>|||Prince, I use your user defined function in a view. When i execute the view
programmatically using the SQLOLEDB COM object , it still runs okay. Do you
have to use the SQLDMO COM object or can you switch to the SQLOLEDB COM
object? Thank you.
"frank chang" wrote:
> Prince, Please try this Visual C++ code. It runs okay for me;
> _ConnectionPtr pConn = NULL;
> _bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=Enterprise;Data Source=(local)");
> HRESULT hr = S_OK;
> CoInitialize(NULL);
> try
> {
> hr = pConn.CreateInstance((__uuidof(Connection)));
> if (FAILED(hr))
> {
> cout << "Error instantiating Connection object" << endl;
> CoUninitialize();
> return 0;
> }
> //Open the SQL Server connection
> hr = pConn->Open(strCon,"","",0);
> if (FAILED(hr))
> {
> cout << "Error opening Database Object using ADO _ConnectionPtr" << end
l;
> CoUninitialize();
> return 0;
> }
> }
> catch (_com_error& ce)
> {
> cout << "Error= " << ce.ErrorInfo << endl;
> }
>
> _RecordsetPtr pRst = NULL;
> pRst.CreateInstance(__uuidof(Recordset));
>
> _bstr_t strSQL("select dbo.DBCreationDate('Enterprise')");
> VARIANT* ptr = NULL;
> IADORecordBinding *picRs = NULL; // Interface Pointer declared
> CCustomRs rs;
> try {
> pRst->Open(strSQL, variant_t((IDispatch *)pConn,true), adOpenStatic,
> adLockOptimistic, adCmdText);
> if(SUCCEEDED(pRst-> QueryInterface(__uuidof(IADORecordBindin
g), (void
> **)&picRs)))
> {
> TESTHR(picRs->BindToRecordset(&rs));
> }
> }
> catch(_com_error &e)
> {
> _bstr_t bstrSource(e.Source());
> _bstr_t bstrDescription(e.Description());
> // Print Com errors.
> cout << "Error" <<endl;
> cout << "Code = " << e.Error();
> cout << "Code meaning = " <<
> e.ErrorMessage();
> cout << "Source = " << (LPCSTR) bstrSource;
> cout << "Description = " << (LPCSTR)
> bstrDescription;
> }
> Please email me at frankchang91@.gmail.com if you have any questions.
> "Prince" wrote:
>

Calling user defined function from stored procedure

When i call user defined function from stored procedure, i am getting an error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.

why this error is happening?.

i am calling this way... dbo.MyFunction(param1,param2)

Can you give the full statement? This still does not look like enough information.

|||My first guess would be that your funciton is not residing in the same database as the stored procedure.

Calling user defined function from other server

I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000

Call to the following function returns an error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'srv2000'.

select [srv2000].db_test.dbo.F_TEST()

You cannot call remote UDFs in SQL Server right now. So you will have to use pass-through query using OPENQUERY to call the UDF or EXECUTE AT in SQL Server 2005.

-- SQL Server 2000/2005

select i from openquery([srv2000], 'select db_test.dbo.F_TEST() as i')

-- SQL Server 2005

exec('select db_test.dbo.F_TEST() as i') at [srv2000]

-- Or

declare @.i int
exec('select ? = db_test.dbo.F_TEST() ', @.i output) at [srv2000]
select @.i

|||Thanks for help!

Calling the user defined function!

hai,

the problem is - I have created a userdefined function using SQL 2000

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)

return @.getfulldate
end

and normally we call this in the SQL statements as

select *, dbo.getfulldate('2006-05-03') from emp

This works fine and what I need was, I need to invoke the user-defined function like

select *, getfulldate('2006-05-03') from emp that is, without using "dbo".

If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.

So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.

anyone guide me, thanks!

Hi,

User defined functions can not be called with schema's reference. You must include schema "dbo." reference on each function calls.

|||

however we are able to call without the reference of dbo, functions that return table except scalar values...that is Why?...so there must be some thing to known as how to call without dbo reference. also visit

http://microsoft.apress.com/asptodayarchive/73823/sql-user-defined-functions

waiting for reply

|||

Hi anandh2007,

You are right. We can call a table-value functin without schema name while we must specify the schema name when calling a scalar value function.

This problem is by design (something relating the sql-server compiler),see the online book, I've bolderd the important part

Function Invocation

Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement.

Scalar-valued functions must be invoked by using at least the two-part name of the function.

For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). Table-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. For more information, see Executing User-defined Functions (Database Engine).

Hope my suggestion helps