To 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.
Hello, 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?
Have you ever tried to call the SqlDependency.Stop() method in a class destructor (C#)? It seems like the finalization process hangs after the call to the SqlDependency.Stop() method (for example the assignment after the SqlDependency.Stop() method call is never executed).
I tried to use the ADO.NET 2.0 tracing, and it shows that the SqlCommand.Cancel() method call throws an exception during finalization, but it€™s not possible to intercept it. Do you have any clue about it or have you ever experinced the same problem?
Using RAISERROR from within a stored prcedure will result in a SQLException being thrown at the client side of a JDBC connection. I am trying to provide a meaningfull error message to be shown to the end user. However, all exceptions thrown will inevitably start with : "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]". The following questions then arise :
- Is there a way of configuring SQL Server not to display this header ?
alternatively,
- Is there another way of conveying error messages to the client through JDBC ?
Hi, 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
Hi 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(); } }
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!
Hi 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
I 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:
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:
I 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:
Hello 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.
We 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.
I 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:
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 ?
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.
if i have a loop that runs through records in a dataset like thisfor(int i=0;i<ds.Tables[0].Rows.Count;++i) and in this loop i have several sql commands that run as a transaction in a try / catch block like : try{ // do stuff}catch{ trans.RollBack();}how can i keep the loop going even if a transaction failed. So the transaction works for each individual row. if row 100 fails for whatever i would like the loop to continue running, do i just simply remove the "throw" and it will continue looping ? my catch block currently looks like catch(Exception ex){transaction.Rollback();activity.Log("Transaction aborted, rolling back. Error Message: " + ex.Message + " Stack Trace: " + ex.StackTrace.ToString());throw; }thanks,mcm
I'm sure that the try part of following code are all executedand the session("isLogin") has set to Truebut it always catch a exceptionand redirect to error1.aspx can't figure it out 1 Try 2 mySqlCon = New SqlConnection(strMySqlCon) 3 mySqlCmd = New SqlCommand(strMySqlCmd, mySqlCon) 4 5 mySqlCon.Open() 6 myDataReader = mySqlCmd.ExecuteReader() 7 8 If myDataReader.Read() = True Then 9 10 11 webPwdMd5 = System.Web.Security.FormsAuthentication. _ 12 HashPasswordForStoringInConfigFile(Me.TextBox1.Text, "MD5") 13 14 If webPwdMd5 = myDataReader.Item("password") Then 15 Session("isLogin") = True 16 'Me.TextBox1.Text = "ppp" 17 Response.Redirect("main.aspx") 18 Else 19 Session("islogin") = False 20 Me.Label1.Visible = True 21 End If 22 Else 23 Session("isLogin") = False 24 Me.Label1.Visible = True 25 End If 26 27 mySqlCon.Close() 28 29 Catch Myexception As Exception 30 Session("isLogin") = False 31 Response.Redirect("error1.aspx") 32 33 Finally 34 35 End Try
I am a novice with SQL, and I am failing to understand an aspect of Transactions. The following is the code I have created, having referred to a handful of resources including MSDN documentation. public void CreateSection(Section section) { string sql = @"INSERT INTO sectiontable (nodeid, title) VALUES (@nodeid, @title) SELECT Scope_Identity() FROM sectiontable"; SqlConnection connection = new SqlConnection(this.connectionString); SqlCommand command = new SqlCommand(sql, connection); command.Parameters.AddWithValue("@nodeid", section.Node.Id); command.Parameters.AddWithValue("@title", section.Title); connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { command.Transaction = transaction; object result = command.ExecuteScalar(); if (result == null) throw new DataException("Returned identity was invalid."); section.Id = Convert.ToInt32(result); transaction.Commit(); } catch (Exception exception) { string message = "Could not create section."; try { transaction.Rollback(); message += " Transaction was reversed."; } catch (SqlException booboo) { message += " An exception of type " + booboo.GetType(); message += " occurred while attempting to roll back the transaction."; } // Note: DataAccessException below is a custom class. throw new DataAccessException(message, exception); } finally { connection.Close(); } } What seems clumsy is that we must have connection.Open() outside the try-catch block. The following cannot work, because the compiler notes the "Use of unassigned local variable 'transaction'" at the location shown below: SqlConnection connection = new SqlConnection(this.connectionString); SqlTransaction transaction; try { connection.Open(); transaction = connection.BeginTransaction(); // commands execute transaction.Commit(); } catch (Exception exception) { transaction.Rollback(); // <-- Compiler notes use of unassigned variable. // (First testing the variable does not help.) // handle exception. } finally { connection.Close(); } The following also does not work, as the BeginTransaction() method must be called against an open connection. SqlConnection connection = new SqlConnection(this.connectionString); SqlTransaction transaction = connection.BeginTransaction(); try { connection.Open(); // commands execute transaction.Commit(); } catch (Exception exception) { transaction.Rollback(); // handle exception. } finally { connection.Close(); } So, in MSDN documentation, the connection.Open() method is called outside the try-catch block (as shown in my full code). Yet, that same documentation notes that this method can lead to two types of exceptions. So it seems that the use of transactions forces an exception-throwing method to be used outside of a try-catch block. My question, then: is this unavoidable?
we are converting Informix Stored procedures to SQL Server stored procedures.. In Informix they have handled the exceptions as shown below, ON EXCEPTION LET @l_status = 1; RETURN @l_delete,@l_status; END EXCEPTION
We have to convert this to the corresponding SQL server statements.. How to ahbdle exceptions in SQL Server.
First off, congrats and thank you to everyone at Microsoft for all of the hard work they have put into Sql Server 2005 and .NET 2.0 - it is simply amazing technology.
On that note, I was wondering if it was possible to create my own custom exceptions that I can throw in my stored procedures and then catch in my application code?
For example, say I wanted to create a Custom Sql Exception called "DuplicateEmailInSqlDatabaseTableException" and then, within a stored procedure where data is being attempted to be inserted, I could check for a duplicate email record and then throw the exception. At that point, I would like to be able to catch that exception in my C# data layer and work from there.
Is this possible? I feel like it could be but am unsure where to start.
I am trying to write a query that will only give me the data that is not in both queries. I need a query that will give me the data that is in the first query but not in the second query. I tried a union but that does not work. The date and PosType are important.
SELECT Symbol,PosType,HistDate
FROM EntityView
WHERE Histdate >'01/01/07' and PosType = 'S'
union
SELECT Symbol,PosType,HistDate FROM EntityView
WHERE Histdate >'01/01/07' and PosType = 'S' and EntityCode = 'HIS'
So I have some SQLCLR stored procedures, that use some .NET classes. I have a table in the database for exceptions, and I want to log all exceptions (except SqlExceptions of course) to that table.
SqlParameter param = new SqlParameter("@message", ex.Message);
cmd.Parameters.Add(param);
param = new SqlParameter("@stackTrace", "test");
cmd.Parameters.Add(param);
param = new SqlParameter("@localtime", DateTime.UtcNow);
cmd.Parameters.Add(param);
try
{
cmd.ExecuteNonQuery(); // always throws IOE, as does SqlContext.Pipe.ExecuteAndSend(cmd);
}
catch (InvalidOperationException)
{
return;
}
}
The exception is: _COMPlusExceptionCode = -532459699 (couldn't find anything useful on that)
Ideally I don't want to be passing the connection to my class all the time, which is why I wanted to have overloaded methods, some that take the connection, others that don't, and open their own. Neither scenario works unfortunately.
If I run the code under LogException in my caller class (ExceptionTest) it works fine (I mean doesn't throw this exception, but I can't call from a SqlFunction - different issue, SQLCLR restriction).
I've been trying to debug this for a while now, and I'm running out of ideas, so any suggestion(s) would be highly appreciated.
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)
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?
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...
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!
Hi, 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>
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.
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!
I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions: 1. Exclude seconds coming from the date which are Saturday and Sunday 2. Exclude seconds coming from time range between 7:01pm and 6:59am 3. Exclude seconds coming from Jan 1st and Jul 4th. How can I do this?
We are running into problems using CLR stored procedure in SQL Server 2005. We are using a transaction scope (ambient transaction). If an SQL exception with class 16 is thrown, and this exception is directly caught (and handled) then the transaction is somehow no longer valid. Subsequent use of this transaction gives the message "The current transaction cannot be committed and cannot support operations that write to the log file. Rollback the transaction".
Is there a way to gracefully handle the SqlException and continue the transaction?
