Stored Proc not updating multiple rows

#11

EmbersFire (embers_fire (AT) fastmail (DOT) fm) writes:

Quote:

I'm using a stored proceedure which should update a number of rows in a
table depending on a key value supplied (in this case 'JobID'). But
what's happening is when I call the proc from within the program, only
one row gets updated.

So

When I call the proc from Query Analyser, all rows get updated.
When I call the proc from within the program, only one row gets updated

That's funny. A SET ROWCOUNT in effect sounds like the most like, but
it appears that you have already ruled this out.

Could there be trigger on the table? (Although that would be a strange
trigger.)

One thing, though:

Quote:

.Execute

Make this

.Execute ,,adExecuteNoRecords

this can save you from a few funny surprises in other situations. Use
this option whenever you run a command that does not generate any result
set.

And have you double-checked that you run the VB-app against the same
database as you run QA? And have you made sure that there is only one
instance of the table and the stored procedure?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#12

There are no triggers operating on the table.
I've checked that it's running against the correct database
I was told that it could be a problem with the index for the table,
although when I checked, there were non specified for it.

#13

EmbersFire (embers_fire (AT) fastmail (DOT) fm) writes:

Quote:

There are no triggers operating on the table.
I've checked that it's running against the correct database
I was told that it could be a problem with the index for the table,
although when I checked, there were non specified for it.

There should at least be a primary key, one would hope. You could try
DBCC CHECKTABLE to see if it uncovers any corruption, but I would not
really expect it.

Without a reproducible scenario, and no access to your system, it's very
difficult to diagnose.

Another things to try is to create a new table and a new stored procedure
to go with it, and load the table with same data, and see if this behaves
in the same way.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#14

I ran the DBCC CHECKTABLE command and if brought up no errors.
The JobID field is a foreign key that points to the JobID field in
another table called tbl_Jobs.
I may try creating a new table as you suggested and see what happens
with that.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Stored Proc not updating multiple rows

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Stored Proc not updating multiple rows - 05-05-2005 , 05:20 PM

Re: Stored Proc not updating multiple rows - 05-06-2005 , 04:29 AM

Re: Stored Proc not updating multiple rows - 05-06-2005 , 06:27 AM

Re: Stored Proc not updating multiple rows - 05-06-2005 , 07:11 AM