I've wrote a component that take advantage of the query notification macjanizm, i use this component in a web service , and its working great, with a little problem , i have imporsonation enabled in my web service and a user and password is set in the identity node in web.config , the impersonation is working great and the web service is running under the user name i've set in the web.config , but, when i change a table in the database and the change event is raised in my application the user name is no more the user in the web.config...The user is now ASPNET ....
I have an sql dependancy setup on a particular table that is not updated all that often. When I load my application that has the dependancy code in it, the onChange event of the SqlDependancy fires over and over again non stop. I am positive I am not updating the information in the table I am querying so I can not figure out why it would do that. But I bet one of you knows! If you need code I can supply it and if you need anything else let me know. Thanks in advance.
I am using the SqlDependency to notify me of data changes in a table. However, as soon as I start it, the OnChange event fires over and over even though no data has changed.
Essentially All I want to do is be notified when records are inserted into a table. I am able to get another example to work using a Service Broker Queue and a sql query of WAITFOR ( RECEIVE CONVERT(int, message_body) AS msg FROM QueueMailReceiveQueue ) However I would prefer not to use a queue for once my messages have been read they are taken off the queue and I would rather control that manually.
Any help with getting SqlDependency to notify my app when records are added and not over and over when the data has changed would be great.
Is the SQLDependancy Class supported in SQL Server Express? I have code that is working that uses the SQLDependancy and SQL Server Express but I have seen many post around the internet saying that this feature is not supported. I do not want to use this feature to later find out that a service back breaks it or that Microsoft will not do support for it.
I have a windows application in C# that pretty much is a copy of the example found in http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx although I am using a different database.
My problem is that the SqlDependency event OnChange is firing all the time although on insert, update or delete is performed in the database. The event fires approximately 1000times in one minute!
After many problems with permissions I have got got SQL to accept a notification request but the public static void OnChange(object sender, SqlNotificationEventArgs e) is never triggered. The notification registers and de-registers ok. The same connect string successfuly connects to the same database to process queries.
I can see the GUID suffixed stored procedure, queue and service being created. Where does SQL2005 store the address/name of the routine it is to trigger? (When the notification is cancelled, the guid-siffixed items disappear) I have looked at the generated stored procedure, queue and service, but there is no indication of what is to be called back.
I have followed the instructions at http://msdn2.microsoft.com/en-us/library/ms181122.aspx, but so far without avail. I have checked the Application and system event logs, but there is indication therein. Also the SQL log.
So my questions are:
1) Where is the callback stored (is it a pointer or an actual name) ?
"FROM dbo.request_queue INNER JOIN track on request_queue.track_id=track.track_id " +
"inner join artist on track.artist_id=artist.artist_id " +
"inner join album on track.album_id=album.album_id";
cmd.CommandType = CommandType.Text;
if (con.State != ConnectionState.Open)
con.Open();
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
SqlDataReader rdr = cmd.ExecuteReader();
List<Track> l = new List<Track>();
while (rdr.Read())
{
Track t = new Track();
t.TrackID = (int)rdr["track_id"];
t.Filename = (string)rdr["file_name"];
if (rdr["track_name"] != DBNull.Value)
t.TrackName = (string)rdr["track_name"];
t.TrackNumber = (int)rdr["track_number"];
l.Add(t);
}
rdr.Close();
and for some reason, after i do multiple changes to the request_queue table, (adding rows), the dep_on_change never fires, and if i check dep.HasChanges it is always false.
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))
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
Following are the two events frequently observed in the system event error log has any body come across these errors
1.The description for Event ID ( 318 ) in Source ( SQLServerAgent$XYZ ) could not be found. It contains the following insertion string(s):
2.he Open Procedure for service "MSSQLServer" in DLL "SQLCTR70.DLL" failed. Performance data for this service will not be available. Status code returned is DWORD 0.
I get this error in My Event Log on My WSUS server. I am running a Windows 2000 with SQL 2K SP4.
Connection to database failed. Reason=Cannot open database requested in login 'SUSDB'. Login fails. Login failed for user 'WSUSASPNET'.. Connection string: Data Source=WSUS;Initial Catalog=SUSDB;Connection Timeout=60;Application Name=WSUS SQL Connection; Trustedd_Connection=Yes;Pooling='true'; Max Pool Size = 100
i heard somewhere that there is a time a "time event wizard" or something like that in SQL. and i like to know were can i find it. any link or answer will be really appreciated thanks.
hello all, i have a nested gridview. how do i add a SELECTING event for this datasource for my inner gridview? because i need to increase the commandtimeout. thnx. private SqlDataSource ChildDataSource(string strCustno) { string strQRY = "Select ..."; SqlDataSource dsTemp = new SqlDataSource(); dsTemp.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString ; dsTemp.SelectCommand = strQRY; return dsTemp; }
i need some clarification here,i changed the CommandField for 'Delete" to the template field like this and its working well with the extender .<asp:TemplateField> <ItemTemplate><asp:LinkButton ID="lnkDelete" runat=server Text="Delete" OnCommand=onCommand_deleter CommandArgument="Deleting" CommandName="Delete" ></asp:LinkButton> <ajax:ConfirmButtonExtender ID="cnfDelete" TargetControlID="lnkDelete" runat=server ConfirmText="Are you sure you want to delete this ?" ></ajax:ConfirmButtonExtender> </ItemTemplate> </asp:TemplateField> I am confused b/c Earlier,i was handling the delete of a gridview row under OnRowDeleting event.Now its being handled by "onCommand_deletePlayer".Then why do I still need to handle onRowDeleting event?It gave me error,when i tried to avoid it.What exactly i shud code in OnRowDeleting. I am checking something like this rightnow in onRowDeleting,but this doesn't cancel the deletion despite of raising the messageif (gridView.Rows.Count <= 1) {e.Cancel = true; lblMessage.Text = "You must keep at least one record // e.cancel doe NOT work,rows gets deleted despite the message,if delete link is clicked..} Is there a way to stop the user from deleting the LAST and ONLY row from db for a given condition?thanks
We are trying to schedule a DTS package. If we run the package when the server is logged in the job works. If we logoff the server the job fails. We looked in the NT Event log and we get a message from the SQLServerAgent that the job failed with Event 208. If we check the job Run History in the Next Run we get message 'Date and Time is not Available'. I should mention that the DTS pachage contains some mapping to drives on the server. Any ideas? We have been struggling with this for a long time. Thanks.
Why would I get an application event viewer error when I truncate the transaction logs on my databases? All the error tells me is that i truncated the database log.
I need to run an SQL job if a specific file exists. Actually, that specific files gets created different time in different day. I need to run the job when the file arrives. Is there any way to do this in SQL? I have done this in seagate scheduler but I need to do it in SQL. Please help!!!!!!!
Hi, We would like to capture events in our system. There seem to be three obvious capture points for us - application, triggers, transaction log. The latter seems to be the most attractive, since we`re looking for a solution with minimal performance impact. In general, our problem is similar to populating data warehouses from on-line databases. Can anyone proffer some advice? In particular, being quite new to SQL Server, I am not sure how difficult/possible it is to read the transaction log in order to cull events. Some direction here would be greatly appreciated. Thanks, Karl
I want to experiment with setting the Truncate Log on Checkpoint option to True to see if this will lessen the chance of my transaction log running out of space. Before I do this I want to be sure that the Transaction Log is not tied to the NT Event Log, SQL Error Log, etc. For example, does the NT Event Log (or any other log) use the Transaction Log? Thanks, Kevin.
I see these 4 events posted in my event log every second. I am using SQL 6.5 The first event says that "Login succeeded- User: probe Connection: Non-Trusted" The second event in the log has this description "DB-LIBRARY - SQL Server message: EXECUTE permission denied on object sp_replcounters, database master, owner dbo" The third event has "DB-LIBRARY error - General SQL Server error: Check messages from the SQL Server." and the fourth event contains "CollectSQLPerformanceData : dbsqlsend failed "
These messages are being posted repeatedly. Could someone shed some light on this please. Thanks Sudarshan
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?
We have a data import t-sql job which runs every morning to extract data from a large Unix database. This has worked fine on an old server which ran the same SQL version and service pack.
The t-sql uses a linked server and simply truncates the local tables and reimports the whole lot. Appreciate there are other ways to do this but it works fine for a medium sized business and is not mission critical.
However, when we have moved this onto the new server its throwing an error message in the event log when the job finishes. It actually completes the job and reports it as successful but at the exact same time it throws the following error message into the event log:
Error: 0, Severity: 19, State: 0 SqlDumpExceptionHandler: Process 14 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
INSERT INTO arista_caclient SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caclient WHERE cl_datopn>=''01/01/1900'' OR cl_datopn is null') INSERT INTO arista_camatgrp SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM camatgrp WHERE (mg_datcls>=''01/01/1900'' OR mg_datcls is null) AND (mg_datopn>=''01/01/1900'' OR mg_datopn is null)') INSERT INTO arista_camatter SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM camatter WHERE (mt_estcmp>=''01/01/1900'' OR mt_estcmp is null)') INSERT INTO arista_cabilhis SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cabilhis WHERE (bh_bildat>=''01/01/1900'' OR bh_bildat is null) AND (bh_laspay>=''01/01/1900'' OR bh_laspay is null) AND (bh_rundat>=''01/01/1900'' OR bh_rundat is null) AND (bh_remdat>=''01/01/1900'' OR bh_remdat is null)') INSERT INTO arista_cablaloc SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cablaloc') INSERT INTO arista_cafintrn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cafintrn WHERE (tr_trdate>=''01/01/1900'' OR tr_trdate is null)') INSERT INTO arista_cadescrp SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cadescrp') INSERT INTO arista_catimtrn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM catimtrn WHERE (tt_trndat>=''01/01/1900'' OR tt_trndat is null)') INSERT INTO arista_cafeextn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cafeextn') INSERT INTO arista_fmsaddr SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM fmsaddr') INSERT INTO arista_cabilloi SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cabilloi') INSERT INTO arista_caferate SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caferate')
I want to monitor updates on a certain table using an alert. When the table gets updated (any update), the alert will trigger a job.
Btw, I'm not allowed to add a trigger on the table, hence my idea to use an alert. Somehow I think I should use a WMI event alert, but I'm not sure which server event I should choose. In fact I'm not even sure I shóuld use a WMI event alert.
