Another SQL Server Example

Create Procedure "spEMAIL_QMailer"
@strToAddressList varchar(255),
@strCCAddressList varchar(255),
@strBccAddressList varchar(255),
@strFromName varchar(32),
@strFromAddressList varchar(255),
@strMessageSubject varchar(255),
@strMessageText text,
@whenToSend datetime

As

DECLARE @strAccountName varchar(20)
DECLARE @strMessage varChar(255)
DECLARE @strSMTPServer varchar(32)

DECLARE @strPriority varchar(32)
DECLARE @strReturnReceipt varchar(255)

DECLARE @strAttachmentList varchar

DECLARE @intObject int
DECLARE @intResult int
DECLARE @strReturn varchar(255)
DECLARE @intSupplierID int
DECLARE @strFromAddress varchar(255)

/*     

exec  spEMAIL_QMailer 'kevin@flicks.com', '', '', 'From Name', 'From
Address',  'Subject', 'Text of message', 0

And within an ASP page (where BMNames is the database I'm using)  ...

    dim objBMNames, rsMailer

    Set objBMNames = Server.CreateObject("ADODB.Connection")
    objBMNames.Open "Provider=SQLOLEDB;Data Source=Server; Initial
Catalog=BMNames; User ID=logonid; Password=logonpassword;"

    Set rsMailer = objBMNames.Execute("{call spEMAIL_QMailer('" & _
         strEmailAddress & "','" & _
         trim(request("fldCCAddressList")) & "','" & _
         trim(request("fldBccAddressList")) & "','" & _
         trim(request("fldFromName")) & "','" & _
         trim(request("fldFromAddressList")) & "','" & _
         trim(request("fldMessageSubject")) & "','" & _
         strFile & "'," & _
         numMinWTS &  "')}")

*/
/* select values for constants */
SELECT
 @strPriority = '',
 @strReturnReceipt = '',
 @strAttachmentList = '',
 @strFromAddress = '',
 @strSMTPServer = 'mailhost.webfarm.net.uk'

/* Create ocxQMail object */

EXEC  @intResult = sp_OACreate 'ocxQmail.ocxQMailCtrl.1', @intObject OUT
IF @intResult <> 0
BEGIN
 /* exec sp_displayoaerrorinfo @intObject, @intResult */
 RETURN @intResult
END


IF ISNULL(@whenToSend,0) > 0
BEGIN

 /* Call SendAt method */
 EXEC @intResult = sp_OAMethod @intObject, SendAt, NULL, @whenToSend
 IF @intResult <> 0
 BEGIN
  RETURN @intResult
 END
END

/* Call Q method */

EXEC @intResult = sp_OAMethod @intObject, Q, NULL, @strSMTPServer,
@strFromName,
 @strFromAddressList, @strPriority, @strReturnReceipt, @strToAddressList,
 @strCCAddressList, @strBccAddressList, @strAttachmentList,
@strMessageSubject,
 @strMessageText

IF @intResult <> 0
 RETURN @intResult



/* Destroy Mail Object */

EXEC @intResult = sp_OADestroy @intObject
IF @intResult <> 0
  RETURN @intResult

RETURN 0

Another SQL Server Example, that addresses the 255 char limit

I found the solution for the SQL Server 255 character limit when using ocxQmail directly from with a SQL stored procedure. Your "Another SQL Example" sample already has the solution but it does not appear that it is known that it is there. The solution is to pass the body of the message into the stored procedure as a text data type instead of declaring a local varchar data type variable greater than 255 characters.

I have attached the sql stored procedure that I am using currently for reference. The that is under the samples page has the fix it as it passes in the body as a text parameter but I did not even realize the this was the fix until I started playing around with some other components.

Justin James

CREATE PROCEDURE prc_SendOcxQmail
	@FromName		varchar(80),
	@FromAddress		varchar(80),
	@Priority		varchar(32) 	= '',
	@ToWhom			varchar(3000),			
	@CCToWhom		nvarchar(3000)	= '',
	@BCCToWhom		nvarchar(3000)	= '',
	@Subject		varchar(255)	= '',
	@Body			text,
	@ReturnReceipt 		varchar (3)	= 'No',
	@SMTPServer		varchar(100)	=   'default_smtp_server_here',
	@AttachmentList 	varchar(8000)	= '' 
AS
BEGIN
--*****************************
-- DECLARE VARIABLE SECTION
--****************************
DECLARE 
	@intObject int,
	 @intResult int

--*****************************
-- VARIABLE INIT SECTION
--****************************


--*****************************
-- PROGRAM SECTION
--****************************
	-- Create ocxQmail object.
	EXEC @intResult = sp_OACreate 'ocxQmail.ocxQmailCtrl.1', @intObject OUT
	IF @intResult <> 0    goto Err 

	-- Call Q method.
	EXEC @intResult = sp_OAMethod @intObject, Q, NULL, @SMTPServer, @FromName,
		@FromAddress, @Priority, @ReturnReceipt, @ToWhom,
		@CCToWhom, @BCCToWhom, @AttachmentList, @Subject, @Body 
	IF @intResult <> 0   goto Err 

	PRINT 'MESSAGE SENT'
	goto Done	
-- **************************************
--       ERROR CAPTURE SECTION
-- **************************************
Err:
	DECLARE @vErrorMsg varchar(255),
		@source varchar(30), 
		@desc varchar(200),
		@hr varchar(500)

	-- PRINT ERROR INFO TO SCREEN
	Print ' '
	Print '## ERROR ##'
	exec sp_OAGetErrorInfo null, @source OUT, @desc OUT
	select hr = convert(binary(4), @intResult), source=@source, description=@desc

	-- LOG TO EVENT VIEWER
	SELECT @vErrorMsg = char(10) + 'ocxQMail ALERT:   '
	SELECT @vErrorMsg = @vErrorMsg + char(10) +  '      SOURCE: ' + @source + char(10) + '      DESCRIPTION: ' + @desc
		-- NOT USED SINCE IT DOES NOT PRODUCE OUTPUT WHEN CONVERTED
		--+ 'HRESULT: ' + CONVERT(char(50), (convert(binary(4), @intResult)))
	
	SELECT hr = 'ERROR MSG ' + @vErrorMsg
	EXEC master..xp_logevent 88888, @vErrorMsg , Error
	goto Done

--*****************************
-- Clean Up Section
--****************************
Done:
	exec sp_OADestroy @intObject
	return 
END

To create an empty string on SQL Server you have to ensure that the sp_dbcmptlevel is set to 70 on the database on which you are working. As an administrator on our database I have access to set this level - it is currently 70 so the empty string passed should truly be an empty string. Passing NULL should also be the equivalent of an empty string but this does not work either. To set this level the database command is :

sp_dbcmptlevel [[@dbname =] name] [, [@new_cmptlevel =] version]

[ Microsoft documentation from 'Server Books Online' under the search phrase 'empty string' : Interpretation of an empty string is controlled by the setting of sp_dbcmptlevel. If the setting of sp_dbcmptlevel is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the setting of sp_dbcmptlevel is equal to 70, SQL Server interprets empty strings as empty strings. The empty string literal (' ') is interpreted as an empty string for the sp_dbcmptlevel of 70.]

The stored procedure call is obviously passing a space for some reason - I'll investigate the documentation further and also attempt the same function from an ASP page directly.

Concerning messages longer than 255 characters
=================================
I haven't used strings greater than 255 characters stored on the database, but I have been storing file names, the files for which are then opened as a text stream into a variable which is then passed into the stored procedure. My test message was 6K (1K text and 5K image) and worked perfectly well with the exception of the return receipt problem.. I am using this method because the email messages are written in HTML with embedded images etc. I've modified the stored procedure to receive the header content which is manipulated within the procedure with the XHeader function, so HTML files create an HTML header and vice versa. What I was hoping to do was to send a combined email - an HTML header with an HTML page and a plain text header with plain text. In that way, any recipients of the mail who did not have an HTML parser on their email program could read the plain version. However, it appears from your online documentation that it's one header per message. The data type within the stored procedure that receives this long text string is 'text' but 'ntext' should work equally well.
"Jason Woods" jason@screenpages.com

Using with .Net/ASPX

The GUID for ocxQmail is EAFF591D-6364-11D1-BC45-02608CAD9C7D So

< Reference
Name = "OCXQMAILLib"
Guid = "{EAFF591D-6364-11D1-BC45-02608CAD9C7D}"
VersionMajor = "1"
VersionMinor = "0"
Lcid = "0"
WrapperTool = "tlbimp"
/>