November 25, 2007 at 6:37 am
How can we print system messages using queries.
Say Error 547 from sysmessage table.
The sysmessage table shows following description against 547, but I need to print it with all variable names, just like it appears in results pane of query analyzer.
%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.
November 26, 2007 at 4:49 pm
Do you need to do this programmatically? If not, you could just change your query results to Text mode for easy printing, then run:
SELECT description FROM sysmessages where msglangid = 1033
-- Note that I've limited this to just English messages
If you're looking to do this programmatically, you could loop through all the system messages (up to 50,000, at which point custom messages begin.)
declare @i int, @msg varchar(8000)
set @i = 1
while (@i < 50000)
begin
select @msg = [description]
from sysmessages
where error = @i and msglangid = 1033
set @i = @i + 1
if (@msg <> '') print cast(@i as varchar) + ': ' + @msg
set @msg = ''
end
Rick
townsends.ca
November 27, 2007 at 2:01 am
Mark, I need to capture the error message alongwith all the variables. I want to record this message in my table ErrorsTable along with message number.
sysmessage table gives me coded message while I need it alongwith all the variables. See example below:
SysMessages table shows the following:
%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.
While on results pane I get the following: and this is the one that I want to capture through my storedprocedure whenver this error appears.
Server: Msg 547, Level 16, State 1, Procedure MyProcedure, Line 204
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'AnyConstraint'. The conflict occurred in database 'MyDB', table 'MyTable', column 'C1'.
The statement has been terminated.
November 27, 2007 at 12:15 pm
Ah, yes, I see. From your first post, it seemed like you wanted the parameterized string, not the result.
I don't think you can get the result string in SQL Server 7 or 2000. In SQL 2005 you can use Try/Catch and the Error_Message() function to retrieve it, but in earlier versions you'd have to catch the message at the Application layer (ie: outside the database), then re-insert it into your Errors table.
The only other way I can think of would be to use Profiler's "Save to table" output functionality. Set up a trace that only watches the Error events, filter it to just the stored procedures you want, and set up the Save to table option so it points to your Errors table. I think that's your best solution for this version of SQL Server.
(By the way, who's Mark?)
Rick
townsends.ca
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply