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

Collation troubles with SQLServer Express 2005

Greetings,
I installed SQL Server Express 2005 in my system, and created a simple database to store mailng addresses. The database has an email field and a name field. The data to populate the database came from an Excel file saved as a Unicode Text file containing the data as:
xxx@.somemail.com; To?o Peres yyy@.someothermail.com; Iván Cárdenas Note that this data has names with accented characters.
I then tried to import the data into the SQLServer Express with the following command:
bulk insert lista
from 'c:\temp\todas.csv'
with (
DATAFILETYPE ='widechar',
FIELDTERMINATOR =';'
)
I got the following error message:
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
And the data was imported as:
xxx@.somemail.com To±o Peres yyy@.someothermail.com Ivfn C?rdenas
I tried using the DATAFILETYPE ='native' and DATAFILETYPE ='widenative' options but these caused the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I want to avoid re-installing the server and the Express 2005 edition does not come with the tools needed to rebuild the master database anyway (see this link).
When I type the data with the Server Management Studio Express program, I can insert the accented characters correctly.
Does anyone out there know how to deal with this problem? How do I tell SQL Server Express 2005 to insert the text from the text file and keep the accented characters as they were initially?
I will welcome any help.
Ivan.

Hi Ivan,

Are you sure you saved the spreadsheet as Unicode Text? The .csv filetype suggests you didn't - unless you manually changed it.

I tried using the data above in Excel, saving as Unicode Text and then bulk inserting and it worked fine.

Thanks