OCBC call failed on one server but not another

[grnzbra]

I have an Access application that links to a SQL Server. Everything seems to run fine with the exception of the following:


retval = SysCmd(acSysCmdInitMeter, "Creating SQL Property Listing", 1)
Set odbcws = CreateWorkspace("", "", "", dbUseODBC)
connectstr = CurrentDb.TableDefs("Sales").Connect
Set createconn = odbcws.OpenConnection("ListingCreate", , , connectstr)
If OurListing Then
SwitchValue = 1
Else
SwitchValue = 0
End If

parameters = "@ListingNo = '" & ListingNo & "', @UserName = '" & SafeName & "', @ListingDate = '" & LISTINGDATE & "', @BranchNo = '" & BranchNo & "', @CompanyNo = '" & CompanyNo & "', @OfcListing = " & SwitchValue & ", @Status = '" & ListingStatus & "'"
commandtext = "InsertPropertyListing " & parameters
Set Qdef = createconn.CreateQueryDef("", commandtext)
With Qdef
.Prepare = dbQUnprepare
.Execute

When this is run against the live system, it works fine. However, when it is run against the test system, the last line generates the message, "Warning Error: ODBC--call failed.

The InsertPropertyListing is a stored procedure on the Server. I checked the one on the test system against the one on the live system. They both are the same. Just for the heck of it, I copied the one from the live system to the test system and it still fails.

What could be doing this?

 

[cmmrfrds]

Do you have execute permissions on the stored procedure in the test database?

 

[grnzbra]

Yes

 

[grnzbra]

Actually, I'm beginning to think the problem is in the server, not in Access.