Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SqlConnection cn;
SqlCommand cmd;
SqlDependency dependency;
DataTable dt;
static string obj= string.Empty;
public Form1()
    {
        InitializeComponent();
        CheckForIllegalCrossThreadCalls = false;
        try
        {
            SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
            perm.Demand();
        }
        catch
        {
            throw new ApplicationException("No permission");
        }
    }

private void Form1_Load(object sender, EventArgs e)
    {
        MyMethod();
    }
void Dependency()
    {
        dependency = new SqlDependency(cmd);
        SqlDependency.Start(cn.ConnectionString);

        dependency.OnChange += Dependency_OnChange;
    }
void MyMethod()
    {
        try
        {
            cn= new SqlConnection("My Connection String");
            cmd = new SqlCommand("Select Column1,Column2,Column3,Column4,Column5 from [dbo].TableName where Column1=value", cn);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            Dependency();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (dr.Read())
            {
                obj=dr["Column2"].ToString();
            }
            MessageBox.Show(obj);
            cn.Close();
            cmd.Dispose();
            dr.Close();
    }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        MessageBox.Show("Data Change. " + e.Info.ToString());
        dependency.OnChange -= Dependency_OnChange;
        MyMethod();
    }


What I have tried:

This code is working on a local database. My question is, why is it not working when the database is on a server. When this code runs at the server, I get this error message (

"Login failed for user"
). However, this is working when I remove the dependency code block. So the ConnectionString and the sql query is not the problem. I added 'persist security info=True' to the connectionstring nonstop

"MessageBox.Show("Data Change. " + e.Info.ToString())"
working and e.Info value is Invalid. I have the most authoritative user.I gave all grant permission.But still it did not work.Help me please.
Posted
Updated 12-Nov-17 22:26pm

Is Query notifications ON on the server?
Enabling Query Notifications | Microsoft Docs[^]
 
Share this answer
 
Comments
CHill60 13-Nov-17 8:43am    
OP is trying to respond via solution 3 "yes open.Info value is Invalid when I run the code".
there is no one who can help me
 
Share this answer
 
yes open.Info value is Invalid  when I run the code.
 
Share this answer
 
Comments
Santosh kumar Pithani 13-Nov-17 7:52am    
Don't post like this its not a solution , give replay by using comment box

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900