VFP 8.0 - SQL Server

[JoeReed]

I need to execute an UPDATE query against SQL Server up to 7000 times and I wanted to use the SQLPREPARE command to speed things up. The problem is I need to know how many rows were affected by each update. Is there away to get this information?

I can not use "SELECT @@ROWCOUNT AS affectedRows" with this connection as it will affect the SQL Prep command and I can not use another connection to get this info.

Should I just create a stored procedure?

Any help would be appreciated.

 

[rgbean]

I believe if you set up two separate connections, then you wouldn't lose any SQLPREPAREd data. While I haven't tried this, it makes logical sense that different connections wouldn't "step" on each other.

Rick

 

[zhavic]

it makes logical sense that different connections wouldn't "step" on each other

It's right, but it also means, that 'SELECT @@ROWCOUNT AS affectedRows' will not return number of rows affected by another connection .

Which type of updates you want to do ?
Has you some data in local variables or tables ant want to update rows in server database ?
Can you use stored procedures ? <-- this may improve performance, but it depends of what you want to do

Can you post there more information of that updates you want to do ?

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Mike Lewis]

Rick,

I believe if you set up two separate connections, then you wouldn't lose any SQLPREPAREd data.

The problem with that is that @@ROWCOUNT is scoped to the current connection, so it won't return the desired information for the 'other' connection.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[rgbean]

Mike,
Yup I knew that - I'm not sure what I was thinking when I made that reply!

I think Joe is right when he suggest that the best way to handle it is to create a stored procedure that provides both functions he needs.

Rick

 

[JoeReed]

Just an FYI that the Stored Procedure is working well...

Thank you for your responses, I just wanted to see if there was a different way of handling this.