Cannot Find The Queue 'SqlQueryNotificationService-{guid}'
Jul 6, 2006
I am trying to get my application running on a customer site. They are being very picky about security on the database. What are the correct permissions to setup to allow my sql user to use the Service Broker.
Currently I am receiving the following error:
Cannot fin the user 'owner', because it does not exist or you do not have permission.
Cannot find the queue 'SqlQueryNotificationService-<guid>', because it does not exist or you do not have permission.
If I make my user db_owner it seems to work on other installs.
I have troublesome problem with service broker. I move a lot of databases from one server to another servers (by backup and restore) but always have problem with databases where service broker is enabled. Bellow is my scenario:
1. backup database db1 (srv1) 2. restore db1 (srv2, srv3, srv4.....) 3. drop manually Services and Queues, only green filed (http://img122.imageshack.us/img122/549/clipqk5.jpg) (srv2, srv3, srv4.....) 4. alter database db1 set enable_broker (srv2, srv3, srv4.....)
After that SQL Server log on each server is full bellow errors, nobody so far can help me
Could you help, please
############ SRV2 03/03/2008 18:54:41,spid21s,Unknown,The query notification dialog on conversation handle '{E6BF547B-0AE6-DC11-A4BD-00E08127B819}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-53c1dc9b-7d22-4310-a5b6-18ac97ae4bf3' because it does not exist.</Description></Error>'. 03/03/2008 18:54:41,spid27s,Unknown,The query notification dialog on conversation handle '{AA68F7D8-F7E5-DC11-A4BD-00E08127B819}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service
SERV3 03/02/2008 00:32:25,spid21s,Unknown,The query notification dialog on conversation handle '{2BC70B27-F1E3-DC11-AB50-0017085CAA29}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-abc6a7ce-53f1-411e-85a7-50c09e2699e1' because it does not exist.</Description></Error>'. 03/02/2008 00:29:20,spid22s,Unknown,The query notification dialog on conversation handle '{DF112861-F9E3-DC11-AB50-0017085CAA29}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service
SRV4 03/02/2008 16:15:07,spid25s,Unknown,The query notification dialog on conversation handle '{4179E578-7DE4-DC11-9A69-00145E1B71FC}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-01e16746-e114-4e8c-b587-91e93768a9a8' because it does not exist.</Description></Error>'. 03/02/2008 16:15:07,spid20s,Unknown,The query notification dialog on conversation handle '{5079E578-7DE4-DC11-9A69-00145E1B71FC}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service ############
GRANT SEND ON SERVICE::SqlQueryNotificationService TO guestI need to run that T-SQL command to allow Query Notifications and I keep getting this error.Cannot find the service 'QueryNotificationService', because it does not exist or you do not have permission.I am logged in with my user account with Administrator rights along with just about every permission I can think of for the SQL Server account. I even logged in as SA and it also fails when trying to run this command. This is being done on SQL Express. So I also logged into my Windows 2003 Server with a fresh install of SQL Server 2005 Dev Edition and this also fails with the same error.Clearly I need some permission correction or something else is missing from both installations.Please help!
I found every several minutes, sometimes one or twice in an hour, I still get a dozen error like below logged in SQL Log. See the error is "You do not have permission to access the service". I found some articles on other errors, but nothing about this error. I want to get more information on this, and a way to trace what is the permission about and which user doesn't have the permission.
Source spid26s
Message The query notification dialog on conversation handle '{E6FC299F-8BFE-DC11-A4E1-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-9ff8b39d-90a8-45bc-83a7-23837920774d'.</Description></Error>'.
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
we are currently using id's as primary key and replication is not part of our project. will this be a problem if we decide to do replication? will microsoft generate an identifier then. what is the advantages of using GUID now or doing it latter?
I'm working on a smart client app that has an offline sql express store and needs to work with several types of central databases (support for multiple products - ms sql, DB2 etc)
While trying to put together some offline functionality that needs the user to create records on the offline sql express data store, we've run into the need of being able to uniquely identify records so replicating the data in the offline store back into the primary database should not be a problem.
The data created offline spans many tables and involves several tables with relation ships - FK etc...Clearly not a simple case of store and forward.
We dont want to get into the mess of performing key replacement during a synch job with the server. Thats way too much trouble.
GUID seems like a good choice, but as always we have several stake holders having different opinions. And with databases other than MS SQL we will have to store them as strings.
To cut to the chase - can we not hash a GUID to get an integer while retaining atleast the same likelyhood of producing unique ids ? [no drop]
I am using a SqlDatasource and need to set a SelectParamter to the ProviderUserKey (The GUID of the user when Profiles are enabled) Can anyone tell me whether it is possible and How? I am currently using the session state to store it in and then using the session=... to get the value into the parameter. Is there a direct way of passing this value into a SelectParameter when using a SqlDataSource? Thanks in advance.
Hi Everyone, I'm trying to create a SQL Delete statement using a string builder and the WHERE clause uses a Guid. Here is the code:stb.Append("DELETE FROM UserRights WHERE UserIDPtr = "); stb.Append(TargetUserID);The resulting string is: "DELETE FROM UserRights WHERE UserIDPtr = e01549fb-edf5-4668-de8b-b13dd5661a6e" When I try to do an ExecuteNonQuery() using the string as the CommandText, I get an error. Invalid column name 'e01549fb',Invalid column name 'edf5',Invalid column name 'de8b',Invalid column name 'b13dd5661a6e' It is also strange that '4668' did not show up as an invalid column name, but I don't think that is relavent to this issue. Can someone show me (or point me to an article) about using Guid's in a text string as a SQL command? Thanks in advance!
My database is using the membership store for all the user information. I added a tabel "Skills" with 3 fields "SkillID (GUID)" "SeekerID (GUID, This is the UserId)" and "SkillName ( Varchar(MAX))" on the page i have a ListView setup to display the SkillName fields based on the SeekerID. The original code was Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ' Get a reference to the currently logged on user Dim CurrentUser As MembershipUser = Membership.GetUser ' Determine the currently logged on user's UserId value Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) End Subthat kept returning string to GUID conversion errors, so i had to change Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) to Dim SeekerID As Object = CType(CurrentUser.ProviderUserKey.ToString(), Object) that appears to be working now. On the same page however, I want to insert records into the table, I tried 2 options both of which have a different problem. Option 1: Use a textbox (ID = NewSkill) and a button with the following code on it: Protected Sub SetNewSkill_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As MembershipUser = Membership.GetUser Dim SeekerGUID As Object = CType(CurrentUser.ProviderUserKey, Object)
Dim NewSkill As TextBox = CType(FindControl("NewSkill"), TextBox) Dim connectionString As String = ConfigurationManager.ConnectionStrings("QJSdatabase").ConnectionString() Dim insertSql As String = "INSERT INTO Skills(SeekerID, SkillName)VALUES(@SeekerGUID, @NewSkill)" Using myConnection As New SqlConnection(connectionString) myConnection.Open() Dim myCommand As New SqlCommand(insertSql, myConnection) myCommand.Parameters.AddWithValue("@SeekerGUID", SeekerGUID) myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim()) myCommand.ExecuteNonQuery() myConnection.Close() End Using End Sub
Problem: myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim()) gets outlined and returns the error: Object reference not set to an instance of an object
Option 2: Use a DetailsView linked to a SQLDataSource on the page <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="210px" AutoGenerateRows="False" DataSourceID="SqlDataSource2" DefaultMode="Insert"> <Fields> <asp:BoundField DataField="SkillName" HeaderText="Add Skill:" SortExpression="SkillName" /> <asp:CommandField ShowInsertButton="True" /> <asp:TemplateField InsertVisible="False"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server"></asp:Label> </ItemTemplate> </asp:TemplateField> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:database %>" DeleteCommand="DELETE FROM [Skills] WHERE [SkillID] = @SkillID" InsertCommand="INSERT INTO [Skills] ([SeekerID], [SkillName]) VALUES (@SeekerGUID, @SkillName)" SelectCommand="SELECT SkillName FROM [Skills]" UpdateCommand="UPDATE [Skills] SET [SkillName] = @SkillName WHERE [SkillID] = @SkillID"> <DeleteParameters> <asp:Parameter Name="SkillID" Type="Object" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="SeekerGUID" Type="Object" /> <asp:Parameter Name="SkillName" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="SkillName" Type="String" /> <asp:Parameter Name="SkillID" Type="Object" /> </UpdateParameters> </asp:SqlDataSource>
http://junk.icore-studios.com/junk/Codeissues/postresumeerror.html The @SeekerGUID is being generated by the same pageload code as the first chunk i gave (I have 2 variables SeekerID is the GUID converted to string to work with the ListView filter and SeekerGUID is the GUID)
Ultimatly getting either option to work would be fine. Though I think the second would be preferable because I think it'd be easier to replicate later on. Thanks in advance for your time and any help
I am having a hard time updating a database row using a UNIQUEIDENTIFIER. I retrieve the row into a datagrid and then use the GUID as a parameter to a stored procedure, but it doesn't update. If I run the query in SQL Analyser ... it works. Any ideas ? Here's my stored proc ... I tried passing a varchar and doing the conversion in the SP ... no go !! I am using MApplicationBlockD.
CREATE PROCEDURE spScanUpdate @id varchar (100), @name varchar (75) AS DECLARE @GUID_ID as uniqueidentifier SELECT@GUID_ID = CAST ( @id as uniqueidentifier )
UPDATE tScan SET name = @name WHEREid = @GUID_ID GO
I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)
I have a database with tables with GUID's als primary keys.
Because of the uniqueness GUID's are a performance hit on INSERT records.
So I wanted to use NEWSQUENTIALID() to make INSERTS faster.
Does NEWSQUENTIALID() increase performance as well with SELECT statements? Or is the benefit between NEWID() and NEWSQUENTIALID() only when INSERTing records?
I'm reluctant to admit I'm in love with uniqueidentifiers AKA guid's.
However, this love comes with a cost because of the size and thus SQL Cache and thus IO reads (and fragmented index pages and so on)
My love has to do with generic programming and keeping multiple database updateable (adding core functionality with the flexibility to let users add and change data in tables that have core data in it as well). Also merging databases is easy or relate different entities by storing a metadata link table.
So, what I'm trying to do is making the disadvantages as small as possible.
So I'm using NEWSEQUENTIAL Id's and retrieve new made records with OUTPUT as TABLE.
So I changed the default CLUSTERED INDEX as NON-CLUSTERED.
I'm not fond on usings numerics for some primary keys and uniqueidentifiers for others.
What else can I do to keep using GUID's as primary keys with as less disadvantages as possible?
Hi,Is there any replace for "Select @@identity" that could return "justinserted" GUID as a primary key?Has anyone tested what's faster, working with Guid or Autonumber ?
Greetings All, I have read many upon many articles here regarding GUIDdata types and uniqueness. There have been many opinions regardingthe effectiveness of GUID's and when they should/should not be used.However, every article strongly implies, if it does not state itoutright, that GUID's are always unique. My question is this, whathappens if you have a database that uses GUID's and the NIC is changedout on the box? From what I understand the MAC address of the NIC isused as part of the algorithm to generate a GUID. If you change outthe NIC after generating 1 billion GUID's do you run the chance ofgenerating a duplicate GUID?I look forward to your insightfulness on this issue.Regards, Louis.
I have been trying to setup the GUID new GUID when I add a row into a Pocket PC WM 5.0 Mobile SQL (*.sdf) utilizing C# through Visual Studio 2005 - targetting Pocket PC Smart Device
I am reading a book and it talks a little bit about using GUIDs as primary keys. I am very confused about this because if this is a generated value per table, how could you use it as a foreign key in another table as it appears to be uniquely generated per table. Here is the code in the book which I don't understand. Can someone explain this in simple terms?
I know the identity column would be a problem if I had a multi-terabyte database with huge tables, so what do I do in this situation?
once upon a time someone told me how to do this, but i forget, and it was for classic asp.. so what im trying to do is create a new record (a workstation) and get back the auto generated ID (its not actually a GUID, its an int which is set to the identity) and then once i have that create other records (for external devices, different table) that will have a link to which machine they are attached to (that Id im looking to get returned) I know this can be done in a two step process, create the record, query it for its id, and then slap that in my next insert statement for the external devices... but... i know there was a way to do it before and i assume there is a way to do it now... anyone know how? Thanks Justin
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
I need to SELECT something from a database that has a UNIQUEIDENTIFIER (GUID) field,
If the number is wrong (has some other than A-Z 0-9) than the ASP page just freaks out and gets "error converting from a character string to uniqueidentifier"
How can I check that the GUID is OK before I SELECT ? this is the number format: {7A9B5F81-4936-4A31-B4E2-9168AAB75A0}
I tried to cast this "error" number with no successs: "WHERE Deceased_ID = cast('"& "---------4936-4A31-B4E2-9168AAB75A0" &"' as uniqueidentifier)"
I am having a terrible time doing an insert.... dispite what the error message says, I think it has something to do with trying to insert a GUID (my primary key).... but I am probably wrong :-)
Any ideas
The error message
Server Error in '/LightQuote' Application. --------------------------------------------------------------------------------
Prepared statement '(@User_ID uniqueidentifier,@User_Type int,@Status int,@UserName ' expects parameter @UserName, which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Prepared statement '(@User_ID uniqueidentifier,@User_Type int,@Status int,@UserName ' expects parameter @UserName, which was not supplied.
Source Error:
Line 195: dbConnection.Open() Line 196: Try Line 197: rowsAffected = dbCommand.ExecuteNonQuery Line 198: Finally Line 199: dbConnection.Close()
The Insert Function
Public Function InsertOneUser( _ ByVal user_ID As System.Guid, _ ByVal user_Type As Integer, _ ByVal status As Integer, _ ByVal userName As String, _ ByVal password As String, _ ByVal secret_Question As Integer, _ ByVal secret_Answer As String, _ ByVal company As String, _ ByVal company_Type As Integer, _ ByVal first_Name As String, _ ByVal last_Name As String, _ ByVal address1 As String, _ ByVal address2 As String, _ ByVal city As String, _ ByVal state As String, _ ByVal zip As String, _ ByVal country As String, _ ByVal day_Phone As String, _ ByVal night_Phone As String, _ ByVal fax As String, _ ByVal email As String, _ ByVal web_Site As String) As Integer Dim myConnection As New Connection Dim connectionString As String = myConnection.ConnString Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
I have a 3rd party app which creates tables for developers. It uses a guid as the unique key which is indexed. However none of these indexes are clustered. I have hear both good and bad about clustering on a GUID. i can't change this design other than to make these indexes clustered. Any opinions?
Hi everybody I have a table named GrpMembership which has columns ParticipantId and InterestGroupId that have datatypes uniqueidentifier.When I try to run the following insert stmt: insert into grpmembership (guparticipantid,guInterestGroupId,bIsAdmin,dtCrea ted) values ({EE0BC83E-01DD-4B0D-BFAA-34B508C28CF3} {D149BD88-55B6-4EB9-AA81-1E1B5141EFA6}, 0,6/7/2002)
it gives error:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
Does SQL Server 2000 (Enterprise Edition), mandate the creation of a GUID on each table used in transactional replication?
I was told that in addition to defining a Primary Key, I "MUST" also define a GUID on each table. As I recall - this is not true. Can someone who's setup Transactional Replication please field this question?
If I return a GUID as an output parameter in an ASP page, it has curly braces around it. When I query the table in QA, there are no braces around it. Why is that.
Let's say that I have a database with 2 tables named 'TableA' and 'TableB'. Each one of those two tables contains the two fields 'MyKey' and 'MyData'. The field 'MyKey' has the type 'uniqueidentifier' and its default value is 'getnewid()'.
Now I would like to consider a third table named 'TableC' with two fields 'KeyRef' and 'MetaData'. The type of 'KeyRef' is 'uniqueidenfier' and corresponds to an element of either 'TableA' or 'TableB'.
If the GUID identifiers are trully unique then there is no issue to proceed that way (I am not consering perf matters here). On the contrary, if GUID are not unique, then there is a collision possibility.
My question is simply: can I consider the GUID to be unique in MS SQL Server (2000 or 2005).
