SQL Server Performance Forum – Threads Archive

error log capture in to a table in 2005 failure

I’m trying to put the SQL-ErrorLog in to a table.the same code is working fine in SQLServer2000 where as in 2005 its throwing error. any body can find out what can be the reason….
here is the detail… code to get the errorlog in to a tmptable
use tempdb
declare @my_err varchar(1500)
SET NOCOUNT ON
CREATE TABLE #error_lg (
errortext varchar(1500),
continuerow int)
INSERT INTO #error_lg exec master..xp_readerrorlog
go
Error
Msg 213, Level 16, State 7, Procedure xp_readerrorlog, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

---

Interesting…
Have you looked at what this stored proceudre is returning in each environment?
Here is the output: –In 2005
EXEC xp_readerrorlog
LogDate ProcessInfo Text –In 2000
EXEC xp_readerrorlog
ERRORLOG ContinuationRow
Name
———
Dilli Grg (1 row(s) affected)

---

As mentioned many times, undocumented procedures behaviour and/or format changes version to version…
So you need to modify the scripts as needed… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I would like to know why you are adding the error log content to a table? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

the reason i m capturing the errorlog in to a table is from errorlog i capture the error information -> filter it out as per my need the one i should bother -> put in to a string through cursor -> through scheduled job i m sending to dba to the mail / page from there the dba start looking in to .. to sortit out… needed the exact queries guys… that should put in to the table

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjandba</i><br /><br /><br /><br />needed the exact queries guys… that should put in to the table<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Not sure what you mean by that? Since you already know what this stored procedure returns, you should be able to figure out the query. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">

---

He wants to dump the log into a table then delete the rows he don’t want and then run the queries against the table to send the alerts to dba… Just change your table schema to work…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

i’ve already tried couple of times since it didn’t work (notsure why)did put in forum…by changing the table schema did it work for you…?

---

Are you getting the same error what you got at the beginning? You should not use local temp table if you want to use it to send an email because it local temp tables are session specific where as when you use sp-sendmail command it will be different session…
So use Global temp table (##error_lg) MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Post the query including the create table you are using to get more information where it is failing in SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>