Remote SQL Debug: Server cannot connect to the debugger on my mach

Hello,
I am trying to debug a stored procedure on remote server. The debugger
starts and passes through without stopping.
No error messages are generated on the client.
On the server the following error appears in the Application Log:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 10/7/2005
Time: 4:41:47 PM
User: N/A
Computer: <server>
Description:
SQL Server is running as '<domain account>' and cannot connect to the
debugger on machine '<my machine>' (error = 0x80070005 Access is denied. ).
Use one of the following options to fix this error. 1) Run SQL Server as
"Local System", as a domain account, or as a local account with identical
usernames and passwords on both machine '<server>' and '<my machine>'. 2)
Verify that machine '<server>' can open files on machine '<my machine>'.
Debugging disabled for connection 55.
Server is Windows 2003 Standard running SQL 2000 SP4 with .NET remote
debugging support.
Client is Windows XP Pro SP2 running SQL 2000 SP4 and .NET 1.1 SP1.
Windows Firewall is disabled on both machines.
All DCOM permissions seems to be set OK on the server. I am a local admin on
the server and sys admin on SQL server and I can run sp_sdidebug without any
problems.
I can debug SQL on my local machine using Query Analyzer and VS.NET without
any problems. I can also debug SQL using Query Analyzer on the server if I
log there with my account.
What could prevent SQL debugger from connecting to my workstation? I tried
to add that SQL service account to my local admin group, but it did not help
.
I am not sure what is required to "2) Verify that machine '<server>' can ope
n
files on machine '<my machine>'." Does it mean that I have to trust my serve
r
machine? How can I do that?
What else could be wrong?Hello Serge,
You wrote in conference microsoft.public.sqlserver.programming on Fri, 7
Oct 2005 17:25:03 -0700:
SM> I am trying to debug a stored procedure on remote server. The debugger
SM> starts and passes through without stopping.
http://support.microsoft.com/defaul...kb;en-us;841249
and especially
http://support.microsoft.com/defaul...kb;en-us;839280
Vadm Rapp|||After spending few hours troubleshooting my local COM+ security and killing
my registry :) I found the settings that make remote SQL debugging work for
SQL Analyzer and VS.NET.
All these settings apply to the Default COM Security for Windows XP
Workstation from which debugging is initiated.
Access Permissions:
-- Limits:
* ANONYMOUS LOGON - Allow All (Required to debug from SQL Analyzer)
* Everyone - Allow All (Required to debug from VS.NET)
Launch and Activation Permissions:
Limits (add)
* ANONYMOUS LOGON - Allow All (Required to debug from VS.NET)
Default (add)
* ANONYMOUS LOGON - Allow All (Required to debug from VS.NET)
Huh!
Of course all settings on the server must be done first as described in
several knowlege articles. If firewall is anabled, then it should be adresse
d
as well.
"Serge Matsevilo" wrote:

> Hello,
> I am trying to debug a stored procedure on remote server. The debugger
> starts and passes through without stopping.
> No error messages are generated on the client.
> On the server the following error appears in the Application Log:
> Event Type: Error
> Event Source: SQLDebugging98
> Event Category: None
> Event ID: 1
> Date: 10/7/2005
> Time: 4:41:47 PM
> User: N/A
> Computer: <server>
> Description:
> SQL Server is running as '<domain account>' and cannot connect to the
> debugger on machine '<my machine>' (error = 0x80070005 Access is denied. )
.
> Use one of the following options to fix this error. 1) Run SQL Server as
> "Local System", as a domain account, or as a local account with identical
> usernames and passwords on both machine '<server>' and '<my machine>'. 2)
> Verify that machine '<server>' can open files on machine '<my machine>'.
> Debugging disabled for connection 55.
> Server is Windows 2003 Standard running SQL 2000 SP4 with .NET remote
> debugging support.
> Client is Windows XP Pro SP2 running SQL 2000 SP4 and .NET 1.1 SP1.
> Windows Firewall is disabled on both machines.
> All DCOM permissions seems to be set OK on the server. I am a local admin
on
> the server and sys admin on SQL server and I can run sp_sdidebug without a
ny
> problems.
> I can debug SQL on my local machine using Query Analyzer and VS.NET withou
t
> any problems. I can also debug SQL using Query Analyzer on the server if I
> log there with my account.
> What could prevent SQL debugger from connecting to my workstation? I tried
> to add that SQL service account to my local admin group, but it did not he
lp.
> I am not sure what is required to "2) Verify that machine '<server>' can o
pen
> files on machine '<my machine>'." Does it mean that I have to trust my ser
ver
> machine? How can I do that?
> What else could be wrong?

Remote ODBC connections fail to SQL 2005 database (Error: 18456, Severity: 14, State: 8.)

I have reviewed the blog posting "Understanding "login failed" (Error
18456) error messages in SQL Server 2005" at
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
2007-03-21 11:49:14.55 Logon Error: 18456, Severity: 14, State: 8.
2007-03-21 11:49:14.55 Logon Login failed for user 'USERNAMEOBSCURED'.
[CLIENT: 192.168.183.144]
According to the blog, this indicates that the password is invalid, but I
have reset it 5 times, making sure the password is the same at both ends.
This username, USERNAMEOBSCURED, is a SQL Server authentication type user,
and I have verfied that Enforce password policy, expiration, must change
password are all unchecked and the account is not set to locked out.
The ODBC client is another Windows Server 2003 SP1 computer in the same
workgroup as the SQL 2005 SP1 server. I am using the Microsoft SQL Server
ODBC Driver Version 03.86.1830 via TCP/IP.
Since I don't have that much experence running SQL server, which and
what database permissions should USERNAMEOBSCURED be given? If I want to
create a new username to test it, how should that be done for SQL Server
authentication? If these are in the books online, please point me to the
right location.
Hi,
I understand that you encountered the error:
Login failed for user 'USERNAMEOBSCURED'
When you remotely use ODBC driver to connect to your SQL 2005 instance.
If I have misunderstood, please let me know.
For troubleshooting the login errors, you may refer to:
Troubleshooting: Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx
How to: Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
Though the error state indicates that it is a password error, I recommend
that also check the following and let me know the results:
1. Check if you can log on your SQL Server locally with the login and
password;
2. Open SQL Server Configuration Manager to see if the TCP/IP protocol has
been enabled;
3. Open SQL Server Surface Area Configuration, click Surface Area
Configuration for Services and Connections to see if Local and remote
connections is selected under the Remote Connections.
4. If the issue persists, try creating a new login to see if this issue
persists;
5. Run "osql /S<server name> /U<login id> /P<password>" from command line
on your client computer to check if you can connect to your SQL Server;
6. Disable the firewall on your SQL Server computer and run "netstat -nba
>C:\serviceslist.log" to check which TCP port has been listened by your SQL
Server service (sqlservr.exe); then run "telnet <server name> <TCP port>"
from command on the client computer to check if you can log on the port.
7. Run "cliconfg.exe" from command line on the client computer, switch to
the Alias tab, add an alias with TCP/IP protocol for the SQL instance.
Please note that fill in the correct Tcp port and server name (the SQL
Server computer name) in the input fields. Then run the step 2 again.
If all above methods still cannot make it work, please use NetMon to
monitor the network stream by filter the TCP protocal and port.
You may refer to:
Microsoft Network Monitor 3
http://www.microsoft.com/downloads/details.aspx?familyid=AA8BE06D-4A6A-4B69-
B861-2043B665CB53&displaylang=en
How To Capture TCP and HTTP Data Frames Using Network Monitor and MSXML
HTTP Code
http://support.microsoft.com/kb/295405
How to capture network traffic with Network Monitor
http://support.microsoft.com/kb/148942
Troubleshooting by Using Network Monitor
http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/intwork/
inbc_ias_ykqb.mspx?mfr=true
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Using suggestion 4 by creating a new user USERNAMEOBSCUREDTEST and
setting the User Mapping set to DATABASENAMEOBSCURED database under Default
Schema dbo is proving to be a temporary workaround. I am consulting with
the 3rd party creator of the database to try and understand why the username
is already in the database but is not mapped according to SQL 2005. I am
wondering if this is due to the fact that the database was not created fresh
in SQL 2005, it was imported from an existing SQL 7 database from another
machine. Apparently USERNAMEOBSCURED is not mapped to any database according
to the view in Microsoft SQL Server Management Studio, and when I attempt to
map the username to DATABASENAMEOBSCURED with the default Database role
membership of public I get this error:
"TITLE: Microsoft SQL Server Management Studio
Create failed for User 'USERNAMEOBSCURED'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+User&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
User, group, or role 'USERNAMEOBSCURED' already exists in the current
database. (Microsoft SQL Server, Error: 15023)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476[ /url]
BUTTONS:
OK
--"
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ic0m3bFbHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I understand that you encountered the error:
> Login failed for user 'USERNAMEOBSCURED'
> When you remotely use ODBC driver to connect to your SQL 2005 instance.
> If I have misunderstood, please let me know.
> For troubleshooting the login errors, you may refer to:
> Troubleshooting: Login failed for user 'x'
> http://msdn2.microsoft.com/en-us/library/ms366351.aspx
> How to: Change Server Authentication Mode
> http://msdn2.microsoft.com/en-us/library/ms188670.aspx
> Though the error state indicates that it is a password error, I recommend
> that also check the following and let me know the results:
> 1. Check if you can log on your SQL Server locally with the login and
> password;
> 2. Open SQL Server Configuration Manager to see if the TCP/IP protocol has
> been enabled;
> 3. Open SQL Server Surface Area Configuration, click Surface Area
> Configuration for Services and Connections to see if Local and remote
> connections is selected under the Remote Connections.
> 4. If the issue persists, try creating a new login to see if this issue
> persists;
> 5. Run "osql /S<server name> /U<login id> /P<password>" from command line
> on your client computer to check if you can connect to your SQL Server;
> 6. Disable the firewall on your SQL Server computer and run "netstat -nba
> Server service (sqlservr.exe); then run "telnet <server name> <TCP port>"
> from command on the client computer to check if you can log on the port.
> 7. Run "cliconfg.exe" from command line on the client computer, switch to
> the Alias tab, add an alias with TCP/IP protocol for the SQL instance.
> Please note that fill in the correct Tcp port and server name (the SQL
> Server computer name) in the input fields. Then run the step 2 again.
> If all above methods still cannot make it work, please use NetMon to
> monitor the network stream by filter the TCP protocal and port.
> You may refer to:
> Microsoft Network Monitor 3
> http://www.microsoft.com/downloads/details.aspx?familyid=AA8BE06D-4A6A-4B69-
> B861-2043B665CB53&displaylang=en
> How To Capture TCP and HTTP Data Frames Using Network Monitor and MSXML
> HTTP Code
> http://support.microsoft.com/kb/295405
> How to capture network traffic with Network Monitor
> http://support.microsoft.com/kb/148942
> Troubleshooting by Using Network Monitor
> http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/intwork/
> inbc_ias_ykqb.mspx?mfr=true
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
>
>
>
|||Hi,
Thanks for your response.
Since your database is migrated from SQL 7.0, this issue is indeed
possible. Please try the tool mapsids.exe according to this article to see
if this issue persists:
How to resolve permission issues when you move a database between servers
that are running SQL Server
http://support.microsoft.com/kb/240872/EN-US/
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/kb/274188/
Hope this helps.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Sorry for the long delay, we were able to fix the issue with the
duplicate users, I'll have to dig up my notes from two weeks ago to recall
the procedure we used. Sorry for not closing this issue sooner.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:Y6QkJsFcHHA.2060@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hi,
Thanks for your updating and response. Please do not worry about it.
Anytime your posting back is welcome.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Remote ODBC connections fail to SQL 2005 database (Error: 18456, Severity: 14, State: 8.)

I have reviewed the blog posting "Understanding "login failed" (Error
18456) error messages in SQL Server 2005" at
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
2007-03-21 11:49:14.55 Logon Error: 18456, Severity: 14, State: 8.
2007-03-21 11:49:14.55 Logon Login failed for user 'USERNAMEOBSCURED'.
[CLIENT: 192.168.183.144]
According to the blog, this indicates that the password is invalid, but I
have reset it 5 times, making sure the password is the same at both ends.
This username, USERNAMEOBSCURED, is a SQL Server authentication type user,
and I have verfied that Enforce password policy, expiration, must change
password are all unchecked and the account is not set to locked out.
The ODBC client is another Windows Server 2003 SP1 computer in the same
workgroup as the SQL 2005 SP1 server. I am using the Microsoft SQL Server
ODBC Driver Version 03.86.1830 via TCP/IP.
Since I don't have that much experence running SQL server, which and
what database permissions should USERNAMEOBSCURED be given? If I want to
create a new username to test it, how should that be done for SQL Server
authentication? If these are in the books online, please point me to the
right location.Hi,
I understand that you encountered the error:
Login failed for user 'USERNAMEOBSCURED'
When you remotely use ODBC driver to connect to your SQL 2005 instance.
If I have misunderstood, please let me know.
For troubleshooting the login errors, you may refer to:
Troubleshooting: Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx
How to: Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
Though the error state indicates that it is a password error, I recommend
that also check the following and let me know the results:
1. Check if you can log on your SQL Server locally with the login and
password;
2. Open SQL Server Configuration Manager to see if the TCP/IP protocol has
been enabled;
3. Open SQL Server Surface Area Configuration, click Surface Area
Configuration for Services and Connections to see if Local and remote
connections is selected under the Remote Connections.
4. If the issue persists, try creating a new login to see if this issue
persists;
5. Run "osql /S<server name> /U<login id> /P<password>" from command line
on your client computer to check if you can connect to your SQL Server;
6. Disable the firewall on your SQL Server computer and run "netstat -nba
>C:\serviceslist.log" to check which TCP port has been listened by your SQL
Server service (sqlservr.exe); then run "telnet <server name> <TCP port>"
from command on the client computer to check if you can log on the port.
7. Run "cliconfg.exe" from command line on the client computer, switch to
the Alias tab, add an alias with TCP/IP protocol for the SQL instance.
Please note that fill in the correct Tcp port and server name (the SQL
Server computer name) in the input fields. Then run the step 2 again.
If all above methods still cannot make it work, please use NetMon to
monitor the network stream by filter the TCP protocal and port.
You may refer to:
Microsoft Network Monitor 3
http://www.microsoft.com/downloads/details.aspx?familyid=AA8BE06D-4A6A-4B69-
B861-2043B665CB53&displaylang=en
How To Capture TCP and HTTP Data Frames Using Network Monitor and MSXML
HTTP Code
http://support.microsoft.com/kb/295405
How to capture network traffic with Network Monitor
http://support.microsoft.com/kb/148942
Troubleshooting by Using Network Monitor
http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/intwork/
inbc_ias_ykqb.mspx?mfr=true
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Using suggestion 4 by creating a new user USERNAMEOBSCUREDTEST and
setting the User Mapping set to DATABASENAMEOBSCURED database under Default
Schema dbo is proving to be a temporary workaround. I am consulting with
the 3rd party creator of the database to try and understand why the username
is already in the database but is not mapped according to SQL 2005. I am
wondering if this is due to the fact that the database was not created fresh
in SQL 2005, it was imported from an existing SQL 7 database from another
machine. Apparently USERNAMEOBSCURED is not mapped to any database according
to the view in Microsoft SQL Server Management Studio, and when I attempt to
map the username to DATABASENAMEOBSCURED with the default Database role
membership of public I get this error:
"TITLE: Microsoft SQL Server Management Studio
--
Create failed for User 'USERNAMEOBSCURED'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
User, group, or role 'USERNAMEOBSCURED' already exists in the current
database. (Microsoft SQL Server, Error: 15023)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476
--
BUTTONS:
OK
--"
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ic0m3bFbHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I understand that you encountered the error:
> Login failed for user 'USERNAMEOBSCURED'
> When you remotely use ODBC driver to connect to your SQL 2005 instance.
> If I have misunderstood, please let me know.
> For troubleshooting the login errors, you may refer to:
> Troubleshooting: Login failed for user 'x'
> http://msdn2.microsoft.com/en-us/library/ms366351.aspx
> How to: Change Server Authentication Mode
> http://msdn2.microsoft.com/en-us/library/ms188670.aspx
> Though the error state indicates that it is a password error, I recommend
> that also check the following and let me know the results:
> 1. Check if you can log on your SQL Server locally with the login and
> password;
> 2. Open SQL Server Configuration Manager to see if the TCP/IP protocol has
> been enabled;
> 3. Open SQL Server Surface Area Configuration, click Surface Area
> Configuration for Services and Connections to see if Local and remote
> connections is selected under the Remote Connections.
> 4. If the issue persists, try creating a new login to see if this issue
> persists;
> 5. Run "osql /S<server name> /U<login id> /P<password>" from command line
> on your client computer to check if you can connect to your SQL Server;
> 6. Disable the firewall on your SQL Server computer and run "netstat -nba
>>C:\serviceslist.log" to check which TCP port has been listened by your SQL
> Server service (sqlservr.exe); then run "telnet <server name> <TCP port>"
> from command on the client computer to check if you can log on the port.
> 7. Run "cliconfg.exe" from command line on the client computer, switch to
> the Alias tab, add an alias with TCP/IP protocol for the SQL instance.
> Please note that fill in the correct Tcp port and server name (the SQL
> Server computer name) in the input fields. Then run the step 2 again.
> If all above methods still cannot make it work, please use NetMon to
> monitor the network stream by filter the TCP protocal and port.
> You may refer to:
> Microsoft Network Monitor 3
> http://www.microsoft.com/downloads/details.aspx?familyid=AA8BE06D-4A6A-4B69-
> B861-2043B665CB53&displaylang=en
> How To Capture TCP and HTTP Data Frames Using Network Monitor and MSXML
> HTTP Code
> http://support.microsoft.com/kb/295405
> How to capture network traffic with Network Monitor
> http://support.microsoft.com/kb/148942
> Troubleshooting by Using Network Monitor
> http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/intwork/
> inbc_ias_ykqb.mspx?mfr=true
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>
>
>
>
>|||Hi,
Thanks for your response.
Since your database is migrated from SQL 7.0, this issue is indeed
possible. Please try the tool mapsids.exe according to this article to see
if this issue persists:
How to resolve permission issues when you move a database between servers
that are running SQL Server
http://support.microsoft.com/kb/240872/EN-US/
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/kb/274188/
Hope this helps.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Sorry for the long delay, we were able to fix the issue with the
duplicate users, I'll have to dig up my notes from two weeks ago to recall
the procedure we used. Sorry for not closing this issue sooner.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:Y6QkJsFcHHA.2060@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi,
Thanks for your updating and response. Please do not worry about it.
Anytime your posting back is welcome.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Remote messages not working – message vanishes

I need some assistance with getting service broker to work across servers. I have 2 separate servers and I am trying to send a message from one server to the other. No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server. Where is the message going?

Here is the code I use to create the service broker objects that are being used…

RUN THIS ON THE SENDING SERVER:

CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE

CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE

GO

CREATE CONTRACT [MyContract] (

MyMessage SENT BY INITIATOR,

MyResponse SENT BY TARGET)

GO

CREATE QUEUE [MyInitiatorQueue] with status = ON

CREATE QUEUE [MyTargetQueue] with status = ON

GO

CREATE SERVICE [MyInitiatorService] ON QUEUE [MyInitiatorQueue]

GO

CREATE ROUTE [RouteToODS]

WITH

SERVICE_NAME = N'MyTargetService',

BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E',

ADDRESS = N'TCP://CONSULT01:4022'

GO

CREATE ENDPOINT DWHEndPoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (

AUTHENTICATION = WINDOWS,

ENCRYPTION = DISABLED)

GO

RUN THIS ON THE RECEIVING SERVER:

CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE

CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE

GO

CREATE CONTRACT [MyContract] (

MyMessage SENT BY INITIATOR,

MyResponse SENT BY TARGET)

GO

CREATE QUEUE [MyInitiatorQueue] with status = ON

CREATE QUEUE [MyTargetQueue] with status = ON

GO

CREATE SERVICE [MyTargetService] ON QUEUE [MyTargetQueue] ([MyContract])

GO

CREATE ROUTE [RouteToDWH]

WITH

SERVICE_NAME = N'MyInitiatorService',

BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5',

ADDRESS = N'TCP://DWH:4022'

GO

CREATE ENDPOINT ODSEndPoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (

AUTHENTICATION = WINDOWS,

ENCRYPTION = DISABLED)

GO

SEND A MESSAGE USING THE FOLLOWING:

Declare @.ConversationHandle uniqueidentifier

Begin Transaction

Begin Dialog @.ConversationHandle

From Service [MyInitiatorService]

To Service 'MyTargetService'

On Contract [MyContract]

With Encryption = Off,

Lifetime = 600;

Send on Conversation @.ConversationHandle

Message Type [MyMessage] (N'This is a my message')

End Conversation @.ConversationHandle

Commit

Select GET_TRANSMISSION_STATUS(@.ConversationHandle)

I've done a bit more in trying to resolve the issue and I'm more confused than ever...

As per the Service Broker Routing article on MSDN I have removed the route from the database of the target server and created it in MSDB instead. After doing that the message now remains in the transmission queue of the sending server but without any error (transmission_status is blank). However when I look at the summary report of Service Broker on the sending server under #Messages in Transmission Queue it shows 2 messages and “MyInitiatorService(Non existent service)” in the key to the graph. If there was a problem with the service why would it not show the error in the transmission queue, and what is the problem with the service?

|||I don't know what routing article you mention, but you should definitely have a route both on the sending side as well as on the receiving side - in the databases, not in MSDB.

Also, take out the END CONVERSATION after you have send. End conversation kills the conversation and discards un-send messages. That's probably why you don't see a message in the sys.transmission_queue.

Niels|||

Most likely your message reaches the target, it gets denied access on the service (since you use unsecure dialogs and you don't grant SEND permission to [Public]), the error comes back and then the dialog gets deleted, since you already ended it.

1. Grant SEND permission to [Public] on the target service
2. Don't end the dialog on the initiator side prematurely

HTH,
~ Remus

|||

BTW, you could try following the steps from this article to identify the problem: http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx

HTH,
~ Remus

|||

Niels, End conversation does not discard pending messages. However, once you have closed the conversation on your end, you will no longer be able to receive response messages, including errors sent back by target (eg> sender does not have permission to send to this target service).

Flamin, therefore you should not end conversation at the initiator until you've confirmed that it has been delivered to the target service.

Rushi

|||

Thanks for the clarification on End Conversation, I didn’t know that ending the conversation would block errors too. As soon as I removed the End Conversation I received a permissions error. As you suspected Remus the problem was due to a lack of send permissions on the service.

|||

Hi Remus,

I am facing problem which is somewhat related to this it will be great if you can help me in resolving it. Actually for me every thing is working fine in the sense i am able to send and receive the messages.

But i see their are so many messages which are stuck up in sys.conversation_endpoints table. All this messages are having state as DI and initiator as 0.

As i am sending number of messages using same queue i am not able to get to the route of the cause due to which messages are getting stuck up.

Even i am ending conversation on initiator end so can that be the reason for the issue.

Please let me know if you need any more information from me.

Thanks

|||

Those are not messages, but conversation endpoints. DI state for is_initiator = 0 means that you receive EndDialog message on the target, but you don't end the target endpoint of the dialog. To completely close, a conversation must be ended by both conversation endpoints (initiator and target). Typicaly this means than wehn the RECEIVE returns a message of type [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] you should issue END CONVERSATION on the received handle.

HTH,
~ Remus

|||

Thanks for your suggestions

But the real issue is when and how should i delete those records from target end.

This is how i am sending message

SELECT @.SendService = '//'+@.Pod_ID+'/TransactionQueue',
@.ReceiveService = '//ODS/'+@.Pod_ID+'/TransactionQueue',
@.Contract = '//Contract/TransactionQueue';

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

And this is how i am processing the message on target end

RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

I am having a code

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
RETURN 0
END

Which i thought is creating problem. But i see their are no records with this message type in TransactionQueue (user defined queue) and sys.transmission_queue (system queue).

I am still curious to know how can i get rid of those records from conversation_endpoints. As the count is growing like anything.

Just one more question. If solution is ending conversation on target end, which approach should i follow

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

2: IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

TIA

Prashant

|||

In case 1, you are ending the dialog regardless of the type of message received. If you expect more than one message interaction, there will be no way to receive more messages.

In case 2, you are ending the dialog only when the initiator has closed the dialog and you know for sure, no more messages are expected.

Also, let me warn you that the pattern you are using (which we call fire-and-forget), where the initiator begins a dialog, sends a message and ends the conversation can be problematic for two main reasons:

i) There is no app-level reliability, i.e. without getting a response back, the initiating app will never know whether its message was processed or not.

ii) During development, you will not be able to see error messages sent back by the target.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

Remote messages not working – message vanishes

I need some assistance with getting service broker to work across servers. I have 2 separate servers and I am trying to send a message from one server to the other. No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server. Where is the message going?

Here is the code I use to create the service broker objects that are being used…

RUN THIS ON THE SENDING SERVER:

CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE

CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE

GO

CREATE CONTRACT [MyContract] (

MyMessage SENT BY INITIATOR,

MyResponse SENT BY TARGET)

GO

CREATE QUEUE [MyInitiatorQueue] with status = ON

CREATE QUEUE [MyTargetQueue] with status = ON

GO

CREATE SERVICE [MyInitiatorService] ON QUEUE [MyInitiatorQueue]

GO

CREATE ROUTE [RouteToODS]

WITH

SERVICE_NAME = N'MyTargetService',

BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E',

ADDRESS = N'TCP://CONSULT01:4022'

GO

CREATE ENDPOINT DWHEndPoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (

AUTHENTICATION = WINDOWS,

ENCRYPTION = DISABLED)

GO

RUN THIS ON THE RECEIVING SERVER:

CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE

CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE

GO

CREATE CONTRACT [MyContract] (

MyMessage SENT BY INITIATOR,

MyResponse SENT BY TARGET)

GO

CREATE QUEUE [MyInitiatorQueue] with status = ON

CREATE QUEUE [MyTargetQueue] with status = ON

GO

CREATE SERVICE [MyTargetService] ON QUEUE [MyTargetQueue] ([MyContract])

GO

CREATE ROUTE [RouteToDWH]

WITH

SERVICE_NAME = N'MyInitiatorService',

BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5',

ADDRESS = N'TCP://DWH:4022'

GO

CREATE ENDPOINT ODSEndPoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (

AUTHENTICATION = WINDOWS,

ENCRYPTION = DISABLED)

GO

SEND A MESSAGE USING THE FOLLOWING:

Declare @.ConversationHandle uniqueidentifier

Begin Transaction

Begin Dialog @.ConversationHandle

From Service [MyInitiatorService]

To Service 'MyTargetService'

On Contract [MyContract]

With Encryption = Off,

Lifetime = 600;

Send on Conversation @.ConversationHandle

Message Type [MyMessage] (N'This is a my message')

End Conversation @.ConversationHandle

Commit

Select GET_TRANSMISSION_STATUS(@.ConversationHandle)

I've done a bit more in trying to resolve the issue and I'm more confused than ever...

As per the Service Broker Routing article on MSDN I have removed the route from the database of the target server and created it in MSDB instead. After doing that the message now remains in the transmission queue of the sending server but without any error (transmission_status is blank). However when I look at the summary report of Service Broker on the sending server under #Messages in Transmission Queue it shows 2 messages and “MyInitiatorService(Non existent service)” in the key to the graph. If there was a problem with the service why would it not show the error in the transmission queue, and what is the problem with the service?

|||I don't know what routing article you mention, but you should definitely have a route both on the sending side as well as on the receiving side - in the databases, not in MSDB.

Also, take out the END CONVERSATION after you have send. End conversation kills the conversation and discards un-send messages. That's probably why you don't see a message in the sys.transmission_queue.

Niels|||

Most likely your message reaches the target, it gets denied access on the service (since you use unsecure dialogs and you don't grant SEND permission to [Public]), the error comes back and then the dialog gets deleted, since you already ended it.

1. Grant SEND permission to [Public] on the target service
2. Don't end the dialog on the initiator side prematurely

HTH,
~ Remus

|||

BTW, you could try following the steps from this article to identify the problem: http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx

HTH,
~ Remus

|||

Niels, End conversation does not discard pending messages. However, once you have closed the conversation on your end, you will no longer be able to receive response messages, including errors sent back by target (eg> sender does not have permission to send to this target service).

Flamin, therefore you should not end conversation at the initiator until you've confirmed that it has been delivered to the target service.

Rushi

|||

Thanks for the clarification on End Conversation, I didn’t know that ending the conversation would block errors too. As soon as I removed the End Conversation I received a permissions error. As you suspected Remus the problem was due to a lack of send permissions on the service.

|||

Hi Remus,

I am facing problem which is somewhat related to this it will be great if you can help me in resolving it. Actually for me every thing is working fine in the sense i am able to send and receive the messages.

But i see their are so many messages which are stuck up in sys.conversation_endpoints table. All this messages are having state as DI and initiator as 0.

As i am sending number of messages using same queue i am not able to get to the route of the cause due to which messages are getting stuck up.

Even i am ending conversation on initiator end so can that be the reason for the issue.

Please let me know if you need any more information from me.

Thanks

|||

Those are not messages, but conversation endpoints. DI state for is_initiator = 0 means that you receive EndDialog message on the target, but you don't end the target endpoint of the dialog. To completely close, a conversation must be ended by both conversation endpoints (initiator and target). Typicaly this means than wehn the RECEIVE returns a message of type [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] you should issue END CONVERSATION on the received handle.

HTH,
~ Remus

|||

Thanks for your suggestions

But the real issue is when and how should i delete those records from target end.

This is how i am sending message

SELECT @.SendService = '//'+@.Pod_ID+'/TransactionQueue',
@.ReceiveService = '//ODS/'+@.Pod_ID+'/TransactionQueue',
@.Contract = '//Contract/TransactionQueue';

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

And this is how i am processing the message on target end

RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

I am having a code

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
RETURN 0
END

Which i thought is creating problem. But i see their are no records with this message type in TransactionQueue (user defined queue) and sys.transmission_queue (system queue).

I am still curious to know how can i get rid of those records from conversation_endpoints. As the count is growing like anything.

Just one more question. If solution is ending conversation on target end, which approach should i follow

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

2: IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

TIA

Prashant

|||

In case 1, you are ending the dialog regardless of the type of message received. If you expect more than one message interaction, there will be no way to receive more messages.

In case 2, you are ending the dialog only when the initiator has closed the dialog and you know for sure, no more messages are expected.

Also, let me warn you that the pattern you are using (which we call fire-and-forget), where the initiator begins a dialog, sends a message and ends the conversation can be problematic for two main reasons:

i) There is no app-level reliability, i.e. without getting a response back, the initiating app will never know whether its message was processed or not.

ii) During development, you will not be able to see error messages sent back by the target.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

Remote messages not working – message vanishes

I need some assistance with getting service broker to work across servers. I have 2 separate servers and I am trying to send a message from one server to the other. No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server. Where is the message going?

Here is the code I use to create the service broker objects that are being used…

RUN THIS ON THE SENDING SERVER:

CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE

CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE

GO

CREATE CONTRACT [MyContract] (

MyMessage SENT BY INITIATOR,

MyResponse SENT BY TARGET)

GO

CREATE QUEUE [MyInitiatorQueue] with status = ON

CREATE QUEUE [MyTargetQueue] with status = ON

GO

CREATE SERVICE [MyInitiatorService] ON QUEUE [MyInitiatorQueue]

GO

CREATE ROUTE [RouteToODS]

WITH

SERVICE_NAME = N'MyTargetService',

BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E',

ADDRESS = N'TCP://CONSULT01:4022'

GO

CREATE ENDPOINT DWHEndPoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (

AUTHENTICATION = WINDOWS,

ENCRYPTION = DISABLED)

GO

RUN THIS ON THE RECEIVING SERVER:

CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE

CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE

GO

CREATE CONTRACT [MyContract] (

MyMessage SENT BY INITIATOR,

MyResponse SENT BY TARGET)

GO

CREATE QUEUE [MyInitiatorQueue] with status = ON

CREATE QUEUE [MyTargetQueue] with status = ON

GO

CREATE SERVICE [MyTargetService] ON QUEUE [MyTargetQueue] ([MyContract])

GO

CREATE ROUTE [RouteToDWH]

WITH

SERVICE_NAME = N'MyInitiatorService',

BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5',

ADDRESS = N'TCP://DWH:4022'

GO

CREATE ENDPOINT ODSEndPoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (

AUTHENTICATION = WINDOWS,

ENCRYPTION = DISABLED)

GO

SEND A MESSAGE USING THE FOLLOWING:

Declare @.ConversationHandle uniqueidentifier

Begin Transaction

Begin Dialog @.ConversationHandle

From Service [MyInitiatorService]

To Service 'MyTargetService'

On Contract [MyContract]

With Encryption = Off,

Lifetime = 600;

Send on Conversation @.ConversationHandle

Message Type [MyMessage] (N'This is a my message')

End Conversation @.ConversationHandle

Commit

Select GET_TRANSMISSION_STATUS(@.ConversationHandle)

I've done a bit more in trying to resolve the issue and I'm more confused than ever...

As per the Service Broker Routing article on MSDN I have removed the route from the database of the target server and created it in MSDB instead. After doing that the message now remains in the transmission queue of the sending server but without any error (transmission_status is blank). However when I look at the summary report of Service Broker on the sending server under #Messages in Transmission Queue it shows 2 messages and “MyInitiatorService(Non existent service)” in the key to the graph. If there was a problem with the service why would it not show the error in the transmission queue, and what is the problem with the service?

|||I don't know what routing article you mention, but you should definitely have a route both on the sending side as well as on the receiving side - in the databases, not in MSDB.

Also, take out the END CONVERSATION after you have send. End conversation kills the conversation and discards un-send messages. That's probably why you don't see a message in the sys.transmission_queue.

Niels|||

Most likely your message reaches the target, it gets denied access on the service (since you use unsecure dialogs and you don't grant SEND permission to [Public]), the error comes back and then the dialog gets deleted, since you already ended it.

1. Grant SEND permission to [Public] on the target service
2. Don't end the dialog on the initiator side prematurely

HTH,
~ Remus

|||

BTW, you could try following the steps from this article to identify the problem: http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx

HTH,
~ Remus

|||

Niels, End conversation does not discard pending messages. However, once you have closed the conversation on your end, you will no longer be able to receive response messages, including errors sent back by target (eg> sender does not have permission to send to this target service).

Flamin, therefore you should not end conversation at the initiator until you've confirmed that it has been delivered to the target service.

Rushi

|||

Thanks for the clarification on End Conversation, I didn’t know that ending the conversation would block errors too. As soon as I removed the End Conversation I received a permissions error. As you suspected Remus the problem was due to a lack of send permissions on the service.

|||

Hi Remus,

I am facing problem which is somewhat related to this it will be great if you can help me in resolving it. Actually for me every thing is working fine in the sense i am able to send and receive the messages.

But i see their are so many messages which are stuck up in sys.conversation_endpoints table. All this messages are having state as DI and initiator as 0.

As i am sending number of messages using same queue i am not able to get to the route of the cause due to which messages are getting stuck up.

Even i am ending conversation on initiator end so can that be the reason for the issue.

Please let me know if you need any more information from me.

Thanks

|||

Those are not messages, but conversation endpoints. DI state for is_initiator = 0 means that you receive EndDialog message on the target, but you don't end the target endpoint of the dialog. To completely close, a conversation must be ended by both conversation endpoints (initiator and target). Typicaly this means than wehn the RECEIVE returns a message of type [http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] you should issue END CONVERSATION on the received handle.

HTH,
~ Remus

|||

Thanks for your suggestions

But the real issue is when and how should i delete those records from target end.

This is how i am sending message

SELECT @.SendService = '//'+@.Pod_ID+'/TransactionQueue',
@.ReceiveService = '//ODS/'+@.Pod_ID+'/TransactionQueue',
@.Contract = '//Contract/TransactionQueue';

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

And this is how i am processing the message on target end

RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

I am having a code

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
RETURN 0
END

Which i thought is creating problem. But i see their are no records with this message type in TransactionQueue (user defined queue) and sys.transmission_queue (system queue).

I am still curious to know how can i get rid of those records from conversation_endpoints. As the count is growing like anything.

Just one more question. If solution is ending conversation on target end, which approach should i follow

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

2: IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

TIA

Prashant

|||

In case 1, you are ending the dialog regardless of the type of message received. If you expect more than one message interaction, there will be no way to receive more messages.

In case 2, you are ending the dialog only when the initiator has closed the dialog and you know for sure, no more messages are expected.

Also, let me warn you that the pattern you are using (which we call fire-and-forget), where the initiator begins a dialog, sends a message and ends the conversation can be problematic for two main reasons:

i) There is no app-level reliability, i.e. without getting a response back, the initiating app will never know whether its message was processed or not.

ii) During development, you will not be able to see error messages sent back by the target.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.

|||

Prashant wrote:

1: RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;
END CONVERSATION @.handle;

Here you are ending the dialog regardless of the type of message being received. If you expect more than a single message to be received on the same dialog, this would be incorrect.

Prashant wrote:

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
RETURN 0
END

Here you expect the initiator to close the dialog first and then the target closes the dialog upon receiving the end-dialog message. This seems more appropriate if you will have multiple messages on the dialog and the initiator decides when it is done sending messages. Remember, when the initiator ends a dialog, there is no way for it to know if its last message was processed or not.