Remove SQL Messages in Query Result (SQL Server 2005)

Question

Post reply

Remove SQL Messages in Query Result (SQL Server 2005)

Ramesh-585279

Old Hand

Points: 356
More actions
October 15, 2008 at 6:01 am

#383090

We have a stored procedure that restores a database, it returns a single column called "Return" that would contain a 1 or 0: but the procedure also returns messages:
10 percent processed.
20 percent processed.
30 percent processed.
...
...
When this procedure is call through sqlexecute(). It returns SQL_ERROR with the message:
Error: SQLState=01000, Native=3211, MsgTxt=[Microsoft][ODBC SQL Server Driver][SQL Server]10 percent processed.
Is there any way to ignore these messages and make sqlexecute() return success. Thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 740241 More actions · Answer 1

I don't think you can prevent this. The thing to do is deal with it in your application somehow.

http://msdn.microsoft.com/en-us/library/ms178615(SQL.90).aspx

Shows the default is every 10%, but you can set a STATS= in there to do something else.

Ramesh-585279 Old Hand Points: 356 More actions · Answer 2

Executing the stored procedure using SQL query analyser returns 1. But the problem is only when executing with sqlexecute() function.

Steve Jones - SSC Editor SSC Guru Points: 740241 More actions · Answer 3

It returns the values in Query Analyzer as well, they're in the messages tab

Ramesh-585279 Old Hand Points: 356 More actions · Answer 4

My application just passes command like "exec stpprocedure" etc which inturn calls sql functions like sqlexecute(). I use "exec stpprocedure" command in my application which inturn triggers sqlexecute() fucntion. sqlexecute returns SQL_ERROR. I feel the problem is with the sqlexecute() function which should not return error. I even tried with {call stprocedure} with output parameters, then sqlexecute() returned SQL_SUCCESS_WITH_INFO but no value stored in output parameter. In my application i can only play with the return values of sqlexecute() function but if it returns error, i wonder how to handle. can you please suggest is there any way or sql command to call this stored procedure.