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 - 2006-08-10 : 09:44:37
|
| Henry writes "Hello All,My front end is VB, back end is SQL Server 2k.I have a stored procedure, from which I need to get the message.In it there r lot of Inserts, Updates etc.I added code to rollback or commit depending on an error occured or not.All working correctly except :the message "Successfully Completed" is unable to be displayed in client side.I'm having a statementSelect 'Successfully Completed' in the Commit Tran ... partTrying to use it in VB side by rs.open "Exec TestStoredProc" msgbox rs(0)but it returns an errorIf I comment out all the Inserts & Updates in the Stored Procedure, then it works correctly.Any suggestion to have the Inserts / Updates etc & to get the message as well ?Thankyou for your time" |
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-08-10 : 09:50:22
|
| Declare one variable in SP i.e. @Messageafter ur insert, update statement assign message to this variable.e.g. Set @Message = "Transaction Successful!!!" and then usePRINT @Message at the end of ur SPMahesh |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-08-10 : 10:39:02
|
Ensure that Set Nocount is set to On at the start of the procedure.Assign message to a variable in the Commit Tran part like this..set @Message = 'Successfully Completed' and as a last statement of the SP, write this...select @message as Message If this doesn't work, try using OUTPUT parameter and return message in that parameter instead of recordset.Mahesh, your suggestion will work on back-end side but message will not be returned to the client-side which Henry wants.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-10 : 12:48:42
|
| Handle all messaging at Client which is easy and effecientMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Return value from a stored procedure