Dot Net Tips and Tricks

Part III – Connection Fail when SqlClient connects to Sql Server 2005

I found this article for troubleshooting Service broker and thought others might find it useful. I’ve just posted the summary and the link to the original is below.

When you connect to SQL Server 2005 either using “SQL Server Managment Studio” or any application compiled with .NET Framework 2.0, you are using SqlClient provider(Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.)

Error Message 1:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Troubleshoot:
1) Make sure your sql service is running, use either “net start” or “sc query <InstanceName>” or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.

2) You might explicitly use “np:”prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:

Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]

Notice that “sql\query” is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is “sql\query1″, then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

3) You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.

4) You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.

5) You might explictly specify “lpc:” prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

Error Message 2:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

1) You might explicitly use “np:”prefix which ask for connect through named pipe and specify FQDN/LoopbackIP/IPAddress as server name in the connection string.
2) You might use FQDN/IPAddress/LoopbackIP to connect to the server.

To resolve 1) and 2), you can specify <machinename> instead of FQDN/IPADress/LoopbackIP.

More of the article…

Summary:

1) In any case, SqlClient should be able to connect to SQL Instance through any of the protocols(Shared Memory/Named Pipe/TCP) as long as SQL Instance was started successfully.

2) And if you speculate any protocol in connection string (”lpc:”/”np:”/”tcp:”), the error message would display “<Protocol> Provider, error <Num1> -….<Num2>.” <Protocol> stands for “Shared Memory” or “Named Pipes” or “TCP”; If you do not speculate
any protocol, the error message indicates that connection fails when connecting through specific <Protocol>.

3) In the error message format for SqlClient, please notice two different error number. <Num1> stands for internal error thrown out by SQL Protocols, <Num2> is the OS error(eg: 233 - No process is on the other end of pipe). When you see <Num1>=0, that means the connection fails due to OS error not caused by SQL Protocols, under this situation, you can use “net helpmsg” to check specific OS info.

Finally, if you were developing .NET framework application and came across above issues in your client app, the best way is first try SQL Server Management Studio to connect to SQL Server using the exact same connection string in your app, and watch the error message, normally, there is additional error info at the end of error string, eg ( Microsoft SQL Server, Error:87) which gives you clue(net helpmsg 87) that problem inside your connection string.

I just found this great thread:

An easier way: Service Listing Manager
Setting up Service Broker routes, endpoints and security is just too hard. One has to run pages and pages of Transact-SQL code just to get the ‘Hello, world’ example work between two separate SQL Instances and the chances of making a mistake are overwhelming.

Well, not anymore! I’ve just uploaded into the Service Broker team code gallery a new GUI tool for doing just that: easily configure two services to be able to have conversations. The tool uses the ‘Service Listing’ concept. A Service Listing is like an identity card for a service. It is an XML document that contains all the necessary information needed to establish a conversation with that service. When two parties need to establish a conversation, they can exchange the Service Listings of the two services and the tool will create the entire infrastructure needed to establish the conversation. Optionally it can also create the message types and contracts supported by a target service in the initiator service database….

Here is a link to it:
Service Listing Manager

While testing a Notification Services install I found I had alot of dead conversations left in my SYS.CONVERSATION_ENDPOINTS

DECLARE @HANDLE UNIQUEIDENTIFIER
DECLARE C CURSOR FAST_FORWARD FOR SELECT CONVERSATION_HANDLE FROM SYS.CONVERSATION_ENDPOINTS
OPEN C;
FETCH NEXT FROM C INTO @HANDLE;
WHILE @@fetch_status = 0
BEGIN
END CONVERSATION @HANDLE WITH CLEANUP
FETCH NEXT FROM C INTO @HANDLE;
END
CLOSE C;
DEALLOCATE C;
GO

You would need to add a check to make sure the timeout period has expired but this works nicely to cleanup any outstanding conversations.

For more info: Microsoft Blog Post

I ran into the problem where I would send messages to the a remote service and the xml validation would fail. After checking the intiator side I couldn’t find an error message being returned from the receiving end. No event logs, nothing inserted into my error table… nothing. This got me digging around and I found a serious bug in my code.

I had purchased the Rational Guide to Service Broker 2005 by Roger Wolter and his pattern details the following:

DECLARE @conversationHandle uniqueidentifier

Begin Transaction

– Begin a dialog to the Order Service
BEGIN TRY
BEGIN DIALOG @conversationHandle
FROM SERVICE [TestCarolina_Message_Service]
TO SERVICE ‘Carolina_Message_Service’
ON CONTRACT [CarolinaOnly_Contract]
WITH ENCRYPTION = OFF, LIFETIME = 600;

– Send a message on the dialog
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [msg_to_carolina]
(@Message)

END CONVERSATION @conversationHandle

END TRY
BEGIN CATCH
ROLLBACK
INSERT GENERAL.ERRORS (ERROR)
VALUES( ERROR_MESSAGE())
– Reenable queue
ALTER QUEUE TestCarolina_Message_Queue WITH STATUS = ON
END CATCH

commit

There is one glaring problem with this pattern. If an error happens on the receiving side, the conversation has already been ended on the sender’s side. When the Error is sent from the receiver, the message gets dropped, No EventLog, nothing. The conversation that the receiver is sending on does not exist on the sender anymore.

This leaves the question, how do we cleanup the conversation? Allow the receiver to end the conversation rather than explicitly end in on the sender. This requires the addition of an activated procedure on the sender’s queue that will handle the end conversations, but it adds the ability to handle error messages also. The following in pattern would be the correct way:

CREATE PROCEDURE usp_Message_Queue_Activation
AS
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]), TIMEOUT 1000;
WHILE @dh IS NOT NULL
BEGIN
IF @message_type = N’http://schemas.microsoft.com/SQL/ServiceBroker/Error’
BEGIN
INSERT Service_Broker_Errors
(error, [message]) values(N’Received error %s from service [Target]‘, @message_body);
END
END CONVERSATION @dh;
COMMIT;
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]
), TIMEOUT 1000;
END
COMMIT;
GO

– Create queue for messages to go to
CREATE QUEUE Test_Message_Queue
WITH ACTIVATION (
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = [usp_Message_Queue_Activation],
EXECUTE AS OWNER);
GO

This would allow errors that occur during transmission to be handled gracefully.