Results 1 to 1 of 1
2005-02-18, 13:17 #1
- Join Date
- Jul 2003
- Thanked 0 Times in 0 Posts
pass-thru query help! Calling a stored procedure (Access00/02/03 & SQL-S2
I am trying to run a stored procedure on a SQL-Server 2000 from an Access Database Front-End using a stored procedure. I am getting different errors, at different times and having trouble getting this thing to work. Sometimes it works, sometimes it doesn't. The stored procedure is a "Wrapper" for xp_sendmail:
CREATE proc dbo.sp_smtp_sendmail
@TO NVARCHAR(4000) = NULL,
@subject NVARCHAR(4000) = NULL,
@message NVARCHAR(4000) = NULL,
@attachments NVARCHAR(4000) = NULL,
declare @rc int
exec @rc = master.dbo.xp_sendmail
@recipients = @TO,
@message = @message,
@subject = @subject,
@attachments = @attachments
if (@@error <> 0 or @rc <> 0)
raiserror(N'Sending message using xp_smtp_sendmail failed', 16, 1)
Here is how I call it:
Function sqls_mail(str_to As String, str_copy As String, str_subj As String, str_body As String, Optional str_attachment As String)
On Error GoTo OH_######
Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
Set mydatabase = DBEngine.Workspaces(0).Databases(0)
DoCmd.DeleteObject acQuery, "qry_SendSQLMail"
Set myquerydef = mydatabase.CreateQueryDef("qry_SendSQLMail")
myquerydef.Connect = "ODBC;Description=Applications Database;DRIVER=SQL Server;SERVER=SYRON-AP-01SYRONSQL;DATABASE=db_applications;Trusted_Connec tion=Yes"
myquerydef.sql = "EXEC sp_smtp_sendmail @TO = '" & str_to _
& "', @message = '" & str_body _
& "', @CC = '" & str_copy _
& "', @subject = '" & str_subj & "'"
myquerydef.ReturnsRecords = False
For z = 1 To 100
DoCmd.OpenQuery "qry_SendSQLMail" 'this runs the new query which sends the email to SQL Server
Set mydatabase = Nothing
Set myquerydef = Nothing
If Err.Number = 7874 Then
MsgBox "Please try again." & vbCrLf & "Error Number: " & Err.Number & ". Error Desc: " & Err.Description, vbCritical, "email errorisom"
I am getting these errors: 3167, 7874, but sometimes I get no errors and the code runs fine. I often get 7874 when it tries to delete the qrydef, but also get 7874 when it goes to open the query.
Any help would be great! Even If I should scrap this code and start from scratch.