SqlDependency.Start(conn,queueName) Invalid object name '<queueNam

[JM]

I am getting a SqlException "Invalid object ame
'MySchema.MyNotificationQueue'." on the SqlDependency.Start( conn,
queueName) statement when I try to use a queue and service I created for a
Query Notification. It works when I do not use the queue argument to the
method.

I create and set the permissions with the following SQL.

USE MyDb
GO

-- =================================================
-- Define MyNotificationQueue Query Notification
-- =================================================
IF EXISTS(SELECT * FROM sys.services WHERE [name]=N'MyChangedService')
DROP SERVICE MyChangedService
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[MyDb].[MySchema].[MyNotificationQueue]', N'SQ'))
DROP QUEUE [MySchema].[MyNotificationQueue];

CREATE QUEUE [MySchema].[MyNotificationQueue] WITH STATUS=ON;
CREATE SERVICE MyChangedService
ON QUEUE [MySchema].[MyNotificationQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

-- =============================================
-- QUERY Notification Permissions
-- =============================================
GRANT CREATE PROCEDURE TO [dbRole1];
GRANT CREATE QUEUE TO [dbRole1];
GRANT CREATE SERVICE TO [dbRole1];
GRANT REFERENCES ON
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotifications]TO [dbRole1];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [dbRole1];
GRANT RECEIVE ON QueryNotificationErrorQueue TO [dbRole1];
GRANT RECEIVE ON MyNotificationQueue to [dbRole1];

GRANT SEND ON SERVICE::MyChangeService TO [dbRole1];

EXEC sys.sp_AddRole 'sql_dependency_subscriber'
EXEC sys.sp_addrolemember 'sql_dependency_subscriber','[dbRole1]'

GRANT SELECT TO [dbRole1];

GRANT VIEW DEFINITION TO [dbRole1];
GRANT VIEW DATABASE STATE TO [dbRole1];

I have check in SSMS and the queue(s) exist and the permissions on the
defined database role were set as specified.

The information from the SqlException is:
Message: Invalid object ame 'MySchema.MyNotificationQueue'
Class: 16
LineNumber: 1
Number: 208
Procedure: ""
Server: "MySystem"
Source: ".Net SqlClient Data Provider"
State: 12
Data:
[0] ["HelpLink.BaseHelpUrl"] - "http://go.microsoft.com/fwlink"
[1] ["HelpLink.EvtID"] - "208"
[2] ["HelpLink.EvtSrc"] - "MSSQLServer"
[3] ["HelpLink.LinkId"] - "20476"
[4] ["HelpLink.ProdName"] - "Microsoft SQL Server"
[5] ["HelpLink.ProdVer"] - "09.00.3042"

I ran the SQL Service Profiler and the RPC Starting right before the
Exception and User Error Message was
SQL Profiler Information

RPC Starting

SQL Settings TextData="exec sp_executesql N'WAITFOR(RECEIVE TOP (1)
message_type_name, conversation_handle, cast(message_body AS XML) as
message_body from [MySchema.MyNotificationQueue]), TIMEOUT @p2;',N'@p2
int',@p2=0"

Any ideas on the cause or what I missed?

Thanks

[Bob Beauchemin]

You can't specify a two-part queue name with SqlDependency.Start(). You must
specify a one-part queue name; this means that the queue must live in the
principal that's being specified in the first parameter (connection string)
default schema. Here's a reference:
http://www.sqlskills.com/blogs/bobb/2006/06/28/PreprovisioningAndSqlDependency.aspx

Hope this helps,
Bob Beauchemin
SQLskills

"JM" <J...@discussions.microsoft.com> wrote in message
news:861B17D3-EC50-49CA...@microsoft.com...

[JM]

Bob,

Thank you for your response. I used a one-part name the first time I tried
this. When I looked at the queues in ssms I noticed that the queues were
shown with the schema name. Therefore thay was my last attempt before
creating this entry.

Is there any other trace event I should be looking at to identify this
problem or permission I have missed?

[Bob Beauchemin]

Couple of things to look at.

You're granting permissions at the role level. Can your try 'execute as'
with the user corresponding to the login that you're using in the first
parameter if see if you can access all of the objects?

If you use a one-part queue name, the queue must be in the default schema
for the principal (user) specified. Roles (looks like you are granting
permissions on a role level) and users based on Windows groups cannot have
default schemas, so when you try this out with 'execute as' use only the
one-part queue name and see if it can be resolved.

To see if its the default schema problem, you can always try configuring the
queue in the dbo schema (and always use a one-part name in
SqlDependency.Start). See if this fixes the object resolution problem. If
so, its schema-related.

You could always run a SQL Profiler trace to see where it gets in the
proceedings, but you won't see the statement that causes the object
resolution problem.

I'll have a look at your permissions and see if I can spot anything. I'll be
traveling for the next few days, but send me mail, I have SqlDependency code
that works. But first try using a custom user, assigning permissions to the
user (rather than role) and using the dbo schema for your queue.

Hope this helps,
Bob Beauchemin
SQLskills

"JM" <J...@discussions.microsoft.com> wrote in message

news:733EE096-C4C3-4B8C...@microsoft.com...

[JM]

I tried your idea of using the default schema (dbo) for defining the
notification queues.

CREATE QUEUE [MyNotificationQueue] WITH STATUS=ON;
CREATE SERVICE MyChangedService
ON QUEUE [MyNotificationQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

The SqlDependency.Start(myConnection, "MyNotificationQueue") statement
worked but the execution of the database select command fails with "When
using SqlDependency without providing an options value, SqlDependency.Start()
must be called prior to execution of a command added to the SqlDependency
instance.

The above error caused me to look at the SqlDependency constructor. I was
using the single argument version where only the SQL command was given. The
three argument version solves the above error by setting the options argument
to define the service name and letting the time default to the server
default.

SqlConnection connection;
:
SqlCommand command = new SqlCommand(“SELECT F1,F2,…Fn FROM Table”,
connection);
:
SqlDependency.Start(connection.ConnectionString);
SqlDependency d = new SqlDependency(command, “service= MyChangedService”, 0)
d.OnChange += new OnChangeEventHandler( MyOnChangeMethod );
:

You would think if you could define a queue with a schema you could use the
SqlDependency Start method with the schema, otherwise why allow the queue
create statement to include a schema?

When I get a chance to redact my generic class that sets everything up I
will post it.

Thanks for pointing me in the right direction.

[news.microsoft.com]

I agree. Filed it as Connect issue
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=365819

Cheers,
Bob

"JM" <J...@discussions.microsoft.com> wrote in message

news:C2B71A69-2037-44D2...@microsoft.com...

[news.microsoft.com]

I agree. Filed it as Connect issue
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=365819

Cheers,
Bob

"JM" <J...@discussions.microsoft.com> wrote in message
news:C2B71A69-2037-44D2...@microsoft.com...