Sql Server 2000 (NEW SP4 installed) Access ADO EXEC call fails when variable names are passed

[DaveSwain]

Hello,

our sql server admins installed the SP4 to our sql 2000 database. Now
when we are using access ado to call a stored proc like so it
FAILS...(also this code was in place and working prior to the patch):

EXEC someproc_name @mydate = '12/4/2007', myint=2

however if I take out the variable names like so,

EXEC someproc_name '12/4/2007', 2

then it works...but this won't do as a work-a-round.

and...the proc (with variable names) still runs in SQL 2000 query
analyser, just not from access 2003 ADO.

anyone have a clue what I should start with?

PS, if there is only a SELECT in the stored proc, it will take the
variable names..but if there is an UPDATE OR INSERT IT FAILS

-thanks

 

[Bill Mosca, MS Access MVP]

You might want to explicitly declare your parameters as I do. This example
uses 2 parameters.
Dim strStoredProc As String
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command


strStoredProc = "ImportLM_" & strFileType
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=sqloledb;Data Source=" & gconSQLServer
& ";" _
& "Initial Catalog=" & gconSQLDB & ";Integrated
Security=SSPI;"
cnn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strStoredProc
cmd.Parameters(1) = CStr(varPayDate)

If Not IsMissing(varFiscalYear) Then
cmd.Parameters(2) = varFiscalYear
End If

cmd.Execute