pass-thru query woes - calling a stored procedure

[NightZEN]

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, 
as

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)

return @rc
GO

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_SHIT

Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
    
    Set mydatabase = DBEngine.Workspaces(0).Databases(0)

    DoCmd.DeleteObject acQuery, "qry_SendSQLMail"


    DoEvents

    Set myquerydef = mydatabase.CreateQueryDef("qry_SendSQLMail")

    myquerydef.Connect = "ODBC;Description=Applications Database;DRIVER=SQL Server;SERVER=SYRON-AP-01\SYRONSQL;DATABASE=db_applications;Trusted_Connection=Yes"
    
    myquerydef.sql = "EXEC sp_smtp_sendmail @TO = '" & str_to _
                        & "', @message = '" & str_body _
                        & "', @CC = '" & str_copy _
                        & "', @subject = '" & str_subj & "'"
    
    myquerydef.ReturnsRecords = False
    myquerydef.Close
    For z = 1 To 100
        DoEvents
    Next z
    
    
    DoCmd.OpenQuery "qry_SendSQLMail" 'this runs the new query which sends the email to SQL Server
    
    
    Set mydatabase = Nothing
    Set myquerydef = Nothing
    
    
GOODBYE:
    Exit Function

OH_SHIT:
    If Err.Number = 7874 Then
        Resume Next
    Else
        MsgBox "Please try again." & vbCrLf & "Error Number: " & Err.Number & ". Error Desc: " & Err.Description, vbCritical, "email errorisom"
        Resume GOODBYE
    End If

End Function

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.

 

[DougP]

Try ADO inste4ad of DAO
Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Set Conn = New ADODB.Connection
Set Rs1 = New ADODB.Recordset

Conn.Open "driver=SQL Server;server=yourservernameSQL;uid=userid;pwd=password;database=yourdatabasename;"
Dim SQLCode As String
SQLCode = "EXEC sp_smtp_sendmail @TO = '" & str_to _
& "', @message = '" & str_body _
& "', @CC = '" & str_copy _
& "', @subject = '" & str_subj & "'"

For z = 1 To 100
DoEvents
Next z

Rs1.Open SQLCode, Conn, adOpenStatic, adLockOptimistic


Set Rs1 = Nothing
Set Conn = Nothing

DougP, MCP, A+

 

[Bugget]

I would recommend not deleting and recreating the querydef as you do here:

Set mydatabase = DBEngine.Workspaces(0).Databases(0)

DoCmd.DeleteObject acQuery, "qry_SendSQLMail"

The reason being is that I am not sure that Access recognizes the new one as a pass through anymore. Using DAO I achieve the same thing by just creating one query, designating it as a pass through and then just changing the .SQL paramter like so:

Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef

Set mydatabase = DBEngine.Workspaces(0).Databases(0)

Set myquerydef = mydatabase.CreateQueryDef("qry_SendSQLMail")

myquerydef.sql = "EXEC sp_smtp_sendmail @TO = '" & str_to _
& "', @message = '" & str_body _
& "', @CC = '" & str_copy _
& "', @subject = '" & str_subj & "'"

myquerydef.ReturnsRecords = False
myquerydef.Close
For z = 1 To 100
DoEvents
Next z


DoCmd.OpenQuery "qry_SendSQLMail" 'this runs the new query which sends the email to SQL Server


Set mydatabase = Nothing
Set myquerydef = Nothing


GOODBYE:
Exit Function

OH_SHIT:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox "Please try again." & vbCrLf & "Error Number: " & Err.Number & ". Error Desc: " & Err.Description, vbCritical, "email errorisom"
Resume GOODBYE
End If

End Function

 

[Bugget]

Actually change this line:

Set myquerydef = mydatabase.CreateQueryDef("qry_SendSQLMail")

to:
Set myquerydef = mydatabase.QueryDefs("qry_SendSQLMail")

 

[NightZEN]

Thanks Bugget, What you suggest is eventuslly what I did. It seemed the errors all involved deleteing and recreating the query. Thanks for you help!

Doug, I would actually prefer to use ADO, but have yet to be able to get it to work - my problem usually comes in when I try to make the connection. My question is the user id and password. I use windows domain security for authentication. what user id and password should I use here? Do I need to create a new user with access too this database on the sql server?

Thanks!

 

[NightZEN]

Doug, I figured out the connection String, and it works!

in case it may help someone eles:
"Provider=sqloledb;Data Source=syron-ap-01\SyronSQL;Initial catalog=db_applications;Integrated Security=SSPI;"

Now I am trying to get a return value from the stored procedure. If it error's, I want to get the message in Access. How?

Thanks a million!

 

[Bugget]

VBA has a built in error collection that you can use for ODBC errors that SQL Server will throw. Have a look at this short example:

Public Function ErrorHandling()
Dim errX As DAO.Error
Dim Str As String

If Errors.count > 0 Then
Str = "ODBC Error:" & vbCrLf & vbCrLf
For Each errX In DAO.Errors
Debug.Print "ODBC Error"
Debug.Print errX.Number
Debug.Print errX.Description
Str = Str & errX.Number & " - " & " " & errX.Description & vbCrLf & vbCrLf
Next errX
Else
Str = "VBA Error: "
Debug.Print "VBA Error"
Debug.Print Err.Number
Debug.Print Err.Description
Str = Str & Err.Description
End If

MsgBox Str

End Function

Glad I could help, but its even better that you got ADO working IMO. Have a good one!

- Bugget