SQL Server Agent Notifications To Multiple (three-plus) Addresses Fail
Jul 20, 2005
We have SQL Server 2000 Standard and recently moved from Exchange 5.5
to Exchange 2003. This problem occurred both before and after the
e-mail change.
We would like to send notifications of failed jobs to multiple people
in case one person happens not to be in on the day there is a problem.
Setting up an operator with an e-mail name that combines two
addresses seems to work ok as long as we use a format like the
following:
smith_john; jones_mary
or
Join Bytes!; Join Bytes!
However, when we add a third name, we start getting problems. The
first and third accounts listed get the message but the second one
does not -- apparently because the address gets corrupted. For
example (based on the message received at one of the good addresses),
a message sent to:
Join Bytes!; Join Bytes!;
Join Bytes!
goes to Smith and Jackson successfully but Jones' address is corrupted
to:
Join Bytes!om
and, of course, fails.
From some other testing, it appears that the last three characters of
the first address always get appended to the end of the second
address.
Has anyone else run across this? If so, is there a known fix? I had
hoped Exchange 2003 would fix the problem. Most likely, we will try
setting up a personal distribution list under the account from which
the messages are sent. I just wanted to give one more shot to finding
the problem -- both because I would like to know and because being
able to string together addresses is a little easier than having to go
through the person who can give us access to the account to set the
PDL.
Assistance PleaseI have a couple of scheduled jobs that are failing to e-mailnotifications on completion (via SQL Agent Mail).I can send a Test e-mail without any problems. I can run the jobsmanually, and the e-mail notification works effectively.Left to run according to the schedule, and no e-mail notification isgenerated.Any assistance greatly appreciated.Thanks*** Sent via Developersdex http://www.developersdex.com ***
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.
I have several 64 bit SQL 2005 servers that up to 4 named instances residing on them. Before going into production I need to change the port addressing to use fixed TCP ports. I have SQL Browser running to enbale me to see the named instances.
What steps are required to change to using specific TCP port addresses and is there a particular range to use ?
I'm beginer with service brooker and i wandering if someone could help me to clarify what would be the proper usage of service broker for my current situation...
Suppose i have 3 tables: Customers, Orders and Providers. I would like my c# application to receive a notification for any data change on any of these tables. I know i could use my 3 tables in a SELECT statement for a global event notification but i need to know from wich table change occured.
I also though to create 3 separate event notification but i'm really not sure...
I think theres something I'm missing about AG listeners. My test config is 1 SharePoint WFE & 2 SQL servers configured in an AAG.WFE, SQL1 & SQL2 servers are all on the same subnet.On the WFE, I configured an alias called SP15 to be used for the SQL servers. If I failover from SQL1 to SQL2 I just need to change where the alias is pointing on the WFE. So why do I need a listener? This works fine, albeit manually.I have read on some posts that an automatic failover process is possible. How can I add a 2nd IP address to the listener when both SQL servers are on the same subnet? The GUI doesn't seem to allow this.
The SQL Server Agent process is running. When I use EM to start a database integrity check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base and research this problem.
In the mean time if someone can help, it is much appreciated.
I'm trying to figure out a way to automatically restart the SQL Agent service when an AG fails over to a secondary node. I've created a job on each AG node that runs every 15 seconds and detects when a fail over has happened. It then issues a SQL Agent stop command and then a SQL Agent start command. The problem is that once the stop command happens the start command never fires because the SQL Agent is now stopped.
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
I have sql 2000 sp4 running on a cluster, we had to fail over to other cluster, and now the sql agent choice to use a mail profile is grayed out. I can go to sql mail and test the profile and it works, but I can't change it for the sql agent? Anyone seen this before
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 - ? [297] 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 - ? [297] 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 - ? [297] 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 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168 2007-10-22 15:31:15 - ? [101] SQL Server TESTSERVER version 9.00.3042 (0 connection limit) 2007-10-22 15:31:15 - ? [102] SQL Server ODBC driver version 9.00.3042 2007-10-22 15:31:15 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is 2007-10-22 15:31:15 - ? [310] 4 processor(s) and 3328 MB RAM detected 2007-10-22 15:31:15 - ? [339] Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2 2007-10-22 15:31:15 - ? [431] Populating subsystems cache... 2007-10-22 15:31:15 - ? [432] There are 11 subsystems in the subsystems cache 2007-10-22 15:31:15 - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 80) 2007-10-22 15:31:16 - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 100) 2007-10-22 15:31:16 - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'SSIS' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control 2007-10-22 15:31:16 - + [260] Unable to start mail session (reason: No mail profile defined) 2007-10-22 15:31:16 - ? [174] Job scheduler engine started (maximum worker threads: 400) 2007-10-22 15:31:16 - ? [193] Alert engine started (using Eventlog Events) 2007-10-22 15:31:16 - ? [146] Request servicer engine started 2007-10-22 15:31:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect 2007-10-22 15:31:16 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient... 2007-10-22 15:31:16 - ? [133] Support engine started 2007-10-22 15:31:16 - ? [167] Populating job cache... 2007-10-22 15:31:16 - ? [297] SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000] 2007-10-22 15:31:16 - ? [168] There are 2 job(s) [0 disabled] in the job cache 2007-10-22 15:31:16 - ? [170] Populating alert cache... 2007-10-22 15:31:16 - ? [171] There are 0 alert(s) in the alert cache 2007-10-22 15:32:59 - ? [177] Job Test1 has been requested to run by User DOMAINmyaccount 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ? [184] Job completion for Test1 is being logged to sysjobhistory 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
A quick question for you all, We will be changing the IP address of our SQL 7's server sometime next week. Does any1 know of any issues/problems that could occur if we do so?
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.
Does anyone have any idea how I would go about creating an object that can be informed of any modifications to a SQL data base. i.e. a .NET object that can detect whenever a table is updated. It is NOT possible to modify the database in anway, so I can't add triggers etc.
I had been trying to solve this error with no success :
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.
The syntax for a fully-qualified TCP address is: TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
I had installed three instances on my local machine to test Data base mirroring :
Principal : running SQL Developer Instance
Mirror : running SQL Developer Instance .
Witness : Running SQL Express.
Database mirroring already enabled using startup flag : -T1400
i even tried to configure it with out a witness but still have the same error .
I used the follwoing server name in the mirroring wizard(not localhost) :
I have a Windows application in C# and a SQL Server 2005 having a table "Notifications" in a DB.
This table is being updated at regular intervals and i want that my application should get a notification for this everytime a Record is inserted, updated or deleted.
What is the common mechanism (if any) to make the server notify aclient when some condition is met (say, upon completion of asql-transaction)? The only way to implement this feature I am aware ofnow is to set up a timer and query the server every once in a whilebut surely this technique isn't much good.
when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Can the NT Server Name, SQL Server Name and IP Addresses be changed without reinstalling NT(4.0) and without reinstalling SQL Server (6.5). We have SQL Server on an NT Server and they both have the same name. If this can be done how would it impact the Registry. Would the appropriate settings in the Registry be updated? Thanks.
Can anyone help me with a problem I have with SSRS 2005? How do I configure SQL Server 2005 Reporting Services to deliver subscription e-mails to addresses external to my windows domain?
I've been having trouble configuring the Report Server e-mail delivery component to send subscription e-mails to addresses outside the domain on which the report server is hosted.
For e.g. since my local windows domain is named "smartsystems.com", I have found that the Report Manager does not have any problems sending subscription mails to "eawagu@smartsystems.com" but throws up an error whenever I include "eawagu@yahoo.com" in the recipients' list.
I've heard that the Standard version supports the use of CommandNotification and that the Express version does not. What about the Workgroup edition? The reason I'm asking is because there is a substantial monthly difference in cost between the two versions if I were to have my site hosted. I've been developing with MS SQL 2005 Express so obviously I have not tested this caching feature. Are command notifications reliable? On paper, it sounds impressive and extremely efficient especially if a site is to have heavy traffic and you can't afford the overhead of heavy database processing that is useless if the data is unchanged. Does the Workgroup edition support automatic change notifications? Have automatic notifications been proven reliable and vastly more efficient than crude polling methods? Thanks in advance.
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
I have a MASTER package containing multiple packages using execute package task in SSIS.When am running this MASTER Package in Sql server agent in SSMS, am getting error as below.
steps how to run this and let me know what information am missing to run the Master package.
ERROR :Failed to decrypt protected XML node “DTS: Password” with error 0x8009000B “Keynot valid for use in specified state”. You may not be authorized to access this information.
This error occurs when there is cryptographic error. Verify that the correct key is available.
I'm trying to connect to a local instance of SQL Server using an IP address and its not working. When I looked up the connect string, it shows only the IP address replacing the Server name, not the instance name, i.e. Server=127.0.0.1 - how does it know which instance to use? Can I use the 127.0.0.1 loopback address for the server (as its on a local machine)? Do I have to use 'Network Library = DBMSSOCN' to tell the client to use TCP/IP? (TCP/IP is showing as an enabled protocol in the Client Network Utility)
I need to allow for many addresses in my database for each client that is entered. I'm looking for suggestions on how I should handle this. I will need to do mailings to the clients and will need a way to select the address record that the client indicates as the mailing place.
I got got a pile of bad email addresses to update in our SQL database.I know how to do this for individual records using the update command.Is there a way to execute an update using the list of addresses in anexcel spreadsheet or some other form of list? This kind of scriptingis new territory for me.
I want to replicate between 2 servers not on the same network. One has to be addressed by IP. Currently replication does not support IPs - anyone have a workaround. Will this be resolved in the future? Seems this is a pretty common scenario if your db is on a hosted site.