Creating an aggregate function

Hi,
Is there a way to create an aggeregate function in MS SQL?
For example, if you wanted to re-implement fucntion SUM(), how would you do
that?
Any ideas would be highly appreciated!
Thank you,
AndreyThis functionality is planned for the upcoming release of SQL Server 2005.
You can write aggregate functions in a CLR language.
Hope this helps.
Dan Guzman
SQL Server MVP
"MuZZy" <leyandrew@.yahoo.com> wrote in message
news:TJCdnXEc-KWYpKffRVn-pA@.comcast.com...
> Hi,
> Is there a way to create an aggeregate function in MS SQL?
> For example, if you wanted to re-implement fucntion SUM(), how would you
> do that?
> Any ideas would be highly appreciated!
> Thank you,
> Andrey|||You won't be able to do that with SQL Server 2000, unfortunately. Stay
tuned for SQL Server 2005.
On 3/17/05 9:12 PM, in article TJCdnXEc-KWYpKffRVn-pA@.comcast.com, "MuZZy"
<leyandrew@.yahoo.com> wrote:

> Hi,
> Is there a way to create an aggeregate function in MS SQL?
> For example, if you wanted to re-implement fucntion SUM(), how would you d
o
> that?
> Any ideas would be highly appreciated!
> Thank you,
> Andrey|||Andrey,
As others mentioned, no support for creating new custom aggregates, but
there are several techniques that you can implement in SQL Server 2000.
These include Pivoting if the number of elements is small enough, and other
techniques for a large number of elements depending on the specific
calculation you are trying to achieve.
If you have a specific custom aggregate you want to calculate, and would
like to share, maybe we can come up with a solution.
BG, SQL Server MVP
www.SolidQualityLearning.com
"MuZZy" <leyandrew@.yahoo.com> wrote in message
news:TJCdnXEc-KWYpKffRVn-pA@.comcast.com...
> Hi,
> Is there a way to create an aggeregate function in MS SQL?
> For example, if you wanted to re-implement fucntion SUM(), how would you
> do that?
> Any ideas would be highly appreciated!
> Thank you,
> Andrey|||>>For example, if you wanted to re-implement function SUM(), how would
you do that? <<
You can put expressions inside the existing aggregate functions and do
quite a lot. For example, you can google some old posts on how to
write a product aggregate with logs and exponent functions.
If you have a particular one you want to implement, post a spec and we
will see what we can do.

Creating Alert..?

Hi all,
How can I create a alert that can be activated on Sql responses:
If I for example would like to send a email alert to a sales rep if the
revenue is to low.
Example: select custno, revenue from MyTable where revenue<1000
SQL-mail response: "The level is to low! for custno"
/Kent J.
Kent,
1. Custom trigger --> xp_sendmail
2. Scheduled proc --> xp_sendmail
3. User Settable custom counter --> mail response
Also see:
http://www.sqldev.net/xp/xpsmtp.htm
HTH
Jerry
"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:2VT5f.148747$dP1.506595@.newsc.telia.net...
> Hi all,
> How can I create a alert that can be activated on Sql responses:
> If I for example would like to send a email alert to a sales rep if the
> revenue is to low.
> Example: select custno, revenue from MyTable where revenue<1000
> SQL-mail response: "The level is to low! for custno"
> /Kent J.
>
|||Jerry,
I have installed XPSMTP80.ZIP
This is what I have tried:
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'kentjohnson@.test1.com',
@.TO = N'kent.johnson@.test2.se'
select RC = @.rc
This is the result:
Error: connecting to server smarthost
RC
1
Can I send the result from a stored procedure in the mail?
How can I do that?
/Kent J.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:#pvT02b1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Kent,
> 1. Custom trigger --> xp_sendmail
> 2. Scheduled proc --> xp_sendmail
> 3. User Settable custom counter --> mail response
> Also see:
> http://www.sqldev.net/xp/xpsmtp.htm
> HTH
> Jerry
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:2VT5f.148747$dP1.506595@.newsc.telia.net...
>
sql

Creating Alert..?

Hi all,
How can I create a alert that can be activated on Sql responses:
If I for example would like to send a email alert to a sales rep if the
revenue is to low.
Example: select custno, revenue from MyTable where revenue<1000
SQL-mail response: "The level is to low! for custno"
/Kent J.Kent,
1. Custom trigger --> xp_sendmail
2. Scheduled proc --> xp_sendmail
3. User Settable custom counter --> mail response
Also see:
http://www.sqldev.net/xp/xpsmtp.htm
HTH
Jerry
"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:2VT5f.148747$dP1.506595@.newsc.telia.net...
> Hi all,
> How can I create a alert that can be activated on Sql responses:
> If I for example would like to send a email alert to a sales rep if the
> revenue is to low.
> Example: select custno, revenue from MyTable where revenue<1000
> SQL-mail response: "The level is to low! for custno"
> /Kent J.
>|||Jerry,
I have installed XPSMTP80.ZIP
This is what I have tried:
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'kentjohnson@.test1.com',
@.TO = N'kent.johnson@.test2.se'
select RC = @.rc
This is the result:
Error: connecting to server smarthost
RC
--
1
Can I send the result from a stored procedure in the mail?
How can I do that?
/Kent J.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:#pvT02b1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Kent,
> 1. Custom trigger --> xp_sendmail
> 2. Scheduled proc --> xp_sendmail
> 3. User Settable custom counter --> mail response
> Also see:
> http://www.sqldev.net/xp/xpsmtp.htm
> HTH
> Jerry
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:2VT5f.148747$dP1.506595@.newsc.telia.net...
>

Creating Alert..?

Hi all,
How can I create a alert that can be activated on Sql responses:
If I for example would like to send a email alert to a sales rep if the
revenue is to low.
Example: select custno, revenue from MyTable where revenue<1000
SQL-mail response: "The level is to low! for custno"
/Kent J.Kent,
1. Custom trigger --> xp_sendmail
2. Scheduled proc --> xp_sendmail
3. User Settable custom counter --> mail response
Also see:
http://www.sqldev.net/xp/xpsmtp.htm
HTH
Jerry
"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:2VT5f.148747$dP1.506595@.newsc.telia.net...
> Hi all,
> How can I create a alert that can be activated on Sql responses:
> If I for example would like to send a email alert to a sales rep if the
> revenue is to low.
> Example: select custno, revenue from MyTable where revenue<1000
> SQL-mail response: "The level is to low! for custno"
> /Kent J.
>|||Jerry,
I have installed XPSMTP80.ZIP
This is what I have tried:
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'kentjohnson@.test1.com',
@.TO = N'kent.johnson@.test2.se'
select RC = @.rc
This is the result:
Error: connecting to server smarthost
RC
--
1
Can I send the result from a stored procedure in the mail?
How can I do that?
/Kent J.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:#pvT02b1FHA.3336@.TK2MSFTNGP12.phx.gbl...
> Kent,
> 1. Custom trigger --> xp_sendmail
> 2. Scheduled proc --> xp_sendmail
> 3. User Settable custom counter --> mail response
> Also see:
> http://www.sqldev.net/xp/xpsmtp.htm
> HTH
> Jerry
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:2VT5f.148747$dP1.506595@.newsc.telia.net...
> > Hi all,
> >
> > How can I create a alert that can be activated on Sql responses:
> > If I for example would like to send a email alert to a sales rep if the
> > revenue is to low.
> > Example: select custno, revenue from MyTable where revenue<1000
> > SQL-mail response: "The level is to low! for custno"
> >
> > /Kent J.
> >
> >
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
Try:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>
|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>

Creating a trace file with the date appended to its name

I'm trying to create a trace file with the file as part of it's name.
For example, I'd like to create a file called FailedLogins-20050428. So
far I haven't been able to figure out how to get the name of the file
and the date together (I'm sill very new to SQL Server and tracing).

What I've done is:
declare @.rc int
declare @.traceid int
declare @.maxfilesize bigint
set @.maxfilesize = 50
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins', @.maxfilesize=@.maxfilesize,
@.stoptime=NULL
if @.rc > 0 print 'sp_trace_code failed with error code ' +
rtrim(cast(@.rc as char))
else print 'traceid for the trace is ' + rtrim(cast(@.traceid as char))

I can create a trace file on C drive without difficulty. I've tried
creating a file like this:
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins + convert (varchar,getdate(),112',
@.maxfilesize=@.maxfilesize, @.stoptime=NULL

But what I end up created is a file on C called
failedlogins + convert(varchar,getdate(),112).trc

I have no doubt what I want to do can be done. I just done know how to
do it.

If anyone could tell me where I'm going wrong, I'd really appreciate
it.

Thanks in advance.Hiya Bill,

Try this in Query Analyzer.. @.tracefile is your variable,
@.tracefile_new is the proposed fix. Take note of the @.tracefile_new
output..

declare @.tracefile varchar(1000),
@.tracefile_new varchar(1000)

set @.tracefile=N'C:\trace\failedlogins + convert
(varchar,getdate(),112'
set @.tracefile_new=N'C:\trace\failedlogins_' + convert
(varchar,getdate(),112)

print @.tracefile

print @.tracefile_new|||Thanks Greg! It works great. Just the way I wanted it.

creating a stored procedure

hi,
can we use table as an out parameter in sql stored procedure?
if so can any one help me out with an example.
thanks in advance
regards,
ThamaraiThis seem to be what you are locking for: http://www.sommarskog.se/share_data.html

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<thamarai.82@.gmail.com> wrote in message
news:1157544749.151831.33840@.h48g2000cwc.googlegroups.com...
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>|||thamarai.82@.gmail.com wrote:
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>
Look up "CREATE PROCEDURE" in Books Online... You'll find the answer is
no...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Can you please explain a bit more about your requirement? Incase if you want
to return a table as a result set then take a look into
URL from Tibors post.
Thanks
Hari
SQL Server MVP
<thamarai.82@.gmail.com> wrote in message
news:1157544749.151831.33840@.h48g2000cwc.googlegroups.com...
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>|||The answer is No. But even if you could have a table datatype as an OUTPUT
parameter, what would you catch that with in the application? Does the
application provide a table datatype for a variable?
A Stored Procedure and provide a resultset, which the application can
capture as a table in a dataset. (Look up the use of the dataadapter
object.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<thamarai.82@.gmail.com> wrote in message
news:1157544749.151831.33840@.h48g2000cwc.googlegroups.com...
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>

creating a stored procedure

hi,
can we use table as an out parameter in sql stored procedure?
if so can any one help me out with an example.
thanks in advance
regards,
ThamaraiThis seem to be what you are locking for: http://www.sommarskog.se/share_data.html
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<thamarai.82@.gmail.com> wrote in message
news:1157544749.151831.33840@.h48g2000cwc.googlegroups.com...
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>|||thamarai.82@.gmail.com wrote:
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>
Look up "CREATE PROCEDURE" in Books Online... You'll find the answer is
no...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Can you please explain a bit more about your requirement? Incase if you want
to return a table as a result set then take a look into
URL from Tibors post.
Thanks
Hari
SQL Server MVP
<thamarai.82@.gmail.com> wrote in message
news:1157544749.151831.33840@.h48g2000cwc.googlegroups.com...
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>|||The answer is No. But even if you could have a table datatype as an OUTPUT
parameter, what would you catch that with in the application? Does the
application provide a table datatype for a variable?
A Stored Procedure and provide a resultset, which the application can
capture as a table in a dataset. (Look up the use of the dataadapter
object.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<thamarai.82@.gmail.com> wrote in message
news:1157544749.151831.33840@.h48g2000cwc.googlegroups.com...
> hi,
> can we use table as an out parameter in sql stored procedure?
> if so can any one help me out with an example.
> thanks in advance
> regards,
> Thamarai
>

Creating a store procedure with output parameter

Hi all,
Could someone give me an example on how to create and execute the store procedure with using output parameter?
In normal, I create the store procedure without using output parameter, and I did it as follow:
CREATE PROC NewEmployee
(ID int(9), Name Varchar (30), hiredate DateTime, etc...)
AS
BEGIN
//my code
END
GO
When i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc...
For output parameter:
CREATE PROC NewEmployee
(ID int(9), Name Varchar (30), hiredate DateTime,@.message Varchar(40) out)
AS
BEGIN
insert into Employee .....
//if error encountered
set@.message = "Insertion failure"
END
GO
Exec NewEmployee 123456789, 'peter mailler', getDate(),do I need to input something for the output parameter here?

Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?
Many thanks.

Hi,

when calling EXEC newEmployee... you need ti declarare a parameter, assign it to the call and then you'd get the output value from it.

DECLARE @.msg nvarchar(40);
Exec NewEmployee 123456789, 'peter mailler', getDate(),@.msg;

-- Here you could access @.msg to get what was outputted

Creating a Mobile Application with SQL Server Compact Edition

Hello!!

I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.

Can anyone give me a hand?

thanks

This sample is a download only scenario. Are you still running this code in form_Load ?:

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();

// TODO: Delete this line of code.
this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

This sample allows two way sync: http://technet.microsoft.com/en-us/library/ms346580.aspx

and so does this Hands On Lab: http://msdn2.microsoft.com/en-us/library/aa454892.aspx

|||

I will try to make those example.

Thank you very much.

Creating a Mobile Application with SQL Server Compact Edition

Hello!!

I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.

Can anyone give me a hand?

thanks

This sample is a download only scenario. Are you still running this code in form_Load ?:

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();

// TODO: Delete this line of code.
this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

This sample allows two way sync: http://technet.microsoft.com/en-us/library/ms346580.aspx

and so does this Hands On Lab: http://msdn2.microsoft.com/en-us/library/aa454892.aspx

|||

I will try to make those example.

Thank you very much.

Creating a heirarchical output from SQL statement

This may be a basic question, but defining anything other than a cursor
is preffered.

I have, as an example, 2 tables. One with customer data (addresses,
phones, etc), the other is a listing of all 50 states (a cross reference
for short state alias to long state name, i.e. FL - Florida, etc...).

I want to sort the out put by state long name, and show each customer in
the state ... BUT ...
the output needs to be like so:

Florida
ABC,Inc Address1 City, State Zip, other Info
Dummy Corp Address1 City, State Zip, other Info
...
Georgia
XYZ, Inc Address1 City, State Zip, other Info
...
etc ...

This is a basic heirarchical listing. Can this be done with a single
T-SQL statement or are cursors needed?

Thanks in advance.

"Excellence is achieved through 1% inspiration and 99% perspiration." A.
Einstein

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!>> the output needs to be like so: ... <<

That is a report; SQL is not a report writer; you need to do this in
the front end with a report writer. This is the whole idea of
client/server architecture.

Creating a cube...and some ?s

1. Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.

2. Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements:
a. IS NULL-
b. GROUPING-
c. AS-

3. Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.

4. Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows.
a. A CUBE query with Grouping used to distinguish Null values.
b. A CUBE query showing a multidimensional cube.
c. A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.

5. Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.I have no clue how to do this|||Sounds Like Homework to me .

Better gear unp for some lashing from sundialsvcs (http://www.dbforums.com/showthread.php?threadid=979777)

Creating a Calculated Member Based on a Range

Is there a way to create a calculated member based on a range of values?

For example, I have two measures in my cube: ResponseTime and TransactionCount.

What I want to do is create a calculated member that returns the total number of Transactions where the ResponseTime is between a certain range, i.e. between 1 and 2 seconds.

Here is what I've tried to do:

IIF( [Measures].[ResponseTime] > 1 And [Measures].[ResponseTime] < 2, [Measures].[TransactionCount], 0)

However, when I browse the cube, all the values for this calculated member are blank. Any ideas?

This problem can easily can generalized. My main problem is just finding a way to group values based on a certain range of values.

Thanks!!

Joel,

Given that you want to look at the "response time" for each individual transaction in your fact table and the cube is going to show you values aggregated based on selections that the end user makes, you may want to consider the following solution:

In your data source view:

Add a name calculation to your fact table named "ResponseTimeRangeKey" using a CASE statement:

CASE

WHEN ResponseTime <= 1 THEN 1

WHEN ResponseTime > 1 AND ResponseTime < 2 THEN 2

ELSE 3

END CASE

Next you can create a dimension table that can be used to build a cube dimension for your response time ranges using either a database view or a named query with a simple SQL statement something like this:

SELECT

1 AS ResponseTimeRangeKey,

'1 sec or less' AS ResponseTimeRangeName

UNION

SELECT

2 AS ResponseTimeRangeKey,

'1-2 sec' AS ResponseTimeRangeName

UNION

SELECT

3 AS ResponseTimeRangeKey,

'more than 2 sec' AS ResponseTimeRangeName

You can then relate your fact table to the new dimension in the data source view. Next you can create your response time range dimension and add it to your cube.

This solution has the benefit of allowing users to look at any measure in your cube using the response time ranges and will accurately aggregate your values for all dimensions.

HTH,

Steve

|||

Thanks for the help. I tried it out, and it worked brilliantly.

|||

I have somewhat the same scenario, I need to find a way to group values based on certain range of values. I have a calculated member, YTD investment. From my fact table I created a named calculation field for SegmentID with initial value of -1 to relate it with Segment Dimension. The key -1 has "Unknown" as the caption. This SegmentID is my dimension key on the fact table and the key of Segment Dimension. The Segment Dimension has the Min and Max attributes where the minimum and maximum range of values for a particular dimension member is defined.

Is there a way for me to populate or assign values to the named calculation field SegmentID by getting the dimension key of segment dimension where the YTD Investment falls on segment dim's min and max attributes/range?

Thanks in advance,
May Lanie

|||

May,

This is can be tricky depending on the dimensionality of your cube and how you want the YTD to roll up. I have done this type of "dynamic" bucketing before using a separate measure group that only contains the row count for the fact table. You can then use the SCOPE function to allocate values along a pre-defined range of buckets according to the value you want to use for distribution purposes. Here is an example of what I am talking about:

Scope([Measures].[Customer Count by Bucket]);

([Bucket].[Bucket Key].[< 50K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 50000).Count;

([Bucket].[Bucket Key].[50K - 100K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) > 50000 AND

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 100000).Count;

....

End Scope;

The "Measures.[Customer Count by Bucket]" is the fact table row count measure. There is a predefined "Bucket" dimension that has key values indicating a range, but you could just as easily use your "low" and "high" member values. The "Filter" statements are used to count the number of customers that fall into the buckets using "Sales Amt". You would need to substitute your "YTD Amount" calculation.

HTH,

Steve

CreateSubscription Error

I am trying to create an email subscription programmatically, so I first followed the BOL example which worked ok.
Later I tried to replace the fixed ExtensionParams array of the example with a call to the web service method GetExtensionSettings, where, for the desired delivery extension, a parameter array would first be dynamically filled with the required parameter names by the web service and then I would only have to supply the respective parameter values.
After that change, the call to the method CreateSubscription does not work, as it doesnt recognize a SendEmailToUserAlias=True parameter which is now also used for the call.
Why is that?Some properties returned by the GetExtensionSettings method are marked as
read-only and can not be passed back in during CreateSubscription calls.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"vsiat" <vsiat@.discussions.microsoft.com> wrote in message
news:853377AF-B0DC-477A-AD28-9507DC2559D8@.microsoft.com...
> I am trying to create an email subscription programmatically, so I first
followed the BOL example which worked ok.
> Later I tried to replace the fixed ExtensionParams array of the example
with a call to the web service method GetExtensionSettings, where, for the
desired delivery extension, a parameter array would first be dynamically
filled with the required parameter names by the web service and then I would
only have to supply the respective parameter values.
> After that change, the call to the method CreateSubscription does not
work, as it doesnt recognize a SendEmailToUserAlias=True parameter which is
now also used for the call.
> Why is that?
>

CreateDataDrivenSubscription

I try to use .NET to subscribe report which email address is retrived from Access databse. I follow the online example of CreateDataDrivenSubscription and the main difference is the mail list come from Access DB.
The code is as follow:
Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
But when I run the report, the following error is shown:
rsMissingElement
400
The required field DataSourceDefinitionOrReference is missing from the input structure.
What's wrong with my code ?Can you post more of your code? From the looks of it perhaps you are not
setting the Item property on your DataRetrievalPlan to the
DataSourceDefinition that you defined below.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> I try to use .NET to subscribe report which email address is retrived from
Access databse. I follow the online example of CreateDataDrivenSubscription
and the main difference is the mail list come from Access DB.
> The code is as follow:
> Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> But when I run the report, the following error is shown:
> rsMissingElement
> 400
> The required field DataSourceDefinitionOrReference is missing from
the input structure.
> What's wrong with my code ?|||The code are as follows:
Dim delivery As New RptSvc.DataSource
delivery.Name = ""
Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
dataSrcDefinition.CredentialRetrieval = RptSvc.CredentialRetrievalEnum.Store
dataSrcDefinition.Enabled = True
dataSrcDefinition.EnabledSpecified = True
dataSrcDefinition.Extension = "OLEDB"
dataSrcDefinition.ImpersonateUser = False
dataSrcDefinition.ImpersonateUserSpecified = False
dataSrcDefinition.UserName = "admin"
dataSrcDefinition.Password = ""
dataSrcDefinition.Prompt = Nothing
dataSrcDefinition.WindowsCredentials = False
delivery.Item = dataSrcDefinition
' Create the fields list.
Dim fieldsList(1) As RptSvc.Field
fieldsList(0) = New RptSvc.Field
fieldsList(0).Name = "EmailAddress"
fieldsList(0).Alias = "EmailAddress"
fieldsList(1) = New RptSvc.Field
fieldsList(1).Name = "EmplID"
fieldsList(1).Alias = "EmplID"
' Create the data set for the delivery query.
Dim dataSetDefinition As New RptSvc.DataSetDefinition
dataSetDefinition.AccentSensitivitySpecified = False
dataSetDefinition.CaseSensitivitySpecified = False
dataSetDefinition.KanatypeSensitivitySpecified = False
dataSetDefinition.WidthSensitivitySpecified = False
dataSetDefinition.Fields = fieldsList
Dim queryDefinition As New RptSvc.QueryDefinition
queryDefinition.CommandText = "Select * from MailList"
queryDefinition.CommandType = "Text"
queryDefinition.Timeout = 45
queryDefinition.TimeoutSpecified = True
dataSetDefinition.Query = queryDefinition
Dim results As New RptSvc.DataSetDefinition
Dim changed As Boolean
Try
results = rs.PrepareQuery(delivery, dataSetDefinition, changed)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerText.ToString())
End Try
' Set the event type and match data for the delivery.
Dim dataRetrieval As New RptSvc.DataRetrievalPlan
dataRetrieval.DataSet = results
' Set the event type and match data for the delivery.
Dim eventType As String = "TimedSubscription"
Dim matchData As String = "<ScheduleDefinition>" & _
"<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" & _
"<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
"<DaysOfWeek>" & _
"<Thursday>True</Thursday>" & _
"</DaysOfWeek></WeeklyRecurrence>" & _
"</ScheduleDefinition>"
' Set the report parameter values.
Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
empID.ParameterName = "EmplID"
empID.FieldAlias = "EmplID"
parameters(0) = empID
Dim VVD As New RptSvc.ParameterValue
VVD.Name = "VVD"
VVD.Value = "CB1345"
parameters(1) = VVD
Dim OPR As New RptSvc.ParameterValue
OPR.Name = "OPR"
OPR.Value = "*"
parameters(2) = OPR
Dim BillOpr As New RptSvc.ParameterValue
BillOpr.Name = "BILL_COMP"
BillOpr.Value = "*"
parameters(3) = BillOpr
Try
Dim subscriptionID As String = rs.CreateDataDrivenSubscription( _
reportPath, settings, dataRetrieval, sEmaiDesc, eventType, matchData, parameters)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerText.ToString())
End Try
Actually, the code is simliar as the example
Thanks a lot !!!
"Daniel Reib [MSFT]" wrote:
> Can you post more of your code? From the looks of it perhaps you are not
> setting the Item property on your DataRetrievalPlan to the
> DataSourceDefinition that you defined below.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > I try to use .NET to subscribe report which email address is retrived from
> Access databse. I follow the online example of CreateDataDrivenSubscription
> and the main difference is the mail list come from Access DB.
> >
> > The code is as follow:
> > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> >
> > But when I run the report, the following error is shown:
> > rsMissingElement
> > 400
> > The required field DataSourceDefinitionOrReference is missing from
> the input structure.
> >
> > What's wrong with my code ?
>
>|||I find a Microsoft document about the errors of sample code. As you say, I forget to set item property of dataRetrievel plan to dataSurceDefinition.
Thanks for your help !!!
"May Liu" wrote:
> The code are as follows:
> Dim delivery As New RptSvc.DataSource
> delivery.Name = ""
> Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> dataSrcDefinition.CredentialRetrieval = RptSvc.CredentialRetrievalEnum.Store
> dataSrcDefinition.Enabled = True
> dataSrcDefinition.EnabledSpecified = True
> dataSrcDefinition.Extension = "OLEDB"
> dataSrcDefinition.ImpersonateUser = False
> dataSrcDefinition.ImpersonateUserSpecified = False
> dataSrcDefinition.UserName = "admin"
> dataSrcDefinition.Password = ""
> dataSrcDefinition.Prompt = Nothing
> dataSrcDefinition.WindowsCredentials = False
> delivery.Item = dataSrcDefinition
> ' Create the fields list.
> Dim fieldsList(1) As RptSvc.Field
> fieldsList(0) = New RptSvc.Field
> fieldsList(0).Name = "EmailAddress"
> fieldsList(0).Alias = "EmailAddress"
> fieldsList(1) = New RptSvc.Field
> fieldsList(1).Name = "EmplID"
> fieldsList(1).Alias = "EmplID"
> ' Create the data set for the delivery query.
> Dim dataSetDefinition As New RptSvc.DataSetDefinition
> dataSetDefinition.AccentSensitivitySpecified = False
> dataSetDefinition.CaseSensitivitySpecified = False
> dataSetDefinition.KanatypeSensitivitySpecified = False
> dataSetDefinition.WidthSensitivitySpecified = False
> dataSetDefinition.Fields = fieldsList
> Dim queryDefinition As New RptSvc.QueryDefinition
> queryDefinition.CommandText = "Select * from MailList"
> queryDefinition.CommandType = "Text"
> queryDefinition.Timeout = 45
> queryDefinition.TimeoutSpecified = True
> dataSetDefinition.Query = queryDefinition
> Dim results As New RptSvc.DataSetDefinition
> Dim changed As Boolean
> Try
> results = rs.PrepareQuery(delivery, dataSetDefinition, changed)
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerText.ToString())
> End Try
> ' Set the event type and match data for the delivery.
> Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> dataRetrieval.DataSet = results
> ' Set the event type and match data for the delivery.
> Dim eventType As String = "TimedSubscription"
> Dim matchData As String = "<ScheduleDefinition>" & _
> "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" & _
> "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> "<DaysOfWeek>" & _
> "<Thursday>True</Thursday>" & _
> "</DaysOfWeek></WeeklyRecurrence>" & _
> "</ScheduleDefinition>"
> ' Set the report parameter values.
> Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> empID.ParameterName = "EmplID"
> empID.FieldAlias = "EmplID"
> parameters(0) = empID
> Dim VVD As New RptSvc.ParameterValue
> VVD.Name = "VVD"
> VVD.Value = "CB1345"
> parameters(1) = VVD
> Dim OPR As New RptSvc.ParameterValue
> OPR.Name = "OPR"
> OPR.Value = "*"
> parameters(2) = OPR
> Dim BillOpr As New RptSvc.ParameterValue
> BillOpr.Name = "BILL_COMP"
> BillOpr.Value = "*"
> parameters(3) = BillOpr
> Try
> Dim subscriptionID As String = rs.CreateDataDrivenSubscription( _
> reportPath, settings, dataRetrieval, sEmaiDesc, eventType, matchData, parameters)
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerText.ToString())
> End Try
> Actually, the code is simliar as the example
> Thanks a lot !!!
> "Daniel Reib [MSFT]" wrote:
> > Can you post more of your code? From the looks of it perhaps you are not
> > setting the Item property on your DataRetrievalPlan to the
> > DataSourceDefinition that you defined below.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > I try to use .NET to subscribe report which email address is retrived from
> > Access databse. I follow the online example of CreateDataDrivenSubscription
> > and the main difference is the mail list come from Access DB.
> > >
> > > The code is as follow:
> > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > >
> > > But when I run the report, the following error is shown:
> > > rsMissingElement
> > > 400
> > > The required field DataSourceDefinitionOrReference is missing from
> > the input structure.
> > >
> > > What's wrong with my code ?
> >
> >
> >|||In case anyone is interested:
http://support.microsoft.com/?kbid=842854
--
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.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:5D614D3D-B132-48C0-9388-C2ED19F0ABFE@.microsoft.com...
> I find a Microsoft document about the errors of sample code. As you say,
I forget to set item property of dataRetrievel plan to dataSurceDefinition.
> Thanks for your help !!!
> "May Liu" wrote:
> > The code are as follows:
> >
> > Dim delivery As New RptSvc.DataSource
> > delivery.Name = ""
> > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> >
> > dataSrcDefinition.ConnectString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist
Security Info=False"
> > dataSrcDefinition.CredentialRetrieval =RptSvc.CredentialRetrievalEnum.Store
> > dataSrcDefinition.Enabled = True
> > dataSrcDefinition.EnabledSpecified = True
> > dataSrcDefinition.Extension = "OLEDB"
> > dataSrcDefinition.ImpersonateUser = False
> > dataSrcDefinition.ImpersonateUserSpecified = False
> > dataSrcDefinition.UserName = "admin"
> > dataSrcDefinition.Password = ""
> > dataSrcDefinition.Prompt = Nothing
> > dataSrcDefinition.WindowsCredentials = False
> >
> > delivery.Item = dataSrcDefinition
> >
> > ' Create the fields list.
> > Dim fieldsList(1) As RptSvc.Field
> > fieldsList(0) = New RptSvc.Field
> > fieldsList(0).Name = "EmailAddress"
> > fieldsList(0).Alias = "EmailAddress"
> > fieldsList(1) = New RptSvc.Field
> > fieldsList(1).Name = "EmplID"
> > fieldsList(1).Alias = "EmplID"
> >
> > ' Create the data set for the delivery query.
> > Dim dataSetDefinition As New RptSvc.DataSetDefinition
> > dataSetDefinition.AccentSensitivitySpecified = False
> > dataSetDefinition.CaseSensitivitySpecified = False
> > dataSetDefinition.KanatypeSensitivitySpecified = False
> > dataSetDefinition.WidthSensitivitySpecified = False
> > dataSetDefinition.Fields = fieldsList
> >
> > Dim queryDefinition As New RptSvc.QueryDefinition
> > queryDefinition.CommandText = "Select * from MailList"
> > queryDefinition.CommandType = "Text"
> > queryDefinition.Timeout = 45
> > queryDefinition.TimeoutSpecified = True
> > dataSetDefinition.Query = queryDefinition
> > Dim results As New RptSvc.DataSetDefinition
> > Dim changed As Boolean
> >
> > Try
> > results = rs.PrepareQuery(delivery, dataSetDefinition,
changed)
> > Catch e As SoapException
> > Console.WriteLine(e.Detail.InnerText.ToString())
> > End Try
> >
> > ' Set the event type and match data for the delivery.
> > Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> > dataRetrieval.DataSet = results
> >
> > ' Set the event type and match data for the delivery.
> > Dim eventType As String = "TimedSubscription"
> > Dim matchData As String = "<ScheduleDefinition>" & _
> > "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" &
_
> > "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> > "<DaysOfWeek>" & _
> > "<Thursday>True</Thursday>" & _
> > "</DaysOfWeek></WeeklyRecurrence>" & _
> > "</ScheduleDefinition>"
> >
> > ' Set the report parameter values.
> > Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> >
> > Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> > empID.ParameterName = "EmplID"
> > empID.FieldAlias = "EmplID"
> > parameters(0) = empID
> >
> > Dim VVD As New RptSvc.ParameterValue
> > VVD.Name = "VVD"
> > VVD.Value = "CB1345"
> > parameters(1) = VVD
> >
> > Dim OPR As New RptSvc.ParameterValue
> > OPR.Name = "OPR"
> > OPR.Value = "*"
> > parameters(2) = OPR
> >
> > Dim BillOpr As New RptSvc.ParameterValue
> > BillOpr.Name = "BILL_COMP"
> > BillOpr.Value = "*"
> > parameters(3) = BillOpr
> >
> > Try
> > Dim subscriptionID As String =rs.CreateDataDrivenSubscription( _
> > reportPath, settings, dataRetrieval, sEmaiDesc,
eventType, matchData, parameters)
> > Catch e As SoapException
> > Console.WriteLine(e.Detail.InnerText.ToString())
> > End Try
> >
> > Actually, the code is simliar as the example
> >
> > Thanks a lot !!!
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > Can you post more of your code? From the looks of it perhaps you are
not
> > > setting the Item property on your DataRetrievalPlan to the
> > > DataSourceDefinition that you defined below.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > >
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > > I try to use .NET to subscribe report which email address is
retrived from
> > > Access databse. I follow the online example of
CreateDataDrivenSubscription
> > > and the main difference is the mail list come from Access DB.
> > > >
> > > > The code is as follow:
> > > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > > dataSrcDefinition.ConnectString ="Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > > >
> > > > But when I run the report, the following error is shown:
> > > > rsMissingElement
> > > > 400
> > > > The required field DataSourceDefinitionOrReference is
missing from
> > > the input structure.
> > > >
> > > > What's wrong with my code ?
> > >
> > >
> > >|||I have two more question. Where is the physical location of generated file after the report is generated and email to user ? Does reporting service provide UI to let me to open this report ?
"Bryan Keller [MSFT]" wrote:
> In case anyone is interested:
> http://support.microsoft.com/?kbid=842854
> --
> 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.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:5D614D3D-B132-48C0-9388-C2ED19F0ABFE@.microsoft.com...
> > I find a Microsoft document about the errors of sample code. As you say,
> I forget to set item property of dataRetrievel plan to dataSurceDefinition.
> >
> > Thanks for your help !!!
> >
> > "May Liu" wrote:
> >
> > > The code are as follows:
> > >
> > > Dim delivery As New RptSvc.DataSource
> > > delivery.Name = ""
> > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > >
> > > dataSrcDefinition.ConnectString => "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist
> Security Info=False"
> > > dataSrcDefinition.CredentialRetrieval => RptSvc.CredentialRetrievalEnum.Store
> > > dataSrcDefinition.Enabled = True
> > > dataSrcDefinition.EnabledSpecified = True
> > > dataSrcDefinition.Extension = "OLEDB"
> > > dataSrcDefinition.ImpersonateUser = False
> > > dataSrcDefinition.ImpersonateUserSpecified = False
> > > dataSrcDefinition.UserName = "admin"
> > > dataSrcDefinition.Password = ""
> > > dataSrcDefinition.Prompt = Nothing
> > > dataSrcDefinition.WindowsCredentials = False
> > >
> > > delivery.Item = dataSrcDefinition
> > >
> > > ' Create the fields list.
> > > Dim fieldsList(1) As RptSvc.Field
> > > fieldsList(0) = New RptSvc.Field
> > > fieldsList(0).Name = "EmailAddress"
> > > fieldsList(0).Alias = "EmailAddress"
> > > fieldsList(1) = New RptSvc.Field
> > > fieldsList(1).Name = "EmplID"
> > > fieldsList(1).Alias = "EmplID"
> > >
> > > ' Create the data set for the delivery query.
> > > Dim dataSetDefinition As New RptSvc.DataSetDefinition
> > > dataSetDefinition.AccentSensitivitySpecified = False
> > > dataSetDefinition.CaseSensitivitySpecified = False
> > > dataSetDefinition.KanatypeSensitivitySpecified = False
> > > dataSetDefinition.WidthSensitivitySpecified = False
> > > dataSetDefinition.Fields = fieldsList
> > >
> > > Dim queryDefinition As New RptSvc.QueryDefinition
> > > queryDefinition.CommandText = "Select * from MailList"
> > > queryDefinition.CommandType = "Text"
> > > queryDefinition.Timeout = 45
> > > queryDefinition.TimeoutSpecified = True
> > > dataSetDefinition.Query = queryDefinition
> > > Dim results As New RptSvc.DataSetDefinition
> > > Dim changed As Boolean
> > >
> > > Try
> > > results = rs.PrepareQuery(delivery, dataSetDefinition,
> changed)
> > > Catch e As SoapException
> > > Console.WriteLine(e.Detail.InnerText.ToString())
> > > End Try
> > >
> > > ' Set the event type and match data for the delivery.
> > > Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> > > dataRetrieval.DataSet = results
> > >
> > > ' Set the event type and match data for the delivery.
> > > Dim eventType As String = "TimedSubscription"
> > > Dim matchData As String = "<ScheduleDefinition>" & _
> > > "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" &
> _
> > > "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> > > "<DaysOfWeek>" & _
> > > "<Thursday>True</Thursday>" & _
> > > "</DaysOfWeek></WeeklyRecurrence>" & _
> > > "</ScheduleDefinition>"
> > >
> > > ' Set the report parameter values.
> > > Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> > >
> > > Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> > > empID.ParameterName = "EmplID"
> > > empID.FieldAlias = "EmplID"
> > > parameters(0) = empID
> > >
> > > Dim VVD As New RptSvc.ParameterValue
> > > VVD.Name = "VVD"
> > > VVD.Value = "CB1345"
> > > parameters(1) = VVD
> > >
> > > Dim OPR As New RptSvc.ParameterValue
> > > OPR.Name = "OPR"
> > > OPR.Value = "*"
> > > parameters(2) = OPR
> > >
> > > Dim BillOpr As New RptSvc.ParameterValue
> > > BillOpr.Name = "BILL_COMP"
> > > BillOpr.Value = "*"
> > > parameters(3) = BillOpr
> > >
> > > Try
> > > Dim subscriptionID As String => rs.CreateDataDrivenSubscription( _
> > > reportPath, settings, dataRetrieval, sEmaiDesc,
> eventType, matchData, parameters)
> > > Catch e As SoapException
> > > Console.WriteLine(e.Detail.InnerText.ToString())
> > > End Try
> > >
> > > Actually, the code is simliar as the example
> > >
> > > Thanks a lot !!!
> > >
> > > "Daniel Reib [MSFT]" wrote:
> > >
> > > > Can you post more of your code? From the looks of it perhaps you are
> not
> > > > setting the Item property on your DataRetrievalPlan to the
> > > > DataSourceDefinition that you defined below.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > >
> > > >
> > > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > > > I try to use .NET to subscribe report which email address is
> retrived from
> > > > Access databse. I follow the online example of
> CreateDataDrivenSubscription
> > > > and the main difference is the mail list come from Access DB.
> > > > >
> > > > > The code is as follow:
> > > > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > > > dataSrcDefinition.ConnectString => "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > > > >
> > > > > But when I run the report, the following error is shown:
> > > > > rsMissingElement
> > > > > 400
> > > > > The required field DataSourceDefinitionOrReference is
> missing from
> > > > the input structure.
> > > > >
> > > > > What's wrong with my code ?
> > > >
> > > >
> > > >
>
>

Create View Syntax

Why can't one use a three part naming convention when creating a view?

example:

CREATE VIEW databasename.schemaname.anyview

AS

SELECT * FROM anytable

The following error is returned:

Msg 166, Level 15, State 1, Line 1

'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

You are creating the VIEW in the current database, therefore the three part name is not allowed since that would be creating a view in a different database.

You can, however, use the three part name for the table(s) used in the VIEW definition.