SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

 SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

Checklist in case of above error

#1. SQL Server service must be running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

#2. TCP/IP enabled.

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

#3. Add Port  in Windows Firewall on SQL Server instance.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

#4 Enable Remote Connection

In order to connect to machine other than local we need to enable remote connection on instance.

#5 Enable SQL Server Browser service

Variable Scope in T-SQL & PL/SQL

As per general programming point of view variable declared inner most block should not be available to outer blocks. Let's see if we can conclude how it is approached in T-SQl and PL/SQL.

Scope of TSQL Variables
begin
declare @a int -- vaiable in outer begin/end
set @a=20
Select @a
    begin
        Declare @b int -- @b declared inner begin/end
        set @b=30
        Select @b
    end
Select @a,@b -- @b is accessible
end


SQL Server scoping rules are per batch. BEGIN/END have no meaning on scope in this example.

That is, the variable is *not* declared per BEGIN/END. It is declared once in the batch, so accessible to batch.

MSDN (http://msdn.microsoft.com/en-us/library/ms188927.aspx) says “The scope of a local variable is the batch in which it is declared. “

Scope of PS/SQL Variables

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.







Declare
      pa number; /* vaiable in outer begin/end*/

Begin
      pa :=20;

      declare 
          pb number; /* declared inner begin/end */
      begin

         pb:=30;
      end;    pc number;
    pc:= pa+pb; /* pa is accessible while pb is not */

end;


Hope it helps.

provider: TCP Provider, error:

Error:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

General Explanation:
This error will show if a connection is drawn from the connection pool and the connection to the server has been lost.
There is no way for a connection in the pool to know that the connection has been severed.

Reason:
There are a few common reasons for this.
1 The server has been restarted, this will close the existing connections.
2 Someone or something has killed the SPID that is being used. Connection drop or Deadlock where a victim is chosen could be one of the reason.

Probale Solutions:
#1 Connection pooling (Not recommended)
Because of reason 1, we tried turning off connection pooling at application level even that did not help.

#2 TCP Chimney Offload
This may also be caused by other, non SQL Server related, reasons. 
I have for example seen issues where the TCP Chimney Offload feature on the server machine is turned ON, causing this. Short info about this feature;
When TCP Chimney Offload is enabled and the NIC implements what is called the TCP Offload Engine, then some of the TCP processing is handed over to the hardware, i.e. the NIC.
By doing this, the CPU is offloaded, and since TCP could require a lot of processing this would mean that the CPU will be allowed to perform other tasks.

We tried by disabling it on server but it did not help.

#3. Deadlock
We checked using profiler for deadlock events, but there were none in trace.
 
#4 Setting Static IP address on NIC
It helped us in solving the issue.



Hope it helps.

Why is the Data Loader importing dates incorrectly?

We were trying to upload to an object.Most of the dates uploaded exactly as defined in the CSV file, however others were one day out e.g 2-Jul uploaded as 1-Jul.

While using DataLoader it turns out that the Time Zone setting within data loader has a big impact.

Solution #1:

we changed it to "GMT" all the dates loaded without issue.
We also check the "Use European date format (dd/mm/yyyy)" within Data Loader.

Another option we worked out is specifying these setting in process-conf.xml file.

Solution #2 (for Automating Data Loader)
process-conf.xml
....


....

specified the dates in CSV file as dd/mm/yyyy

Hope it Helps

Myth : NOLOCK as silver bullet for performance

It is general practice to recommend NOLOCK to improve performance of query, though it result in dirty reads. But if dirty reads are fine in context of a code segment NOLOCK is recommended most of time. While looking for Error 605 explanation and remedy stumbled upon this on microsoft site.

Error 601

Severity Level 12

Message Text

Could not continue scan with NOLOCK due to data movement.

Explanation

When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft SQL Server is not able to continue the scan.

Action

This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint.

Database 'XYZ' is Already Open and Can Only Have One User at a Time

Database 'XYZ' is Already Open and Can Only Have One User at a Time

Reason:
The reason you are receiving this error is that your DB is set to single user mode.

Solution:
#1 To correct this error go into Enterprise Manager and right click on the DB 'XYZ' and go to Properties, than go to the options tab and uncheck the Single User check box.

#2 If we wish to from a separate connection

a) find who has what connections

exec sp_who

b) Disconnect the SPID which is using database 'XYZ'

kill 53

c) set database back to multiuser

alter database XYZ set multi_user

The path of the item '/Reports/Salesreport' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

One of SSRS report was crashing with the message "must be less than 260 characters". This message is bit confusing as we were having many urls which are bigger than 260 character limit and yet all those reports are rendered successfully.

Even the same report was successful with one set of parameter yet failed with another set of parameter. So, we arrived at conclusion that issue is with parameter value not URL length.

Reason : One or more parameters passed through query string may have restricted characters, which causes this particular issue.

Solution: Encode the parameter values before passing it to report. We used HttpUtility.UrlEncode(parameter.ToString()) while setting the value.

Hope it Helps