Disable messages in SQL2008 result set

Question

Hi, further to these two questions, is there a way to disable the messages that may get sent along with the resultset in SQL2008?

(please note this is nothing to do with the ANSI_WARNINGS setting. Or NOCOUNT.)

Thanks for any help.

Edit: It's not a problem with compatibility settings or table owners. And it's nothing to do with NOCOUNT. Trust me.

Answer 1

You need the NOCOUNT in the body of the Sproc anyway (I appreciate that you've tested it with and without)

In circumstances like this I get the actual call to the Sproc (either from a Debug in my APP, or using SQL Profiler) and then plug that into SSMS or whatever IDE you use, wrapping it in a ROLLBACK transaction (so it can't accidentally make any changes). Note: Log on to SQL Server, with your IDE, using the same credentials as the App will use.

BEGIN TRANSACTION
EXEC StaffEnquirySurnameSearch @searchterm = 'FOOBAR'
ROLLBACK

and see what you get. Use TEXT mode for output, rather than GRID mode which might hide something

Just to show how I think NOCOUNT shoud be added to your SProc:

CREATE PROCEDURE StaffEnquirySurnameSearch
    @searchterm varchar(255)
AS

SET NOCOUNT ON

SELECT  AD.Name, AD.Company, AD.telephoneNumber, AD.manager, CVS.Position,
    CVS.CompanyArea, CVS.Location, CVS.Title, AD.guid AS guid,
    AD.firstname, AD.surname
FROM ADCVS AD
LEFT OUTER JOIN CVS ON
    AD.Guid=CVS.Guid 
WHERE AD.SurName LIKE @searchterm
ORDER BY AD.Surname, AD.Firstname
GO

I note that you are not prefixing the tables with a database owner (most commonly "dbo") which might mean that there are additional copies owned by whomever and that they turn out to be the default from the applications permissions perspective, although I don't think that will change the resultsets [between SQL versions], However, same thing applies to ownership of the Sproc, and there you might be calling some earlier version, created for a different owner.

Ditto where your Sproc name is defined in your ASP.NET code (which I can't seem to find in your linked question) should also have the owner defined, i.e.

EXEC dbo.StaffEnquirySurnameSearch @searchterm = 'FOOBAR'

Answer 2

Did you change the compatibility level when you upgraded from SQL 2000 to 2008? If it is some sort of backward compatibility warning message that might cure it.

Answer 3

No, there's not a way to disable all messages that get sent along with the result sets. Set nocount on/off doesn't have an effect on these types of messages.

Answer 4

Have you tried running the same CONTAINS query without the "OR"?

i.e.:

SELECT * FROM my_table
WHERE CONTAINS(my_column, 'a monkey')  -- "a" is a noise word

instead of

SELECT * FROM my_table
WHERE CONTAINS(my_column, 'a OR monkey')  -- "a" is a noise word

Answer 5

You can wrap it in a try catch... more info in books online

For example:

CREATE TABLE Test_ShortString(

ShortString varchar(10) NULL

)

begin Try

insert into 
Test_ShortString (ShortString) 
values ('123456789012345')

End Try

Begin catch

--Select Error_Number() as ErrorNumber

end catch