I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:
CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue
I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....
Finally, I have also run into the problem whereby SQL issues the following error:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEADyerN', error code 0x54b.'
I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.
I have written some code that selects all the databases on our server then uses a while loop to execute some dynamic sql to query a table in each database. The problem is some of the databases dont have the correct permissions so the query breaks. Is it possible to check to see if the database has the correct permissions before I execute the dynamic SQL and if it doesn't move onto the next one in the list?
When SQL server notifies the application that the records in a table has changed, is there a way to know which record has changed from the application?
Let me give an eg.
Consider that we have a table customer. We have registered for a notfication for 2 customers, say Cust id 1 and Cust id 2. Now , when records for these two customers changes we get a notification. Is there a way in .Net by whihc we can get whihc customer has been modified?
I've got an application that's using query notifications with a two-user setup (a user that starts the dependency running, and an application user that subscribes to it). I'm having problems getting the query notifications to go through however... according to the Profiler, when the application user does something that triggers a query notification, it causes this error:
This message could not be delivered because the user with ID [application user] in database ID [database] does not have permission to send to the service. Service name: SqlQueryNotificationService-[guid].
The problem is that [guid] is different each time, and there doesn't seem to be a generic "grant send on all services" statement, so I can't grant send on the particular service in question to the application user to get rid of the error. (If I grant db_owner to the application user, the problem goes away and everything works, but I'd rather not do that. The application user already has subscribe query notifications, receive on QueryNotificationErrorsQueue, and references on contract::...PostQueryNotification.)
Obviously I'm doing something wrong, but I am completely out of ideas and can't seem to find anything new to try. Any ideas? Thanks.
After having problems with SqlDependency logging an infinite number of errors I've rewritten my code to use SqlNotificationRequest instead. Now I have a different kind of cleanup problem and am looking for help.
My code registers to be notified when table data is changed. When the event triggers, it re-registers. It works fine. The problem comes when my subscribing program ends: it orphans the last subscription. When the program re-starts it subscribes as before, but now there are now two subscriptions (or three, or however many times my program is shut down). As an Admin I can run KILL QUERY NOTIFICATION SUBSCRIPTION ALL; I can even hard code this into my app as it is the only one (for now) using this instance of SS. But there must be a better way.
When I subscribe (.NET) how do I get the subscription id? Then I could just KILL that single id when my code shuts down. Specifically what code should I include in StopListener()? Thank you.
public void StartListener()
{
// starts a new thread whose sole purpose is to sleep until waked
SqlNotificationEventArgs e = new SqlNotificationEventArgs( SqlNotificationType.Change, SqlNotificationInfo.AlreadyChanged, SqlNotificationSource.Database );
_delegate( this, e );
}
catch( SqlException )
{
// if the queue name doesn't match one in the db an exception will be thrown
In Work , we created a Cache component that serves a large business application, we are subscribing to almost 200 queries , the component worked quite well in the first few months of the development, but lately i am getting notification events that contain "Error" in the SqlNotificationEventArgs of the event , the "Source" value is "Client".
I have no idea what might cause this error event , we are close to "go live" with our product and we cannot depend on the Query Notification mechanism until this problem is solved.
Can it be a DataBase problem? Application Problem?
Hi, I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1 id item color 1 shoe red 2 shoe blue 3 coat green 4 coat black
Table2 item color shoe red coat green
I want everything in Table1 where item and color are not a match.
So my results should be: 2 shoe blue 4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
We have encountered a weird problem; we have an application that registers to several queries for notification.
For several weeks everything worked fine, any change to any query result in the database triggered the notification event, but since yesterday the mystery began€¦ since yesterday not all changes triggers events , changing one table raise the notification event, changing the other doesn€™t , all seems fine in the database , the application reports that all queries were registered successfully , in the subscription view in the database we can see that all the queries that the application subscribed to are present€¦
What can be the problem? Is it something in the database? Can it be a problem in our application, please keep in mind that everything worked until yesterday€¦
I'm having some problems setting up a SQL Server 2005 Query Notification application. I've only recently moved from SQL Server 2000 so 2005 is still a little alien to me!
I've followed example found on the internet in the following steps:
1.
ALTER DATABASE <dbname> SET ENABLE_BROKER
2.
GRANT CREATE PROCEDURE TO <user>
GRANT CREATE QUEUE TO <user>
GRANT CREATE SERVICE TO <user>
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <user>
GRANT RECEIVE ON QueryNotificationErrorsQueue TO <user>
3.
ALTER DATABASE <dbname> SET TRUSTWORTHY ON
The application developers have also followed the same example setting up SQL cache and the connection code. When they start the application I can see the connection in the activity monitor but it has a status of SUSPENDED. I've also checked the view:
SELECT * FROM sys.dm_qn_subscriptions
There are no subscriptions setup I can however see that there is a new SP created. I tried to creating master keys as well but I'm not sure what the reasoning behind this is.
Is there anything else I need to take into consideration or check? When I change the data which they use for their select statement SQL profiler doesn't register anything (except the update). I have however seen this:
exec sp_executesql N'END CONVERSATION @p1; BEGIN CONVERSATION TIMER (''69e2c786-9d33-dc11-a751-0050568146e8'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-5fb25d04-6ef6-47e6-9406-854f15e16eb7]), TIMEOUT @p2;',N'@p2 int,@p1 uniqueidentifier',@p2=60000,@p1='BD98F6B4-A133-DC11-A751-0050568146E8'
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?
Dear, I have designed a SiteMap provider for SQL Server. But I am worried about the notification service of SQL Server. My SiteMap requires service broker enabled in SQL Server. When I have web farm (multiple clustered server) does the Sql Server service broker notify all the web servers? Or, it notifies only the server request? Have anubody implement such scenario? Looking forward for your reply. Sincere Regards,Sultan
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.'
We built a Cache component that take advantage of the SQL Server 2005 query notification mechanism, all went well , we tested the component in a console application , and notifications kept coming for as long time as the console application ran.
When we initiate our Cache Component in our web service global.asx application start event , the query notification works for a few minutes , but if we came after 10 minutes or so , we stoped getting notifications from sql, the SQL Server queue is empty , and all is showing that there is nothing wrong on the DB side...
Our Cache component is a Singleton class , that perform all registrations ,catch the notification events and resubscribe for notifications.
What can be the problem? is our Cache component object are being collected by GC?
Does IIS disposes the SQL Connection that the Query notification uses?
We are in development stage of an application which uses Query Notification feature of SQL Server 2005. When the development started what the development team did was, they just Enabled broker on the database by SET ENABLE_Broker statement and they started using Query Notification. Now the application is in testing phase and when we test with many user, the performance is really slow. What i want to know is , when we use Query Notification what all are the setting to be done at the database level. Is this setting enough. And also how can i tune this system. any useful link is appreciated.
I'm using query notifications and it worked fine as long as I just edited the data that the "query points to" (one table). That is, my graphical represenation of the data (in a gridview) was updated correctly with the new data 1ms after I changed it, just like it should.
When I inserted a new row to the table the query notification stopped working, meaning my gridview wasn't updated. Any idea of why? Also, after this, not even editing of the data made the query notification trigger so it must have totally stopped.
In order to avoid a large ammount of network traffic we decided to use Query Notification to Update our Clients. Usually we have 20 up to 200 Clients connected to the DB, and some of the data QN is pointing at, are changed frequently (about 5 times per second) . Now i heard it is recomended to connect just 10 Clients (max) to get acceptable performance out of SQL Server.
So my questions:
Is Query Notification the rigth technique to handle this kind of Data Changes.?
Is there an other feature or technique to get updated at client-side with less performance and ressource consumtion (without polling)?
Are there improvements in SQL-Server 2008?
How should the Server be configured to handle more than 10 Clients? Thanks in advance Raimund
Suppose I have two tables(Customer and Order) which are as follows:
Code: Customer customer_id first_name
[Code]....
Another thing I am concerned about is that in the line INNER JOIN Order ON Customer.customer_id = Order.customer_id , I have written Customer.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign?
I tried using Query Notification on my computer at home: * Win XP Pro with all the SPs and hotfixes * SQL 2005 with SP1 qand hotfix
Query Notification worked fine.
Then I tried using it at work:
* Win XP Pro with all the SPs and hotfixes
* SQL 2005 with SP1 qand hotfix
and I see the following error in the SQL server log file and notification does not get to the client app:
---------------------------------------------------------- Date 9/1/2006 10:18:30 AM Log SQL Server (Current - 9/1/2006 10:18:00 AM)
Source spid17s
Message An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domainmyuser', error code 0x6e. ----------------------------------------------------------
A similar error shows up in the machine's Event Log.
I am sysadmin and full OS admin on both boxes. The difference is that the computer at home is standalone while the one at work is part of a domain.
I found in error log of my server is full of message like this
The query notification dialog on conversation handle '{9AD14600-28AD-DC11-9B36-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-174a4df0-bac8-4a51-8564-28d750d7c11a'.</Description></Error>'.
My database is logging frequent errors and I am unable to determine the cause. These errors appear to be related to the Service Broker. Below is the database log file after a database restart and attempted access to the database through a web application. The first error (bottom of the logfile) is error 28054. I have searched on this error code and have found nothing helpful. Any assistance or direction would be greatly appreciated.
Database Log:
04/13/2006 12:26:05,spid22s,Unknown,An error occurred in the service broker message dispatcher<c/> Error: 15517 State: 1. 04/13/2006 12:26:05,spid22s,Unknown,Error: 9644<c/> Severity: 16<c/> State: 14. 04/13/2006 12:26:04,spid57s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.' 04/13/2006 12:26:01,spid22s,Unknown,An error occurred in the service broker message dispatcher<c/> Error: 15517 State: 1. 04/13/2006 12:26:01,spid22s,Unknown,Error: 9644<c/> Severity: 16<c/> State: 14. 04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.' 04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.' 04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.' 04/13/2006 12:26:01,spid52,Unknown,Service Broker needs to access the master key in the database 'CDR'. Error code:25. The master key has to exist and the service master key encryption is required. 04/13/2006 12:26:01,spid52,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.
I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:
I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.
This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:
private bool Start()
{
try
{
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop(_currentConnectionString);
if (SqlDependency.Start(_currentConnectionString))
{
if (connection == null)
{
connection = new SqlConnection(_currentConnectionString);
}
if (command == null)
{
command = new SqlCommand(_sqlQueryString, connection);
}
return (true);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
private void Stop()
{
SqlDependency.Stop(_currentConnectionString);
if (command != null)
{
command.Notification = null;
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
private void GetDataSnapshot()
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct pg.grid_id [Price Grid], pg.grid_name [Grid Name], case when exists (Select c.grid_id from customers c inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5 where c.type = 'C' and dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.
sys.conversation_endpoints shows one endpoint in status SO. (along with a lot of left over garbage - about 300K records - but this should not be a problem other than storage).
I know SP2 is suppose to solve the garbage records problem but this is not our problem. This setup was working and then just stoped.
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
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.
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.
