InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 02:03:34 GMT20http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHi,I am testing the script on one server and it works.However it inserts four records for the same event.Any thoughts?AdamMon, 04 Jun 2012 16:38:17 GMTadjashttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]Geoff A (5/30/2012)[/b][hr]try this script instead;query = "Select * from __InstanceCreationEvent" _ & " " & "where TargetInstance isa 'Win32_NTLogEvent'" _ & " " & "and (TargetInstance.Logfile = 'Application' or TargetInstance.Logfile = 'System')" _ & " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" [/quote]Thanks for that, I've given it a try but I'm having the same results.I thought the package might have cached the script or something so I recreated it with the correct script from the beginning, and it's still ignoring non-SQL errors.Really confusing one.Thu, 31 May 2012 03:02:12 GMTphildalehttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxtry this script instead;query = "Select * from __InstanceCreationEvent" _ & " " & "where TargetInstance isa 'Win32_NTLogEvent'" _ & " " & "and (TargetInstance.Logfile = 'Application' or TargetInstance.Logfile = 'System')" _ & " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" Wed, 30 May 2012 04:46:01 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]Geoff A (5/30/2012)[/b][hr]Hi Phil,Only MSSQL logs will be forwarded to the central server. That is a SQL Server Agent function.SQL Server Agent will not forward non SQL Application log entries.The modification to the script will work, but only on the server where the error occurs.Thanks[/quote]Thanks for the quick reply Geoff.I'm having a little trouble trying to grasp the concept. Maybe I'm misunderstanding SQL Server Agent's role in this.I only want to monitor one SQL Server, and write the errors into a database on that server. In this case, all errors are occurring on that one server, the server that is monitoring for errors. But the script isn't picking up non-SQL specific errors. If the script is reading directly from wherever the Event Viewer Application log is, it shouldn't matter that the errors are not SQL specific?ThanksPhilWed, 30 May 2012 04:38:27 GMTphildalehttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHi Phil,Only MSSQL logs will be forwarded to the central server. That is a SQL Server Agent function.SQL Server Agent will not forward non SQL Application log entries.The modification to the script will work, but only on the server where the error occurs.ThanksWed, 30 May 2012 04:24:37 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHi Geoff,Thanks for the articles, they've been a great use and were easy to follow.I've followed through the tutorial and the EventLogStaging table is written to when using the RAISERROR example that you mentioned.However, I want to use this for all SQL Server errors, and I've noticed that other Errors which end up in the Event Viewer Application log are not being picked up by the VB script. I'm not sure why, as to my knowledge the 'where' in the VB script should not exclude them.Here is an example of an error which exists in the Event Viewer Application log but hasn't been picked up by the script:Log Name: ApplicationSource: SQLISPackage100EventID: 12291Level: ErrorUser: DOMAIN\SQLServiceAccountOpCode:Task Category: NoneKeywords: ClassicComputer: SQLSERVER.DOMAIN.LOCALMessage: Package "Package1" failed.And here is the test one which you used in the article, which is being picked up by the script:Log Name: ApplicationSource: MSSQLSERVEREventID: 17063Level: ErrorUser: DOMAIN\myuseraccountOpCode:Task Category: ServerKeywords: ClassicComputer: SQLSERVER.DOMAIN.LOCALMessage: Error: 50000 Severity: 16 State: 1 working greatThis is the select which I am using (I've removed the 'like 'MSSQL%'' from the where):query = "Select * from __InstanceCreationEvent" _ & " " & "where TargetInstance isa 'Win32_NTLogEvent' and" _ & " " & "(TargetInstance.Logfile = 'Application' or TargetInstance.Logfile = 'System')" _ & " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)"Any ideas why other types of errors (which aren't raised using RAISERROR) aren't being picked up by the script?Any help would be appreciated.ThanksPhilWed, 30 May 2012 03:34:10 GMTphildalehttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxhi, this is really a great thing for logging. i have also used the script for this purpose and succesfully runing the code. but i wana one change. this code enter the value into the db table when events trigger. i want that this code should run once in the day at a specifice time and log the whole information. i tried serveral thing but didnt get any thing, i made this change in the queryquery = "Select * from __InstanceCreationEvent" _ & " " & "where TargetInstance isa 'Win32_NTLogEvent'" _ & " " & "and TargetInstance.Logfile = 'Application'" _ & " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _ & " " & "and (TargetInstance.SourceName like 'MSSQL%')"_ & " " & "and (TargetInstance.timewritten = (now))" --- serveral more option also tried without any successFaisal DBAplease any one has any solution on thisMon, 28 May 2012 09:37:26 GMTfaisalsaeedmkhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxQuick question for all gurus....i want to run the job once a day instead of running all the time and dump all the event logs into staging table...any suggestions???Mon, 28 May 2012 04:52:01 GMTperfectillusion29http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxsorry, having trouble quoting the post I wanted to - mine is a few panes back, re the authentication problems. I enabled DTC on the 'sending' server, but it still cannot post to the event log of the Central server. Do I also need to enable DTC on the Central server?Fri, 25 May 2012 11:38:34 GMTbev.bellisiohttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHow do I create a filegroup?Thanks,MarceleneFri, 25 May 2012 10:18:24 GMTmsilverhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxThanks for your quick response. No, I don't have a FILEGROUPFri, 25 May 2012 10:08:00 GMTmsilverhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]msilver (5/25/2012)[/b][hr]I'm using SQL Server 2008 R2. I created the database successfully but when I run the next script, I get an error (Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'PRIMARY'. What am I doing wrong?Thanks,Marcelene Silver[/quote]Do you have a FileGroup named PRIMARY ?Fri, 25 May 2012 09:29:31 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]bev.bellisio (5/25/2012)[/b][hr]I was successful setting this up on the server I wanted to use as Central server. However, when I tried to change one of my other servers' Agent properties to forward events to this server, it doesn't work. I get messages on the remote server's Event log - Unable to open the eventlog on forwarding server 'xxxxx' (reason: Access is denied). (Central server name has been masked). What permissions do I need to give on the Central server to allow the other servers to do this forwarding?Thanks!Bev Bellisio[/quote]make sure that you have enabled the Distributed Transacation Coordinator on the 'sending' server.Under Security Settings you have to Allow Network DTC AccessFri, 25 May 2012 09:26:03 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxI'm using SQL Server 2008 R2. I created the database successfully but when I run the next script, I get an error (Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'PRIMARY'. What am I doing wrong?Thanks,Marcelene SilverFri, 25 May 2012 09:20:26 GMTmsilverhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxI was successful setting this up on the server I wanted to use as Central server. However, when I tried to change one of my other servers' Agent properties to forward events to this server, it doesn't work. I get messages on the remote server's Event log - Unable to open the eventlog on forwarding server 'xxxxx' (reason: Access is denied). (Central server name has been masked). What permissions do I need to give on the Central server to allow the other servers to do this forwarding?Thanks!Bev BellisioFri, 25 May 2012 09:12:14 GMTbev.bellisiohttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxthanks for the reply Geoff....actually i need something similar to what you did with application log i.e without using any tool(i.e log parser) and automate the task in similar fashion....any suggestion please????Fri, 18 May 2012 07:44:23 GMTperfectillusion29http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxits part of SSMSThu, 17 May 2012 08:54:12 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxwhere can i find Query analyzer in sql server 2008 R2 ???Thu, 17 May 2012 08:49:14 GMTperfectillusion29http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]perfectillusion29 (5/17/2012)[/b][hr]help..!!!![/quote][url]http://blogs.iis.net/rakkimk/archive/2008/04/16/how-to-configure-iis-7-0-for-odbc-logging.aspx[/url]Thu, 17 May 2012 08:11:04 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxwill logparser work?Thu, 17 May 2012 08:04:40 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxhelp..!!!!Thu, 17 May 2012 07:59:50 GMTperfectillusion29http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxthank you for the prompt response...we have IIS 7.5....can you point me in the right direction what steps should i take to get IIS logs into our DBA database???thanks in anticipationWed, 16 May 2012 01:56:41 GMTperfectillusion29http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxWith IIS 7.0' , it's super easy because you can choose a data sources to do this. It's a setting you have to install, but it's very straight forward.With IIS 6.0 or lower, it's another article.Tue, 15 May 2012 16:04:00 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxgreat article....geoff i want IIS log events in this database...how can i do that???thanks in anticipationTue, 15 May 2012 15:24:24 GMTperfectillusion29http://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]Geoff A (4/2/2012)[/b][hr]i know, in know.... i will eventually get to it. i promise. should be pretty simple since this is still the method i use for my alerting... thanks for the interest.[/quote]That's great news!And thank you for such a detailed and useful article. You really saved me a lot of time and frustration.I look forward to the next installment.Mon, 02 Apr 2012 11:59:27 GMTdaniel blisshttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxi know, in know.... i will eventually get to it. i promise. should be pretty simple since this is still the method i use for my alerting... thanks for the interest.Mon, 02 Apr 2012 11:28:27 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]guillermob (11/4/2011)[/b][hr]Did you ever write the article about the triggers and store procrdures because I don't find it anywhere?[/quote]Ditto.I'd love to see what other tricks Geoff has up his sleeve.Mon, 02 Apr 2012 10:38:08 GMTdaniel blisshttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxDid you ever write the article about the triggers and store procrdures because I don't find it anywhere?Fri, 04 Nov 2011 11:10:53 GMTguillermobhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxIt worked fine in my SQL Server 2005 machine but I received the following error on my SQL Server 2008 machine.Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.E:\Monitor\EventLog2DB.vbs(15, 1) SWbemServicesEx: Access denied. Process Exit Code 0. The step succeeded.Anybody any idea!?Mon, 25 Jul 2011 07:48:13 GMTremi.boshttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxYour procedure is fantastic...! I congratulate youMon, 03 Jan 2011 11:47:56 GMTandresysblhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHi Geoff,thanks for your fast reply. In that case i must have overlooked something..i will give it another trycpppWed, 01 Dec 2010 22:35:13 GMTcppphttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHi cppp,whatever account is running the VB Script will require access to your database DBA.So, if you followed the instructions, that means your SQL Server agent account is running the code. Wed, 01 Dec 2010 04:57:35 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxGeoff, thank you for sharing this information. i followed the given instructions as well as considered workarounds in case of error. all seems fine but i keep getting this vbscript error cannot open database "'DBA"' requested by login - the login failedcode:80004005source microsoft OLE DB Provider for SQL Server(running sql2005 enterprise edition - logon mixed mode...)any suggestions are welcomeppcpWed, 01 Dec 2010 01:41:27 GMTcppphttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxWhoa! Very cool, Geoff. I've not done a deep dive on the code, [i]yet[/i], but I read the article. Very nicely done.Tue, 16 Nov 2010 01:28:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]Geoff A (11/10/2010)[/b][hr][quote][b]jumarcil (11/9/2010)[/b][hr]Hi Guys;I am getting the same error as Newbie: "invalid query" The DB is created; so is the sql agent job; running in mixed mode. I am using Script engine 5.6; I have notice that you use 5.7. would it make a difference ?Any help appreciated since it really is a nice little app; I manage quite a lot of SQL servers throughout the company and it would really simplify my work.RegardsJules[/quote]the script engine will make no difference.did you see what newbie did to resolve his issue. he created an alias in SQL Server Configuration Manager because he was running a named instance on a non-standard port. Do you happening to be running your SQL Server instance on a named instance or a port other than 1433?[/quote]I will try to create an alias and will verify If my default instance is running on default port 1433 as I have many instance on that box.RegardsJUlesUPDATE :I found why it isn't working on my server. If I used the "like" operator in the query it generates an error.Ex: TargetInstance.SourceName like 'MSSQL%' will generate a script error but TargetInstance.SourceName = 'MSSQL' works fine. I will just create a couple of "or" clause for the amount of instances that I have on the box and It will work fine.ThanksJules Thu, 11 Nov 2010 08:45:12 GMTjumarcilhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]jumarcil (11/9/2010)[/b][hr]Hi Guys;I am getting the same error as Newbie: "invalid query" The DB is created; so is the sql agent job; running in mixed mode. I am using Script engine 5.6; I have notice that you use 5.7. would it make a difference ?Any help appreciated since it really is a nice little app; I manage quite a lot of SQL servers throughout the company and it would really simplify my work.RegardsJules[/quote]the script engine will make no difference.did you see what newbie did to resolve his issue. he created an alias in SQL Server Configuration Manager because he was running a named instance on a non-standard port. Do you happening to be running your SQL Server instance on a named instance or a port other than 1433?Wed, 10 Nov 2010 09:00:44 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxHi Guys;I am getting the same error as Newbie: "invalid query" The DB is created; so is the sql agent job; running in mixed mode. I am using Script engine 5.6; I have notice that you use 5.7. would it make a difference ?Any help appreciated since it really is a nice little app; I manage quite a lot of SQL servers throughout the company and it would really simplify my work.RegardsJulesTue, 09 Nov 2010 14:36:17 GMTjumarcilhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspx[quote][b]mchofman (11/8/2010)[/b][hr]I also found this. I haven't tried it yet, but reading the eventlogs through SSIS is also a very nice method.http://www.softpedia.com/get/Internet/Servers/Server-Tools/SSIS-Event-Log-Source.shtmlThis package provides an SSIS 2005 Data Source component for loading Windows 2003 / XP event logs (.evt) into SQL Server 2005 for analysis.Regards,Marco[/quote]Thanks for the link Marco.the solution here will work on windows 2000 through Windows 2008 R2 and probably beyond....it also will work with SQL 2000 through SQL 2008 R2 and probably beyond. ;-)Mon, 08 Nov 2010 13:08:40 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxI also found this. I haven't tried it yet, but reading the eventlogs through SSIS is also a very nice method.http://www.softpedia.com/get/Internet/Servers/Server-Tools/SSIS-Event-Log-Source.shtmlThis package provides an SSIS 2005 Data Source component for loading Windows 2003 / XP event logs (.evt) into SQL Server 2005 for analysis.Regards,MarcoMon, 08 Nov 2010 12:43:12 GMTmchofmanhttp://www.sqlservercentral.com/Forums/Topic1016895-2831-1.aspxFixed it. I changed Datasource=. to Datasource=localhost in the adodb connection string and now the records are being written to the table when I run the script directly from the command line.I'm assuming it will now work from the Agent which I"ll test now. Thanks very much for the assistance.Mon, 08 Nov 2010 11:05:45 GMTLocker916