Monday

Fix SQL error: 26 - A network-related error occurred in SQL Server

Introduction

This article explains how to configure an instance of the SQL Server Database Engine to listen on a particular fixed port by utilizing the SQL Server Configuration Manager. The default example of the SQL Server Database Engine listens on TCP port 1433. Named instances of the Database Engine and SQL Server Compact are arranged for dynamic ports. This implies they select an accessible port when the SQL Server administration is begun. When you are interfacing with a named instance through a firewall, setup the Database Engine to listen on a particular port, so that the fitting port can be opened in the firewall. Here are given 5 different approaches to get resolution of 26 - A network-related error occurred in SQL Server. In my earlier, I have already explained how to find and delete duplicate values in SQL server.

Error Description:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Approach 1: We can run this query to view your TCP endpoints 


SELECT name, protocol_desc, type_desc, state_desc, is_admin_endpoint, port, is_dynamic_port, ip_address FROM sys.tcp_endpoints

tcp endpoints

Approach 2: Here you can check which SQL port is running on your machine.


USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

Approach 3: Run below command to check applications listening on ports, try this command on command line:


netstat -ap TCP

Approach 4:


i) Open Immediate Window (Windows + R) on your machine.
ii) Next type %windir%\System32\cliconfg.exe and click on Enter button.
iii) Now a new window will open, left side will show disabled port and right side will show enabled port.
disable tcp ip protocols

iv) Now Select TCP/IP disabled protocols and click on Enable button to add in enabled protocols list.

tcp ip properties

v) Now you will enabled TCP/IP protocols and click on properties, then finally you will see SQL Server port i.e. 1433



sql port 1433

Approach 5: 

  • Start from open your SQL Server Configuration Manager
  • Next click  on "SQL Server Network Configuration" and click on "Protocols for Name".
  • Now right click on TCP/IP, click on properties and make sure it is enabled.
  • Presently Select "IP Addresses" Tab - and-Go to the last passage "IP All".
  • Now enter SQL server port TCP/IP 1433
  • Remove 0 (keep totally blank) from TCP Dynamic Ports property
  • Now restart your SQL Server (Right click on SQL server Management Studio and click restart button).

Conclusion

Here are given 5 different approaches to resolve issue 26 - A network-related error occurred in SQL Server. Hopefully, these approaches would work to all who are looking to resolve issue 26 - A network-related error occurred in SQL Server.

Relevant Reading

SQL Server Schema Corruption Error 211: A Technical Solution

Introduction

The SQL Schema is a logical collection of SQL Objects including the tables, associated with columns, its relevant entries, or other elements. While creating the schema, a user will be able to access the database in more effective manner. However, a situation occurs in SQL server that different errors occurs while accessing it. One such error is SQL server error 211, which occurs when the server table is getting updated. In the following section, we will cover reasons for corruption in the server Schema and then possible solutions to overcome this error.

Problem Statement

Suppose a user tries to update some database tables on his server and meanwhile a schema corruption error message occurs on the system. This error can exist in any version of SQL Server such as Server 2005, 2008 etc.
“Msg: 211, Level 23, State 51, Line 1 Possible schema corruption.
Run DBCC CHECKCATALOG
Msg 0, Level 20, State 0, Line 0”

Reasons Behind SQL Server Error 211

Generally, schema corruption in database evolve very rarely in the SQL Server. When this error took place, a user have to check out the main cause of the error i.e., either via SQL error Log files or some other.
There are multiple reasons where schema gets corrupted and some of them are mentioned below:
  • Due to improper computer activities by server users
  • Sudden software failure leads to Schema corruption
  • Hardware failure is the major issue behind corruption

How to Fix Corruption Error 211 in SQL Server

There are several methods to resolve this server error. A user can choose any one approach to remove or delete the schema corruption which is present within the server. Following are the all possible solutions to fix the error:
  • One of the compatible solution for resolving this error is to restore the backup files of the database. In addition, go through following scenarios that will help you in restoring backup files:
    1. If a user has backup of transaction log, then take a backup of tail-log and restore the latest backup completely with entire transaction logs that are ending with tail-log backup. In such case, the result will become in 0 data loss.
    2. If a user does not have a backup of the transaction log then, develop a new database and import more data as possible or if a user is able to determine culprit table, then drop that table & recreate it.
  • The another approach is to repair entire MDF database file and then, you will have to deploy the database again.
  • The last manual trick is to execute DBCC CHECKCATALOG command on the server screen.
When you will run DBCC command the error will be displayed. Sometimes, much exceptions might occur while executing the Transact-SQL statement through SQL users with dedicated Schema. Make sure, a user will get the suitable suggestion from SQL server database by running DBCC CHECKCATALOG syntax. With the help of this command, users will get to know the location and reason behind the SQL server error 211. However, DBCC CHECKCATALOG signifies no error, but it is not possible at all time.

Alternate Solution to Resolve SQL Server Database Corruption

The most appropriate solution for fixing SQL error code 211 is third party SQL recovery tool. It is safe and secure way to troubleshoot this error with help of following steps:
Step1: Launch SQL Recovery, Add MDF file and press Open button

Step 2: Analyze all your data of the server file and then click on Export button

Step 3: Now, select an option between two i.e., with the only Schema or with Schema & Data for exporting MDF file and click on save to start migration process

Finally, you will be able to get a healthy MDF file with help of this solution and hence, fix SQL server error 211.

Conclusion

Here we are ending with one of the server error, which create hurdle while accessing the SQL Server database. The best possible solutions are also discussed to overcome this possible Corruption Schema error. Well, it is recommended to first restore the backup file of server, if a user is having it. Else, you can go for other solution for resolving SQL server schema corruption error 211.

Suggested Reading

Product Review of Kernel for SQL Server

SQL Server is the well-developed Microsoft entity which provides flexible and correlational data management services to the organizations. Also referred sometimes as MS SQL and Microsoft SQL Server, The SQL Server is basically a set of relational database management service which extensively supports ANSI SQL, which is a standard server language. The SQL server also contains T-SQL, which is its own SQL implementation. Microsoft offers SQL server in different editions with different features and outcomes.
  • Enterprise Edition: Being the most expensive among SQL Servers, the Enterprise edition is meant for large organizations that deal in bulk volume of data every second.
  • Standard Edition: Perfectly designed to act as support for small and medium scale enterprises. It is prefect for medium level data volume.
  • Workgroup Edition: Perfect for startups and very small scale business groups. It is perfectly used for backend database for small webservers.
  • Express Edition: It is a free edition that is meant for distribution as it has limited number of features to be used. This edition is often referred to replace the Access database.
The availability of different editions of SQL Server is not so difficult, but the most common observation in every edition of SQL Server is issues with MDF and LDF files. The entire database of SQL server is saved in Master copy called MDF files. The MDF database files are the primary database file which entails schema and data. The other file format is LDF that maintains Log files. Due to some reasons the MDF files getcorrupt. To fix these MDF files it is important to take required action through some reliable source like Kernel for SQL Server.

Necessity of External Assistance

Kernel for SQL Server is an external agency designed to fix the MDF files that got corrupt due to technical complications. This SQL Server tool is perfect to fix the most notorious SQL-related issues. It includes:
  • MSDTC configuration issues.
  • Non-existent Disaster Recovery Plan.
  • Weak SQL login password.
  • TEMPDB pagelatch contention.
  • System Memory issues.
  • Usage of NOLOCK.
  • Non-scheduled Database Integrity checks.
From being a perfect tool to fix the SQL related issues to being a tool for maintaining the SQL Server, Kernel for SQL Server effectively manages every task assigned to it in a very simple manner. The entire credit goes to the inbuilt algorithm that drives the entire process.

An Intro to Kernel for SQL Server

Designed to fix the MDF file corruption with Auto SQL Version Detection Feature, Kernel for SQL Server haunts the SQL – related issues every time. The QFSCI algorithm takes care of the entire process with a great care for MDF file components. To experience the real functioning of Kernel for SQL Server the evaluation version can be downloaded that works exactly like the licensed version with a limitation of not restoring the recovered MDF files. The performance of this celebrity tool also depends upon the system configuration and supported versions. To eliminate any chance of incompatibility, the tool supports all versions of MS SQL Server, Windows OS and Windows Server. Moreover, prior downloading the evaluation version of the tool, it must be known to the user that the tool perfectly works in the system that comprises Pentium class processor with minimum 64 MB RAM and enough disk space to save results.

What is so special about Kernel for SQL Server?

Ardent features, which were once the dream of SQL Server users are now the reality and that too in the platform which can be easily managed. With user-friendly graphic interface no special training is required nor is any technical background needed. The only thing expected from the user is knowledge of the location of the MDB files and a rough estimate about the SQL repair tool’s working.
The specialty of Kernel for SQL Server lies in its features. These features play significant role in recovering the damaged MDB files:
  • Reveals the SQL Server version automatically.
  • Effective recovery of MDF files.
  • Essentially restores MDF file components like Tables, Index, keys and much more.
  • Dual Saving modes to save the MDF files differently.
  • Provides ‘Preview’ option to view the recovered items to ensure correctness.
  • Maintains data integrity throughout the process.
  • Independent of MDF file size limit.
  • Recovery of multiple MDF files in a single repair cycle.
To explore more about the tool it is suggested to download the trial version of the tool that works alike licensed version with a limitation of not saving the recovered MDF files.

Expanding Every Valuable Feature

  1. Witty Selection of MDF Files: Kernel for SQL Server makes quick selection of MDF files required to be recovered. To make this happen, the user-friendly GUI of the tool is available which can be independently tackled by any naïve user. To initiate it, user has to launch the SQL Recovery software followed by selecting the concerned MDF files. Once it is done the tool is ready to go.

Select Database to Recover

  1. Automatic Selection of SQL Server Version: To know the version of affected MDB files normally user applies conventional techniques but owing to the new technology that is used by external tool it has become very easy to find it out. To make this happen user has to select the concerned EDB file, upload it and tick the box that reads ‘Auto Detect Database Version’. On selection of this feature, your workload remains half and rest is performed by the third-party tool.

    Select SQL Database

  1. Stunning Recovery: The MDB files subjected to recovery are perfectly loaded by the user. With this action the manual support for the tool ends here. Now, following the remaining steps, everything is automated and just needs manual clicks to keep the process on going. On recovery of concerned MDF files, the user’s screen appears as like
Stunning Recovery

  1. Schematic Representation of Recovered Items: SQL Recovery Tool delivers B-tree structure of the recovered MDF file components. The schematic representation of the recovered items clearly makes the way for the user to select the specific file to ensure the correctness in the recovery process. User has to just select the object from the tree prior saving. When selection is made, the requisite content will be displayed on the right pane of the Window.
Schematic Representation of Recovered

  1. Save what is recovered: Post deciding that recovery is perfect, now is the time to get introduced to the stunning saving option. The saving mode gives a choice of saving the recovered files directly to SQL Server or simply to the Batch File.

Saving Mode

  1. Knowing the Performance Capacity: The Performance capacity of Kernel for SQL Server can be witnessed without spending a money or clicking the dialog box for online annual subscription. User has to simply download the evaluation version of the SQL Server recovery tool that has some limited potential to recover the damaged MDF files up to some extent. However, user cannot save any recovered files with it. To do so, the licensed version has to be purchased that facilitates limitless recovery irrespective of the SQL Server version.
  2. Speedy Delivery of License Key: Kernel for SQL Server is known for its outstanding performance. If user is satisfied with the tool’s performance in the evaluation version and is willing to purchase the licensed version of the same, then it can be done by using any of the different payment options like PayPal, Credit/Debit, E-Cheque and Pay Order. Once payment is received, the key of the licensed version is sent through the email to the user. In totality, it merely takes few minutes to procure the license key of the tool.
  3. Round-the-Clock Technical Support: The software is ardent in maintaining the client relationship. It is made possible by allowing technical support to the client whenever needed. Every ticket raised by the user is important for us and it is dealt with a great concern. After providing the required solution, it is conformed if user is facing any issues. The technical follow-up is kept to trace the history of the query that made the client concerned. To gain the online help related to SQL database recovery tool, user can simply log on to https://www.kerneldatarecovery.com/sql-recovery.html

Kernel for SQL Server Provides a Common Stage

Kernel for SQL Server is a center-stage to different versions of MS SQL and other required applications. The single stop solution for affected SQL files is responsible for fixing the SQL database irrespective of the version of its origin. To materialize the claimed capacity, the tool supports all versions of the required programs. This also eliminates all chances of incompatibility issues.
Supported Versions
  • MS SQL Server 2014/2012/2008 R2/2008/2005
  • Windows 10/8.1/8/7/Vista/XP/2000/98/NT/95
  • Windows Server 2012/2008 R2/2008/2003 R2/2003
Where Kernel for SQL Server fits the best?
The SQL Server Recovery tool graciously performs well in the system that comprises:
  • Pentium Processor
  • Minimum 64 MB RAM
  • 50 MB Space for Software Installation, and
  • Some space to save results.
The tool’s performance also depends upon the system configuration; therefore, prior downloading any version of the tool, it is recommended to check the system requirements.

Wednesday

How to find and delete duplicate values in sql server

Introduction

Today, I am writing about how to Find and delete duplicate values in SQL server and delete in our SQL Server database, sometimes data is inconsistent due to not proper relational integrity it results to duplicate rows in corresponding tables. Duplicate records of any database will lead to unstable application and it will not generate information according to end user expectation. I have clarified extremely known SQL Server error SQL Server Error 25 And 87.
SQL duplicate records