我不在服务器上使用sqldependency，但它正在使用本地服务器 [英] I don't use sqldependency at server but it is working local server

问题描述

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.

推荐答案

是查询在服务器上打开通知？

启用查询通知| Microsoft Docs [ ^ ]

Is Query notifications ON on the server?
Enabling Query Notifications | Microsoft Docs[^]

there is no one who can help me

yes open.Info value is Invalid  when I run the code.

这篇关于我不在服务器上使用sqldependency，但它正在使用本地服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！