How to Enable Remote Access in sql server 2008

 

To resolve following error , follow the steps specified below:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

OR

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 28 - server does not support requested protocol)

Check the following things if remote access is failed :

1)Allow Remote Connections to this server

This must be enabled to allow remote connections to the server. To enable this , Go to start – > All Programs – > Microssft Sql Server 2008 –> Sql Server Management Studio.

Now connect to the instance to which you need to enable remote access. Now right click on the instance and select “Properties”. Select “Connections” from left side and see if “Allow Remote Connections to this server” is checked or not. If unchecked , check it to allow remote access.

2)Enable TCP/IP protocol and Sql Server Browser

Open Sql Server Configuration Manager.

Go to start – > All Programs – > Microssft Sql Server 2008 –> Configuration Tools –> Sql server configuration Manager

TCP/IP protocol

Expand “Sql Server Network Configuration” in the left pane , Double click on “Protocols for MSSQLSERVER” ( Protocols for YourServerInstanceName) . Enable TCP/IP .

Some times TCP/IP under Sql Native Client 10.0 Configuration –>Client Protocols –>TCP/IP  might also be disabled . Check this and enable it.

It gives the following message ,

 

so restart the sql server service . under Sql Server Configuration Manager –> Sql Server Services –> Sql Server (Your Instance Name)

Sql Server Browser

under Sql Server Configuration Manager –> Sql Server Services –>Sql Server Browser

Right click and enable it.

4)Firewall

In Start –> Run , give the command “controlpanel.cpl” and click enter.

or

Start – > Control Panel –> Windows Firewall - >Allow a program or feature through firewall

Browse “Sqlserver.exe” and add.

FULLTEXT INDEX / FULLTEXT SEARCH

 

Use FullText Search to run full-text queries against character-based data in SQL Server tables . To run full-text queries on a table, one needs to create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. Each full-text index indexes one or more columns from the table, and each column can use a specific language.

For more information on FullText Index , see the following link

http://msdn.microsoft.com/en-IN/library/ms142571.aspx

How to Check if FullText Index is enabled in SQL Server :

Execute the following query to verify if “FullText Indexing” is installed or not in the server.

select SERVERPROPERTY('IsFullTextInstalled')

If the query returns 1 , then FullText Indexing is installed and one can create FullText Index on the required table. If it returns 0 , then its not installed , and one needs administrative privileges to install it.

CREATE FULLTEXT INDEX ON A TABLE :

1)Before creating FullText Index on a table , one needs to create

- a Unique Index on that table and

- Catalog for FullText Index.

2)Syntax for creating Full Text Index

CREATE FULLTEXT INDEX ON table_name [ ( { column_name   TYPE COLUMN type_column_name ]                       [ LANGUAGE language_term ]                      [ STATISTICAL_SEMANTICS ] } [ ,...n]       ) ]    KEY INDEX index_name   [ ON <catalog_filegroup_option> ] [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ] [;]

Sample code for creating and dropping Full Text Index on a table :

/*CREATE TABLE*/ CREATE TABLE CandidateData ( CandidateId INT NOT NULL, Resume VARCHAR(MAX) ) ON [PRIMARY] GO /*CREATE UNIQUE INDEX ON THE TABLE*/ CREATE UNIQUE CLUSTERED INDEX PK_CandidateData ON CandidateData(CandidateId); /*CREATE FULLTEXT CATALOG*/ CREATE FULLTEXT CATALOG ft AS DEFAULT; /*CREATE FULLTEXT INDEX*/ CREATE FULLTEXT INDEX ON CandidateData(Resume)    KEY INDEX PK_CandidateData    ON ft WITH CHANGE_TRACKING AUTO; /*START FULLTEXT SEARCH*/ SELECT * FROM CandidateData WHERE CONTAINS(Resume, 'SQL SERVER') /*DROP FULL TEXT INDEX*/ DROP FULLTEXT INDEX ON CandidateData

Note : Full-text queries use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE)

Example using RequiredFieldValidator ,CompareValidator , RegularExpressionValidator in Registration From

 

Aspx Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RegistrationForm_withvalidations.aspx.cs" Inherits="Forum_Topics_RegistrationForm_withvalidations" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title> </head> <body>     <form id="form1" runat="server">     <div>     Enter Name:<asp:TextBox id="txtName" runat="server"></asp:TextBox>     <br />     <asp:RequiredFieldValidator ID="RFV1" runat="server" ControlToValidate="txtName" ErrorMessage="Name is must"></asp:RequiredFieldValidator>     <br />     Enter Password:     <asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>     <br />     <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtPwd" ErrorMessage="Password is must"></asp:RequiredFieldValidator>     <br />     Confirm Password:     <asp:TextBox ID="txtConfirmPwd" runat="server"></asp:TextBox>     <br />     <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtConfirmPwd" ErrorMessage="Confirm Password is must"></asp:RequiredFieldValidator>     <br />     <asp:CompareValidator ID="CV1" runat="server" ControlToValidate ="txtConfirmPwd" ControlToCompare ="txtPwd" ErrorMessage="Password and Confirm Password does not match">     </asp:CompareValidator>     <br />      Enter Email - ID :<asp:TextBox ID="txtEmail" runat="server" MaxLength="50" Width="140px"></asp:TextBox>      <br />      <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtEmail" ErrorMessage="Email is must"></asp:RequiredFieldValidator>      <br />        <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"            ControlToValidate="txtEmail" ErrorMessage="*Invalid Email."            ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>     <br />     <asp:Button ID="btnSubmit" runat="server" Text="Submit" />     </div>     </form> </body> </html>

Output :

Named Pipes Provider: Could not open a connection to SQL Server

 

This failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Follow the steps in the link to resolve this error.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Create Linked Server in Sql Server 2008 / Insert from one Db to another in Sql Server 2008

Linked Servers can be used to connect databases on remote server .

Suppose in the production environment if you have two servers on the network and we need to trigger insert in a table of one database to a table in different database , we can use Linked Servers.

We can create Linked Server using the Stored Procedure “sp_addlinkedserver” .To execute this , we must have the admin privileges. One has to login into sql server management studio with “Sa” user.

exec sp_addlinkedserver     @server = 'MyLinkedServer', -- LINKED SERVER NAME (ANYTHING)     @srvproduct = '' ,     @provider = 'SQLNCLI', -- SQL SERVER PROVIDER .. SO SQLNCLI     @datasrc ='SERVER=11.11.1.1', -- REMOTE DATABASE SEREVR IP ADDRESS     @location = '' ,     @provstr = '',     @catalog = 'Test' -- DATABASE ON REMOTE SERVER                   OR exec sp_addlinkedserver 'MyLinkedServer','','SQLNCLI','SERVER=11.11.1.1','','','Test'

We can also create Linked Server from Sql Server Management Studio by following these steps :

Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.

In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect with Sa login.

In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

                                                                                                                                          

In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Server you want to link to.

Under general , Choose the following things :

In the left pane of the New Linked Server dialog,  choose Security and provide remote user name and password .

You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.

Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

In the left pane of the New Linked Server dialog, choose Server Options.   Set the Rpc and Rpc Out parameters to True and then click OK .

To verify Linked Server is created or not :

using the Stored Procedure sp_linkedservers

                                                   exec sp_linkedservers

                                                                 OR

1) In the object explorer , expand Server Objects , expand Linked Servers . Now verify for the linked server created by you.

2)To test the connection , right click on the linked server created and select Test Connection .  If connection is successful , then you can perform all DML operations on the tables in the remote database.

Querying Linked Server :

SELECT * FROM [LinkedServerName].[RemoteDatabaseName].[DBOwner].TableName

Similarly use Insert , Delete and Update queries. We can call functions , execute SPs etc.

Delete / Drop Linked Server :

1) In the object explorer , expand Server Objects , expand Linked Servers . Right Click on the Linked Server which you wish to delete and click Delete.

2)Drop Linked Server using Stored Procedure “sp_dropserver”

                          EXEC sp_dropserver MyLinkedServer

Difference between Asp.net MVC and Web Forms