SQLTeam.com | Weblogs | Forums

Stored Procedure Help


#1

We have a database for our ERP that has numerous stored procedures for sending emails out of the ERP.
One of the stored procedures generates a signature that grabs data from the ERP such as name, company name etc...
I want to remove this signature since we use an exchange signature but I am not sure how to trim this string.
Can someone help?
I am guessing it is somewhere in here:

SET @lsPropertyName = 'HTMLBody'
SET @lsPropertyValue = REPLACE(@avc8000_EMailMessage, CHAR(10), '<BR>')
DECLARE @TEXT AS VARCHAR (100) 
EXEC @llHResult = sp_OASetProperty @objCDOMessage, @lsPropertyName, @lsPropertyValue
IF @ac1_SetSDOLETraceOn = 'Y'
	INSERT INTO @tblSDOLETrace values ('"EXEC @llHResult = sp_OASetProperty @objCDOMessage, ' + @lsPropertyName + ', ' + @lsPropertyValue + '"', @llHResult)

#2

using Sp_helptext you can get the script used in the SP . Please check the SP definition


#3

Is there a way to attach a word document here so someone can help me review this?
sorry
i am new here
i ran Sp_helptext and have the results but not sure what to look for

thank you


#4

We prefer SQL code included in the message. Attachments, and in particular "images of screen shots", mean we cannot just cut & paste code in order to make tests, locally, or easily make changes/suggestions.

If you want to post some SQL code here please surround it with a Markdown tag and then it will format properly and NOT get mangled by the message software. The tag you need is like this:

    ```sql

    .... your SQL code here ....

    ```

#5

'''


#6

I tried posting code from the stored procedure
Hope it worked
Sorry if I did something incorrectly


#7

It needed the backticks as i entered them (rather than single quotes).

What is here is fine ... but no way of telling if the software has gobbled something up :frowning:

So if you are still able to use the pencil icon to EDIT your post you could try putting the backtick MARKUP codes in place, if you succeed you will see that the code all formats nicely :slight_smile: )

I'm not sure, but to me it looks like the FOOTER is already within the @avc8000_EMailMessage passed to this Sproc (i.e. not added within this SProc), but it might be one of the sp_OASetProperty calls


#8

Kristen,
I have the same suspicion that the footer is part of @avc8000_EmailMessage
If that is the case, how do I trim it from the end?


#9

If you can match it exactly you could use

SELECT @avc8000_EmailMessage, = REPLACE(@avc8000_EmailMessage, 'Your exact footer message', '')