Combining Text and value in Trigger

Hi,

I have written a trigger that emails a specified person.

I am trying to include a body of the email which comprises of the stock level and a warning. Pulling out my hair...Help

Code so far in the trigger is :

CREATE TRIGGER Warnings ON [dbo].[tbl_sql_cartridges_kh]
for update
AS
declare @.SL as int
declare @.SS as int
declare @.Msg as nvarchar(100)
set @.SL= (select stock_level from inserted)
set @.SS =(select cartridge_key from inserted)
set @.Msg = 'Print Cartridges Level Warning'
if @.SL < 3
begin

exec sp_send_cdontsmail 'Print-Cartridges','XXX@.XXXX.co.uk','Print Cartridges Level Warning',@.Msg
end

I would like the @.Msg to say something like Cartridge XXX stock level is YYY, where XXX and YYY are taken from the table after update. I can get the values, but cant put them in the MSG string..

Like @.msg & @.SL (SL being Stock Level)

Many Thanks

KenFirst problem you have is that you are treating the virtual tables as if they have only 1 row...inserted may have n rows, so

set @.SL= (select stock_level from inserted)

Would only return the last results...

Second, sending emails from a trigger is very messy. Why not just do it from a stored procedure? If all the code is isolated to sproc calls then you're golden. If you allow dynamic sql from code, then it's a problem...

As for the email, we a notus lotes so we're hosed here...|||This calls a stored procedure.

The trigger will only ever have 1 row as this Sql dbase has adreamweaver front end that only lets a singke line be updated.

I can grab any items that have been updated, I just cant combine them.

I have made sure all constraints are working..

It actually tells you @.SS will be cartridge HP045a for example and @.SL could 1.

I need the @.msg to say something like Cartridge HP045a stock level is now 1.

The Cdonts procedure is effective and uses SMTP and works well..

Combine two or more reports into one PDF Report

We are building a "Annual Statement" which will include a series or Reports
that will be bound into a Booklet. We will need a table of Contents (With
Page Numbers) and also need the pages to be continuous throughout.
Our question is what is the best approach to build this dynamically in
Report Server?
Build One "Master Report which includes Sub-Reports or Build the Sub Reports
and pass them into the Master?
We have a middle "Business Logic" Tier that we will be driving the creation
of these reoprts from.
Thanks!
...BryanUpdate:
I am thinking that we can programattically create a "Master RDL" that
includes the Sub Reports.
Question: would we be able to control the processing of the sub reports so
that they generate first then generate the Master Report genterating the
Table of Contents after the Sub Reports have been created (and we know the
sub Report Page Counts)
Any Suggestions would be really appreachiated
Thanks!
...Bryan
"Bryan E." wrote:
> We are building a "Annual Statement" which will include a series or Reports
> that will be bound into a Booklet. We will need a table of Contents (With
> Page Numbers) and also need the pages to be continuous throughout.
> Our question is what is the best approach to build this dynamically in
> Report Server?
> Build One "Master Report which includes Sub-Reports or Build the Sub Reports
> and pass them into the Master?
> We have a middle "Business Logic" Tier that we will be driving the creation
> of these reoprts from.
> Thanks!
> ...Bryan|||Bryan -
Did you ever get an answer? We are looking to do something similar. The
first page of one of our reports needs to be a table of contents.
Thanks
Jen
"Bryan E." wrote:
> Update:
> I am thinking that we can programattically create a "Master RDL" that
> includes the Sub Reports.
> Question: would we be able to control the processing of the sub reports so
> that they generate first then generate the Master Report genterating the
> Table of Contents after the Sub Reports have been created (and we know the
> sub Report Page Counts)
> Any Suggestions would be really appreachiated
> Thanks!
> ...Bryan
> "Bryan E." wrote:
> > We are building a "Annual Statement" which will include a series or Reports
> > that will be bound into a Booklet. We will need a table of Contents (With
> > Page Numbers) and also need the pages to be continuous throughout.
> >
> > Our question is what is the best approach to build this dynamically in
> > Report Server?
> >
> > Build One "Master Report which includes Sub-Reports or Build the Sub Reports
> > and pass them into the Master?
> >
> > We have a middle "Business Logic" Tier that we will be driving the creation
> > of these reoprts from.
> >
> > Thanks!
> > ...Bryan

Column Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.

Quote:

Originally Posted by J

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.

You have to create trigger

Column Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.You can create a trigger to set the GroupID to the UserID value.
CREATE TABLE Foo (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
GroupID INT,
datacol CHAR(1));
GO
CREATE TRIGGER SetGroupID
ON Foo
AFTER INSERT
AS
UPDATE Foo
SET GroupID = I.UserID
FROM Foo AS F
JOIN Inserted AS I
ON F.UserID = I.UserID
AND I.GroupID IS NULL;
GO
INSERT INTO Foo (datacol) VALUES('a');
INSERT INTO Foo (GroupID, datacol) VALUES(5, 'b');
SELECT UserID, GroupID, datacol
FROM Foo;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Collect SSRS/SSAS schema (metadata)

Hi all,

I would like to collect metadata from cubes&reports automatically from servers SSAS and SSRS. Metadata include name, description, dimensions, members, permissions, etc. Then I store it into a table to be searchable.

I am searching the best solution for this.
Maybe it would be a SQL stored procedure.
Do anybody has an idea or some piece of help?

thx.
attila

Both servers expose management APIs. The Report Server has web service management APIs while SSAS comes with AMO library.

Collect SSAS / SSRS schema (metadata)

Hi all,

I would like to collect metadata from cubes&reports automatically from servers SSAS and SSRS. Metadata include name, description, dimensions, members, permissions, etc. Then I store it into a table to be searchable.

I am searching the best solution for this.
Maybe it would be a SQL stored procedure.
Has anybody an idea or some piece of help?

thx.
attila

Hey attila,

This can be done using AMO and a little code to store the metadata in some sort of db.

There is some good information here

(really good code examples, too)

Hope that helps a little,
C