Could not get notification in the .net service

Question

Could not get notification in the .net service

Sunil Gami

Grasshopper

Points: 22
More actions
May 25, 2009 at 6:36 am

#210102

Hi Guys,
I have crated the Notification service in .net 2008 and added handler on the change data even when I start the subscription by SqlDependency.start, it create queue in the service broker. But there is no rows return if I get the service broker data and even I am not able to get the notification in the front end service on modification of the data.
Can anyone have any solution on this problem. Or any expect comments or tips?
Thanks In Advance 🙂
Regards,
Sunil

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

What is your code that you are creating the SqlDependency with, and what kind of object are you "watching"?

Sunil Gami Grasshopper Points: 22 More actions · Answer 2

Here is the code which I am running to get notification

static SqlDependency sqlodepnd;

[STAThread]

static void Main()

{

try

{

string conStr = "Data Source=" + "ServerName\\SQLEXPRESS2008" + ";Initial Catalog=" + "NSDB" + "; Trusted_Connection=True;" ;

SqlConnection sqlcon = new SqlConnection(conStr);

if ((sqlcon.State == ConnectionState.Closed) || (sqlcon.State == ConnectionState.Connecting))

sqlcon.Open();

SqlCommand cmd = new SqlCommand("select [UserName] from [dbo].[users]");

sqlodepnd = new SqlDependency(cmd);

sqlodepnd.OnChange += new OnChangeEventHandler(onDatabaseChange);

SqlDependency.Start(conStr);

SqlDataAdapter sqlAdp = new SqlDataAdapter("select [UserName] from [dbo].[users]", sqlcon);

DataSet myData = new DataSet();

sqlAdp.Fill(myData);

Application.Run();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

public static void onDatabaseChange(object sender, SqlNotificationEventArgs e)

{

string DepInfo = e.ToString();

MessageBox.Show(DepInfo);

}

Also I have done the following things on the database to enable the CLR and NS.

1 Enable the .NET CLR

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

go

2 Enable Broker

ALTER DATABASE databasename SET ENABLE_BROKER

3 Grant Permissions

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username

Here in the UserName I tried my windows username and aspnet user .

Let me know your comment on it.

Thanks and Regards,

SuNiL