I'm trying to use the SPROC below (courtesy of Erland!) to capture the
error message but it fails owing to insufficient permissions (I can't
reproduce it just now, but I think it's because it can't get access to
the DBCC OUTPUTBUFFER).

How do I give the SPROC permission to execute?

Many thanks

Edward


CREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT AS
DECLARE @dbccrow nchar(77),
@msglen int,
@lenstr nchar(2),
@sql nvarchar(2000),
@s tinyint

-- Catch the output buffer.
CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)
INSERT INTO #DBCCOUT
EXEC ('DBCC OUTPUTBUFFER(@@spid)')

-- Set up a cursor over the table. We skip the first
-- row, because there is nothing of interest.
DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FOR
SELECT col1
FROM #DBCCOUT
WHERE left(col1, 8) <> replicate('0', 8)
ORDER BY col1

-- Init variable, and open cursor.
SELECT @errmsg = ''
OPEN error_cursor
FETCH NEXT FROM error_cursor INTO @dbccrow

-- On this first row we find the length.
SELECT @lenstr = substring(@dbccrow, 15, 2)

-- Convert hexstring to int
SELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'
EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT

-- @s is where the text part of the buffer starts.
SELECT @s = 62

-- Now assemble rest of string.
WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglen
BEGIN
SELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +
substring(@dbccrow, @s + 3, 1) +
substring(@dbccrow, @s + 5, 1) +
substring(@dbccrow, @s + 7, 1) +
substring(@dbccrow, @s + 9, 1) +
substring(@dbccrow, @s + 11, 1) +
substring(@dbccrow, @s + 13, 1) +
substring(@dbccrow, @s + 15, 1)
FETCH NEXT FROM error_cursor INTO @dbccrow
END

CLOSE error_cursor
DEALLOCATE error_cursor

-- Now chop first character which is the length, and cut after end.
SELECT @errmsg = substring(@errmsg, 2, @msglen)
GO

> How do I give the SPROC permission to execute?

Since DBCC OUTPUTBUFFER can only be executed by sysadmin role members, you
must be a sysadmin to use this technique to get the error message. I
believe you will find this disclaimer in Erland's article.

SQL 2005 has structured error handling that allows you can get error details
in a CATCH block without the kludge.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<teddysnips@hotmail.com> wrote in message
news:1151658530.478948.38290@x69g2000cwx.googlegro ups.com...
> I'm trying to use the SPROC below (courtesy of Erland!) to capture the
> error message but it fails owing to insufficient permissions (I can't
> reproduce it just now, but I think it's because it can't get access to
> the DBCC OUTPUTBUFFER).
>
> How do I give the SPROC permission to execute?
>
> Many thanks
>
> Edward
>
>
> CREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT AS
> DECLARE @dbccrow nchar(77),
> @msglen int,
> @lenstr nchar(2),
> @sql nvarchar(2000),
> @s tinyint
>
> -- Catch the output buffer.
> CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)
> INSERT INTO #DBCCOUT
> EXEC ('DBCC OUTPUTBUFFER(@@spid)')
>
> -- Set up a cursor over the table. We skip the first
> -- row, because there is nothing of interest.
> DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FOR
> SELECT col1
> FROM #DBCCOUT
> WHERE left(col1, 8) <> replicate('0', 8)
> ORDER BY col1
>
> -- Init variable, and open cursor.
> SELECT @errmsg = ''
> OPEN error_cursor
> FETCH NEXT FROM error_cursor INTO @dbccrow
>
> -- On this first row we find the length.
> SELECT @lenstr = substring(@dbccrow, 15, 2)
>
> -- Convert hexstring to int
> SELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'
> EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT
>
> -- @s is where the text part of the buffer starts.
> SELECT @s = 62
>
> -- Now assemble rest of string.
> WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglen
> BEGIN
> SELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +
> substring(@dbccrow, @s + 3, 1) +
> substring(@dbccrow, @s + 5, 1) +
> substring(@dbccrow, @s + 7, 1) +
> substring(@dbccrow, @s + 9, 1) +
> substring(@dbccrow, @s + 11, 1) +
> substring(@dbccrow, @s + 13, 1) +
> substring(@dbccrow, @s + 15, 1)
> FETCH NEXT FROM error_cursor INTO @dbccrow
> END
>
> CLOSE error_cursor
> DEALLOCATE error_cursor
>
> -- Now chop first character which is the length, and cut after end.
> SELECT @errmsg = substring(@errmsg, 2, @msglen)
> GO
>

Dan Guzman wrote:

> > How do I give the SPROC permission to execute?
>
> Since DBCC OUTPUTBUFFER can only be executed by sysadmin role members, you
> must be a sysadmin to use this technique to get the error message. I
> believe you will find this disclaimer in Erland's article.
>
> SQL 2005 has structured error handling that allows you can get error details
> in a CATCH block without the kludge.

The SPROC executes in a Job, which is owned by SA, which is a member of
the sysadmin role. Where am I going wrong?!?

Unfortunately, SQL2005 is not an option.

Edward

(teddysnips@hotmail.com) writes:
> The SPROC executes in a Job, which is owned by SA, which is a member of
> the sysadmin role. Where am I going wrong?!?

Probably in the assumption in the ownership of the job.

Could help if you posted the complete error message.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx