Which Event Can Catch Web Application Name?

Dec 12, 2007

Hello, everyone:

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.

Thanks

ZYT

View 2 Replies


ADVERTISEMENT

How To Catch Messages On Event Handlers

Oct 19, 2006

hello everyone

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?

regards

View 3 Replies View Related

Event To Catch A Primary Key Violation In A DetailsView?

Apr 23, 2008

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.

View 1 Replies View Related

Will OnError Event Fire If I Use Try/Catch In Script Task?

Feb 4, 2008

Greetings,

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?

Gracias!

BCB

View 5 Replies View Related

To Catch The Event When There Is Empty Dataset As A Result Of A Query In Sqldatasource

Aug 1, 2007

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

View 1 Replies View Related

SQL Server Events Logged In As Windows Application Event

Jul 10, 2003

Hello Everyone,

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.

Thanks.

View 5 Replies View Related

SQL Tools :: Disable Event Logging To Windows Application Log?

Oct 6, 2006

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...

how to get this done?

View 7 Replies View Related

SQL Server 2008 :: Reading Application Event Log To Remote Computer

May 1, 2015

I have a remote server where I dont have RDP access.

I opened Powershell and wrote the below query to read some of the message, but getting error:-

Clear-Host
$Machine = "XXXXX"
#Get-Eventlog -Logname System -ComputerName $Machine -newest 1000 | Where-Object {$_.EventID -lt '100'} |

Error Message:

[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.

+ CategoryInfo : OpenError: (XXXXX:String) [], PSRemotingTransportException
+ FullyQualifiedErrorId : WinRMOperationTimeout,PSSessionStateBroken

How to read the data (app/system eventviewerlog) using xp_cmdshell from SQL Server Mgmt studio itself?

View 2 Replies View Related

How Do I Prevent SQL 2000 From Posting This Message To The Event Viewer - Application Log

Sep 12, 2005

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

View 9 Replies View Related

Try Catch Doesn't Catch Errors Inside A Data Flow Transformation Script Component

Feb 15, 2007

Hi,

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?

Hope someone could help.

View 3 Replies View Related

Try Catch Does Not Catch Exception

May 15, 2007

hi all,



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)

sqlcon.Close();

sqlcon.Dispose();

}

}//end of finlally

View 4 Replies View Related

Recovery :: Configure Extended Event To Trigger A Mail Whenever Any Event Occurs

Jun 2, 2015

Recently we migrated our environment to 2012.

We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.

Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.

I have gone through so many websites but i never find.

View 13 Replies View Related

DB Engine :: Event Tracing For Windows Failed To Send Event

Oct 25, 2011

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.

View 2 Replies View Related

WMI Event Watcher Task Continual Firing Event When Not Triggered

Apr 8, 2008

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 ?

View 2 Replies View Related

Help - Security Event Log Posts Error Event ID 560 Every Few Seconds

May 31, 2007

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

Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015



Applied the following fix

http://support.microsoft.com/kb/907460/


Now we are getting



Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
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,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D

View 4 Replies View Related

Query Or Script To Get The Event Viewer Event Properties?

Nov 2, 2007



Hi all,


Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?

Scenario:

>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application

click on one event.We can get the log info which is the manual procudure.

But now i want to get the event properties through the Query analyzer...

Any help would be great?


Thanks,

View 4 Replies View Related

EVENT ID 18456 STATE: 16 Login Failed For User 'DOMAIN/user'. [CLIENT: &&<local Machine&&>] Every Minute In Event Log.

Oct 22, 2007

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!

David

View 5 Replies View Related

Distrib.exe Application Error , Application Failed To Initialize Properly(0xx0000142)

Apr 13, 2008

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

View 1 Replies View Related

Try Catch

Jan 15, 2007

Hi

for MS SQL 2000
how can I do ?:


INSERT INTO [Users] (Name)
SELECT Names FROM OtherUsers


I am having an UNIQUE INDEX on [Users].Name

how can I avoid an error ?
if the [Users].Name allready exists I want to jump over the error and keep on inserting

thank you for helping

View 14 Replies View Related

TRY CATCH

Apr 22, 2008

Is it any better than using @@ERROR?

It doesn't seem so

Any opinions?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View 4 Replies View Related

Try Catch

May 13, 2008

Hi!

I'm trying to use Try Catch together with transaction handling but it seems like it doesn't do Rollback although I produce an error.

It looks something like this:


DECLARE @soID INT
BEGIN TRY
BEGIN TRANSACTION
UPDATE Serviceobjekt
SET ServiceobjektstypID = 13
WHERE ServiceobjektID = 26555

UPDATE .... 2 more tables

INSERT INTO Serviceobjekt (
Namn...)
VALUES ('XXXX')

SET @soID = @@IDENTITY
INSERT INTO Atgard (
Namn, ServiceobjektID)
VALUES ('sssss',@soID)

COMMIT TRANSACTION
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH


I get after changing some id's in the where clause which I know is wrong I get a result like this:

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

Shouldn't it be 0 rows affected on all if just one update or insert statement is wrong?

/Magnus

View 4 Replies View Related

Try And Catch

May 22, 2008

Hi I use Try catch and transactions in one of my stored procedures.

Inside I make a call to another stored procedure.

Is it possible to roll back the outer sp?

Please advise.

View 1 Replies View Related

Catch Me If You Can / Bug In The Bug?

Oct 7, 2007

Hello!
Try this (-:

BEGIN TRY

SELECT *

FROM NonExistentTable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can' ;

END CATCH ;

/*

results in:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'NonExistentTable'.

statement not printet */

BEGIN TRY

BEGIN TRY

SELECT *

FROM NonExistentTable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can' ;

END CATCH ;

END TRY

BEGIN CATCH

PRINT 'catched?'

END CATCH ;

-- same behaviour

CREATE PROC catchme

AS

BEGIN

BEGIN TRY

SELECT *

FROM nonexistingtable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can'

END CATCH ;

END ;

BEGIN TRY

EXEC catchme ;

END TRY

BEGIN CATCH

PRINT 'catched' ;

END CATCH ;

-- prints "catched"

BEGIN TRY

EXEC catchnonexistingproc ;

END TRY

BEGIN CATCH

PRINT 'catched again' ;

SELECT error_state()

END CATCH ;

-- prints "catched again"





BEGIN TRY

RAISERROR ('catch this' , 10 , 1) ;

END TRY

BEGIN CATCH

PRINT 'hello' ;

END CATCH ;

-- no hello

BEGIN TRY

RAISERROR ('catch this' , 11 , 1) ;

END TRY

BEGIN CATCH

PRINT 'hello' ;

END CATCH ;

-- prints hello



CREATE PROC catchme2

AS

BEGIN

BEGIN TRY

RAISERROR ('catch this' , 10 , 1) ;

END TRY

BEGIN CATCH

PRINT 'CATCH ME IF YOU CAN' ;

END CATCH ;

END ;



BEGIN TRY

EXEC catchme2 ;

END TRY

BEGIN CATCH

PRINT 'gotcha?' ;

END CATCH ;

-- prints only "catch this"





CREATE PROC catchme3

AS

BEGIN

BEGIN TRY

RAISERROR ('catch this' , 20 , 1) ;

END TRY

BEGIN CATCH

PRINT 'CATCH ME IF YOU CAN' ;

END CATCH ;

END ;

BEGIN TRY

EXEC catchme3 ;

END TRY

BEGIN CATCH

PRINT 'gotcha?' ;

END CATCH ;

-- prints "CATCH ME IF YOU CAN" ;-)

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...

Greetings




View 7 Replies View Related

Try And Catch Problem

Feb 26, 2007

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

View 4 Replies View Related

Try,Catch And Response

Jul 2, 2007

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.

View 11 Replies View Related

Help Using Try Catch Exception Please

Mar 25, 2008

  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

View 12 Replies View Related

SP Catch Error

Aug 21, 2007

Hi,

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

CLOSE @getRecords
DEALLOCATE @getRecords
GO

View 1 Replies View Related

Try.. Catch Block

Dec 11, 2006

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





Any idea why is it so?

View 5 Replies View Related

Try..catch Statement

Apr 13, 2008

can someone help me how to corect the query,


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'.

View 2 Replies View Related

Try Catch Syntax

Mar 14, 2007

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

SELECT
@ErrorCode = @@ERROR,
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()

IF EXISTS (SELECT *FROM Users WHERE Username = @Username)
RAISERROR('The username already exists.', 15, 1)
ELSE IF EXISTS (SELECT * FROM Users WHERE Email = @Email)
RAISERROR('The email already exists.', 15, 1)
ELSE
RAISERROR(@ErrorMessage, @ErrorSeverity, 1)

RETURN @ErrorCode

END CATCH

END

View 2 Replies View Related

How To Try-catch 'sp_addrolemember'?

Dec 27, 2006

how to try-catch sp_addrolemember?

View 4 Replies View Related

How To Catch An Exception?

Jul 20, 2005

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!

View 4 Replies View Related

Error Catch In SQL

Jul 20, 2005

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved