SQLDependency In Web Farm
Sep 24, 2007Hi everyone,
Anyone know how SQLDependency works in a web farm setup? Which machine will receive the notification - All of them or just the machine that started the dependency?
Marc
ADVERTISEMENT
Question/doubt On SqlDependency.Start/SqlDependency.Stop
Apr 24, 2008Hello, I have a Question/doubt on SqlDependency.Start/SqlDependency.Stop.
After the implementation of my solution the question sounds "stupid" ... maybe is only the stress due to the fact that I'm going to deploy the application on Test.
Anyway.. I developed this application (windows application) that uses query Notification features to subscribe and get notification from Sql Server 2005 so it use the tipical pattern: SqlDependency.Start, SqlDependency_OnChange,SqlDependency.Stop.
This is application is installed on several client so actually I get have several notifications running on the Server.
When one of the client exit the application and call SqlDependency.Stop the service, queue and procedure are dropped.
Does the command drops all the query notification' service, queue and procedure running on the instance or only the one created by the specific user?
Thank you
Marina B.
SQL Server Used With Web Farm
Jul 17, 2007I am developing a web app that uses a SQL Server backend and will be served by a Web Farm at some point in the future. Can anyone give me some guidance at to what the common architectual considerations are when using SQL Server with a Web Farm? Initially, would it be allright to continue to have a single SQL Server on the network, with several webservers connecting to it instead of just one, or would this be a big performance nightmare? Eventually, since the goal of using the Web Farm would be to reduce individual server load, I would think having multiple SQL server instances would be preferred. Is this even done in the real world? I'm not a dba, so I'm not quite sure of the how multiple instances would be synchronized so that they held the same data and how applications on varying web servers would know to connect to one or another (by varying the connections string, perhaps). Can anyone give me any guidance? Thanks!
View 4 Replies View RelatedScale Out Web Farm 401.1 - Unauthorized
Jan 18, 2008Our topology:
Internal corporate network domain utilizing Integrated Windows Authentication between local workstations and the web and database servers. (domain similiar to: http://reports.ourdomain.com)
Machine #1: Windows 2003 Server w/SQL Server 2005 (SP2 and hotfixes applied) running SSRS.
Machine #2: Windows 2003 Server w/SSRS only. (pointing to the SQL DB on Machine #1)
Machine #3: Windows 2003 Server w/SSRS only. (pointing to the SQL DB on Machine #1)
Device #1: Arrowpoint load balancer for Machines #2 & #3 (w/sticky sessions).
We recently added 2 web servers and configured them to load balance the web requests to a seperate SQL Server machine. (the load balancing is done thru an Arrowpoint device not Microsoft NLB)
The installation of reporting services (on Machine #2 & #3) went smoothly and the configuration was a breeze. But we could not access the "http://reports.ourdomain.com/Reports" directory for reporting services on the 2 web server machines. We received the "401.1 - Unauthorized" http error.
Since we were using a domain name to access the report server we made sure to update the <URL> tag in the config files.
File location 1:
C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerRSWebApplication.config
<ReportServerUrl>http://reports.ourdomain.com/ReportServer</ReportServerUrl>
File location 2:
C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServer
sreportserver.config
<UrlRoot>http://reports.ourdomain.com/reportserver</UrlRoot>
We still received the error, so then we added a couple of entries to the HOSTS file on each web server:
File location -->
C:WINDOWSsystem32driversetcHOSTS
In order for the load balancers to "see" themselves from the domain reference we had to update the HOSTS file
on each web server to have the domain name "reports.ourdomain.com" point to the IP Address of the specific web server.
(we also added the IP of Machine #1 just to be safe):
MACHINE #2's HOSTS file:
127.0.0.1 localhost
10.10.0.1 Machine1
10.10.0.2 reports.ourdomain.com #IP of Machine2
MACHINE #3's HOSTS file:
127.0.0.1 localhost
10.10.0.1 Machine1
10.10.0.3 reports.ourdomain.com #IP of Machine3
Then the web servers were able to authenticate the users using IWA and NTLM
and we could access the report servers normally. Even the the scheduled reports
were being load balanced and emailed from the web servers which we didn't
expect to happen. (pretty cool!)
I hope this helps someone else as it tooks us about 4 days to figure this out.
SSRS WEB Farm ReportServer Service
Jun 28, 2007Server Software
SQL Server 2000 (all sps up to date)
SSRS 2000 (all sps up to date)
Scenario:
1 - SSRS + SQL2000 Database server
2 - Web servers
We have installed SSRS on 2 web servers that are acting as a web farm for a central Report Server.
After we installed SSRS on the 2 web servers the scheduled reports on the central Report Server stopped working.
We tried disabling the ReportServer service on the web servers and then the scheduled reports began to work again.
Why would the ReportServer service on the 2 web servers interfere with the central Report Server?
Can the web servers be used to load balance the scheduled reports?
Any advice would be appreciated.
Recovery :: Several AG For One SharePoint Farm / How Many Listeners
Sep 22, 2015I have to install several AG on a cluster for a whole SharePoint production farm, it's a nice to have (1 AG for Search database, 1 AG for Profil database, 1 AG for Content database, 1 for applicatif, etc...) We have 2 Front End load balanced, 1 application server and 3 SQL HADR: 2 nodes on synchrone (sql 2014 enterprise licence) and the 3rd asynchrone (standard licence).
The SharePoint farm is configured with the SQL Alias on the primary node, then if there is a failover on node 1, I must move the sql Alias to the second node manualy, unless I'd configured this Alias to point on the AG listener of the availability group in order to use the automatic failover. But as we have several AG, should I create one listener per each AG?
And which AG listener should I have to configure on the SQL Alias then ?
SiteMap Query Notification In Web Farm Scenario
Feb 11, 2007Dear,
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
SQL Cache Dependency With Cluster SQL Server && Web Farm
May 30, 2008I have implemented SQL cache dependency to invalidate cache whenever the result of the stored procedure gets changed. The stored procedure is written obeying all the notification rules.
Our problem:
As long as we are working on Local machine, Development server or Pre production server its working fine means the cache gets invalidated whenever there is any change in the result set. But the same implementation doesn’t work in Production. Cache does not get invalidated.
Difference in Production environment and other environment:
Production uses cluster SQL server, others use standard SQL server. Production has load balancing means application is deployed on 6 servers which hits same clustered SQL server. In application’s global.asax file i have Start dependency so I see 6 different notifications started in SQL server which is right. But whenever there is a change in result set the cache doesn’t get invalidated in any of the application.
My question is
Do we have to follow any different kind of implementation of SQL cache dependency for web farm and cluster SQL server scenario?
On the same note I would like to add, on the same SQL server I have one more database and a different application is accessing this database. This application is also using SQL cache dependency and its working fine. The only thing this .Net application is NOT deployed on web farm. Its deployed on single application server.
Distributed Processing Farm Using Broker For Middleware.
Sep 26, 2006In our current project, we are attempting to use Broker for the middleware for a "queuing" solution that helps in throttling & load-balancing incoming messages. These messages could potentially be either long-running jobs or simply an incoming flood of messages received from our integration web services that are called upon via BizTalk.
For the posting of messages onto the "to do" queue, we are implementing the "fire & forget" pattern that has been discussed here previously and on Remus' blog. This aspect of the design seems to work fine for us. Currently we have this setup where activation is occuring on the target queue, but we don't want to hold open a thread there on the server while waiting for our synchronous calls to various web-services on the processing "farm" to complete.
The next evolution of our design is to try and move activation from off of the primary SQL cluster itself (i.e. activation is currently happening on the clustered SQL boxes) onto the individual processing nodes. For this model, we are looking at using SQL Express on each of the nodes as has been suggested here on the forums for other similar scenarios.
For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.
To invoke the processing on each of the nodes, we need some form of mechanism to send a "wake up" and do the "reads" since no message is being sent to the node itself for any form of activation based on a queue that receives the actual "job". On the same hand, we are considering having a "wake up" queue on the nodes where a simple "wake up" message could be sent to all nodes/queues and then activation on those queues would then invoke the processing "loop" for each node.
My question is how to best establish this "wake up" call to each node. I think I've read about a queue that is internal to Broker itself that receives a message when new items are received in any queue. My initial thought is to put activation on that queue and have a procedure that sends the "wake up" to each of the nodes in our processing farm.
I am looking for any input where others have attempted to solve this type of problem with Broker.
Regards,
Bradley A. Hehe
The RPC Server Is Unavailable Exception - While Performing Web Farm Installation
Aug 28, 2007Hi
I am trying to create a scale-out deployment of Reporting Services
2005 on a NLB cluster web farm by following the instructions in:
http://technet.microsoft.com/en-us/library/ms159114.aspx
However, when I install and configure Reporting Services on the second
node, and then go back to the first node to join the second node to
the scale-out deployment (as described in step 11 under 'To install
and configure the second report server instance"), I get the following
exception:
ReportServicesConfigUI.WMIProvider.WMIProviderException: An unexpected
error has occurred. Details:
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) ---
> System.Runtime.InteropServices.COMException (0x800706BA): The RPC
server is unavailable. (Exception from HRESULT: 0x800706BA)
at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(Object o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String
machineName)
--- End of inner exception stack trace ---
at
ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String
machineName)
at
ReportServicesConfigUI.WMIProvider.RSInstances.GetInstance(String
machineName, String instanceName)
at
ReportServicesConfigUI.Panels.ClusterManagementPanel.ConfigureWebFarm(Object
sender, RSReportServerInfo[] rsInfos)
I have spent hours searching for ideas, but so far no success. I have
also tried the following:
1. Enabling DTC access
2. Ensuring RPC service is running on both web and db servers
3. Installing WMI Windows Installer Provider
4. Granting admin rights on both db and web servers to the account
under which Reporting Services is running
Any help or suggestions would be greatly appreciated. It looks like
this should be a straight-forward task.
Thank you.
Reporting Services In A Web Farm - Is SQL Server 2005 STANDARD Able To Accomplish This Task?
Jan 28, 2008Subject line pretty much sums up the question.
Any information and/or links would be appriciated greatly!
SSRS 2005 Web Farm Error : The Delivery Extension For This Subscription Could Not Be Loaded.
Jun 22, 2007We are using RS2005 for a year now and never had any problems especially with mail subscriptions.
Since we transferred from single RS machine to web farm everything works ok except mail subscriptions. Subscription sometimes (?!?!?!?!) refuse to export report to a document of any kind. I repeat that this is a random thing which is more often with PDF and less with Web Archive or Excel. We are using shared schedules and on demand execution of AddEvent stored procedure by night batches to send mail to users. Also, original error from RS log says something that this kind of operation is not supported for server working in native mode. We don't have SharePoint so we never used it in any other way then native mode and it worked.
Any idea will be appreciated!
Thanks in advance.
Update 26.06.2007
It seems that problem is not loading dll used to export report to a file but loading email subscription extension (dll that needs to be loaded for execution of email subscription.)
Original error from RS Log:
w3wp!library!1!06/22/2007-11:06:43:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation is not supported on a report server that runs in native mode., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation is not supported on a report server that runs in native mode.
w3wp!extensionfactory!1!06/22/2007-11:06:43:: e ERROR: Exception caught instantiating Report Server DocumentLibrary report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.SharePoint.SharePointDeliveryExtension.DocumentLibraryProvider' threw an exception. ---> Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation is not supported on a report server that runs in native mode.
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.CreateExtensionObject(Extension extConfig).
SQLDependency
Mar 31, 2007Hi,
I've read about the subject a little bit and they are talking abut caching; eventhough it's good for some reason for my purpose not needed. So I tred such code which doens't work:): (I also enabled service broker on sql2005)
Publicsqldep As New SqlDependency
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
SqlDependency.Start("Data Source=VPS05-101SQLEXPRESS;Initial Catalog=EM;Trusted_Connection=Yes")
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
If sqldep.HasChanges = True Then
MsgBox("data changed")
End If
End Sub
well what I'm doing is a very simple communication window on my web page where my users can post messages. There is a aimple chat table in my DB that store basicly the time and subject posted. And instead of making useless postbacks I want to refresh the window when a data entered into that chat table... Is the code above related to that purpose?
Thanks
SqlDependency
Oct 19, 2007Hi all,
I am using sqldependency in my web project.Is it possible to use it in my web project.the code is below.
Here the dependency.onchange event is fired when i am changing somthing in database,otherwise the event will not fired so it is working fine.My problem is that when the event
" private void dependency_OnChange(object sender, SqlNotificationEventArgs e) { try {
RefreshData();
} catch (Exception) { }
SqlDependency dependency = (SqlDependency)sender; dependency.OnChange -= dependency_OnChange;
}" is fired so page get automatically give that changes in gridview.So what should be write after refereshdata() function get called and page should be refreshed there.please help me to solve my problem.
Note: This code is working fine in window based application.So it will automatically display the changed data in gridview so how to display it in grid without refreshing the page.???????
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { EnoughPermission(); connstr = "Data Source=NIHONGOW2003;Initial Catalog=DataWatcher;User ID=testlogin;pwd=testlogin"; string ssql = "select Id,Name from dbo.tbl_P ";
SqlDependency.Stop(connstr); SqlDependency.Start(connstr); if (connection == null) connection = new SqlConnection(connstr); if (command == null) command = new SqlCommand(ssql, connection);
if (myDataSet == null) myDataSet = new DataSet(); GetAdvtData(); } private DataSet myDataSet = null; private SqlConnection connection = null; private SqlCommand command = null;
private string connstr;
private bool EnoughPermission() { SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted); try { perm.Demand(); return true; } catch (System.Exception) { return false; } } SqlDependency dependency; private void GetAdvtData() { myDataSet.Clear(); command.Notification = null; dependency = new SqlDependency(command); Label1.Text = System.DateTime.Now.ToString(); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(myDataSet, "dbo.tbl_P"); GridView1.DataSource = myDataSet; GridView1.DataMember = "dbo.tbl_P"; GridView1.DataBind(); } } private void dependency_OnChange(object sender, SqlNotificationEventArgs e) { try {
RefreshData();
} catch (Exception) { }
SqlDependency dependency = (SqlDependency)sender; dependency.OnChange -= dependency_OnChange; }
private void RefreshData() {
Label1.Text = "Database had some changes and are applied in the Grid"; GetAdvtData(); }
}
Thanks
swapnil
Looking For Help With Sqldependency
May 30, 2007Hello all,
I've been digging through this forum for an hour or two, and not found anyone having the same problem we are having.
Basically, we have a Windows service that uses sqldependency to get a notification on the existence of records to be processed. When the service receives the notification, it spawns multiple threads which each work on one of the records in the table, moving the records to a "completed" table. When the table is empty, the service sets up the sqldependency again and waits to do the whole thing over.
Our problem is, occasionally it stops being triggered. We don't know if the service stops listening, or the notification stops being sent, or if there is some other situation where the network connection between the db and the service is severed and the pieces can't resume their conversation.
In order to "fix" the problem, we restart the service. It does it's normal run of cleaning out the table, then sets the notification up again and works for another few hours before it flakes out again.
At the moment, we can't figure out how to determine whether it is the app, or the database, or network (or some combination of any of these parts) that is failing. If I could get some guidance as to how to start narrowing it down, I would be very grateful!
Let me know if you need more specific info, and thanks for any help you guys can offer!
Mike
Possible SqlDependency Listener Bug
Oct 4, 2007I am new to Service Broker and Query Notification. I've used Books Online and the other help files to successfully create a class to subscribe and listen for mods to a table on a SqlServer 2005 (SP1 applied) server. My prototype worked exactly as expected.
However, when I put my code into production, the listener was calling my NotifyEvent() callback repeatedly, and without any changes being made to the table! Of course, I'd tested with a simple two column table and implemented with a more complex multi-column one. After some experimentation I discovered that the problem occured when the SELECT statement refrenced a "blob" column.
That is, the CREATE TABLE command included a column:
[Data] [varbinary] (max) NULL
and the SELECT statement looked like:
private const string _select = "SELECT [Role_s], [User_s], [DataType], [Project_s], [DataName], [tag], [Data_s], [ScopeType], [ScopeOwner], [Data1], [Data2], [Data3], [Data4], [IsProject], [Data] FROM [ovs].[OVS_AppData_tbl] WHERE DataType='SchedTask'";
SqlServer called my NotifyEvent() continuously. If I removed the [Data] column from the select, then the code worked as I had tested. I also tried this on another table (fewer columns) that also had a [Data] defined similarly, and again the continuous notifications came. I've got a workaround because I also calculate a hash on the [Data] column, and I can "watch" the hash instead of the underlying [Data].
Oh, when I ask SSMS to script the table with the [Data] column it gives me:
[Data] [image] NULL
This may (or may) be relevant (vs. varbinary).
SQLDependency Is Very Slow
Nov 3, 2006I posted this in the .Net data access forum with no replies, so I'm trying it here and apologize for the cross-post.
I'm researching using SQLCacheDependency in an application to keep caches up to date between applications. I could also use SQLDependency, just haven't tried that yet.
I've tried a test where I read 1000 rows from a database, cache each one, and create the cache dependency. The dependencies work fine.
My problem is that it is 100 times slower to read and create a dependency than it is to just read the row.
At this rate, I need a 99% cache hit ratio just to make my caches break even! Is caching even worth it at this rate? Why is it so slow? Thanks very much for any insight.
Here is the code:
for (int i = 0; i < 1000; ++i)
{
SqlCommand oRowCommand = new SqlCommand(
"[dbo].[usp_tblDoctor_Select]",
oConnection);
oRowCommand.CommandType = CommandType.StoredProcedure;
oRowCommand.Parameters.Add(new SqlParameter("@id",i));
SqlDataReader objReader = null;
SqlDependency oDependency = new SqlDependency(oRowCommand);
oDependency.OnChange += new OnChangeEventHandler( OnNotificationChange);
objReader = oRowCommand.ExecuteReader();
objReader.Close();
}
And the stored procedure that it uses for the dependency:
CREATE procedure [dbo].[usp_tblDoctor_Select]
@id int
as
select fullname from [dbo].[tblDoctor] where [dbo].[tblDoctor].doctorid = @id
GO
I also tried to end old conversation handles which appear to never get cleaned up. I was able to clean them up but this did not fix the problem either.
Here's a snapshot of the SQL profile showing reading of one row and the creation of the associated dependency:
With sql dependencies
RPC:Starting 2006-11-01 11:41:52.793 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
SP:Starting 2006-11-01 11:41:52.793 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
SP:StmtStarting 2006-11-01 11:41:52.793 select fullname from [dbo].[tblDoctor] where [dbo].[tblDoctor].doctorid = @id
QN: parameter table 2006-11-01 11:41:52.823 <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>LRU counter reset</qnev:EventText><qnev:TableId>1617492891</qnev:TableId></qnev:QNEvent>
QN: parameter table 2006-11-01 11:41:52.823 <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>reference count incremented</qnev:EventText><qnev:TableId>1617492891</qnev:TableId><qnev:RefCount>30293</qnev:RefCount>
QN: subscription 2006-11-01 11:41:52.823 <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>subscription registered</qnev:EventText><qnev:SubscriptionID>106047</qnev:SubscriptionID></qnev:QNEvent>
Broker:Conversation Group 2006-11-01 11:41:52.823
Broker:Conversation 2006-11-01 11:41:52.823 STARTED_OUTBOUND
SP:StmtCompleted 2006-11-01 11:41:52.793 2006-11-01 11:41:52.843 select fullname from [dbo].[tblDoctor] where [dbo].[tblDoctor].doctorid = @id
SP:Completed 2006-11-01 11:41:52.793 2006-11-01 11:41:52.843 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
RPC:Completed 2006-11-01 11:41:52.793 2006-11-01 11:41:52.843 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
Without SQL dependencies, the operation completes in less than the granularity of the measurement.
RPC:Completed 2006-11-01 11:47:52.810 2006-11-01 11:47:52.810 exec [dbo].[usp_deleteme_jerel] @id=N'7'
SQLDependency, SqlNotificationRequest XML
Jun 6, 2007Hello i'm a beginner with SSB. I wandering how can i retreive data when change occured on a specific table. At this time i'm able to receive a notification when data has changed on my table but if i would like to check data change on several table how to make it work??
I think meta data can be accessible in xml format but i have to clear idea on how retreive changed row information and table name from meta data.
I Suppose this can be achieve with SSB
Any help will be appreciated...
SqlDependency And TemplateLimit
Jan 8, 2007We are currently in the process of testing a smart client application that makes use of SqlDependency to monitor and cache relevant data for requesting clients.
As part of our load testing we increased the number of clients requesting data. As we did this the server side process threw an exception and stated that a TemplateLimit had been reached.
TemplateLimit is described as "The subscribing query causes the number of templates on one of the target tables to exceed the maximum allowable limit." from the msdn documentation about the SqlNotificationInfo enumeration.
Is the TemplateLimit configurable and if not, what is the maximum allowable limit?
Hope someone can enlighten me on the limitations of query notifications.
Thanks, James
SQLDependency With Identical DB's
Aug 22, 2007I have a Client-Server - App where every Client-User has his own DB. The server is monitoring
changes to all Client-DB's via SqlDependency.
My problem can be reproduced with a small application, it even might be a €œfeature€? and not a €œbug€?:
- Consider two Databases TestDb1 and TestDb2 running on one SQL Server 2005 instance.
- Both DB€™s have identical Schemas.
- Consider the two DB€™s have each one table named €œTable1€?.
- Both tables have the same schema as already mentioned (the fields Id and Text).
- Now I setup a SQLDependency object on each Database:
private void InitSQLDependencies()
{
string connstr1 = €œData Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDb1€?;
string connstr2 = €œData Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDb2€?;
SqlDependency.Start(connstr1);
SqlDependency.Start(connstr2);
using(SqlConnection connection = new SqlConnection(connstr1))
{
string ssql = €œSELECT Id,Text FROM dbo.Table1 €œ;
SqlCommand command = new SqlCommand(ssql , connection);
SqlDependency dependency =new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
using(SqlConnection connection = new SqlConnection(connstr2))
{
string ssql = €œSELECT Id,Text FROM dbo.Table1 €œ;
SqlCommand command = new SqlCommand(ssql , connection);
SqlDependency dependency =new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
}
If I make any changes to the Table in TestDb1 I get two notifications with the different Id€™s but the same Info,Source,Type (saying e.g. Data,Change,Update).
If I make changes to the Table in TestDb2 I again get two notifications with the same result. As soon as I rename the Table in one of the Db€™s (e.g. Table2) and also change my Sql-Query in the code €“ I get just one
Notification as expected. This behaviour is the same even If I change the connectionstring so that it points to another machine.
So it somehow seems to fire a notification for every change to a table with the same name €“ regardless of the connectionstring where the physical change was done.
Does anybody know if this is a wanted behaviour of SqlDependency ?
Does anybody know how I can set this up so I can have two DB€™s with identical Schemas and only get a Notification from the DB I actually changed ?
SqlDependency And FIPS
Aug 30, 2007Hello!
I am developing with .NET 2.0 and SQL Sever 2005 on WinXP for a production system of Windows 2003 Server with FIPS enabled (http://www.itl.nist.gov/fipspubs/).
When our team tries to use SqlDependency, we get FIPS exceptions. I assume that this is because the inner workings use encryption algorithms that are not FIPS-compliant. Is there a way to configure my application (or even the entire machine) such that SqlDependency will use FIPS-approved encryption?
I guess my real question is how can I use SqlDependency on a FIPS-enabled machine?
We have been using SqlNotificationRequest, but I'm now developing a client, of which, we require multiple instances. So we've run into the problem where when multiple clients run, only one will receive and process a notification.
Thanks!
John
DB Owner && SqlDependency Problem
Mar 24, 2006I deleted the account owning Test Database after creating Test Database.
Everythings work well except SqlDependency.
SqlDependency OnChange Event is not working.
I have tested SQL 2005 ent RTM & VS 2005 Pro RTM.
I'm curious why it happened.
Thank you.
SqlDependency Invalid Statement... Why?
Apr 7, 2006Folks,
Hoping you can help explain why I get this error.
I'm setting up an SqlDependency and things are starting to come together, but the following SELECT query results in a callback with type = Invalid and source = statement. However, I don't understand why that should be, as the select works fine "standalone":
mWatchQuery = New SqlClient.SqlCommand( "Select [pkLogId], [fkWho], [When], [WorkId], [What], [fkEventId] From dbo.tblLog", mDatabase.Connection)
mDependency = New SqlClient.SqlDependency(mWatchQuery)
AddHandler mDependency.OnChange, AddressOf mDependency_OnChange
I tried changing it using a table name alias L and L.[fkWho], L.[When] etc, but that fails too.
The property mDatabase.Connection returns the SqlConnection object for the object's database connection. Note I'm using the same connection every time - problem?
The tblLog DDL is as follows. Is it the use of a Text field?
CREATE TABLE [tblLog](
[pkLogId] int IDENTITY(1,1) NOT NULL,
[When] [datetime] NULL,
[fkEventId] tinyint NULL,
[WorkId] int NULL,
[fkWho] [bigint] NULL,
[What] [text] NULL,
[IsRTF] bit NULL,
PRIMARY KEY ( [pkLogId] ASC )
) TEXTIMAGE_ON [PRIMARY]
Lifetime Of SqlDependency Objects?
Sep 17, 2007
hello,
I am now using SqlDepency objects in a WCF windows service. This service could very well be running for weeks or even months at a time, in a perfect world...
I have some shared, global data caches that I now update only when the table's data changes, thanks to the SqlDepedency objects and Service Broker. I only have one question - what kind of considerations must I make when using this inside a long-running windows service? What if my SQL Server crashes, or the server is stopped and restarted, or someone trips on the cord... Will everything automatically work just as it should, or must I call SqlDepedency.Start() again, or possibly re-load my dataset and re-wire my OnChanged event to my SqlDepdency object? Is there any special events fired to notify me that I must do something of the sort? (e.g. maybe OnChanged will fire with some details). Or does Service Broker automatically handle all of this behind the scenes? Something tells me life isn't that easy...
Thanks very much,
Drew
Exceptions On SqlDependency.Stop?
Jan 3, 2008To preface, I am trying to get a notification in a client side app from SQL Server 2005 (Express) when a datatable changes. The problem is I am getting these exceptions and I have no idea what is causing them, if it is a serious problem, or how to debug it from here.
Here is the setup:
I am working with SqlDependency in .NET 2.0 and running a simple app that just calls start and stop on the dependency object. The result is 3 exceptions in System.Data.Dll
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: A severe error occurred on the current command. The results, if any, should be discarded.'
Operation cancelled by user.
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.
To duplicate this problem, do the following:
1) Create a database on SQL Server 2005 Express
2) Make sure the database has "ENABLE_BROKER" set.
(ALTER DATABASE <DataBase> SET ENABLE_BROKER).
3) Turn on CLR exceptions in your debugger (debug->Exceptions->Common Language Runtime Exceptions)
4) Create a C# windows app. Add the following in form1_load.
string connectstring = "Server=<SERVER>;Integrated security=true;database=<DATABASE>";
SqlDependency.Start(connectstring);
SqlDependency.Stop(connectstring);
5) Add using System.Data.SqlClient at the top of form1.cs
6) Run the app.
-Kent
SqlDependency OnChange Problem
Jan 4, 2008Hi,
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!
Does anyone have a solution to this problem?
Thanks
/Jonas Djurback
SQLDependency Disconnects For Web Application
Jul 7, 2006Hi,
On my ASP.NET 2.0 application and SQL 2005 database server, i am using SQLDependency API to recieve notifications on the dataset changes from SQL 2005 server. I get the notification sucessfully (irrelevant of number of attempts).
So here's the problem, i wait for 3-4 mins and make sure that there is no activity on my web server. Now if the data changes the service broker fires the event (i validated through trace). But the notification is never received by my web application. It gets lost in between.
I read about this 'Abrupt client disconnects' problem in the article http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx but this didn't help much.
Also i sometimes see following messages in the profiler
<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>broker error
intercepted</qnev:EventText><qnev:SubscriptionID>0</qnev:SubscriptionID><qnev:NotificationMsg><?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been
dropped.</Description></Error></qnev:NotificationMsg><qnev:BrokerDlg>9EF36F45-E00D-DB11-85AB-0003FF0B72DB</qnev:BrokerDlg></qnev:QNEvent>
and also
<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>broker error
intercepted</qnev:EventText><qnev:SubscriptionID>0</qnev:SubscriptionID><qnev:NotificationMsg><?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service
&apos;SqlQueryNotificationService-c0aac8a6-24a5-4a34-9d0f-0975538694c4&apos; because it does not
exist.</Description></Error></qnev:NotificationMsg><qnev:BrokerDlg>4A639911-ED0D-DB11-85AB-0003FF0B72DB</qnev:BrokerDlg></qnev:QNEvent>
Thanks,
Ashish
Getting SQLDependency To Fire The OnChange
Oct 20, 2006After 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) ?
2) What steps should I take to resolve this?
SqlDependency.OnChange() Not Firing
Aug 2, 2006I am running
ALTER DATABASE dbname SET ENABLE_BROKER
on my app startup and then SqlDependency.Start(), and then the following code
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT request_queue.track_id, track.file_name, track.track_number, track.track_name, " +
"artist.artist_id, album.album_id, artist.artist_name, album.album_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.
SQLDependency And Stored Procedures
Jun 6, 2006Hi,
Some info about my setup (all on the same local network):
Sql Server 2005 Standard Edition running on a Windows Server 2003 Standard Edition R2 development server
VS Studio .NET Standard Edition running on my XP Pro x64 workstation.
I am working with the source code for transact-sql and console application that can be found here: http://www.codeproject.com/useritems/SqlDependencyPermissions.asp
This example works perfectly.
I am now trying to get SQLDependency working with a stored procedure. I create a sp via Studio Manager on my workstation with the following transact-sql:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE sp_test
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT ID, Name From Users
END
GO
I then add execute permissions for the subscribeUser and startUser users.
I change the code in the console application like so:
SqlCommand oCommand = new SqlCommand("dbo.getUsers", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
When I run this application with this stored procedure rather than the "inline" SQL, the dependency.onchange event keeps firing and I get the following ouput:
Invalid: Subscribe
Any help or a solution will be greatly appreciated!
Kind Regards,
Imran007
Service Broker Broke With ASP.NET 2.0 SqlDependency?
Jul 20, 2007When starting an ASP.NET 2.0 I get the following 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.
Which happens on this line in my Global.asax [the penultimate line in Application_Start()]
SqlDependency.Start(css.ConnectionString);
Relevant web.config entries are:
<connectionStrings>
<add name="MyDBConnString_localdev" connectionString="Data Source=.SQLEXPRESS;Initial Catalog=MyDB_40;Integrated Security=True;Pooling=True;Min Pool Size=5;Max Pool Size=80;Connection Lifetime=300;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="10000">
<databases>
<add name="MyDB_40" connectionStringName="MyDBConnString_localdev" pollTime="2000" />
</databases>
</sqlCacheDependency>
</caching>
The database is running on my local machine and was created from a backup copy of the production database.
q1: How can I troubleshoot this?
I tried each of the following, but had to cancel them as nothing much had happened after 3+ minutes (apart from the activity indicator 'Executing query' showing a green rotation in SQL Server Management Studio Express.)
ALTER database MyDB set NEW_BROKER
ALTER database MyDB SET ENABLE_BROKER
q2: Why does it take so long to run either of the above commands - without actually getting an error?
q3: What Service Broker configuration do I need for my database?
q4: How can I test that it's properly installed?
SqlDependency And Selection Of SqlCommand Columns
Aug 2, 2007Is the following behaviour by design?
I created a SqlDependency object that uses a SqlCommand object that selects a single column:
Code Snippet
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select OrderID from Orders"; // Only one column!
cmd.CommandType = CommandType.Text;
SqlDependency d = new SqlDependency( cmd );
d.OnChange += new OnChangeEventHandler(dependency_OnChange);
Now when I change the value of any field in table Orders, my query notification handler fires. Can I change this behaviour such that SqlDependency handler fires only if the specified column (OrderID) is changed?
SQLDependency.Start() Permission Issue
Jul 11, 2007Hello,
I am trying to issue SQLDependency.Start() however I get the following error message.
{"The SELECT permission was denied on the object 'databases', database 'mssqlsystemresource',
schema 'sys'."}
If I change the permission of the logged-on account (the account is using SQL Server Authentication) to sysadmin, the statement runs successfully (I have tried lower permissions but none of these were successful). If I look in the Profiler it is the following call that fails :-
select is_broker_enabled from sys.databases where database_id=db_id()
Note that the broker is enabled - it is the read of whether the broker is enabled that is failing.
Note also that I issue a SQLDependency.Stop() just before this and it (the SQLDependency.Stop()) is successful.
Do I need to :-
- impersonate a sysadmin account when the SQLDependency.Start() is issued
- make my user sysadmin (not desireable)
- is there another (lower) permission (user or object) I can grant that allows the SQLDependency.Start() to be successful (best solution)
Many thanks
Travis