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

           Am getting (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5) error 
Problems:
1)Not connected to any sql server (both modes)
2)Remote connections also not connected 

An error has occurred while establishing a connection to the server.

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

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) (Microsoft SQL Server, Error: 1326)

I eventually found the resolution to this error on this blog itself; so I was saved from hunting for the solution. I am sure there are a number of developers who had previously fixed this error while installing SQL Server 2008 or SQL Server 2005 but in due course forgot the right solution to this error. This is just a quick refresher. Make sure to bookmark this as you never know when you would need this solution.

Let us check into the steps to resolve this error.

1) SQL Server should be up and 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”.

In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. 

I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.

2) Enable TCP/IP in SQL Server Configuration

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.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

Right Click on TCP/IP >> Click on Enable

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

3) Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running by firewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser should also be added to the exception, as described in Step 7.

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

Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP

4) Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

Go to Left Tab of Connections and check “Allow remote connections to this server”

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

Right Click on SQL Server Browser >> Click on Enable

6) Create exception of sqlbrowser.exe in Firewall

As elucidated in Step 6, sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to add exception for the same in windows firewall.

Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and create the exception of the file in Firewall, as delineated in Step 3.

7) Recreate Alias

It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply create alias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases

Delete the alias that is giving problem and recreate it with identical parameters.

The evolution of SQL Server

SQL Server has evolved over the years into the product it is today. The table below gives a summary of
this process.

Year	Version	Description
1988	SQL Server	Joint application built with Sybase for use on OS/2.
1993	SQL Server 4.2, adesktop database	A low-functionality, desktop database, capable of meeting the data storage and handling needs of a small department. The concept of a database that was integrated with Windows and had an easy-to-use interface proved popular.
1994		Microsoft splits from Sybase.
1995	SQL Server 6.05, a small business database	Major rewrite of the core database engine. First “significant” release. Improved performance and significant feature enhancements. Still a long way behind in terms of the performance and feature set of later versions, but with this version, SQL Server became capable of handling small e-commerce and intranet applications, and was a fraction of the cost of its competitors.
1996	SQL Server 6.5	SQL Server was gaining prominence such that Oracle brought out version 7.1 on the NT platform as direct competition.
1998	SQL Server 7.0, a web database	Another significant rewrite to the core database engine. A defining release, providing a reasonably powerful and featurerich database that was a truly viable (and still cheap) alternative for small-to-medium businesses, between a true desktop database such as MS Access and the high-end enterprise capabilities (and price) of Oracle and DB2. Gained a good reputation for ease of use and for providing crucial business tools (e.g., analysis services, data transformation services) out of the box, which were expensive add-ons with competing databases.
2000	SQL Server 2000, an enterprise database	Vastly improved performance scalability and reliability sees SQL Server become a major player in the enterprise database market (now supporting the online operations of businesses such as NASDAQ, Dell, and Barnes & Noble). A big increase in price (although still reckoned to be about half the cost of Oracle) slowed initial uptake, but the excellent range of management, development, and analysis tools won new customers. In 2001, Oracle (with 34% of the market) finally ceded its No. 1 position in the Windows database market (worth $2.55 billion in 2001) to SQL Server (with 40% of the market). In 2002, the gap had grown, with SQL Server at 45% and Oracle slipping to 27%.
2005	SQL Server 2005	Many areas of SQL Server have been rewritten, such as the ability to load data via a utility called Integration Services, but the greatest leap forward was the introduction of the .NET Framework. This allowed .NET SQL Server–specific objects to be built, giving SQL Server the flexible functionality that Oracle had with its inclusion of Java.
2008	SQL Server 2008	The aim of SQL Server 2008 is to deal with the many different forms that data can now take. It builds on the infrastructure of SQL Server 2005 by offering new data types and the use of Language Integrated Query (LINQ). It also deals with data, such as XML, compact devices, and massive database installations, that reside in many different places. Also, it offers the ability to set rules within a framework to ensure databases and objects meet defined criteria, and it offers the ability to report when these objects do not meet this criteria.

The History of SQL Server and relational databases

SQL stands for Structured Query Language and is pronounced either ess-que-el or sequel. It is the language used by relational database management systems (RDBMS) to access and manipulate data and to create, structure and destroy databases and database objects.

Brief History of SQL

In 1970, Dr. E.F. Codd published "A Relational Model of Data for Large Shared Data Banks," an article that outlined a model for storing and manipulating data using tables. Shortly after Codd's article was published, IBM began working on creating a relational database. Between 1979 and 1982, Oracle (then Relational Software, Inc.), Relational Technology, Inc. (later acquired by Computer Associates), and IBM all put out commercial relational databases, and by 1986 they all were using SQL as the data query language.
In 1986, the American National Standards Institute (ANSI) standardized SQL. This standard was updated in 1989, in 1992 (called SQL2), and again in 1999 (called SQL3). Standard SQL is sometimes called ANSI SQL or SQL92. All major relational databases support this standard but each has its own proprietary extensions. Unless otherwise noted, the SQL taught in this course is the standard ANSI SQL.

Relational Databases

A relational database at its simplest is a set of tables used for storing data. Each table has a unique name and may relate to one or more other tables in the database through common values.

Tables

A table in a database is a collection of rows and columns. Tables are also known as entities or relations.

Rows

A row contains data pertaining to a single item or record in a table. Rows are also known as records or tuples.

Columns

A column contains data representing a specific characteristic of the records in the table. Columns are also known as fields or attributes.

Relationships

A relationship is a link between two tables (i.e, relations). Relationships make it possible to find data in one table that pertains to a specific record in another table.

Datatypes

Each of a table's columns has a defined datatype that specifies the type of data that can exist in that column. For example, the FirstName column might be defined as varchar(20), indicating that it can contain a string of up to 20 characters. Unfortunately, datatypes vary widely between databases.

Primary Keys

Most tables have a column or group of columns that can be used to identify records. For example, an Employees table might have a column called EmployeeID that is unique for every row. This makes it easy to keep track of a record over time and to associate a record with records in other tables.

Foreign Keys

Foreign key columns are columns that link to primary key columns in other tables, thereby creating a relationship. For example, the Customers table might have a foreign key column called SalesRep that links to EmployeeID, the primary key in the Employees table.

Relational Database Management System

A Relational Database Management System (RDBMS), commonly (but incorrectly) called a database, is software for creating, manipulating, and administering a database. For simplicity, we will often refer to RDBMSs as databases.

Popular Databases

Commercial Databases

Oracle

Oracle is the most popular relational database. It runs on both Unix and Windows. It used to be many times more expensive than SQL Server and DB2, but it has come down a lot in price.

SQL Server

SQL Server is Microsoft's database and, not surprisingly, only runs on Windows. It has only a slightly higher market share than Oracle on Windows machines. Many people find it easier to use than Oracle.

DB2

IBM's DB2 was one of the earliest players in the database market. It is still very commonly used on mainframes and runs on both Windows and Unix.

Popular Open Source Databases

MySQL

Because of its small size, its speediness, and its very good documentation, MySQL has quickly become the most popular open source database. MySQL is available on both Windows and Unix, but it lacks some key features such as support for stored procedures.

PostgreSQL

Until recently, PostgreSQL was the most popular open source database until that spot was taken over by MySQL. PostgreSQL now calls itself "the world's most advanced Open Source database software." It is certainly a featureful and robust database management system and a good choice for people who want some of the advanced features that MySQL doesn't yet have. PostgreSQL does not yet natively support Windows, but it is supposed to in the upcoming 7.5 release.

Valid Object References

• server.database.owner.object
• server.database..object
• server..owner.object
• server...object
• database.owner.object
• database..object
• owner.object
• object

SQL Statements

Database Manipulation Language (DML)

DML statements are used to work with data in an existing database. The most common DML statements are:

• SELECT
• INSERT
• UPDATE
• DELETE

Database Definition Language (DDL)

DDL statements are used to structure objects in a database. The most common DDL statements are:

• CREATE
• ALTER
• DROP

Database Control Language (DCL)

DCL statements are used for database administration. The most common DCL statements are:

• GRANT
• DENY (SQL Server Only)
• REVOKE

Relational Database Basics Conclusion

We have covered a little bit of the history of SQL, how databases work, and the common SQL statements. Now we will get into learning how to work with SQL.

SQL SERVER DBA INDEX

The History of SQL Server and relational databases

• Relational database history
• The evolution of SQL Server
• SQL Server and ANSI standards

Installing and configuring SQL Server

• Pre-installation steps
• Installing SQL Server
• Configuring the run-time SQL Server system
• Patching SQL Server - Service packs

SQL Server data storage management

• SQL Server file management architecture
• Creating database files
• Creating SQL Server log files
• Using SQL Server file groups

Managing data with the DTS utility

• Using DTS to export and import SQL Server table data

SQL Server Security management

• Windows security management for the server
• Windows authentication mode
• SQL Server rights and role management

SQL Server Backup & Recovery

• Overview of SQL Server backup & recovery
• Incremental backups
• Restoring a SQL Server database
• Standby database overview
• Shipping SQL Server log files to standby databases

SQL Server database Replication

• Microsoft replication architectures
• Creating a replication topology
• Configuring SQL Server replication

SQL Server Job scheduling

• Common database scheduled jobs
• Configuring SQLMail
• Setting SQL Server and server alert thresholds
• Creating customized error conditions
• Scheduling multi-step job streams

 

VIII. Ratio Analysis - Techniques for quickly getting a bird's eye view of performance
 

 

Key performance ratios can quickly help a SQL Server DBA understand how well their overall system is performing. This chapter provides details on what ratios a DBA needs to monitor, along with recommendations for what to do when key performance metrics are out of line.
 

IX. PLM Steps 3 and 4 - Using History to Prepare for the Future

 

Many SQL Server DBAs work in a reactive mode, which means they do little to plan for the future needs of the databases they oversee. This chapter discusses the importance of historical trend analysis, what key metrics should be tracked, and how to use historical data to forecast future needs.

 

Performance Tuning for the SQL Server DBA

• SQL Server monitoring for performance
• SQL Server startup parameter tuning
• T-SQL tuning
• SQL tuning for SQL Server
• Diagnosing storage and system problems
• Diagnosing session and O/S issues

Microsoft Office run commands

If the Microsoft Office is installed you can use following run commands to run its components:

winword – Microsoft Word
excel – Microsoft Excel
powerpnt – Microsoft PowerPoint
msaccess – Microsoft Access
outlook – Microsoft Outlook
ois – Microsoft Picture Manager

Microsoft natural keyboard shortcuts

Windows Logo+BREAK (Display the System Properties dialog box)
Windows Logo+D (Display the desktop)
Windows Logo+M (Minimize all of the windows)
Windows Logo+SHIFT+M (Restore the minimized windows)
Windows Logo+E (Open My Computer)
Windows Logo+F (Search for a file or a folder)
CTRL+Windows Logo+F (Search for computers)
Windows Logo+F1 (Display Windows Help)
Windows Logo+ L (Lock the keyboard)
Windows Logo+R (Open the Run dialog box)
Windows Logo+U (Open Utility Manager)

Shortcuts(Run) For Windows


access.cpl - Accessibility Options
hdwwiz.cpl - Add New Hardware Wizard
appwiz.cpl - dd/Remove Programs
timedate.cpl - Date and Time Properties
desk.cpl - Display Properties
inetcpl.cpl - Internet Properties
joy.cpl - Joystick Properties
main.cpl keboard - Keyboard Properties
main.cpl - Mouse Properties
ncpa.cpl - Network Connections
ncpl.cpl - Network Properties
telephon.cpl - Phone and Modem options
powercfg.cpl - Power Management
intl.cpl - Regional settings
mmsys.cpl sounds - Sound Properties
mmsys.cpl - Sounds and Audio Device Properties
sysdm.cpl - System Properties
nusrmgr.cpl - User settings
firewall.cpl - Firewall Settings (sp2)
wscui.cpl - Security Center (sp2)

Run commands For Windows


Calc - Calculator
Cfgwiz32 - ISDN Configuration Wizard
Charmap - Character Map
Chkdisk - Repair damaged files
Cleanmgr - Cleans up hard drives
Clipbrd - Windows Clipboard viewer
Cmd - Opens a new Command Window (cmd.exe)
Control - Displays Control Panel
Dcomcnfg - DCOM user security
Debug - Assembly language programming tool
Defrag - Defragmentation tool
Drwatson - Records programs crash & snapshots
Dxdiag - DirectX Diagnostic Utility
Explorer - Windows Explorer
Fontview - Graphical font viewer
Ftp - ftp.exe program
Hostname - Returns Computer's name
Ipconfig - Displays IP configuration for all network adapters
Jview - Microsoft Command-line Loader for Java classes
MMC - Microsoft Management Console
Msconfig - Configuration to edit startup files
Msinfo32 - Microsoft System Information Utility
Nbtstat - Displays stats and current connections using NetBios over TCP/IP
Netstat - Displays all active network connections
Nslookup - Returns your local DNS server
Odbcad32 - ODBC Data Source Administrator
Ping - Sends data to a specified host/IP
Regedit - registry Editor
Regsvr32 - register/de-register DLL/OCX/ActiveX
Regwiz - Reistration wizard
Sfc /scannow - Sytem File Checker
Sndrec32 - Sound Recorder
Sndvol32 - Volume control for soundcard
Sysedit - Edit system startup files (config.sys, autoexec.bat, win.ini, etc.)
Systeminfo - display various system information in text console
Taskmgr - Task manager
Telnet - Telnet program
Taskkill - kill processes using command line interface
Tskill - reduced version of Taskkill from Windows XP Home
Tracert - Traces and displays all paths required to reach an internet host
Winchat - simple chat program for Windows networks
Winipcfg - Displays IP configuration

Selects the no.of pages per page

create table pages(id int,name varchar(50))
create proc insert_increment
as
begin
 declare @x int
 set @x=1000
 while(@x<=100000)
 begin
  insert into pages select @x,'anil_'+cast(@x as varchar(100))
  set @x=@x+1
 end
end
execute insert_increment
select * from pages
create proc page_size_search(@PAGE_SIZE INT,@page_no int)
as
begin
--DECLARE @PAGE_SIZE INT,@page_no int
--SET @PAGE_SIZE=20
--set @page_no=7
 SELECT * FROM pages where id between (@page_no-2)*(@page_size) and (@page_size)*(@page_no)
end
exec page_size_search 20,2

GET THE GIVEN CHARACTERS AND NO OF CHARCATERS IN A GIVEN STRING

CREATE PROC GET_CHARVAL(@STR VARCHAR(100),@GSTR VARCHAR(1))
AS
BEGIN
    DECLARE @TEMP TABLE (RID INT IDENTITY, TCHAR VARCHAR(5))
    DECLARE @CNT INT, @I INT = 1
    SELECT @CNT = LEN(@STR)
    WHILE(@I <= @CNT)
    BEGIN
        INSERT INTO @TEMP
        SELECT SUBSTRING(@STR,@I,1)
        SET @I = @I + 1
    END
    SELECT TCHAR, COUNT(1) NOC FROM @TEMP
    WHERE TCHAR = @GSTR
    GROUP BY TCHAR
END

EXEC GET_CHARVAL 'SREESATYASAIBABA', 'E'