SQL Mail Formating - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Mail Formating

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-07 : 09:08:09

Gilbert writes "Hi

Im fairly new to sql and trying to use a trigger to send a mail as soon as a record is entered in the DB. This seems to work ok, the problem is, the mail that is sent results a query with a nvarchar(1500) field and the mail displays all the spaces. If i set the field in the DB to TEXT the trigger fails. Is there any way of formating the mail to get rid of all the spaces.

G.

CREATE TRIGGER mail
ON dbo.[Work Pack Header]

FOR INSERT
AS
DECLARE @WPID int,
@SpecialInstructions varchar(1500)

SELECT @WPID = @@IDENTITY
SELECT @SpecialInstructions = i.SpecialInstructions

FROM Inserted i

UPDATE [Work Pack Header]
set SpecialInstructions = REPLACE(REPLACE(LTRIM(RTRIM(@SpecialInstructions)), '{{', '"'), '}}', CHAR(39))

WHERE WPHID = @WPID

EXEC master..xp_sendmail 'gilbert'
, @Subject = 'New Record'
,@Message ='Mail'
,@query = 'Select WorkOrderNumber, [Sales Order Number], [Customer], RTRIM([SpecialInstructions])
from [work pack header]
where WPHID =(select max(WPHID) from [work pack header])'
,@dbuse = 'WoodwardTest', @width =300"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-03-07 : 10:06:14

You could try using RTRIM or LTRIM.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources