Home » 2012 » September

Monthly Archives: September 2012

Check List for SQL Server Connectivity Issue !


SQL Server connectivity is the most common issue & below mention errors is also common at time of connectivity issue.

Error :

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

Resolution : Although, There are many reasons of SQL server connectivity issue. But I have setup an small checklist that helps me most of the time to find out the real cause of connectivity issue.

1. Check SQL services are running

2. Check SQL Browser service is running

3. Check remote connections are enabled

4. Network connectivity between database & application servers by TRACERT command

5. Check TCP/IP protocol enabled at SQL server

6. Check talent connectivity – telnet <IP address> <port no on SQL server running>

7. Check UDP port 1434 is open or not on SQL Server

8. Check firewall is running or not Check

9. If firewall running, SQL Server & UDP port must be added in exception in firewall

SP_HELPTEXT showing old Store Procedure name after rename, is it a Bug?


Question : Today morning, I received a call from one of my old friend, with an query for SQL Server. He is sharing a problem with Sp_Helptext. He is considering it as a Bug.

Try to rename any procedure in your database by right click. Then modify it. SSMS will show changed name. But when you write sp_helptext <changedProcName> then it will show you the previous name.

Answer : I do not think it’s a bug. Its normal. This happens because when the store procedure is renamed, the sys.syscomments system table is not getting updated.

SQL statement shown by sp_helptext is from sys.syscomments. This system view return the script run by you at the time of object creation.

1. When you change SP name from GUI, you have not edit the script, you just rename the tittle. Due to which there is no change in sys.syscomments & you got old name when you run sp_helptext <changedProcName>

2. When you perform it from T-SQL script, you actually re-run the procedure code & sys.syscomments got updated (by Alter command or by drop & recreate the procedure).

select * from sys.syscomments where object_name(id) = ‘<changedProcName>’

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. (http://msdn.microsoft.com/en-us/library/ms188351(SQL.90).aspx)

SQL Server || Change Batch separator


SQL Server utilities interpret GO as a keyword to separate the batch. GO is not a Transact-SQL statement. it is a keyword or command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code.

Customizing Changing the Batch separator in SSMS

Steps to customize the batch separator in SSMS:

  1. Launch SSMS
  2. Go to Tools –> Options
  3. Click on the “Query Execution” node
  4. Notice that we have an option to change the Batch Separator
  5. Change the batch separator
  6. Click “OK”

New Batch Seprator working Old Batch “GO” goes unknown word

Customizing Changing the Batch separator in SQLCMD

You need to use SQLCMD option –c to set new batch separator.

Upgrade SQL Server 2005 Full-Text Catalog to SQL Server 2012……By Detach & Attach…….Part 2 of 2


Upgrading fulltext data from a SQL Server 2005 database to SQL Server 2012 by restoring a database backup.

We have copied the database from SQL Server 2005 having MDF, LDF & full-text catalog that is required for upgrading & importing a SQL Server 2005 full-text catalog into SQL Server 2012.

Database files from SQL Server 2005

When the database is restored on SQL Server 2012, a new database file will be created for the full-text catalog. The default name of this file is ftrow_catalog-name.ndf. For example, if you catalog-name is userdetails, the default name of the SQL Server 2012 database file would be ftrow_Userdetails.ndf. But if the default name is already being used in the target directory, the new database file would be named ftrow_Userdetails-name{GUID}.ndf, where GUID is the Globally Unique Identifier of the new file.

After the catalogs have been upgraded, the sys.database_files and sys.master_files are updated to remove the catalog entries and the path column in sys.fulltext_catalogs is set to NULL.

Case 1 : File details after attaching all MDF, LDF & full-text catalog on SQL 2012

Case 2: File details after attaching all MDF, LDF on SQL 2012, in case your catalog folder got lost & misplaced.

If SQL Server cannot find a full-text catalog file or if the full-text file was moved during the attach operation without specifying a new location, the behavior depends on the selected full-text upgrade option. If the full-text upgrade option is Import or Rebuild, the attached full-text catalog is rebuilt. If the full-text upgrade option is Reset, the attached full-text catalog is reset.

Upgrade SQL Server 2005 Full-Text Catalog to SQL Server 2012……By Backup & Restore…….Part 1 of 2


Upgrading fulltext data from a SQL Server 2005 database to SQL Server 2012 by restoring a database backup.

The full database backup will include the full-text catalog is required for upgrading & importing a SQL Server 2005 full-text catalog.

Backup from SQL Server 2005

When the database is restored on SQL Server 2012, a new database file will be created for the full-text catalog. The default name of this file is ftrow_catalog-name.ndf. For example, if you catalog-name is userdetails, the default name of the SQL Server 2012 database file would be ftrow_Userdetails.ndf. But if the default name is already being used in the target directory, the new database file would be named ftrow_Userdetails-name{GUID}.ndf, where GUID is the Globally Unique Identifier of the new file.

After the catalogs have been upgraded, the sys.database_files and sys.master_files are updated to remove the catalog entries and the path column in sys.fulltext_catalogs is set to NULL.

File details after restore on SQL 2012

How to log SQL Server Custom Error in Windows Event Log ?


SQL server gives an option to log custom errors in windows event log. We have 2 options of achieving this.

Option 1 : Using xp_logevent

BEGIN TRY

SELECT 10/0

END TRY

BEGIN CATCH

PRINT ERROR_Message()

DECLARE @msg VARCHAR(100)

SELECT @msg = ERROR_Message()

–xp_logevent error_number , errormessage, severity (INFORMATIONAL, WARNING, or ERROR)

EXEC xp_logevent 60000, @msg, error

END CATCH

Option 2 : Using RAISERROR…..WITH LOG

BEGIN TRY

SELECT 10/0

END TRY

BEGIN CATCH

PRINT ERROR_Message()

DECLARE @msg VARCHAR(100)

SELECT @msg = ERROR_Message()

Surface Area Configuration tool available in SQL Server 2008 & onwards


Surface area configuration is a security measure that involves stopping or disabling unused components. Surface area configuration helps to improve security by providing fewer avenues for potential attacks on a system.

Surface Area Configuration too is introduced in SQL Server 2005 & Direct access of Surface Area Configuration tool has been removed from SQL server 2008 onwards.

To access Surface Area Configuration tool from SQL Server 2008 onwards :-

1) Right Click on SQL server instance in SSMS & Go to Facets

2) Select “Surface Area Configuration” facet & have access of hidden Surface Area Configuration tool

Follow

Get every new post delivered to your Inbox.

Join 228 other followers