I want to know if there is an event in profiler can catch web application name. If yes, which one? For example, there is SP named sp_storedprocedure that is called by www.web.com and www.web.com/forumdisplay.php?f=23. I want create a profiler trace that can catch www.web.com or www.web.com/forumdisplay.php?f=23 calling sp_storedprocedure. Any suggestion is great appreciated.
I'd like to know if there is a way to catch the error messages when a tasks fails, that's because i's like to store every message on a user variable, so i could log all of them later, I was thinking that it may be possible with the event handlers, could it be?
I have a SQLDataSource bound to a DetailsView. In which event would I catch a Primary Key violation? Would be an event of the SQLDataSource or the DetailsView? And which particular event? A small example would be great. Thx.
We have a package that runs on 3 servers, all based in different countries and time zones. Each package does logging to a common SQL Server based in New York. (We log to the same server to make monitoring of the package run results easier.) We expect that our overseas servers will occassionally fail to make a database connection and have coded a Try/Catch block in our Script task to ignore errors that occur when attempting the SQL Server logging.
The problem is that the OnError event handler defined at the package level is being called whenever a server fails to make a database connection. The Catch block appears to not be called at all. I've included below the short method that does the SQL Server logging. The "Dts.Log" line logs to a SQL Server and usually works. However, when it fails, the "WriteEventLogEntry" method is supposed to be called to write an error to the server's event log. (I haven't included the "WriteEventLogEntry" code but it works fine when called elsewhere in the package to do our non-error event logging.)
Public Sub WriteSqlServerLogEntry(ByVal logMessage As String, ByVal dataCode As Integer, ByVal dataBytes() As Byte, ByVal rowsInserted As Integer)
Try
Dts.Log(logMessage, dataCode, dataBytes)
Catch ex As Exception
WriteEventLogEntry("An exception was thrown while attempting to log the following message to the SQL Server provider: " + logMessage, EventLogEntryType.Error, rowsInserted)
End Try
End Sub
Shouldn't a Catch block prevent the OnError event from being raised? How can I change my package to ignore all errors that occur when attempting SQL Server logging?
hi, i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case. How to catch the resultset of SQLDataSource? -nero
I am backing up my all the databases (around 50 servers and 500 database) using scheduled jobs. I backup my transaction log files at every two hours of interval and databases on daily basis.
I have set option of "Write to Windows Application Event Log" - When job fails during the creation of job in the "Notification Tab" of Create New Job.
As per documentation SQL server automatically records some of the events to windows application event log. After succesful completion of Log backup and DB backup, event gets logged in Windows Application Event Log. And when job fails, I get two events logged in the Windows Application Event Log file, one due to my setting and other automatically by SQL Server.
Due to this Windows event log file is growing much faster and I have to clear it in every 3-4 days.
My quesiton - Is there a way such that to get only failures event logged in Windows Application event log file and any successful backup jobs should not go to Windows application event file?
Can any suggest some idea what should I do?
I am using SQL Server 2000 with SP3 on Windows 2000 Advanced server. Some of my databases are still using Version 7.0/6.5 on NT.
We are starting to work with SSIS in our production environment & due to support issues; we are trying to get rid of the "Package xxx started" log entries inside of the Windows Application Event Log...
So far, I have tried many different things, including setting the LoggingMode to "Disabled", as well as adding a new logging reference with a different destination... All of which still do not get rid of the extra log entries...
[XXXXX] Connecting to remote server XXXXX failed with the following error message : WinRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer. By default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet.
How do I prevent SQL Server 2000 from posting successful backupcompletion messages to the Windows 2000 Application Event Log?I have scheduled jobs which backup my transaction logs on 50+ databasesand it always writes to the Windows 2000 application event log uponcompletion.Due to the frequency of the jobs it only takes a day for theApplication Log to fill up, which is causing other jobs to get hung upwhen trying to write to it. On my Windows 2000 server, I have theapplication log event viewer setting correctly set as:"When maximum log size is reached - Overwrite events as needed" but forsome reason this setting no longer applies like it did for the pastthree years. SQLServerAgent and MSSQLSERVER both run under a localAdmin account, without a domain.When I researched how prevent SQL Server from logging this type ofmessage, I found that I can use sp_update_alert to disable thismessage, but I cannot findthe message_id to correctly disable this message. In sysmessages, themessage I am trying to suppress iserror:18265severity:10dlevel:128description:Log backed up: Database:%1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number ofdump devices: %7!d!, device information: (%8).mslangid:1033I tried calling sp_update_alert as follows:exec sp_update_alert @name = 'Log backed up: Database: %1, creationdate(time): %2(%3), first LSN: %4, last LSN: %5, number of dumpdevices: %7!d!, device information: (%8).', @enabled = 0but got the error message:Server: Msg 14262, Level 16, State 1, Procedure sp_update_alert, Line105The specified @name ('Log backed up: Database: %1, creation date(time):%2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!,device inf') does not exist.Looks like it can only handle 128 characters.How can I disable this message from being logged in the ApplicationLog? Or alternatively, how can I get the event viewer to behave asexpected and"Overwrite events as needed"?Thanks,Mike Orlando
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception? b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement
my problem catch does not catch the error. There is no way to find out teh what is causing this error
SqlCeConnection sqlcon = new SqlCeConnection("
Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"
);
SqlCeCommand sqlcmd = new SqlCeCommand();
sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');
sqlcmd.Connection = sqlcon;
SqlCeDataReader sqldr = null;
try
{
sqlcon.Open();
//use nonquery if result is not needed
sqlcmd.ExecuteNonQuery(); // application crashes here
}
catch (Exception e)
{
base.myErrorMsg = e.ToString();
}
finally
{
if (sqlcon != null)
{
if (sqlcon.State != System.Data.ConnectionState.Closed)
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.) Replication past two months working fine, Now 1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber). Iam attaching thw file. Thanks Sandeep
See also -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
The above behaviour is as described in BOL, but comparing to other languages such as Java, C#, PL/SQL I would expect that catch always catch unless you take the power from the machine, special in the case for "catchnonexistingproc". Remark the different behaviour compared to "nonexistingtable" Documentation shows not example for non existing proc! That catch doesn't catch "is as bug". So is the catch of non existing proc a bug in the bug? ;^) I hope not, because it is the behaviour we want...
I want to use a try/catch to catch any error with this datareader so if no data then exit sub, eventually I will create custom error pagebut for now I just want to exit sub so following sql statements are not executed. I get the following problem when I try to close connection.this was not a problem before using try and catch, do I have in right place? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'declare vars Dim iPreqid As String = CStr(Session("iPreqid")) Dim sql As String = "select * from Vrequest where req_id = '" & iPreqid & "'" 'Connection String value Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString 'Create a SqlConnection instance Using myConnection As New SqlConnection(conn) Dim myCommand As New SqlCommand(sql, myConnection) 'declare sqldatareader Dim dbReader As SqlDataReader Try myConnection.Open() myCommand.ExecuteNonQuery() dbReader = myCommand.ExecuteReader 'Initialize reader dbReader.Read() 'assign reader index values using column names to controls Label8.Text = CStr(dbReader("User_Name")) Label11.Text = CStr(dbReader("NHI")) Label9.Text = CStr(dbReader("Event_Number")) Label10.Text = CStr(dbReader("ACC_Number")) Label7.Text = CStr(dbReader("Required_Date")) Label1.Text = CStr(dbReader("Title")) Label3.Text = CStr(dbReader("address1")) Label4.Text = CStr(dbReader("Birth_date")) Label2.Text = CStr(dbReader("surname")) Label5.Text = CStr(dbReader("home_phone")) Label21.Text = CStr(dbReader("firstname")) Label20.Text = CStr(dbReader("delivery")) Label22.Text = CStr(dbReader("ward")) Label23.Text = CStr(dbReader("request_date")) Catch ex As Exception Session("exception") = ex exit sub End Try End Using 'if connection not closed then close If (Not conn Is Nothing) Then myConnection.Close()<---------------------------------------->Name 'myconnection is not declared' myConnection.Dispose()<---------------------------------------->Name 'myconnection is not declared' End If
Hi, Here is the code:Try If DS.Tables(0).Rows.Count = 0 ThenResponse.Write("Cannot delete an unexist row!") Response.End() End If DS.Tables(0).Rows(0).Delete() Catch BUG as Exception Response.Write(Bug.Message) End Try I removed all the connection stuff.. What I don't understand is why if ds.table(0).rows.count is zero I still get Bug.Message? I did Response.End ... Thank you.
Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs) Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label) Try
Catch sqlEx As SqlClient.SqlException If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then label2.Visible = True label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it" e.Cancel = True End If End Try End Sub Hi, Im using vb.net sql2005 and visual studio 2005 I have 2 tables which have a foregin key relationship. When i try to delete information from within one of my aspx pages it rightly comes up with an application errror, something along the lines of DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_callScore_agentType'. The conflict occurred in database 'Merlin_####', table 'callScores', column 'typeID'.The statement has been terminated. I have looked around and can see people talking about using a try catch excpetion however i need to know how id implement this using the detailsview1_itemdeleting event. Ive never used this before and havent found a decent tutorial to help.So far i have this code but im stuck as im not sure that this is correct but more importantly what i put in the try method. Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs) Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label) Try (WHAT GOES HERE) Catch sqlEx As SqlClient.SqlException If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then label2.Visible = True label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it" e.Cancel = True End If End Try End Sub Your help would be greatly appreciated
I have the following stored procedure which is added to the windows scheduler. When it is run I'm getting an "Invalid use of null error", therefore I need to capture and any errors but I'm not sure how to do this. Can someone help? Thanks.
Code:
CREATE PROCEDURE [dbo].[usp_Reminders] AS
DECLARE @ReminderSent datetime DECLARE @getRecords CURSOR DECLARE @err int
SELECT E.[RL Staff No], E.Forename, E.Surname, C.CourseName, V.CourseDate , E.Email, V.ReminderSent FROM empdetails.dbo.v_Employee E INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No] INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode WHERE V.Completed Is Null AND V.ReminderSent Is Null AND V.CourseDate <= dateadd(dd, -3, getdate()) order by e.[rl staff no]
SET @getRecords = CURSOR FOR
SELECT V.ReminderSent FROM empdetails.dbo.v_Employee E INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No] INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode WHERE V.Completed IS NULL AND V.ReminderSent IS NULL AND V.CourseDate <= dateadd(dd, -3, getdate()) ORDER BY e.[rl staff no]
OPEN @getRecords FETCH NEXT FROM @getRecords INTO @ReminderSent WHILE @@FETCH_STATUS= 0 BEGIN UPDATE Validation SET ReminderSent = GetDate() WHERE CURRENT OF @getRecords FETCH NEXT FROM @getRecords INTO @ReminderSent END
HI, i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin begin try insert into dbo.Test values (1,'aaa') -- here we are inserting int value in identity field... END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
Whereas the following block works fine and the Catch block executes.
Begin begin try Select 1/0 --This causes an error. END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
BEGIN TRY INSERT INTO ORDERS VALUES( 1122,'BAC123' ); INSERT INTO ORDLINE VALUES( 1122,991,1 ); PRINT 'VERY GOOD' END TRY BEGIN CATCH PRINT 'FALSE' INSERT INTO LOGTAB ('BAC123',1122,991,1, ERROR_NUMBER,ERROR_MESSAGE); END CATCH
i am getting error: Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'ERROR_NUMBER'.
hi there i have 2 questions about the TRY/CATCH statement that i have written below. any advice from more experienced persons would be highly appreciated. thankyou.
1. re the @ErrorMessage variable, i have seen this declared with a datatype of varchar(MAX), nvarchar(2048) and nvarchar(4000). which is the correct one for me to use?
2. do i need the statement 'RETURN @ErrorCode' at the end or is this redundant?
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
DECLARE @ErrorCode int, @ErrorMessage nvarchar(4000), @ErrorSeverity int
Is there something like exception handling in T-SQL?For example, how to catch an error of convertion at thissample:CREATE PROCEDURE SP@param VARCHAR(50)AS BEGINDELCARE @var INT-- try {SET @var = CONVERT( int, @param)-- } catch (error#245) {-- handle an error right here-- }ENDIt must be invisible for a caller of SP if something wrong inside SP.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi everyone, I am using an SQL extended stored procedure to send emails in aDTS package using a cursor that goes through each row in a table.Email sending code below======================exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'======================Fetch Next From EmailCursor ...Now the problem I have is that if an individual email address in invalidthen an error occurs and the whole DTS package falls over. What I would liketo be able to do is "catch the error", something like this (C# code used asexample)try{exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'} catch {exec master.dbo.xp_smtp_sendmail@FROM = "arealaddress@mybusiness.com",@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'}Is this possible??? Normally I would do all the email validation before theemail is entered into the database but unfortunately, I do not have accessto the application code so I am stuck doing it this way.Thanks in advanceMark