Microsoft SQL Server Error Log Scanning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rudy Panigas » Microsoft SQL Server Error Log Scanning

23 posts, Page 1 of 3

1 2 3 »»»

Microsoft SQL Server Error Log Scanning

Rate Topic

Display Mode

Topic Options

Author

Message

Rudy Panigas

Posted Sunday, February 13, 2011 3:39 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 306, Visits: 1,012

Comments posted to this topic are about the item Microsoft SQL Server Error Log Scanning

Post #1063205

James A Skipwith

Posted Monday, February 14, 2011 1:42 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276

Very helpful article. I will certainly make use of this.

Cheers, James

James
MCM [@TheSQLPimp]

Post #1063356

mohammed moinudheen

Posted Monday, February 14, 2011 1:54 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 2,170, Visits: 3,582

Very useful article. I was looking for something like this for quite some time Smile

Mohammed Moinudheen

Post #1063358

Anipaul

Posted Monday, February 14, 2011 2:39 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785, Visits: 1,334

Good one...

Post #1063374

hakkie42

Posted Monday, February 14, 2011 2:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 8:17 AM
Points: 5, Visits: 103

Aren't you missing a

USE [ErrorLogStorage]
GO

in the database creation script? When I ran it, it created the ErrLogData table in master...

Apart from that, a smart solution: why struggle with text files while you have a database Smile

Post #1063378

abacrotto

Posted Monday, February 14, 2011 4:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, March 23, 2013 7:08 AM
Points: 5, Visits: 44

I'm sorry for asking something that simple. Why would you use a Cursor and a loop for updating the server when you can use an SQL UPDATE statement instead ?

Let's see the chunk of code:

-- Cycle through the ErrLogData table and insert the server's name
DECLARE SrvName_Cursor CURSOR FOR
SELECT [SQLServerName] FROM [ErrorLogStorage].[dbo].[ErrLogData] WHERE [SQLServerName] IS NULL
OPEN SrvName_Cursor
FETCH NEXT FROM SrvName_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [ErrorLogStorage].[dbo].[ErrLogData] SET [SQLServerName] = @@servername
FETCH NEXT FROM SrvName_Cursor
END
CLOSE SrvName_Cursor
DEALLOCATE SrvName_Cursor

I understand you are trying to update [ErrorLogStorage].[dbo].[ErrLogData].[SQLServerName] with @@servernave when that same column is null. Right ? Well, there is an UPDATE statement that can do the same thing without the cursor. It looks like this:

UPDATE
[ErrorLogStorage].[dbo].[ErrLogData]
SET
[SQLServerName] = @@servername
WHERE
[SQLServerName] IS NULL

This code is much simple and faster than the cursor one.
What do you think ?

Regards from Argentina, South America.
Ariel.

Post #1063424

alen teplitsky

Posted Monday, February 14, 2011 6:33 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 5:45 PM
Points: 1,408, Visits: 4,504

thx

i've been meaning to add this to my daily data collection. i'll probably just change the process to use SSIS to import data for numerous servers into a central database

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #1063465

Rudy Panigas

Posted Monday, February 14, 2011 7:42 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 306, Visits: 1,012

hakkie42 (2/14/2011)

Aren't you missing a

USE [ErrorLogStorage]
GO

in the database creation script? When I ran it, it created the ErrLogData table in master...

Apart from that, a smart solution: why struggle with text files while you have a database Smile

Yes, you are correct. I guess my "Copy and Paste" skills need more work.
I have updated the code with this correction.

Thanks,

Rudy

Post #1063530

Rudy Panigas

Posted Monday, February 14, 2011 7:44 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 306, Visits: 1,012

abacrotto (2/14/2011)

I'm sorry for asking something that simple. Why would you use a Cursor and a loop for updating the server when you can use an SQL UPDATE statement instead ?

This code is much simple and faster than the cursor one.
What do you think ?

Regards from Argentina, South America.
Ariel.

Thanks Ariel. You are correct, the update command would work better. I should update the code.

Thanks for comments,

Rudy

Post #1063533

Rudy Panigas

Posted Monday, February 14, 2011 7:46 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 306, Visits: 1,012

alen teplitsky (2/14/2011)

thx

i've been meaning to add this to my daily data collection. i'll probably just change the process to use SSIS to import data for numerous servers into a central database

Excellent idea! If you do write an SSIS package for this collection it would be nice to see it here.

Thanks,

Post #1063535

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions