CODEWITHSUNDEEP
sql-server-2008ms-accessstored-proceduresvb6ms-access-2007
Gedalya
Gedalya

Reputation: 909

"Executing SQL directly; no cursor." error in Access 2007 VB code

I've got a stored procedure I run from VB code in an Access 2007 database. Usually the code runs fine; however sometimes I receive the following error:

Error # -2147217900 was generated by Microsoft OLE DB Provider for ODBC Drivers [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor.

I have done quite a bit of research on this and I haven't come up with much. There are two sources I've seen: one of the reasons is insufficient permissions, and the other has to do with a problem in the Stored Procedure itself. I know the permissions is not an issue. Also the stored procedure runs absolutely fine in management studio no matter how many times I run it.

Help Please!!!

Upvotes: 1

Views: 15628

Answers (2)

Gedalya
Gedalya

Reputation: 909

Working with a colleague the problem was identified. Fundamentally the core issue was that the real problem was being obfuscated by a generic error message; in this case the "no cursor" error. The trick was to figure out what the actual error message is. Having given some thought to it a coworker changed the ODBC driver from "SQL Server Native Client 10" as noted in my post to an older MS SQL Server driver; while the newer version 10 obfuscated the error the older driver revealed the actual error which was in the stored procedure (which I had believed was not the case originally due to my own testing). So the bottom line is there are two points to take from this: first if you are having this error try the above trick to try and reveal the actual error and the second note, the real problem in this case was that the driver was hiding the programming problem.

Upvotes: 6

Mark Bertenshaw
Mark Bertenshaw

Reputation: 5689

You should work out what parameters are being passed to the stored procedure when you get this error. Print out the variables you are passing to the stored procedure object. I would pay particular attention to non-alphanumeric characters such as single quotes or escape characters.

Upvotes: 1

Related Questions