Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-21 : 09:01:38
|
| lana writes "Hello, team. I would like to know if there is a way to tell after a stored procedure has been called and successfully executed from ADO whether any data modification took place during the execution. (SQL Server 2000). Thank you." |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-10-21 : 09:23:46
|
| Every procedure returns how many records were afected during any kind of data manipulation (insert, update, delete). I don't know the exact sintax how to use it in the GUI, but I'm sure, it's in a book about ADO somewhere.Lookup BOL under SET NOCOUNT. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-21 : 12:15:57
|
| You would usually put this test into the SP and have an output parameter for the result.update tblset ...select @rowcount = @@rowcount, @error = @@errorif @rowcount > 1 and @error = 0set @outputrc = 1note that the @@rowcount may not work if you have a trigger.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
LLatinsky
Starting Member
38 Posts |
Posted - 2002-10-23 : 15:55:06
|
| I probably wasn't clear. I can't make any modifications to existing stored procedures or I would just include a character in their name to say whether the stored procedure does any inserts, deletes or updates or just selects. I actually don't care how many records were affected. Even if the 'where' clause filtered out all the rows the procedure still remains a data modification stored procedure.(Maybe there is a way to tell by querying some system table?)Thank you. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-23 : 16:12:38
|
| I'm not trying to be a smartass, but if you're calling the stored procedures from ADO, don't you know what they do? Or is there conditional logic in the procedure that may or may not perform an UPDATE depending on the circumstances? I'm not clear about this. I admit I'm very surprised if you're calling stored procedures without knowing what those procedures do.In any case, there's no system table that will tell you whether a modification occurred, but you could create a trace in SQL Profiler to look for certain SQL operations. However, accessing the trace information from ADO will be difficult, if not impossible, and identifying a particular trace to the recently executed procedure will also be difficult if not impossible. |
|
|
|
LLatinsky
Starting Member
38 Posts |
Posted - 2002-10-23 : 16:48:15
|
| I don't blame you. I would be surprised too if I didn't know the details. It's needed for a different application that receives just a command string with a stored procedure name and a list of parameters after it had been successfully executed on a server. Anyway, thank you for your reply |
|
|
|
|
|
|
|
|
Difference between data modification and data extraction sps