SqlDependency connection failure (using Sceppa sample)

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

Still working with this SqlDependency idea, but have not been able to
successfully muddle through it. I'm using David Sceppa's sample from Ch. 14
(ADO.Net 2.0) to set up a sample DB with SqlDependency working. The script
executes properly but when I try to run the sample app, on connection, I
catch the exception: "A connection was succcessfully 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.)."

The script (shown below) executed successfully on my SQLEXPRESS instance. I
can also see that all the objects have been created in the server (db,
tables, roles, users, logins).

Ok, so this looks like maybe a remote connection issue... I go to the
Surface Area Config and change from local connections to both local and
remote (TCP/IP). I'm doing the test on a local system, so I'm figuring
anyway that this should not matter, and when I run the sample app, I still
get the same message (note that the rest of the app is irrelevant, as it is
failing at the moment it tries to connect).

The development server is set to use Windows Authentication, so I'm
wondering if maybe the user/roles/passwords set by the sample script are
creating this problem. Any advice appreciated.

*****************************************
The connection string:

Dim LISTENER_CONNECTION_STRING As String = "Data Source=.\SQLExpress;Initial
Catalog=SqlDependencyTestDB;User ID=Listener_User;Password=L!st3n3r_Pwd;"
*****************************************

The sample method that uses the connection string:

Sub startButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles
startButton.Click
ClearStatus()
Try
SqlDependency.Start(LISTENER_CONNECTION_STRING)
DisplayStatus("SqlDependency Started!")
If Not Me.queryButton.Enabled Then Me.queryButton.Enabled = True
Catch ex As Exception
DisplayStatus(ex.Message)
End Try
End Sub
****************************************
The setup script:

USE MASTER
GO
CREATE DATABASE SqlDependencyTestDB
GO
ALTER DATABASE SqlDependencyTestDB SET ENABLE_BROKER
GO
USE SqlDependencyTestDB
GO
EXEC sp_addrole 'sql_dependency_listener'
GO
GRANT CREATE PROCEDURE TO [sql_dependency_listener]
GRANT CREATE QUEUE TO [sql_dependency_listener]
GRANT CREATE SERVICE TO [sql_dependency_listener]
GRANT REFERENCES ON CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_listener]
GRANT VIEW DEFINITION TO [sql_dependency_listener]
EXEC sp_addrole 'sql_dependency_subscriber'
GO
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 CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_subscriber]
CREATE LOGIN Listener_User
WITH PASSWORD='L!st3n3r_Pwd'
GO
CREATE LOGIN Subscriber_User
WITH PASSWORD='Subscr!b3r_Pwd'
GO
CREATE LOGIN Other_User
WITH PASSWORD='0th3r_Pwd'
GO
CREATE USER Listener_User
FOR LOGIN Listener_User
WITH DEFAULT_SCHEMA = NotificationSchema
GO
CREATE USER Subscriber_User
FOR LOGIN Subscriber_User
GO
CREATE USER Other_User
FOR LOGIN Other_User
GO
EXEC sp_addrolemember 'sql_dependency_listener', 'Listener_User'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'Subscriber_User'
GO
CREATE SCHEMA NotificationSchema AUTHORIZATION Listener_User
GO
CREATE TABLE NotificationSchema.DependencySampleTable (ID int PRIMARY KEY,
OtherColumn nvarchar(255))
GO
GRANT SELECT ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT INSERT ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT UPDATE ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT DELETE ON NotificationSchema.DependencySampleTable TO [Other_User]
GO
DECLARE @rowCounter int
SET @rowCounter = 1
SET NOCOUNT ON
WHILE (@rowCounter <= 10)
BEGIN
INSERT INTO NotificationSchema.DependencySampleTable (ID, OtherColumn)
VALUES (@rowCounter, 'Created ' + CAST(GetDate() AS NVARCHAR(255)))
SET @rowCounter = @rowCounter + 1
END
SET NOCOUNT OFF
GO
USE MASTER
 
Earl,

Did you already try to connect to your server to use one of the wizards.
This is one of the easiest ones.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

Cor

Earl said:
Still working with this SqlDependency idea, but have not been able to
successfully muddle through it. I'm using David Sceppa's sample from Ch.
14 (ADO.Net 2.0) to set up a sample DB with SqlDependency working. The
script executes properly but when I try to run the sample app, on
connection, I catch the exception: "A connection was succcessfully
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.)."

The script (shown below) executed successfully on my SQLEXPRESS instance.
I can also see that all the objects have been created in the server (db,
tables, roles, users, logins).

Ok, so this looks like maybe a remote connection issue... I go to the
Surface Area Config and change from local connections to both local and
remote (TCP/IP). I'm doing the test on a local system, so I'm figuring
anyway that this should not matter, and when I run the sample app, I still
get the same message (note that the rest of the app is irrelevant, as it
is failing at the moment it tries to connect).

The development server is set to use Windows Authentication, so I'm
wondering if maybe the user/roles/passwords set by the sample script are
creating this problem. Any advice appreciated.

*****************************************
The connection string:

Dim LISTENER_CONNECTION_STRING As String = "Data
Source=.\SQLExpress;Initial Catalog=SqlDependencyTestDB;User
ID=Listener_User;Password=L!st3n3r_Pwd;"
*****************************************

The sample method that uses the connection string:

Sub startButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Handles startButton.Click
ClearStatus()
Try
SqlDependency.Start(LISTENER_CONNECTION_STRING)
DisplayStatus("SqlDependency Started!")
If Not Me.queryButton.Enabled Then Me.queryButton.Enabled = True
Catch ex As Exception
DisplayStatus(ex.Message)
End Try
End Sub
****************************************
The setup script:

USE MASTER
GO
CREATE DATABASE SqlDependencyTestDB
GO
ALTER DATABASE SqlDependencyTestDB SET ENABLE_BROKER
GO
USE SqlDependencyTestDB
GO
EXEC sp_addrole 'sql_dependency_listener'
GO
GRANT CREATE PROCEDURE TO [sql_dependency_listener]
GRANT CREATE QUEUE TO [sql_dependency_listener]
GRANT CREATE SERVICE TO [sql_dependency_listener]
GRANT REFERENCES ON CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_listener]
GRANT VIEW DEFINITION TO [sql_dependency_listener]
EXEC sp_addrole 'sql_dependency_subscriber'
GO
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 CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_subscriber]
CREATE LOGIN Listener_User
WITH PASSWORD='L!st3n3r_Pwd'
GO
CREATE LOGIN Subscriber_User
WITH PASSWORD='Subscr!b3r_Pwd'
GO
CREATE LOGIN Other_User
WITH PASSWORD='0th3r_Pwd'
GO
CREATE USER Listener_User
FOR LOGIN Listener_User
WITH DEFAULT_SCHEMA = NotificationSchema
GO
CREATE USER Subscriber_User
FOR LOGIN Subscriber_User
GO
CREATE USER Other_User
FOR LOGIN Other_User
GO
EXEC sp_addrolemember 'sql_dependency_listener', 'Listener_User'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'Subscriber_User'
GO
CREATE SCHEMA NotificationSchema AUTHORIZATION Listener_User
GO
CREATE TABLE NotificationSchema.DependencySampleTable (ID int PRIMARY KEY,
OtherColumn nvarchar(255))
GO
GRANT SELECT ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT INSERT ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT UPDATE ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT DELETE ON NotificationSchema.DependencySampleTable TO [Other_User]
GO
DECLARE @rowCounter int
SET @rowCounter = 1
SET NOCOUNT ON
WHILE (@rowCounter <= 10)
BEGIN
INSERT INTO NotificationSchema.DependencySampleTable (ID, OtherColumn)
VALUES (@rowCounter, 'Created ' + CAST(GetDate() AS NVARCHAR(255)))
SET @rowCounter = @rowCounter + 1
END
SET NOCOUNT OFF
GO
USE MASTER
Click to expand...
 
Not sure where the link was supposed to take me, it got to your page and was
just blank.

But connecting to the server is the easy part (the app by itself has no
problem connecting, but is failing the login, which appears correct). I
really thought one of those hotshot DBAs might weigh in on this, but it's
starting to seem like there are only about 5 people in the whole world who
use the Sql Notifications and they all work at Microsoft!

Cor Ligthert said:
Earl,

Did you already try to connect to your server to use one of the wizards.
This is one of the easiest ones.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

Cor

Earl said:
Still working with this SqlDependency idea, but have not been able to
successfully muddle through it. I'm using David Sceppa's sample from Ch.
14 (ADO.Net 2.0) to set up a sample DB with SqlDependency working. The
script executes properly but when I try to run the sample app, on
connection, I catch the exception: "A connection was succcessfully
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.)."

The script (shown below) executed successfully on my SQLEXPRESS instance.
I can also see that all the objects have been created in the server (db,
tables, roles, users, logins).

Ok, so this looks like maybe a remote connection issue... I go to the
Surface Area Config and change from local connections to both local and
remote (TCP/IP). I'm doing the test on a local system, so I'm figuring
anyway that this should not matter, and when I run the sample app, I
still get the same message (note that the rest of the app is irrelevant,
as it is failing at the moment it tries to connect).

The development server is set to use Windows Authentication, so I'm
wondering if maybe the user/roles/passwords set by the sample script are
creating this problem. Any advice appreciated.

*****************************************
The connection string:

Dim LISTENER_CONNECTION_STRING As String = "Data
Source=.\SQLExpress;Initial Catalog=SqlDependencyTestDB;User
ID=Listener_User;Password=L!st3n3r_Pwd;"
*****************************************

The sample method that uses the connection string:

Sub startButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Handles startButton.Click
ClearStatus()
Try
SqlDependency.Start(LISTENER_CONNECTION_STRING)
DisplayStatus("SqlDependency Started!")
If Not Me.queryButton.Enabled Then Me.queryButton.Enabled = True
Catch ex As Exception
DisplayStatus(ex.Message)
End Try
End Sub
****************************************
The setup script:

USE MASTER
GO
CREATE DATABASE SqlDependencyTestDB
GO
ALTER DATABASE SqlDependencyTestDB SET ENABLE_BROKER
GO
USE SqlDependencyTestDB
GO
EXEC sp_addrole 'sql_dependency_listener'
GO
GRANT CREATE PROCEDURE TO [sql_dependency_listener]
GRANT CREATE QUEUE TO [sql_dependency_listener]
GRANT CREATE SERVICE TO [sql_dependency_listener]
GRANT REFERENCES ON CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_listener]
GRANT VIEW DEFINITION TO [sql_dependency_listener]
EXEC sp_addrole 'sql_dependency_subscriber'
GO
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 CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_subscriber]
CREATE LOGIN Listener_User
WITH PASSWORD='L!st3n3r_Pwd'
GO
CREATE LOGIN Subscriber_User
WITH PASSWORD='Subscr!b3r_Pwd'
GO
CREATE LOGIN Other_User
WITH PASSWORD='0th3r_Pwd'
GO
CREATE USER Listener_User
FOR LOGIN Listener_User
WITH DEFAULT_SCHEMA = NotificationSchema
GO
CREATE USER Subscriber_User
FOR LOGIN Subscriber_User
GO
CREATE USER Other_User
FOR LOGIN Other_User
GO
EXEC sp_addrolemember 'sql_dependency_listener', 'Listener_User'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'Subscriber_User'
GO
CREATE SCHEMA NotificationSchema AUTHORIZATION Listener_User
GO
CREATE TABLE NotificationSchema.DependencySampleTable (ID int PRIMARY
KEY, OtherColumn nvarchar(255))
GO
GRANT SELECT ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT INSERT ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT UPDATE ON NotificationSchema.DependencySampleTable TO [Other_User]
GRANT DELETE ON NotificationSchema.DependencySampleTable TO [Other_User]
GO
DECLARE @rowCounter int
SET @rowCounter = 1
SET NOCOUNT ON
WHILE (@rowCounter <= 10)
BEGIN
INSERT INTO NotificationSchema.DependencySampleTable (ID, OtherColumn)
VALUES (@rowCounter, 'Created ' + CAST(GetDate() AS NVARCHAR(255)))
SET @rowCounter = @rowCounter + 1
END
SET NOCOUNT OFF
GO
USE MASTER
Click to expand...
Click to expand...
 
I have that book. You're just trying to run his script, or did you write it
yourself?

Robin S.
-------------------------------------------
Earl said:
Not sure where the link was supposed to take me, it got to your page and
was just blank.

But connecting to the server is the easy part (the app by itself has no
problem connecting, but is failing the login, which appears correct). I
really thought one of those hotshot DBAs might weigh in on this, but it's
starting to seem like there are only about 5 people in the whole world
who use the Sql Notifications and they all work at Microsoft!

Cor Ligthert said:
Earl,

Did you already try to connect to your server to use one of the wizards.
This is one of the easiest ones.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

Cor

Earl said:
Still working with this SqlDependency idea, but have not been able to
successfully muddle through it. I'm using David Sceppa's sample from
Ch. 14 (ADO.Net 2.0) to set up a sample DB with SqlDependency working.
The script executes properly but when I try to run the sample app, on
connection, I catch the exception: "A connection was succcessfully
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.)."

The script (shown below) executed successfully on my SQLEXPRESS
instance. I can also see that all the objects have been created in the
server (db, tables, roles, users, logins).

Ok, so this looks like maybe a remote connection issue... I go to the
Surface Area Config and change from local connections to both local and
remote (TCP/IP). I'm doing the test on a local system, so I'm figuring
anyway that this should not matter, and when I run the sample app, I
still get the same message (note that the rest of the app is
irrelevant, as it is failing at the moment it tries to connect).

The development server is set to use Windows Authentication, so I'm
wondering if maybe the user/roles/passwords set by the sample script
are creating this problem. Any advice appreciated.

*****************************************
The connection string:

Dim LISTENER_CONNECTION_STRING As String = "Data
Source=.\SQLExpress;Initial Catalog=SqlDependencyTestDB;User
ID=Listener_User;Password=L!st3n3r_Pwd;"
*****************************************

The sample method that uses the connection string:

Sub startButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Handles startButton.Click
ClearStatus()
Try
SqlDependency.Start(LISTENER_CONNECTION_STRING)
DisplayStatus("SqlDependency Started!")
If Not Me.queryButton.Enabled Then Me.queryButton.Enabled = True
Catch ex As Exception
DisplayStatus(ex.Message)
End Try
End Sub
****************************************
The setup script:

USE MASTER
GO
CREATE DATABASE SqlDependencyTestDB
GO
ALTER DATABASE SqlDependencyTestDB SET ENABLE_BROKER
GO
USE SqlDependencyTestDB
GO
EXEC sp_addrole 'sql_dependency_listener'
GO
GRANT CREATE PROCEDURE TO [sql_dependency_listener]
GRANT CREATE QUEUE TO [sql_dependency_listener]
GRANT CREATE SERVICE TO [sql_dependency_listener]
GRANT REFERENCES ON CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_listener]
GRANT VIEW DEFINITION TO [sql_dependency_listener]
EXEC sp_addrole 'sql_dependency_subscriber'
GO
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 CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_subscriber]
CREATE LOGIN Listener_User
WITH PASSWORD='L!st3n3r_Pwd'
GO
CREATE LOGIN Subscriber_User
WITH PASSWORD='Subscr!b3r_Pwd'
GO
CREATE LOGIN Other_User
WITH PASSWORD='0th3r_Pwd'
GO
CREATE USER Listener_User
FOR LOGIN Listener_User
WITH DEFAULT_SCHEMA = NotificationSchema
GO
CREATE USER Subscriber_User
FOR LOGIN Subscriber_User
GO
CREATE USER Other_User
FOR LOGIN Other_User
GO
EXEC sp_addrolemember 'sql_dependency_listener', 'Listener_User'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'Subscriber_User'
GO
CREATE SCHEMA NotificationSchema AUTHORIZATION Listener_User
GO
CREATE TABLE NotificationSchema.DependencySampleTable (ID int PRIMARY
KEY, OtherColumn nvarchar(255))
GO
GRANT SELECT ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT INSERT ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT UPDATE ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT DELETE ON NotificationSchema.DependencySampleTable TO
[Other_User]
GO
DECLARE @rowCounter int
SET @rowCounter = 1
SET NOCOUNT ON
WHILE (@rowCounter <= 10)
BEGIN
INSERT INTO NotificationSchema.DependencySampleTable (ID, OtherColumn)
VALUES (@rowCounter, 'Created ' + CAST(GetDate() AS NVARCHAR(255)))
SET @rowCounter = @rowCounter + 1
END
SET NOCOUNT OFF
GO
USE MASTER
Click to expand...
Click to expand...
Click to expand...
 
No, that is his script. I did confirm that it created all the objects in the
script though. You can download the script online at the address shown in
the inside cover.

RobinS said:
I have that book. You're just trying to run his script, or did you write it
yourself?

Robin S.
-------------------------------------------
Earl said:
Not sure where the link was supposed to take me, it got to your page and
was just blank.

But connecting to the server is the easy part (the app by itself has no
problem connecting, but is failing the login, which appears correct). I
really thought one of those hotshot DBAs might weigh in on this, but it's
starting to seem like there are only about 5 people in the whole world
who use the Sql Notifications and they all work at Microsoft!

Cor Ligthert said:
Earl,

Did you already try to connect to your server to use one of the wizards.
This is one of the easiest ones.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

Cor

"Earl" <[email protected]> schreef in bericht
Still working with this SqlDependency idea, but have not been able to
successfully muddle through it. I'm using David Sceppa's sample from
Ch. 14 (ADO.Net 2.0) to set up a sample DB with SqlDependency working.
The script executes properly but when I try to run the sample app, on
connection, I catch the exception: "A connection was succcessfully
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.)."

The script (shown below) executed successfully on my SQLEXPRESS
instance. I can also see that all the objects have been created in the
server (db, tables, roles, users, logins).

Ok, so this looks like maybe a remote connection issue... I go to the
Surface Area Config and change from local connections to both local and
remote (TCP/IP). I'm doing the test on a local system, so I'm figuring
anyway that this should not matter, and when I run the sample app, I
still get the same message (note that the rest of the app is
irrelevant, as it is failing at the moment it tries to connect).

The development server is set to use Windows Authentication, so I'm
wondering if maybe the user/roles/passwords set by the sample script
are creating this problem. Any advice appreciated.

*****************************************
The connection string:

Dim LISTENER_CONNECTION_STRING As String = "Data
Source=.\SQLExpress;Initial Catalog=SqlDependencyTestDB;User
ID=Listener_User;Password=L!st3n3r_Pwd;"
*****************************************

The sample method that uses the connection string:

Sub startButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Handles startButton.Click
ClearStatus()
Try
SqlDependency.Start(LISTENER_CONNECTION_STRING)
DisplayStatus("SqlDependency Started!")
If Not Me.queryButton.Enabled Then Me.queryButton.Enabled = True
Catch ex As Exception
DisplayStatus(ex.Message)
End Try
End Sub
****************************************
The setup script:

USE MASTER
GO
CREATE DATABASE SqlDependencyTestDB
GO
ALTER DATABASE SqlDependencyTestDB SET ENABLE_BROKER
GO
USE SqlDependencyTestDB
GO
EXEC sp_addrole 'sql_dependency_listener'
GO
GRANT CREATE PROCEDURE TO [sql_dependency_listener]
GRANT CREATE QUEUE TO [sql_dependency_listener]
GRANT CREATE SERVICE TO [sql_dependency_listener]
GRANT REFERENCES ON CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_listener]
GRANT VIEW DEFINITION TO [sql_dependency_listener]
EXEC sp_addrole 'sql_dependency_subscriber'
GO
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 CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_subscriber]
CREATE LOGIN Listener_User
WITH PASSWORD='L!st3n3r_Pwd'
GO
CREATE LOGIN Subscriber_User
WITH PASSWORD='Subscr!b3r_Pwd'
GO
CREATE LOGIN Other_User
WITH PASSWORD='0th3r_Pwd'
GO
CREATE USER Listener_User
FOR LOGIN Listener_User
WITH DEFAULT_SCHEMA = NotificationSchema
GO
CREATE USER Subscriber_User
FOR LOGIN Subscriber_User
GO
CREATE USER Other_User
FOR LOGIN Other_User
GO
EXEC sp_addrolemember 'sql_dependency_listener', 'Listener_User'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'Subscriber_User'
GO
CREATE SCHEMA NotificationSchema AUTHORIZATION Listener_User
GO
CREATE TABLE NotificationSchema.DependencySampleTable (ID int PRIMARY
KEY, OtherColumn nvarchar(255))
GO
GRANT SELECT ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT INSERT ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT UPDATE ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT DELETE ON NotificationSchema.DependencySampleTable TO
[Other_User]
GO
DECLARE @rowCounter int
SET @rowCounter = 1
SET NOCOUNT ON
WHILE (@rowCounter <= 10)
BEGIN
INSERT INTO NotificationSchema.DependencySampleTable (ID, OtherColumn)
VALUES (@rowCounter, 'Created ' + CAST(GetDate() AS NVARCHAR(255)))
SET @rowCounter = @rowCounter + 1
END
SET NOCOUNT OFF
GO
USE MASTER
Click to expand...
Click to expand...
Click to expand...
 
What error is it giving you when you run it?

Robin S.
--------------------------------------------
Earl said:
No, that is his script. I did confirm that it created all the objects in
the script though. You can download the script online at the address
shown in the inside cover.

RobinS said:
I have that book. You're just trying to run his script, or did you write
it yourself?

Robin S.
-------------------------------------------
Earl said:
Not sure where the link was supposed to take me, it got to your page
and was just blank.

But connecting to the server is the easy part (the app by itself has no
problem connecting, but is failing the login, which appears correct). I
really thought one of those hotshot DBAs might weigh in on this, but
it's starting to seem like there are only about 5 people in the whole
world who use the Sql Notifications and they all work at Microsoft!

Earl,

Did you already try to connect to your server to use one of the
wizards. This is one of the easiest ones.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

Cor

"Earl" <[email protected]> schreef in bericht
Still working with this SqlDependency idea, but have not been able to
successfully muddle through it. I'm using David Sceppa's sample from
Ch. 14 (ADO.Net 2.0) to set up a sample DB with SqlDependency
working. The script executes properly but when I try to run the
sample app, on connection, I catch the exception: "A connection was
succcessfully 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.)."

The script (shown below) executed successfully on my SQLEXPRESS
instance. I can also see that all the objects have been created in
the server (db, tables, roles, users, logins).

Ok, so this looks like maybe a remote connection issue... I go to the
Surface Area Config and change from local connections to both local
and remote (TCP/IP). I'm doing the test on a local system, so I'm
figuring anyway that this should not matter, and when I run the
sample app, I still get the same message (note that the rest of the
app is irrelevant, as it is failing at the moment it tries to
connect).

The development server is set to use Windows Authentication, so I'm
wondering if maybe the user/roles/passwords set by the sample script
are creating this problem. Any advice appreciated.

*****************************************
The connection string:

Dim LISTENER_CONNECTION_STRING As String = "Data
Source=.\SQLExpress;Initial Catalog=SqlDependencyTestDB;User
ID=Listener_User;Password=L!st3n3r_Pwd;"
*****************************************

The sample method that uses the connection string:

Sub startButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Handles startButton.Click
ClearStatus()
Try
SqlDependency.Start(LISTENER_CONNECTION_STRING)
DisplayStatus("SqlDependency Started!")
If Not Me.queryButton.Enabled Then Me.queryButton.Enabled = True
Catch ex As Exception
DisplayStatus(ex.Message)
End Try
End Sub
****************************************
The setup script:

USE MASTER
GO
CREATE DATABASE SqlDependencyTestDB
GO
ALTER DATABASE SqlDependencyTestDB SET ENABLE_BROKER
GO
USE SqlDependencyTestDB
GO
EXEC sp_addrole 'sql_dependency_listener'
GO
GRANT CREATE PROCEDURE TO [sql_dependency_listener]
GRANT CREATE QUEUE TO [sql_dependency_listener]
GRANT CREATE SERVICE TO [sql_dependency_listener]
GRANT REFERENCES ON CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_listener]
GRANT VIEW DEFINITION TO [sql_dependency_listener]
EXEC sp_addrole 'sql_dependency_subscriber'
GO
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 CONTRACT::
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [sql_dependency_subscriber]
CREATE LOGIN Listener_User
WITH PASSWORD='L!st3n3r_Pwd'
GO
CREATE LOGIN Subscriber_User
WITH PASSWORD='Subscr!b3r_Pwd'
GO
CREATE LOGIN Other_User
WITH PASSWORD='0th3r_Pwd'
GO
CREATE USER Listener_User
FOR LOGIN Listener_User
WITH DEFAULT_SCHEMA = NotificationSchema
GO
CREATE USER Subscriber_User
FOR LOGIN Subscriber_User
GO
CREATE USER Other_User
FOR LOGIN Other_User
GO
EXEC sp_addrolemember 'sql_dependency_listener', 'Listener_User'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'Subscriber_User'
GO
CREATE SCHEMA NotificationSchema AUTHORIZATION Listener_User
GO
CREATE TABLE NotificationSchema.DependencySampleTable (ID int PRIMARY
KEY, OtherColumn nvarchar(255))
GO
GRANT SELECT ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT INSERT ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT UPDATE ON NotificationSchema.DependencySampleTable TO
[Other_User]
GRANT DELETE ON NotificationSchema.DependencySampleTable TO
[Other_User]
GO
DECLARE @rowCounter int
SET @rowCounter = 1
SET NOCOUNT ON
WHILE (@rowCounter <= 10)
BEGIN
INSERT INTO NotificationSchema.DependencySampleTable (ID,
OtherColumn) VALUES (@rowCounter, 'Created ' + CAST(GetDate() AS
NVARCHAR(255)))
SET @rowCounter = @rowCounter + 1
END
SET NOCOUNT OFF
GO
USE MASTER
Click to expand...
Click to expand...
Click to expand...
 
Back
Top