Last post Apr 08, 2008 09:26 PM by XPSCodes
Apr 07, 2008 10:04 PM|lax4u|LINK
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user
http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy
existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
-- Ensuring that Service Broker is enabled
ALTER DATABASE [DatabaseName] SET ENABLE_BROKER
-- Switching to our database
CREATE SCHEMA schemaname AUTHORIZATION username
ALTER USER username WITH DEFAULT_SCHEMA = schemaname
* Creating two new roles. We're not going to set the necessary permissions
* on the user-accounts, but we're going to set them on these two new roles.
* At the end of this script, we're simply going to make our two users
* members of these roles.
EXEC sp_addrole 'sql_dependency_subscriber'
EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]
GRANT CREATE PROCEDURE to [sql_dependency_starter]
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
GRANT REFERENCES on
GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber]
GRANT SELECT to [sql_dependency_subscriber]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber]
GRANT REFERENCES on
-- Making sure that my users are member of the correct role.
EXEC sp_addrolemember 'sql_dependency_starter', 'username'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
Apr 07, 2008 10:38 PM|XPSCodes|LINK
Is named pipes enabled?
Apr 07, 2008 11:58 PM|lax4u|LINK
how do i do that? and why i need that
Apr 08, 2008 02:30 AM|XPSCodes|LINK
In start menu, go to sql server 2005/ Configuration tools/ Surface area configuration
Select Surface area configurations and connections
On the right see named pipes is selected or not. You can go for either named pipes only or named pipes and TCP/IP.
Apr 08, 2008 02:49 PM|lax4u|LINK
but my question is why do i need that if my sql server is local to the machine? I have enabled broker on my machine. Does broker only works on TCP/IP or Named Pipe connection? in other words, if i'm using
ALTER DATABASE [DatabaseName]
do i have to have TCP/IP or Named pipe enabled too?
Apr 08, 2008 03:33 PM|XPSCodes|LINK
I guess your connection string could be wrong then. Could you double check the connection string see if its working? Do you have the right username/password?
Apr 08, 2008 05:58 PM|lax4u|LINK
here is my connection string
Apr 08, 2008 06:03 PM|lax4u|LINK
it worked when i change the remote connection to 'local and remote connecttions' and set 'using TCP/IP only. I did this on my local machine. I still i have question why i need to do this if i'm working with local machine.
Now next question is, in production our web server and SQL server are on two different machines but they are both under same domain. Do i have to change remote connection settings in production? and set it to TCP/IP
Apr 08, 2008 07:12 PM|XPSCodes|LINK
I dont have an answer for why we need to change it for local sql server.
Yes, you have to do it even if web server and sql server are on same domain. Basically that setting is server specific, the way connections are handled on the server.
Apr 08, 2008 08:07 PM|lax4u|LINK
can u take a look at my connection string. do you think
Persist Security Info=True or false. Im using SQLCacheDependency in my code
Apr 08, 2008 09:26 PM|XPSCodes|LINK
Persist security info is to define whether the connection info should exist once a a successful connection is made. This is more of a security feature. Its advisable to set it to false, unless you you need it for some reason.