July 26, 2006 at 12:59 am
Hi All,
This one seems to have popped up with no obvious resolution. I'm hoping someone may have had a similar problem to the one I am about to describe and hopefully a resolution to go with it.
I have an Access database that calls a stored procedure using the following syntax:
Dim db As Database
Dim qrydef As QueryDef
Set db = CurrentDb
DoCmd.Hourglass True
Set qrydef = db.QueryDefs("QryGenericPassThrough")
qrydef.SQL = "Exec MergeDuplicatesDoMerge '" & arrUserDet(0) & "','" & arrUserDet(0) & "'"
qrydef.ODBCTimeout = 0
qrydef.ReturnsRecords = False
qrydef.Execute
I have tested the stored procedure itself from Query analyser and it runs without error. However when called via Access I keep getting this stupid ODBC error 3146. I ran a trace against the database and can see that the procedure is actually completing.
I'm pretty sure I have exhausted all suggestions I could find for similar problems described. KB artickle http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195225 talks about set nocount on, so I have ensured that set nocount on is not called....even to the extent of checking any triggers fired to esure that set nocount on is always followed by set nocount off.
Driving me absolutely NUTS! Any suggestions would be much appreciated.
Regards,
Terry
July 26, 2006 at 11:48 am
My first move would be to upgrade it to use an ADO connection and see what happens there. At least then you could look at the ado.errors collection and get more details about the error. Is that an easy choice for you?
Is there any more detail from the error? Does printing the actual SQL give any insights?
July 26, 2006 at 8:10 pm
Hi Stephen,
I have switched the code to ADO but don't seem to be any better off. In fact the stored procedure seems to kick the bucket rather than completing entirely as it did using DAO. Again, I have tested the stored procedure whilst logged in to Query analyser with the same user permission as running it from Access and it works without error. Can you tell me if the code below is fine? Thanks.
Public Sub ExecStoredProc()
Dim cmdMergeDuplicatesDoMerge As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmRunBy As ADODB.Parameter
'record variables
Dim strAuthorID As String
Dim Err As ADODB.Error
Dim strError As String
On Error GoTo ErrorHandler
Set cmdMergeDuplicatesDoMerge = New ADODB.Command
Set cmdMergeDuplicatesDoMerge.ActiveConnection = gSQLADOConnection
' Set the criteria
cmdMergeDuplicatesDoMerge.CommandText = "MergeDuplicatesDoMerge"
cmdMergeDuplicatesDoMerge.CommandType = adCmdStoredProc
cmdMergeDuplicatesDoMerge.CommandTimeout = 0
' Define the stored procedure's input parameters
Set prmUser = New ADODB.Parameter
prmUser.Type = adVarChar
prmUser.Size = 30
prmUser.Direction = adParamInput
prmUser.Value = arrUserDet(0)
cmdMergeDuplicatesDoMerge.Parameters.Append prmUser
Set prmRunBy = New ADODB.Parameter
prmRunBy.Type = adVarChar
prmRunBy.Size = 30
prmRunBy.Direction = adParamInput
prmRunBy.Value = arrUserDet(0)
cmdMergeDuplicatesDoMerge.Parameters.Append prmRunBy
' execute the command.
cmdMergeDuplicatesDoMerge.Execute , , adExecuteNoRecords
ErrorHandler:
' clean up
For Each Err In gSQLADOConnection.Errors
strError = "Error #" & Err.number & vbCr & _
" " & Err.Description & vbCr & _
" (Source: " & Err.Source & ")" & vbCr & _
" (SQL State: " & Err.SQLState & ")" & vbCr & _
" (NativeError: " & Err.NativeError & ")" & vbCr
If Err.HelpFile = "" Then
strError = strError & " No Help file available"
Else
strError = strError & _
" (HelpFile: " & Err.HelpFile & ")" & vbCr & _
" (HelpContext: " & Err.HelpContext & ")" & _
vbCr & vbCr
End If
Next
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
July 27, 2006 at 8:43 am
Without knowing what your Pass-Through query is trying to do, I did a little searching and came across this KB article. Not too sure if it helps in your situation, but if you wanted to post the body of the stored proc, or at least a description of what it is trying to do, maybe we could dig a little deeper.
You may have come across this article already. If so, sorry for the repeat.
http://support.microsoft.com/kb/208481/en-us
R.
July 27, 2006 at 10:35 am
Has the structure of the Sql table changed? Access remembers and stores the schema for attached ODBC data sources only upon the first connect, so if the Sql table was changed then Access won't understand the new schema. Try the Linked Table Manager to re-link the data source.
July 27, 2006 at 12:08 pm
Try this code to call the sp...
Sub wibble()
on error goto error_handler
gSQLADOConnection.MergeDuplicatesDoMerge _
arrUserDet(0), arrUserDet(0)
exit_proc:
exit sub
error_handler:
'usual stuff here
'then ado errors (your code is good)
resume exit_proc
End Sub
This syntax is a bit easier than playing with parameter collections. Apart from that, check the proc is being called properly by looking for it with profiler as it is called on the server.
July 30, 2006 at 6:02 pm
Hi All,
Thanks for the responses. It seems as though the problem sorted itself once I changed the provider in the connection string. Initiallly it was using ODBC. What a head-ache that was!
Set gSQLADOConnection = New ADODB.Connection
gSQLADOConnection.ConnectionString = "Provider='sqloledb';Data Source='Server';" & _
"Initial Catalog='db';User ID='user';Password='pwd';"
gSQLADOConnection.ConnectionTimeout = 30
gSQLADOConnection.Open
Cheers and Thanks,
Terry
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply