Getting Notification / Alert When Failover Occurs
Mar 8, 2007
Hi all,
What kind of notification / alert can I get when a failover occurs?
I need to refresh SqlDependency after a failover.
Thanks,
Avi
ADVERTISEMENT
Unable To Get Notification When Deadlock Occurs
Jun 26, 2006Hi,
I am facing this problem with SQL Server 2000 as well as with SQL Server 2005. I want to get notified when a deadlock occurs. This is what I have done.
I have setup an SQL Server Agent alert for the error number 1205. The alert is enabled and in response to the alert I am invoking a job and also tried email and net send etc.
Now I simulated deadlock situation by using two connections and received the 1205 error. However when I go back to the alert and see it does not invoke the job nor send message nor email. I find the number of occurrances of the alert as 0 which means that the alert never occurred. Why am I not able to trap the 1205 error message and get a notification?
I understand that I can enable trace flag 1204 and trace flag 3605 to get the deadlock event and the complete description logged to the sql server error log. But my question is why am I not able to use the 1205 event in the sql server agent alert. Even if I enable 1204 and 3605 I am only able to get the information of the deadlock in the error log but the alert is not getting triggered.
I am able to get all other alerts except the alert for 1205 and in general for any severity 13 alerts they are not getting triggered.
Any help is appreciated. I got this problem with sql server 2000 sp4 and sql server 2005 sp1.
Regards,
Ravi
How To Setup Multiple Notification/alert?
Jan 21, 2008
Hi there,
I am actually trying to setup multiple notification for one of our SQL Server 2000. The plan is to setup different notifications for administrators and users; so basically if a Job fails on this server Admins will get an alert message. In addition users will be notified with a separate notification to let them know that Admin team is already aware of the issue and is looking into that. All the communication is required to be through emails.
Please let me know if you have a solution to that.
Thanks,
LOA
Need An Alert Notification If The Job Takes More 5 Mins To Execute:
Jan 25, 2008
Does any one have sample script which notify if "XYZ' job takes more than 5 mins to complete then needs to notify about the job status.
Xp_sendmail Fails But Alert System Sends Email Notification.
Nov 30, 2004I receive the following error when trying to invoke xp_sendmail 'xp_sendmail: failed with mail error 0x80070005'. However, for alert notifications the email works fine. SQL is using an alias account to send mail. This account doesn't have any funny characters. Any ideas why the alert system works but xp_sendmail does not? The invokation of xp_sendmail is being done by SA.
Thanks.
SQL Cluster Failover/switch Notification
Nov 29, 2004Hi all,
I need help to send notificaiton mails as soon as my active sql switch to passive one..
sp3a : 2000 Ent Ed.
windows 2003 Ent Ed.
Cluster: A/P
thanks in advance..
Regards
Deepak
Getting E-mail Notification After Mirrored Database Automatic Failover
Jul 12, 2007Hello,
I am a C++/C# developer my SQL skills are very limited.
I have a database set up for mirroring and I would like to get an e-mail notification whenever an Automatic Failover occurs. Can anyone show me how to do this using SQLServer 2005? Please provide T-SQL script sample if possible!
Thanks,
-Agustin
SQL Server Admin 2014 :: Notification On Availability Group Failover
Nov 10, 2014Is there any way to trigger an event (ie. call a procedure/job/etc.) when/if an AG fails over?
Not looking to use agent alerts.
SQL Server Admin 2014 :: AlwaysOn Failover Cluster Session Failover
Oct 29, 20151. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
No Failover And Client Crashes ODBC DSN Setting For Failover In Connection String
Mar 3, 2007We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
Recovery :: AlwaysOn Failover Cluster And Active Sessions Failover Scenario
Oct 29, 20151. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
CPU Utilization Alert Through WMI Alert
Aug 13, 2015Can you use the below query to get CPU high utilisation alert purposes for both named and default instance? or, do I need to make any changes here (@wmi_namespace=N'.ROOTCIMV2' ) ?
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'CPU_WM_Utilization_Check',
@message_id=0,
@severity=0,
[code]....
Records That Occurs Again
Jun 12, 2005Code:
ID User BirthYear Where
1 Tom 1975 USA
2 Alex 1975 UK
3 Ben 1980 UK
4 Tom 1976 UK
5 Tom 1977 Unknown
6 Cindy 1985 USA
7 Tom 1975 UK
I want to print it like below
Tom-USA-1975
Alex-UK-1975
Ben-UK-1980
Tom-UK-1976
Tom-Unknown-1977
Cindy-USA-1985
I dont want
Code:
7 Tom 1975 UK
becasuse there is another record that User field is Tom and BirthDate is 1975
I tried "group by User,year" , it didn't work.
Exception Occurs If Using Clr Sql
Mar 28, 2008Hi all:
We are using a simple clr function in sql server, which splits input strings by a given delimiter. The code is in €œclr_sql.cs€?. And I wrote a simple stored proc in SQL Server 2005 which calls this function(storedproc.slq). Then I used a test application, which creates 80 threads to call this stored proc continuously. The client code is in client.cs. The TPS can reach to 2000+ in my test. Unfortunately, exception occurs sometimes (about 0.01%). Most of the exceptions are:
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
And there are a few others:
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
I wrote a dummy T-Sql stored proc, which returns all the rows from an existing table (60 rows, the same dataset as the test above) directly. It€™s much faster than the clr one, and no exception occurs. And I also wrote a T-Sql stored proc which doing the same task as the clr one and is slower. It doesn€™t fail, neither.
To summarize my problem: exception occurs when using clr sql, but it vanish when using T-SQL. But we need CLR SQL because of the performance is better.
I€™m looking forward to hear from you. Thanks in advanceJ
/* clr sql.cs */
public class KspSplitString
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod(String input, char delimiter)
{
if (null == input)
return new string[] { };
return input.Split(new char[] { delimiter });
}
public static void FillRow(Object obj, out SqlString Term)
{
Term = (obj as string).Trim();
}
}
/* storedproc.sql */
ALTER proc [dbo].[spSplitString2] (@keywords nvarchar(max), @delimiter nchar)
as
set nocount on
select * from dbo.fnKspSplitString(@keywords, @delimiter)
/* client.cs */
SqlConnection conn;
public override void Setup()
{
conn = new SqlConnection(connectionString);
conn.Open();
}
public override void Cleanup()
{
conn.Close();
}
public override void Run()
{
try
{
using (SqlCommand command = new SqlCommand(storedproc, conn))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = sqlCommandTimeout;
SqlParameter sqlParameter = command.Parameters.Add("@Keywords", SqlDbType.NVarChar);
sqlParameter.Value = keywords;
sqlParameter = command.Parameters.Add("@Delimiter", SqlDbType.NChar);
sqlParameter.Value = delimiter;
using (SqlDataReader reader = command.ExecuteReader())
{
tblResult = new DataTable("RESULT");
tblResult.Load(reader);
}
}
}
catch (Exception ex)
{
Cleanup();
Setup();
}
}
Occurs In A Field
Nov 21, 2006Hi,
How do find out how many times a character occurs in a field thru an expression? Like the occurs() function in FoxPro
Thanks
E- Mailing When An Error Occurs
Oct 11, 2001Hi,
I am trying to set up the SQL Server Agent so that when ever a error occurs
the DBA is notified.I created a operator name and when i tested to make sure
it was working it was giving me an error.The error is
Error 22022: SQLServerAgent mail session is not working; check the mail
profile and/or the SQLServerAgent service startup account in the SQLServerAgent properties dialog.
I am new to SQLServer and would like to know how to get this working.
Any help will be appreciated.
Thanks,
Rann
Locking Occurs When Trying To Insert - HELP
Nov 11, 2001I am trying to insert data using the select * command from one table into another, but query analyzer just seems to run and run (over one hour before I stopped it). The current activity shows a huge number of extent locks. The table itself has approx 90,000 rows. What is causing this insert not to finish, and how do I sort it?
I am a little confused over LE thresholds etc. At present the server has the following settings:
LE threshold max - 10000
LE threshold min - 20
LE threshold % - 0
locks - 60000
Will changing these values help??
Derek
Returning How Many Times A Character Occurs In A String
Jul 13, 2004I need to know how to figure out the number of times a character occurs within a string, in SQL.
For example, "happy" concerning "p" would be evaluated to 2.
What Happens To Windows Authentication If Domain Failure Occurs?
Jul 23, 2005Hi,I have a peculiar problem (or maybe not). I have SQL clients installedon XP boxes. These XP boxes are a member of let us say "XYZ" domain.SQL Server is installed on one of the boxes having Windows 2000 Serverin the same network. The installation has been done on local domainaccount. Since SQl Server has been installed with Windowsauthentication, what happens if the XYZ domain fails. Will it bepossible for all clients to access the database present on SQL Serverin the Win 2K box. I think I have made myself clear upto some extent.Please throw light on how to accomplish the connectivity between SQLclients and server in case of domain failure.Regards,Vinodi
View 2 Replies View RelatedError Message Occurs When Closing IE7 Tabs
Feb 18, 2007Ever since I installed Internet Explorer 7 I have recieved an error message when closing the browser tabs. It doesn't matter if I am closing one or all of the open tabs. Here is the message I recieve,
"Access violation at address 02050200 in module iesdsg.dll Read of address 25202E67"
This message didn't occur until I installed IE7. When I uninstall IE7 the message does not show up. Any insight would be appreciated.
Move Flat File When Error Occurs
Mar 12, 2008I Have a data flow task inside ForEachLoop.
Inside that dataflow, I was transfering data from a flat file to SQL Server. Now, When error occurs, I want to move that particular file to another location and proceed with the next file. I can't use file system task inside data flow . How can i achieve this ? Is there any other way other than Script-Task (Here script task will be complex thing as of my understanding).
Any solutions ?
DTS Execute SQL Task Terminates Without Failing When Error Occurs (SP3)
Mar 21, 2001I'm running SQL Server 7.0 SP3 and having trouble with DTS.
I have an Execute SQL Task that runs several stored procedures. When one of the stored procedures fails, the Execute SQL Task just terminates without failing.
I found a knowledge base article (Q238523) dealing with this situation but it was supposedly fixed in SP2 - I have SP3! The other work around suggested, issuing a SET NOCOUNT ON, does not always work.
Has anyone else run into this or have any other suggestions.
I hate the thought of spending days doing another work around in order get basic DTS functionalty to work as it should!
Thanks!
Jerry Dunn
gdunnjr@yahoo.com
Continue Processing Stored Procedure, When A Failure Occurs...
Apr 8, 1999All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.
Thank you,
Scott Kolek
Development Manager
SKM Software
http://www.skm-software.com
Object Required Jave Script Error Occurs
Aug 10, 2006Hi,
I have designed a report in sql server reporting services using SQL Server Bussiness Intelligence Studio tool. I have applied sorting feature for a column. Sorting facility is working fine when there is one or more records.
Its giving "object required" Java Script error When there is no record. Kindly help me how to fix this issue.
Regards
Sagayaraj R
Access Is Denied Occurs After Package Successfully Completes.
Apr 21, 2008
I am getting an 'Access is denied' error:
Error 0x80070005 while loading package file "Package.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task'. The strange thing is that the error comes after 'Package.dtsx' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package.dtsx ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via a SQL Server Agent Job. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
Timeout Expired Occurs In The Middle Of A Stored Procedure
Sep 11, 2006greets all,
i have a express set up and running a table filled with a few batches of records. Each batch has approx. 20k records. I have a program that inserts a new batch into the table. afterwards, it makes a call to a stored procedure. this SP compares the last batch with the latest batch and marks the records as additions, updated, or deleted records (which will be used by the program). the problem im experiencing is that the comparison algorithm is getting the "Timeout expired" error returning back to my program. the SP seems to take roughly 1-3 minutes to make the comparisons. i tried setting the execution timeout to 0 (no timeout) in Options->Query Execution->SQL Server->General but i still encounter the problem. am i changing the wrong option for this or is there something else i can try? im going to try to reproduce this database in SQL Server 2000 so i can use the profiler, but im running into problems exporting the database to an older version (im not familiar with bcp, which im looking into). any advice until then?
Login Failed For User Null Occurs After A Period Of Time
Jul 23, 2005I have an identical SQL database on two machines (my machine and a webserver) that links to a database on a third server (S3). When I executea stored procedure on my machine that accesses a database on S3, italways runs without a problem. However, when I run the same storedprocedure on the webserver (via Query Analyzer on my machine, connectedto the webserver), the stored procedure runs without a problem forabout ten minutes, and then I suddenly receive the error "Login failedfor user '(null)'. Reason: Not associated with a trusted SQL Serverconnection.". If I then connect remotely to the webserver via RemoteDesktop Connection and run the stored procedure there it works, andthen I can run it again on the webserver via Query Analyzer on mymachine, but the same problem will occur again after about ten minutes.The only difference between my machine and the webserver is that thewebserver is running Windows Server 2003, whereas my machine runsWindows XP. All settings in SQL on the two machines are identical, so Iwould love to know if anyone has a possible solution to this seeminglyrandom problem.
View 23 Replies View RelatedControls Are Hidden. Blank Page Occurs In Reporting Services
Dec 26, 2007 We are using SSRS 2005.We designed one report.This report contains Table and chart controls .When Table is hidden,chart only is visible, this chart is going to show only on last page.There is a white space in top of the chart while hiding the table control.
Consider for an Example Report one tables and one chart . We want to show only chart,So we made Table's visible property is false.Total Number of pages is five and table has only four pages and chart has only one page that is in last page. Now the report showing Four pages are empty with header and footer and last page have a chart with header and footer.
Any suggestions how to avoid the empty pages?
I tried to Filter property to filter the table.Same empty page with header and footer is only visible
Thanks In Advance
Mugesh
Copy(paste) 'serialize' Error Always Occurs With Task, Never With Components
Aug 15, 2007Some more insight into the error I am getting.
It always occurs at the task (control flow) level and never at the component (data flow) level
It always occurs on the 'copy' action.
I have tried all the msxml/registry recommendations to no avail.
once again here are the error details
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'SQT Drop Create RAWMigDeal RAWBarrier table' to the clipboard.
(Microsoft.DataTransformationServices.Design)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
Error Access Is Denied.. Occurs After Package Successfully Completes.
Jun 4, 2007I am getting an 'Access is denied' error:
Error 0x80070005 while loading package file "E:SSISPackagesPackage1.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task' that is within a 'For Each Loop' container. The 'For each loop' goes through a single iteration as expected. The strange thing is that the error comes after 'Package1' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package1 ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via Process.Start() from a web page. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
How To Stop Execution Of Stored Procedure If Error Occurs In Try/catch Block
Mar 3, 2008
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet
begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1)
end try
begin catch
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go
begin
print 'hello world'
end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
Thanks,
Flea
SQL Server 2014 :: Trigger On A View If Any Insert / Update Occurs On Base Table Level
Apr 21, 2015I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
Flat File Source - If An Error Occurs, Continue Parsing The Remaining Columns In The Row Before Failing
Jan 14, 2008Hello everyone,
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution:
- I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem:
This configuration gives me only the first erroneous column in the row being processed.
Question:
Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
Thanks in advance...
Samar