pass-through query problems

Question

pass-through query problems

Keith Walter-210080

Valued Member

Points: 70
More actions
February 18, 2005 at 10:54 am

#162657

I am new to stored procedures and calling them from a .mdb, so go easy. 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 (to avoid users connecting to the master database):
CODE
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:
CODE
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 or direction would be great!
Thanks, Keith

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

If the query doesn't exist, you will get the 7874 error. Try checking to see if the query exists before deleting it. Here is an example of checking for the query before deleting it.

dim myQuery as QueryDef

for each myQuery in Currentdb.QueryDefs
if myQuery.Name = "qry_SendSQLMail" then
DoCmd.DeleteObject acQuery, "qry_SendSQLMail" exit for
end if
next

I would also try commenting out the "myquerydef.Close" line. Access help says that the Close method removes the item from the collection, probably the source of the 3167 error.

Hope this helps,

Kathi

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

smknox Hall of Fame Points: 3275 More actions · Answer 2

You don't need a pass-through query to run a SQL stored procedure. This is how you would accomplish the same thing with ADO. If you need the ADO objects, check the appropriate box from references menu.

Function sqls_mail(str_to As String, str_copy As String, str_subj As String, str_body As String, Optional str_attachment As String)

Dim cmd As ADODB.Command

Dim Conn as String

Set cmd = New ADODB.Command

Conn

="Provider=sqloledb;Data Source=Syron-AP-01\SyronSQL;Initial catalog=db_applications;Integrated Security=SSPI;"

With cmd

.ActiveConnection = Conn

.CommandText = "

sp_smtp_sendmail"

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@TO", adVarChar, adParamInput, 4000, str_to)

.Parameters.Append .CreateParameter("@Subject", adVarChar, adParamInput, 4000, str_subj)

.Parameters.Append .CreateParameter("@Message", adVarChar, adParamInput, 4000, str_body)

.Parameters.Append .CreateParameter("@Attachments", adVarChar, adParamInput, 4000, str_attachment)

.Execute

Set cmd = Nothing

End Function

Comments: parameter order must match that of stored procedure.

I noticed you didn't include str_copy in the stored procedure, yet it's in the list of arguments for your function. That could be the source of some of the errors.

HTH,

Sam

Keith Walter-210080 Valued Member Points: 70 More actions · Answer 3

Thank you smknox and Kathi.

Kathi, I have found your assesment to be correct. Thanks

smknox, i very much want to use ado as, if I understand correctly, ado will allow me to get a return value from sql server as to the success of failier of the function. I hope you can induldge me and two more questions:

1. What is wrong with this code? The parameter order matches that of the stored procedure, and I have delt with the str_copy issue. please note that I have switched to the "master" database and the xp_sendmail extended stored procedure. (code is below)

2. How do I capture xp_sendmail's boolean return code?

Thanks!

Function sqls_mail_ado(str_to As String, str_subj As String, str_body As String, Optional str_copy As String, Optional str_attachment As String)

Dim cmd As ADODB.Command

Dim Conn As String

Set cmd = New ADODB.Command

Conn = "Provider=sqloledb;Data Source=Syron-AP-01\SyronSQL;Initial catalog=master;Integrated Security=SSPI;"

With cmd

.ActiveConnection = Conn

.CommandText = "xp_sendmail"

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@recipients", adVarChar, adParamInput, 4000, str_to)

.Parameters.Append .CreateParameter("@message", adVarChar, adParamInput, 4000, str_body)

.Parameters.Append .CreateParameter("@subject", adVarChar, adParamInput, 4000, str_subj)

.Execute

End With

Set cmd = Nothing

End Function

smknox Hall of Fame Points: 3275 More actions · Answer 4

Hi Keith:

1. Make sure that you have a value for each input parameter the SP is looking for. If if needs 5 input parameters, you may want to input copy and attachments as null strings.

You don't want "master" in your connection string. That would indicate that you plan to write data to tables in the master db which is not a good idea. Your connection will find the xp_sendmail in the master db just fine. The initial catalog should be the db you want to write to.

2. Here is an example of how to capture output data back to Access:

Declare ReturnBooleanValue as a variable. Add an output parameter to your list prior to the execute command:

.Parameters.Append .CreateParameter("@BoolValue", adBoolean, adParamOutput)

.Execute

ReturnBooleanValue = .Parameters("@BoolValue").Value

HTH,

Sam

Keith Walter-210080 Valued Member Points: 70 More actions · Answer 5

The function is now working, though I did have to specify the master database - it did not find xp_sendmail via "db_applications".

I am having trouble capturing the return code values (boolean) and/or result sets (from Books Online: Result Sets: xp_sendmail returns this message: "Mail sent"). I would like to return both. I used your same code and I get an error at .execute:

Thanks!

smknox Hall of Fame Points: 3275 More actions · Answer 6

Hi Keith:

Yes, after I told you not to use the master I got to thinking about it and realized I was wrong. But you figured that out. I can't read your attachment, so it might be more helpful to just state what the error message is. Others on this forum are much more expert than I in troubleshooting this. I'm a relative newbie and was pasting code from some of my own apps that others have helped me write. So we need some of you far more capable developers to help finish this off. I get most of my ADO coaching from the MSDN newsgroups. Don't use the Access forum. Go to Visual Studio/ visual basic / vb.ado

Sam

Keith Walter-210080 Valued Member Points: 70 More actions · Answer 7

Your help has been great, Sam. Thanks. I fiigured out how to get the "results" back from xp_sendmail. ADODB.Command.ActiveConnection.Errors... is the key - it returns "mail sent." on success, and that is what I needed.

Thanks again, Keith

sqlSherwin SSCrazy Points: 2884 More actions · Answer 8

You can pass the return value of an ADO command by assigning the @Return_Value parameter which is always the first paramater of an ADO command.

Function sqls_mail_ado(str_to As String, str_subj As String, str_body As String, Optional str_copy As String, Optional str_attachment As String)

Dim cmd As ADODB.Command

Dim Conn As String

Set cmd = New ADODB.Command

Conn = "Provider=sqloledb;Data Source=Syron-AP-01\SyronSQL;Initial catalog=master;Integrated Security=SSPI;"

With cmd

.ActiveConnection = Conn

.CommandText = "xp_sendmail"

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@Return_Value", adInteger, adParamReturnValue)

.Parameters.Append .CreateParameter("@recipients", adVarChar, adParamInput, 4000, str_to)

.Parameters.Append .CreateParameter("@message", adVarChar, adParamInput, 4000, str_body)

.Parameters.Append .CreateParameter("@subject", adVarChar, adParamInput, 4000, str_subj)

.Execute

End With

Set cmd = Nothing

End Function

pass-through query problems

CODE

CODE