I am working with alerts and trying to see if there is a way that I can control the message on the notification. I have the job set to send an email and a net message when the job succeeds. Since this message will be going to the user, I owuld like to be able to make the message as clear as possible for them. Does anyone know if the message being sent can be modified or altered with an alert? And how?
This is on sql server 2005: I created an agent job and I want to be able to notify people by email when it completes and when it fails. I first wanted to test the notification for when it completes. I created an operator and entered the E-mail name. In the job properties, I selected this operator in the Notifications page.
The job runs an ETL process. When I run the job, after it completes, the email is not sent. This seems straight-forward. The email address is valid, I tested the email address and it does get sent when I do it from Outlook 2007.
Today when I came into work, I noticed all of my jobs failed on a particular server. I tried to manually kick off the packages, but to no avail. Both the job history and the error message from the packages state a timeout error. Any idea on this? Also, I have no problem running a query from query analyzer...even on a remote machine. Any help would be appreciated Trevor
Running MSSQL 2000 on Windows2000 in an NT domain. We have several jobs created by the web assistant wizard. They run along just fine for days or weeks, then all of a sudden SOME of them (?!) begin to fail with the message "Unable to perform a SETUSER to the requested username 'dbo' because the username is invalid for database 'xxx'" where xxx is of course the db where the proc resides. SQLAgent service is running as a domain admin and has not changed; job is owned by sa and has not changed; the domain admin in question is in sa role and has not changed. Something sure has changed, but what? Sometimes when this happens we can clear it up by deleting the web job and simply re-creating it. Today we have three culprits and none of our usual tricks are working. Why does this happen? How can we fix it? How can we PREVENT it! Thanks for any words of wisdom out there.
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
After upgrading a server from SQL 2000 to SQL 2005, I can't get any SQL Agent jobs to run, giving logon failed messages. MSSQLSERVER and SQLSERVERAGENT services are set up to run as the same domain account which has administrative rights to the server and is a SQL login with the sysadmin role. For basic testing purposes, I created a test job that all it does is run a T-SQL command 'SELECT @@VERSION'. The job is also set to run as the same account that the Agent is running under. Below are the logs (with trace messages on) from the point of starting the Agent to my attempt to run the job (user and server names have been changed to protect the innocent):
2007-10-22 15:31:15 - ?  SQLServer Message: 15457, Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ?  SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ?  SQLServer Message: 15457, Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ?  Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168 2007-10-22 15:31:15 - ?  SQL Server TESTSERVER version 9.00.3042 (0 connection limit) 2007-10-22 15:31:15 - ?  SQL Server ODBC driver version 9.00.3042 2007-10-22 15:31:15 - ?  NetLib being used by driver is DBNETLIB.DLL; Local host server is 2007-10-22 15:31:15 - ?  4 processor(s) and 3328 MB RAM detected 2007-10-22 15:31:15 - ?  Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2 2007-10-22 15:31:15 - ?  Populating subsystems cache... 2007-10-22 15:31:15 - ?  There are 11 subsystems in the subsystems cache 2007-10-22 15:31:15 - ?  Subsystem 'TSQL' successfully loaded (maximum concurrency: 80) 2007-10-22 15:31:16 - ?  Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ?  Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ?  Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  Subsystem 'LogReader' successfully loaded (maximum concurrency: 100) 2007-10-22 15:31:16 - ?  Subsystem 'Distribution' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  Subsystem 'Merge' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  Subsystem 'SSIS' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ?  SQLSERVERAGENT starting under Windows NT service control 2007-10-22 15:31:16 - +  Unable to start mail session (reason: No mail profile defined) 2007-10-22 15:31:16 - ?  Job scheduler engine started (maximum worker threads: 400) 2007-10-22 15:31:16 - ?  Alert engine started (using Eventlog Events) 2007-10-22 15:31:16 - ?  Request servicer engine started 2007-10-22 15:31:16 - +  An idle CPU condition has not been defined - OnIdle job schedules will have no effect 2007-10-22 15:31:16 - ?  Starting SQLServerAgent Monitor using '' as the notification recipient... 2007-10-22 15:31:16 - ?  Support engine started 2007-10-22 15:31:16 - ?  Populating job cache... 2007-10-22 15:31:16 - ?  SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000] 2007-10-22 15:31:16 - ?  There are 2 job(s) [0 disabled] in the job cache 2007-10-22 15:31:16 - ?  Populating alert cache... 2007-10-22 15:31:16 - ?  There are 0 alert(s) in the alert cache 2007-10-22 15:32:59 - ?  Job Test1 has been requested to run by User DOMAINmyaccount 2007-10-22 15:32:59 - !  Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - !  Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - !  Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ?  Job completion for Test1 is being logged to sysjobhistory 2007-10-22 15:32:59 - !  Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
Can anyone help me to create a URL decode user defined function in SQL Server 2005? I want to use the method [System.Web.HttpUtility.UrlDecode] in .net framework, and I try to add it as a CLR function to SQL Server but always fail. It depends on [System.Web.dll], and when I try to create assembly [System.Web] using following scripts, it will fail:
CREATE ASSEMBLY [System.Web] FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll' WITH PERMISSION_SET = UNSAFE
Error: CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
I have searched the MSDN and then add following scripts before mine:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
CREATE ASYMMETRIC KEY SystemWebKey FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll' CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY SystemWebKey GRANT UNSAFE ASSEMBLY TO CLRLogin
I am creating a job for integration services in SQL server 2005 64-bit edition. I am getting error :The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI). Please help
I am setting up a new pair of SQL 2014 enterprise servers in HA using Availability Groups. One of the servers is located here in our local datacenter (10.0.1.x) and the other SQL server is in our remote datacenter(172.16.1.x). I was able to setup the Windows Failover Custer without much issue. I setup the AG but when I try to setup the listener. I get the following error. I have setup an IP for both networks on the listener. I have confirmed that there is not any DNS records created for AG listener name. But I still get this error.
DTS job fails with the following error, A Server (Windows 2003 SE with SP1) has 4 SQL Server 2000 instances with mixed of SP3 & SP4. But few jobs are failing in all the 4 instances.
Executed as user: DCADB04SYSTEM. DTSRun: Cannot create COM Server to load and execute DTS Package. Error -2147221008 (800401F0): CoInitialize has not been called. Process Exit Code -2147221008. The step failed.
Appreciate you suggestion to fix this, regards, Arul
Hi, I just realized there are several severity alerts on the Server Agent, from Severity 19 to 25. Is there anyways to delete those messages? If I don't delete those, would it be affected later? The Online Book mentions those are some of the system error messages, but they don't show how to delete them. Can anyone helps? Thanks a lot.
I am having trouble with locks and performance issues in one application. The SQL server is specific to this application. What settings should i choose to set an alert to let me know when i get too many locks? the locks i am interested in ar ethe ones that show up in SQL EM. ie where a process is blocking another process(es).
I have tried New Alert | lock requests /sec but that is a huge figure when the server is running OK, so i cant guess what value to set for the counter. Is there a better object/counter i should use?
I've setup alerts and an operator to email on a particular event code. I can successfully test the operator. (It e-mails fine) The problem I am having is that despite having a particular event log to the NT application log, no SQL Alert is triggered. I've tried logging to the NT Application log, as well as the SQL errorlog. The SQLExecutive is running. Thanks in advance. John
I defined an Alert in Enterprise Manager for Severity 19 - Fatal Error In Resource. The alert is enabled, the type is "sql server event alert" and the alert is defined against a specific database. SQLSERVERAGENT and SQL Mail session are both started. I test the alert using the following statement in the specified database.
raiserror (50001,19,1) with log
A message is placed in the SQL Server log, however no alert is fired. The alert history indicates the alert has never occurred. What am I missing?
Does anyone know of a way, either by 3rd party tools or using SQL server, to have alerts get emailed to you in a clustered active/active environment? I work as the DBA at a quickly-growing company, whose production databases need to be online 24X7. I want to set up the alerts to email/page me if there are problems with the databases, but since (according to microsoft) SQL mail is not supported in a clustered environment I'm at a loss.
I'm looking for some help in trying to resolve this issue either by 3rd party means or a SQL Server work around.