A have a stored procedure which finds an unused record in a table and then
populates it with data. When I call it from MS Access, it does exactly what
it is supposed to, but it returns error code -2147467259 - unspecified error.
Here's the code:

CREATE PROCEDURE AddAccountsEntry @Keyname char(15),
@EntryDate smalldateTime, @Details nvarchar(50),
@Debit smallmoney,@Credit smallmoney

AS

Declare MyCursor  Cursor for
Select Source, Keyname, EntryDate, Details, Debit, Credit, InUse From
AccountTransactions WHERE Inuse=0
For Update of Source, Keyname, EntryDate,Details, Debit, Credit, Inuse

Open MyCursor
Fetch Next From MyCursor

If @@Fetch_status = 0
    Begin
        Update AccountTransactions Set Source='ACC.DBF', Keyname=@keyname,
        EntryDate=@EntryDate, Details=@Details, Debit=@Debit, Credit=@Credit,
Inuse=1
        Where Current of MyCursor
    End

Close MyCursor
Deallocate MyCursor

GO

I've stripped out some of the SP for clarity. This code works. So why the
error code?

Hoof Hearted (HoofHear***@discussions.microsoft.com) writes:
No, it doesn't. Stored procedures never returns that sort of error codes,
unless you explicitly tell them to, and you don't. The return code comes
from ADO (or whatever API you are using in Access). Why I can't tell.
Then again, this is a very funny stored procedure. First, here is how
you should have written it:

CREATE PROCEDURE AddAccountsEntry @Keyname char(15),
                                   @EntryDate smalldateTime,
                                   @Details nvarchar(50),
                                   @Debit smallmoney,
                                   @Credit smallmoney

  UPDATE  AccountTransactions
  SET     Source    = 'ACC.DBF',
          Keyname   = @keyname,
          EntryDate = @EntryDate,
          Details   = @Details,
          Debit     = @Debit,
          Credit    = @Credit,
          Inuse     = 1
   WHERE  Inuse = 0

That's all. No cursor. Not only is the code simpler - it also performs
faster.

And here is some theories why Access may have choked:

However, you stored procedure needs a wr

This produces a result set, which you should consume in the Access code,
one way or another.

I believe that for a cursor to be updatable, some conditions have to be
fulfilled. I never use WHERE CURRENT OF, but I don't think you can update
many rows at a time with it. And if there are no keys, the cursor may not
be updatable at all. Anyway, the cursor is not needed, so it's not much
use to speculate about it.

Or maybe the problem was in the parts you stripped out?

Hello Erland,

The way you re-wrote my SP will cause every record to be updated where the
InUse field is zero. I only want the SP to find one record and update it. My
table is repeatledly populated and purged. So to avoid the database growing,
I am clearing records rather than deleting them. Then instead of Inserting
new records, I find an empty record and update it with new values. It's not a
normal approach but it works well for me.

Yes I am calling the the SP from ADO. I am relatively new to ADO so perhaps
I have done something in there. Here's the code:

Set MyCommand = New ADODB.Command
MyCommand.ActiveConnection = MyConnection
MyCommand.CommandText = "AddAccountsEntry"
MyCommand.CommandType = adCmdStoredProc

Set MyParameter = MyCommand.CreateParameter("Keyname", adChar,
adParamInput, 15, ImportTable![Name])
MyCommand.Parameters.Append MyParameter

Set MyParameter = MyCommand.CreateParameter("EntryDate", adDate,
adParamInput, , ImportTable![Date])
MyCommand.Parameters.Append MyParameter

Set MyParameter = MyCommand.CreateParameter("Details", adVarChar,
adParamInput, 50, ImportTable![Details])
MyCommand.Parameters.Append MyParameter

Set MyParameter = MyCommand.CreateParameter("Debit", adCurrency,
adParamInput, , NullToZero(ImportTable![Debit]))
MyCommand.Parameters.Append MyParameter

Set MyParameter = MyCommand.CreateParameter("Credit", adCurrency,
adParamInput, , NullToZero(ImportTable![Credit]))
                MyCommand.Parameters.Append MyParameter

MyCommand.Execute

This looks fine to me, but i'd be grateful if you could see anything wrong
with it.

I didn't want to do any of this. What I really wanted to do is to use a
..Seek in ADO and update the records that way. I can't find a way to do this
though.

Hoof Hearted (HoofHear***@discussions.microsoft.com) writes:
So add a SET ROWCOUNT 1 in the procedure.

It sounds to me that in order to avoid one problem - that you will have
to deal at some time anyway, you are adding kludges that give you more
and more headache as time goes by.

Add adExecuteNoRecords here to the Options parameter (which is the third
and last) to indicate that you don't care about the result set from
the procedure.

The would be an inferior method anyway, as you would bring the data
up to the client, only to find out what to update. Using a stored procedure
and keeping the data in the database is better.

Hello again Erland,

I tried adding adExecuteNoRecords to my code:

MyCommand.CommandType = adCmdText Or adExecuteNoRecords

I get an Error 3001 when this line executes. What have I done wrong?

I didn't know about Set Rowcount. Does it only effect the SQL Statement
immediately following it? Or do I have to turn it off again afterwards.

I would still like to stay with my technique for reusing old records. Within
my SP, how do I determine if an empty record was updated? Because if there
was none available I need to insert a new one.

Thanks for all your help. You might have guessed that I'm quite
inexperienced in SQL Server.

Show quoteHide quote

Hoof Hearted (HoofHear***@discussions.microsoft.com) writes:
I said that you should add it as the third parameter to Execute. Not
that you should add it to CommandType. The third option to Execute
can include more than just CommandType options. The MDAC Books Online
(also in MSDN Library) has more infmration.

The effect of SET commands are always reset when the scope they are
issued in exits. A scope can be a stored procedure, trigger, or the top-
level scope of a connection.

Just for SET ROWCOUNT, it is a good idea to always reset it with SET
ROWCOUNT 0 once it has been used, even if you are about to exit, since you
might add more code later on, and SET ROWCOUNT has quite drastic effects.

You check @@rowcount immediately after the UPDATE statement. (And before you
reset SET ROWCOUNT.) @@rowcount holds the number of rows affected by the
most recently executed statement. Best is to save it to a local variable,
as it is so volatile.

Hello yet again Erland,

I've got it all working now. I've got rid of the cursor from my SP and it
all looks alot tidier. Just a couple more questions now so that I understand
more...

I was trying to use a cursor in my SP because from my MS Access experience,
doing a .Seek is the most efficient way to access a record. I assumed that in
SQL Server, doing a SELECT statement would be slower than using a cursor. Is
this not true?

My instincts tell me to try and do a .Seek from my client. Partly because of
the above reason, but mainly because it looks quite processor intensive to
call an SP with the relevant parameters. ie, a single .Seek statement is
replaced by all the code to create the ADO command object and all the
parameters.

I would appreciate your comments.

Ian

Hoof Hearted (HoofHear***@discussions.microsoft.com) writes:
No. Access and SQL Server have very different architecture. With the
disclaimer that I have never worked with the Access, my understanding is
that when you access an Access database, you do that directly from your
process. Thus running a SELECT or a .Seek in ADO may be equivalent,
because in both cases there is something that operates directly against
a file. There is no Access process somewhere that processes your request.

SQL Server on the other hand is a server, and all access to it are
performed over the wire by TCP/IP or named pipes. When the client is
on the same machine, the communication can be over shared memory, but
it is still SQL Server that processes all requests. This means that
an ADO operation eventually results in SQL statements being sent to
SQL Server (or RPC calls). And it also means that if you do a .Seek
in ADO, it most likely results in data being brought over to the
process space over the client. Something which can be costly if the
SQL Server is on the other side of town.

Ok Erland,

Thanks for all the discussion. I believe my understanding of SQL Server has
increased significantly in the past couple of days. It's a different mindset
from Access, but once your thinking about things correctly, it's a wonderful
product.

Ian

(1) Almost all of the time, cursors are slower.  Most involve copying
information such as keys or even entire rows into tempdb (meaning more
writes).  There are some exceptions, mostly involving self-joins and
correlated subqueries, but even then you should start out with a set-based
query and try adding indexes before resorting to a cursor.  Cursors should
always be your last resort.

(2) The code to create the command object and add the parameters is minimal,
but necessary.  The few extra cpu cycles required on the client are
negligible, and many times will be offset by better performance and/or
concurrency on the server.  In addition, the single .Seek statement must
generate SQL statements to issue to the server.  This happens under the
covers, but you can see what is actually occurring by starting a profiler
trace and watching what happens when you issue a .Seek.  You may find that
creating the command object and adding the parameters takes far fewer cpu
cycles than issuing a .Seek.


Show quoteHide quote

using UDF in 'IN' clause
The uninstall of sql server 2005 beta and visual studio 2005 is a mess
Consecutive values
Random number generation with seed value
Compute sum of count(*) with group by