Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored procedure generated email with hyperlinks that are not working.

Status
Not open for further replies.
Webkins

Webkins

Programmer
Dec 11, 2008
118
US
I really need some expert help here. I have been working on this for 2 days, trying everything I can think of. Here below is the SQL2K stored procedure. The issue is this... There could possible be anywhere from 1 to 4 hyperlinks in the email generated by this stored procedure. If there is 1 or 2 or 3 they all work fine and all is well. When there are 4 hyperlinks present the @attachment_3 is always cut short, as in does not contain the full path to the file on my server no matter what I do or change. This procedure is called by MSAccess 2003 VBA and two variables are then passed here. I am kinda new at this and have reached the end of my expertise. Thanks to all who read this post.

CREATE Procedure sp_SMTPMail_Maintenance_Work_Order_Manager @won varchar(10), @status varchar(50)
AS
SET NOCOUNT ON

DECLARE @requested_by varchar(50), @email varchar(50), @requested_date varchar(50), @description varchar(1500), @Message_Text varchar(8000), @manager_email_reply varchar(50), @maintenance_manager_email varchar(50)

DECLARE @rc int, @area varchar(50), @priority varchar(50), @manager varchar(50), @manager_email varchar(50), @Header varchar(500), @email_reply varchar(50), @manager_comments varchar(500), @ccopy varchar(350)

DECLARE @Text4 varchar(400),@Text5 varchar(400), @Text9 varchar(500), @Text10 varchar(150), @maintenance_manager varchar(50), @manager_date varchar(50), @Text6 varchar(50), @Text7 varchar(50), @Text8 varchar(50)

DECLARE @attachment_1 varchar(350), @Text11 varchar(350), @attachment_2 varchar(350), @Text12 varchar(350), @attachment_3 varchar(350), @Text13 varchar(350)

SELECT @area = area, @requested_by = requested_by, @email = email, @manager = manager, @manager_date = manager_date, @manager_email = manager_email FROM mwo.dbo.mwo WHERE work_order_number = @won

SELECT @requested_date = requested_date, @description = [description], @priority = priority, @manager_comments = manager_comments FROM mwo.dbo.mwo WHERE work_order_number = @won

SELECT @attachment_1 = attachment_1, @attachment_2 = attachment_2, @attachment_3 = attachment_3 FROM mwo.dbo.mwo WHERE work_order_number = @won

SELECT @maintenance_manager_email = maintenance_manager_email FROM mwo.dbo.maintenance

SET @ccopy = @manager_email + ';' + @email
SET @email_reply = @requested_by + '<' + @email + '>'
SET @manager_email_reply = @manager + '<' + @manager_email + '>'
SET @Header = '<html><center><b><font size="7" face="arial" color="blue">PHILIPS</b></font><br>Esta notificación se genera automáticamente. NO responder por favor.'
SET @Text4 = '<br><br>Favor de revisar la orden de trabajo de Mantenimiento.<br>Orden de trabajo #: ' + @won + '<br>Area: ' + @area + '<br>Prioridad: '+ @priority + '<br>Requerida por: ' + @requested_by + '<br>'

IF @status = 'Aprobado'
BEGIN
SET @Text5 = 'Fecha de solicitud: ' + @requested_date + '<br>Descripcion: ' + @description + '<br><br>Gerente: ' + @manager + '<br>Comentarios de gerente: ' + @manager_comments + '<br>Fecha de revición del gerente: ' + @manager_date + '<br>Status del gerente: ' + @status + ',Reenviado a mantenimiento.<br>'
END

IF @status = 'Negado' or @status = 'Retenido'
BEGIN
SET @Text5 = 'Fecha de solicitud: ' + @requested_date + '<br>Descripcion: ' + @description + '<br><br>Gerente: ' + @manager + '<br>Comentarios de gerente: ' + @manager_comments + '<br>Fecha de revición del gerente: ' + @manager_date + '<br>Status de gerente: ' + @status + '.<br>'
END

SET @Text6 = '<br>'
SET @Text7 = '<br>'
SET @Text8 = '<br></center></html>'
SET @Text9 = '<br>Esta acción requiere de tu revición. Gracias.<br><a href="file:\\Advmnn2ms001\common\Maintenance_Work_Orders\MWO.mdb">Pulsa aqui para Ordenes de trabajo de Mantenimiento</a>'
SET @Text10 = 'Maintenance work order ' + @won + ': Manager ' + @status + '...'
SET @Message_Text = @Header + @Text4 + @Text5 + @Text9 + @Text6 + @Text7 + @Text8

If @attachment_1 IS NOT NULL
BEGIN
SET @Text11 = '<a href=file:' + @attachment_1 + '>Pulsa aqui para ver primer archivo agregado </a><br>'
SET @Message_Text = @Header + @Text4 + @Text5 + @Text11+ @Text9 + @Text6 + @Text7 + @Text8
END

If @attachment_2 IS NOT NULL
BEGIN
SET @Text12 = '<a href=file:' + @attachment_2 + '>Pulsa aqui para ver segundo archivo agregado </a><br>'
SET @Message_Text = @Header + @Text4 + @Text5 + @Text11+ @Text12 + @Text9 + @Text6 + @Text7 + @Text8
END

If @attachment_3 IS NOT NULL
BEGIN
SET @Text13 = '<a href=file:' + @attachment_3 + '>Pulsa aqui para ver tercer archivo agregado </a><br>'
SET @Message_Text = @Header + @Text4 + @Text5 + @Text11+ @Text12 + @Text13 + @Text9 + @Text6 + @Text7 + @Text8
END

IF @status = 'Aprobado'
BEGIN
EXECUTE @rc = master.dbo.xp_smtp_sendmail
@FROM =' ',
@FROM_NAME =@manager_email_reply,
@TO =@maintenance_manager_email,
@CC =@ccopy,
@subject =@Text10,
@message =@Message_Text,
@type ='text/html',
@server ='smtprelay-us1.cooper.com'
END

IF @status = 'Negado' or @status = 'Retenido'
BEGIN
EXECUTE @rc = master.dbo.xp_smtp_sendmail
@FROM =' ',
@FROM_NAME =@manager_email_reply,
@TO =@email,
@CC =@manager_email,
@subject =@Text10,
@message =@Message_Text,
@type ='text/html',
@server ='smtprelay-us1.cooper.com'
END

GO
 
Sort by date Sort by votes
If you run this query:

SELECT attachment_3 FROM mwo.dbo.mwo

Do you see the full path in all your records?

If it were my task, I would be doing all this message building and sending in my front-end application (which I guess is Access in your case). First of all, I like my database server to be just that, rather than being an all-in-one database/application/email server. Secondly, it's much easier to debug in a language like VBA than in a stored procedure.

 
Upvote 0 Downvote
Can you run this query and post the results?

Code: 
Select  CHARACTER_MAXIMUM_LENGTH 
From    mwo.Information_Schema.Columns
Where   Table_Name = 'mwo'
        And Column_Name = 'attachment_3'

You are declaring @attachment_3 as a varchar(350). If your column in the table is longer than 350 characters, then it will be cut off when you assign it to the @attachment_3 variable.

For example, run this....

Code: 
Declare @Variable varchar(3)

Set @Variable = 'abcdefghijk'
Select @Variable

Since the variable is declared varchar(3), you will only get the first 3 characters of the string.

I'm not saying that this is your problem, but it is the first thing I would look at.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
213
Cpreston
Cpreston
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
606
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
236
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
351
gmmastros
gmmastros
Cpreston
  • Locked
  • Question
Will not show row with Line Type 2 1
Replies
17
Views
255
Cpreston
Cpreston

Part and Inventory Search

Sponsor

Back
Top