DBCC OUTPUTBUFFER

[SADAF]

سلام دوستان

همان طور که می دانید دستور "DBCC OUTPUTBUFFER" بافر خروجی شما یا به عبارتی اطلاعاتی که از سمت سرور به سمت کلاینت آمده را به شما در قالب هگز و اسکی می دهد. حالا می خواهم بدانم که چگونه ما می توانیم از این اطلاعات استفاده کنیم یا به عبارتی ساختار خروجی این دستور که به شکل ساخت یافته و مرتب است چه اطلاعاتی را به ما می دهد.
با تشکر از تمامی دوستان./

[حمیدرضاصادقیان]

http://msdn2.microsoft.com/en-us/library/ms189821.aspx

http://www.sqlmag.com/Article/ArticleID/22069/sql_server_22069.html

[SADAF]

سایت MSDN که خیلی کلی توضیح داده و مشکل ما را برطرف نمی کند و سایت دیگری که معرفی کردید متاسفانه پولی است و فقط چند خط اول را به طور مجانی در اختیار ما قرار داده!

در واقع به دنبال این هستیم در نوشته هایی که در قسمت MESSAGES در QUERY ANALYZER بعد از اجرای دستورات ایجاد می شود را به نحوی بدست اوریم . با توجه به اینکه در MSDN نوشته این دستور اطلاعاتی که از سمت سرور به سمت کلاینت می اید را نشان می دهد حدس می زنم که در این مورد بتواند به ما کمک کند . اما متاسفانه خروجی این دستور واضح نیست و یک سری عدد به ما برمی گرداند.

حالا در این زمینه اگر کمکی بتوانید بکنید ممنون می شوم.
با تشکر از همه.

[حمیدرضاصادقیان]

ببین این لینکها کمکی بهت میکنه؟
http://doc.ddart.net/mssql/sql70/dbcc_14.htm
http://doc.ddart.net/mssql/sql70/ta-tz_7.htm
http://www.sql-server-performance.com/articles/dba/traceflags_p1.aspx



Capturing the Error Description in a stored procedure
How do you accomplish error handling in a stored procedure? 99.99 % of people will answer use @@ERROR. How do you capture actual error message of the error raised by SQL Server within a stored procedure? Such as:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

The statement has been terminated.
Again 99.99 % of people will answer not possible in TSQL. (or wait for Yukon/SQL 2005)

But,

What do you do if you really want this error message to be available for your systems? Suppose you want to log this error message in to your own log tables, what do you do?

Well, I cam across this situation in one of my recent projects. The requirement was to have a stored procedure running as a batch job. This procedure would collect and transform a large amount of data in a cursor. Results of transformations for each cursor fetch were to be logged into a SQL Table. In case of any errors during the transformation was also required to be logged. The cursor, the transformations, the logic and the data volume were not a big problem. The biggest question was how do I capture Error Description of error raised inside a stored procedure and assign it to a variable.

Some of other SQL professionals also have put up this question at various sites on the internet. On one of the site, somebody put up an idea and a script using DBCC OUTPUTBUFFER. Logic was to read DBCC OUTPUTBUFFER and then cleans it up for readability. However this process is slightly unreliable as even PRINT messages fills DBCC OUTPUTBUFFER.

Second Idea that came to my mind was to use sp_Oa procedures and use dynamic SQL along with SQL-DMO to capture error messages. This was a good idea but It would have made entire SP logic far to complicated.

Third idea was to use strings stored in sysmessages & replace the placeholders. This idea also got dropped very quickly.

So what do I do? While exploring sysmessages table, I remembered sp_altermessage stored procedure. The stored procedure allows DBA to configure the errors to be logged to “SQL Server Error Log”. This suddenly gave me an idea, Configure Most Likely Errors to be logged and then read them of the Error Log. I configured Errors 515, 547, 2601, 2627, 3902 with sp_altermessage to be self logging. So every time there is a Constraint Viuolation (PK, FK, NOT NULL, UNIQUE), the error was logged inside SQL Error log. Like this:

2004-04-05 12:39:37.68 spid61 Error: 515, Severity: 16, State: 2
2004-04-05 12:39:37.68 spid61 Cannot insert the value NULL into column 'PkId',
table 'TESTDB.dbo.testtable'; column does not allow nulls. INSERT fails..
I wrote a stored procedure, which requires an number (Error number captured with @@ERRROR). It reads the SQL Error log and finds out the last error with the error number passed for current spid. A Quick SQL programming allows me to select Error Description lines and join it and send it back as an output parameter.

I EXECUTED FOLLOWING

Set xact_abort off -- required !

Declare @intError INT
Declare @VcDescr VARCHAR(1000)

Insert testtable (PkId ) select null -- expected error as above
Select @intError = @@ERROR

If @intError <> 0
Begin
Exec GetErrorStringSP @intError, @VcDescr OUTPUT
Print @VcDescr
End
And bingo, it worked.

You can download the code here.

Now some downsides of this approach:


I needed to turn on SERVER WIDE settings to turn logging on, which might be slightly troublesome for SQL. Also it increases the SQL Error log size quickly. I increased the total number of error logs for the system to 60 and put a job to cycle SQL Error logs daily midnight.
I needed to give rights to a non sa login to be able to execute xp_readerrorlog.
A technical problem that I purposely kept open in my SP was to select last error that might have occurred. Consider this situation, for SPID=50, Error = 515 has occurred and logging has been turned off after that error has occurred. After 2 hours also this error would continue to be in the SQL Error Log. But no new error descriptions will appear in SQL Error log (it is off now). Then this SP will still throw the error description same as previous one. I could well have made it to look for errors in last 10 seconds. But it was not required in my case.
This effort has seen my project requirements through. I hope it will be useful for many more of SQL professionals.

[SADAF]

متاسفانه دوست عزیز سایت هایی که معرفی کرده بودی در مورد این مشکل کمکم نکرد ولی باعث شد مطلب جدیدی یاد بگیریم .
خودم خروجی dbbcc outputbuffer رو خیلی بالا و پایین کردم تا در انتها موفق شدم به نحوی به دستورات خروجی که توسط دستور print ایجاد می شود را از خروجی به دست آورم .به این نحوه که اگر شما دستور خروجی را dbbcc outputbuffer را ملاحظه کنید متوجه می شود این خروجی از 3 بخش تشکیل شده، بخش اول(8 کاراکتر اول) شماره هر ردیف است که به طور هگز نمایش داده شده . بخش دوم (از کاراکتر 12 تا 54 ) شامل 16 تا عدد است که هر عدد شامل کد اسکی یک کاراکتر در مبنای هگز است. بخش سوم (از کاراکتر 55 تا آخر) شامل کاراکتر های بخش دوم است.
در این خروجی که ما داریم هر وقت 01 00 00 00 00 00 به این عبارت برسیم نشان می دهد که که از دستور print استفاده شده و طول عبارت خروجی دستور print در عدد دوم بعد از عبارت
01 00 00 00 00 00 به صورت هگز نوشته شده است.

حالا مشکل من این است که خیلی زود این بافر پر می شود و من نمی توانم تمام خروجی های print را که در یک sp بزرگ صدا می زنم داشته باشم.
راهنمایی می خواهم که چطور می توانم بافر را افزایش دهم.

[SADAF]

یعنی واقعا کسی پیدا نمی شه که جواب سوال من را بده