CODEWITHSUNDEEP
c#sqlt-sqlservice-broker
michał kudanowski
michał kudanowski

Reputation: 33

C# SqlDependency - Invalid object name

I want to check out SqlDependency but got problem while starting. I'm using code below (which is from https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency).

Error "System.Data.SqlClient.SqlException: 'Invalid object name '[core].[intServiceClient_Queue]'.'" is thrown while running a method SqlDependency.Start(connString,queue).

I am connecting with SSPI on Sql Server Admin account. I am sure that object is service broker queue and it exists. 

void Initialization()  
{  
    // Create a dependency connection.  
    SqlDependency.Start(connectionString, queueName);  
}  

void SomeMethod()  
{  
    // Assume connection is an open SqlConnection.  

    // Create a new SqlCommand object.  
    using (SqlCommand command=new SqlCommand(  
        "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers",   
        connection))  
    {  

        // Create a dependency and associate it with the SqlCommand.  
        SqlDependency dependency=new SqlDependency(command);  
        // Maintain the refence in a class member.  

        // Subscribe to the SqlDependency event.  
        dependency.OnChange+=new  
           OnChangeEventHandler(OnDependencyChange);  

        // Execute the command.  
        using (SqlDataReader reader = command.ExecuteReader())  
        {  
            // Process the DataReader.  
        }  
    }  
}  

// Handler method  
void OnDependencyChange(object sender,   
   SqlNotificationEventArgs e )  
{  
  // Handle the event (for example, invalidate this cache entry).  
}  

void Termination()  
{  
    // Release the dependency.  
    SqlDependency.Stop(connectionString, queueName);  
}

Upvotes: 1

Views: 1283

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

This is a bug/shortcoming in SqlDependency: it does not support schema names. [core].[intServiceClient_Queue] is taken to be the name of an object and escaped, producing an invalid object name. Move your queue to the default schema of your user (most likely dbo), or set the default schema to core and schema-qualify everything else.

See

Upvotes: 1

Related Questions