SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Microsoft SQL Server Error Log Scanning


Microsoft SQL Server Error Log Scanning

Author
Message
Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 1325
Comments posted to this topic are about the item Microsoft SQL Server Error Log Scanning



James A Skipwith
James A Skipwith
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 743
Very helpful article. I will certainly make use of this.

Cheers, James

James
MCM [@TheSQLPimp]
M&M
M&M
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9656 Visits: 3916
Very useful article. I was looking for something like this for quite some time :-)

M&M
Anipaul
Anipaul
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12565 Visits: 1407
Good one...



hakkie42
hakkie42
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 104
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 :-)
abacrotto
abacrotto
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 61
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.
alen teplitsky
alen teplitsky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12382 Visits: 4674
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
Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 1325
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 :-)


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

Thanks,

Rudy



Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 1325
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



Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 1325
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,



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search