Supress warning messages in SQL2000

[Welshbird]

Hello chaps!

I've written some sql queries that get run weekly and exported to a tilde-delimited rpt file.

Some of the scripts give me an warning message about my null values being aggregated. Now I'm happy that they are - that's why I had grouped the query!

But having 'trained' the recipients of these reports to accept the tilde-delimited files rather than demanding an xls the last line of the file contains this warning message.

So how do I suppress this message then so that it doesn't end up in the file?

Thanks in advance,

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[ousoonerjoe]

can't you remove the NULLS all together with something like:

SELECT [Field1] = ISNULL(field1, 0) FROM TableA

Use 0 for numbers and '' for strings/chars.


"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Welshbird]

I guess I could, but so far I have something like 489 lines of SQL accessing 9 tables.... and if I could wrap the whole thing in a 'Do not output warnings' that'd be so much simpler for me.

A bit like SET SERVEROUT OFF in Oracle I guess.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[bborissov]

SET ANSI_WARNINGS OFF

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Welshbird]

I tried that and I get the following message:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Am I missing something?


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Any one else got any bright ideas? I'm going on holiday for a week and I'd love to get these run without the warning messages for the time I'm away..

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Thargy]

Willif,

I have to say, don't suppress warnings - that's a sure-fire wasy to eventually stuff up.

What I do is always print every warning, and make my scripts run correctly, so they produce neither errors nor warnings. If (for legitimate reasons) this is not possible, then in the documentation which accompanies the script/file/report (because there is of course such documentation - right?) you should specifically mention these warnings.

The user guide should state that they are a correct annunciation of aggregation occurring within the script, and they should always be present. Users should be advised that if these warnings do not appear, the relevant administrator/developer should be contacted immediately. Likewise, if any new warnings arise, you should be contacted.

Suppressing warnings is a bit like the boyos at 3 mile island, Chernobyl etc. who did something similar......



Regards

T

 

[ousoonerjoe]

thargtheslayer is right, you really need to address the warnings rather than hide/suppress them. Based on your original posting, a simple NULL check and replace would correct all your warnings.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.