hi i am getting the error for granting
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sa
error is
Cannot find the user 'sa', because it does not exist or you do not have permission.
SO PLEASE GIVE ME THE CODING
step by step for notification
thanks
hi,
An application, every one hour it connects to sqlserver and uploads data from MSAccess database.i should get the notification weather that application is connected to the server or not.because this application gets currepts very often.
any help is appreciated.
thanks
preethi
Hi,
I'm trying to install Sql server notification service on my pc, which has sql server 2000 and vs.net 2003 installed, but its telling me that I should have the .net framework installed before continuing the installation. The .net framework is already installed, any ideas why is it doing this????
Thanks
Hello
This is first post to this forum
I have read that through Notification Services we can send sms through sql server as well as recieve.
Can anyone provide me with links for the same.
Thanks
cmrhema
Hi,I am a newbee to sql server. I know there is a tool calledNotification Services. I want to set up notification for specificentries in sql server table.Thanks a lot for your guidance.AJ
View 1 Replies View Related
I am getting the above error when I try to create Notificatioin services new Instance.
Additional Information is
Failed to connect to server ABC(Microsoft.SqlServer.ConnectionInfo)
An error occured while establishing connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server deoes not allow remote connections.(Provider : Named Pipes Provider, error 40 -could not open connection to SQL Server)(Microsoft SQL Server error: 53)
Please help me I am new to SQL Server 2005.I can't able to proceed.
I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
Has anybody used it . can you give me an idea about the performance etc.?
View 6 Replies View RelatedI have a situation where a person, who created our DW, likes to go in and change table name. This person had no prior SQL xp and is at a higher level then me in the company. I need to know whenever somebody changes a table name I get notified, with the old table name and the new table name. This can be either set on the server level or the DB level. I would like to use the policy management but cant find the way to do it.
View 9 Replies View RelatedHi
I am adapting an OLEDB-based application [ported on VC2005] to access a SQL Server 2005 Express database.
The application opens recordsets on tables and stored procedures. From this I can read records and update the database.
But how can I monitor the changes on this database made by other applications ?
Do triggers resolve the pb ?
I have been testing SQLDependency and I have a two questions;
1) When using a stored procedure to run the dependency query, using a "clean" procedure (containing nothing but a select statement), works fine. Adding try..catch (which is a part of our coding standards), results in the subscription firing immediately because of an invalid query. Is this by design? If so, how can I implement error handling.
2) How does SQLDependency handle SQL Server restarts?
I see two options:
a) The subscription is fired listing a server restart message in the related service broker queue, but as far as I can tell, SQLDependency has no way of handling these messages given the connection has been broken. (Establishing a new connection and dependency won€™t read the said messages).
b) The SQLDependency listener on the client raises an error for the connection being terminated. Can I relay on this event to recreate a connection and dependency?
Help will be appriciated
Boaz
within sql server management studio... we have set up a few sql scripts to run at 2am every night (sql server agent > under jobs)... although it has been successful each night, we'd like to setup sql server agent to automatically email success and failure notifications to our email boxes.
i see under the properties of sql server agent > jobs > properties of a job > there is section that says Notifications but then it doesnt really have a place to type in which email we want to send them to? some reading material or instructions greatly appreciated
Hi,
My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1
GO
reconfigure
GO
-------------------------------------------------------------
-- Database Mail Simple Configuration Template.
--
-- This template creates a Database Mail profile, an SMTP account and
-- associates the account to the profile.
-- The template does not grant access to the new profile for
-- any database principals. Use msdb.dbo.sysmail_add_principalprofile
-- to grant access to the new profile for users who are not
-- members of sysadmin.
-------------------------------------------------------------
DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);
-- Profile name. Replace with the name for your profile
SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang';
SET @SMTP_servername = 'smtp.cgdnow.com';
SET @email_address = 'vdang@cdgnow.com';
SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
GOTO done;
END;
-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;
DECLARE @rv INT;
-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;
-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
Two days ago I discovered that the drive on our test SqlServer2005 was full. The current Error_log had 54+ Gb (yes Gb) of records. Not only did I delete the file I restarted SS and the server.
That afternoon, I discovered that the current log had 33,678 records! I believe 33,616 of the records came from a single run of my app and set of program events. Obviously I'm doing something wrong in my Query Notification implementation and testing. And yet, when I try to to deliberately trigger the phenomenon, I can not.
Here is the head of the exported log file; the two msgs (query dialog closed and cannot drop queue) alternate thousands and thousands of times form a single test run. What is going on?! Pls help!
Date,Source,Severity,Message
10/11/2007 11:42:52,spid5s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
10/11/2007 11:42:52,spid5s,Unknown,SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
10/11/2007 11:42:49,spid14s,Unknown,Service Broker manager has shut down.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
10/11/2007 11:42:04,spid51,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
Is there a way to set up automated email notification beginning 14 days prior to when a SQL Server Login password (that has "Enforce password expiration" enabled) will expire?
Thanks,
-Dave
I'm trying to set enable SQL cache invalidation on a webpage, specifically on a a datasource that fills a GridView.
The website http://beta.asp.net/QUICKSTART/aspnet/doc/caching/SQLInvalidation.aspx makes it sound so very simple, and I have attempted to do all that it says, yet something just isn't right. The site says that the following needs to be done.
Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with Sql Server 2005 query notifications.
Table names in queries must include the ownername. For example, if you issue a query against the authors table in the pubs database, the query must reference the table as "dbo.authors".
The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.
I've done items 1,2 & 3, but when I attempt to do item 4 I get the following result in TSql:
Msg 15151, Level 16, State 1, Line 1
Cannot find the service 'SqlQueryNotificationService', because it does not exist or you do not have permission.
My login account has sysadmin priviledges, so I'm guessing the latter part of the error doesn't apply to me. But what do I have to do to get SqlQueryNoticationService to exist?
Is 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.
In the notification tab of job properties, I can see three options in the drop down list at the right hand side.
When the job succeeds
When the job fails
When the job completes
In which system table these options are saved ? Is it possible to add a custom option in that dropdown list.
Is there any built-in way of kicking off a job on SQL Server 2005 Agent whenever a package/job completes in Oracle?
Are there any (Triggers? Msft queue? Event Notification?) mechanisms to automate running a job on the SQL side?
Any article or knowledge articles would be appreciated also.
If not are there any built-in stardardized polling techniques? Or are there any timers in SSIS?
That way I can delay executing a child package until a certain record has been inserted into a control table in Oracle.
I don't want to write an inefficient for loop that blocks all other processing on the server and iterates once every second.
Thanks
I have used the notification-based cache invalidation with my web application, but how can I check it really works? I mena if the backend data didn't change , the data will come from cache not from the database?
<asp:SqlDataSource ID="SqlDataSource_WebInfo" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="SELECT simplified, traditional FROM temp" ProviderName="System.Data.SqlClient" EnableCaching="True" SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>
Thanks a lot!
Morning all!
My SQL server email job notifications have stopped working, but mails sent through the xp_sendmail command still work.
The only thing that has changed recently is the SQL server agent account passwords have been changed, these accounts are normal win2000 domain accounts.
Any ideas cos I am fully stumped?
Is it possible to set up a Job to send a notification , if a process is blocked for more than a minute on SQL Server 2000.
Thanks for any help on this
I wish to know if there is anyway to find out if a perticular job on SQL Agent was executed or not. I am not looking for Log History of the job, but, something like a trigger that will fire an email to notify that a perticular job that was scheduled to run at a certain time did not run.
To explain in detail, I have a job that is scheduled to run every 15 min. This job is crutial and I wish to be notified if the job was not run for some reason or other. I already have a program that delivers me the history log of the job each day, but, checking history to see if the job ran or not every 15 min is not possible. Again, I am not talking about the job success or failure, but the fact that the job has run or not - thats it !
If you are suggesting a SMO object, I will appriciate if you provide me with the code or alternatively a source to refer.
Thank you.
hi all i have found for function as function: SubscriptionEnumeration in API of notification service, i want display myAlert(notifications) in(for example: one day).the Function SubscriptionEnumeration display Subscription of (subscriberId,subscriptionType,nsApplication) it can be made this ?thanksHungN
View 1 Replies View RelatedHi,
I want to build sql notification service in my web application and i not know how to build it. Please help me how it possible and any sample code then pls send me else give me link of website. Its very urgent for me, now i am using timer for checking for updation in database and i want to notification service for that because its take many time and timer fire every time even database change or not.
Jaydev Jangid
Does anyone know how I can receive some type of notification (an alert) that fires when a new database is created (or an old one deleted) on a server?
Thanks.
I have set up SQL Mail on a SQL 7.0 box and I can successfully schedule tasks and receive e-mails from them. However, when I try to get mail notification from a scheduled DTS package I run into problems and no mail is sent. Under Profile Name the only item selectable is Microsoft Outlook. I've tried selecting this as well as leaving the profile blank and nothing seems to work. We use POP3 server and Microsoft Messaging. Our corporate e-mail system is Notes so we forward mail through the POP3 server via Internet mail to our Lotus Notes system.
Any suggestions so that we could get our DTS mail notification working? Anything I could try that I haven't?
Thanks!
Does anyone know if there is any software available that notifies specified people when an error above a certian level occurs. Im thinking along the lines of email and text message.
Im running sql server 2k at sp3a level, and the software will have to be compatible with it so as to get the event notification to pass the information on.
If there isn't any software, would anyone know of any scripts that will do this?
MSDE 2000? Thanks.
View 2 Replies View RelatedCan you send these to a group of DBA's
View 1 Replies View RelatedHow can I set up the notification email when one of your jobs got failed using Database mail..
I've already set up the database mail ... but I am not quite sure how to use the database mail to send a notification when a job failed...
Dear all,
I would like to know, is there any method that the notification can be send more than one person? (SQL server agent--> Job Properties--> Notification--> email)
The dropdownbox list allow one email only, can I add more people on the email list, or insert more email address in the same operator?
Thanks to all
johnson Mok
Hi,
Can anyone tell me abt SQL Notification Service ???
Is that SQL Programming application or can be implemented with other Dot Net Languagues ???
Need to know further that this services are implemented only by the Database Engineers or integrated with the Application development using .Net Language like C# etc ????
I m n urgent need for this problem
Thanks in advance
Regards
Aravind