Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Microsoft SQL Server Error Log Scanning Expand / Collapse
Author
Message
Posted Sunday, February 13, 2011 3:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
Comments posted to this topic are about the item Microsoft SQL Server Error Log Scanning


Post #1063205
Posted Monday, February 14, 2011 1:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:41 AM
Points: 76, Visits: 345
Very helpful article. I will certainly make use of this.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1063356
Posted Monday, February 14, 2011 1:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
Very useful article. I was looking for something like this for quite some time

Mohammed Moinudheen
Post #1063358
Posted Monday, February 14, 2011 2:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,439, Visits: 1,400
Good one...


Post #1063374
Posted Monday, February 14, 2011 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 5:31 AM
Points: 5, 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
Post #1063378
Posted Monday, February 14, 2011 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:55 PM
Points: 5, Visits: 48
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
Posted Monday, February 14, 2011 6:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 9:36 AM
Points: 1,414, Visits: 4,544
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
Posted Monday, February 14, 2011 7:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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



Post #1063530
Posted Monday, February 14, 2011 7:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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
Posted Monday, February 14, 2011 7:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse