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..

Collations in SSAS. How should it work?

Hi SSAS gurus,

Could you tell me, please, what collation gets a new dimension (cube), that collation isn't specified in create script, from Server collation or from data base collation? In the MSDN there isn't clear information related to it.

If I create a new database from script, where only the collation of the database specified, all dimension and cubes get collation from server, but not from database.
But in the SQL Server DB Engine all the objects inherit collation from database not from server.

How can I specify the collation of the data base object in the SSAS project? Is there in UI any place to set collation of database in the project?

Analysis Server implements general mechanism of static inheritance. Object's properties not specified in the create statement are getting their values based on pre-defined defaults or based on the value of parent object.

This way, the database is going to be created with server collation and any object within database keeps inheriting the database collation.

As for the collaiton exposed in the project UI, couldnt find anywhere I could change it myself :)

Simple workaround is to add following XML tag to your %ProjectName%.database file <Collation>Latin_general</Collation>

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I have following problem (probably a bug). I have spicified the collation Latin_General_Bin for the database in the database script . But the server has collation Latin_General_CI. In my script neither cubes no dimensions don't have collation specified. After script execution, all created cubes and dimesions have collation inherited from server, but not from database, es expected :-(

I have found how to set collation for the databese in UI :-(. In the BI Studio Menu Database -> Edit Database. Then in Properties window you can set collation for the database.

|||

You are probably right. This could be a bug. Though I would check one more thing.

Make sure when you are deploying, the database does not exist on the server. Deploying over existing database wouldnt change it's collation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, a am sure. The database desn't exists on the target server and database script has callation set only for database, neither for cubes nor for dimensions.