Function to Split a Delimited String and Return a Field

Use below UDF to split a delmited string, and return the index value.


CREATE FUNCTION dbo.GetValueOfSplitStringByIndex (@Sep Char(1), @StrTemp VARCHAR(1540), @Index INT)
RETURNS VARCHAR(1540)
AS
-- Takes a @Sep separated string, turns it into a list internally and returns the value from that
-- list found at the @Index location.  Null is return if that @Index does not exist.
-- Sample usage (testing):
-- select dbo.GetValueOfSplitStringByIndex('-','200-2505-0000-01-149-101-10',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',2)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',3)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',4)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l,',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc',0)
BEGIN
DECLARE @Pos INT
DECLARE @Piece VARCHAR(500)
DECLARE @Curr_Index INT
SET @Curr_Index = 0
-- Need to tack a delimiter to the end of the input string if one doesn't exist
if right(Rtrim(@Sep),1) <> @Sep
SET @StrTemp = Rtrim(@Sep)  + @Sep


SET @Pos =  PatIndex('%' + @Sep + '%' , @Sep)
WHILE @Pos <> 0
BEGIN
SET @Piece = Left(@Sep, @Pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
if @Curr_Index=@index
return cast(@Piece as varchar(1540))


SET @StrTemp = stuff(@Sep, 1, @Pos, '')
SET @Pos =  patindex('%' + @Sep + '%' , @Sep)
SET @Curr_Index = @Curr_Index + 1
END
RETURN Null
END


Example:
SELECT dbo.GetValueOfSplitStringByIndex(',','ABC,GOT,klmnop,l0',2)


Result:
'klmnop'

Get SQL Server database backup version

Use below query to get the version of SQL Server database backup:


Restore Headeronly from Disk='C:\Temp\Data.bak'


Now you can get the version information by concatenating three columns in the query result. Which are:


SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild

Find the size of Index in SQL Server

sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.What if you need to capture size of individual index on a table.Thats where the DMF sys.dm_db_index_physical_stats comes handy.

This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They  are avg_record_size_in_bytes and record_count.
If these columns are multiplied the resultant is the size of  that index.

The query given below returns the name of Database, ObjectId, Objectname, IndexId, IndexDescription, Size of Index in MB, Last Updated Statistics Date and Avg Fragmentation.

SELECT DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, CONVERT(DECIMAL(16,1), (SUM(avg_record_size_in_bytes * record_count)/ (1024.0 *1024))) AS  [Size of Index(MB)], last_updated AS [Statistic last Updated], Avg_Fragmentation_In_Percent FROM (SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName', OBJECT_ID AS ObjectId, Object_Name(Object_id) AS ObjectName, Index_ID AS IndexId, Index_Type_Desc AS Index_Description, avg_record_size_in_bytes , record_count, STATS_DATE(object_id,index_id) AS 'last_updated', Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) AS 'Avg_Fragmentation_In_Percent' FROM sys.dm_db_index_physical_stats(db_id('PM_Db'), NULL, NULL, NULL, 'detailed') Where Object_id IS NOT NULL AND Avg_Fragmentation_In_Percent <> 0) T GROUP BY DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, last_updated, Avg_Fragmentation_In_Percent

Detect SQL Server Version, Edition, Product Level

SELECT  
 CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER) AS MajorVersion  
,SERVERPROPERTY('ProductVersion') AS ProductVersion  
,CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1)  
 WHEN 8 THEN 'SQL 2000'  
 WHEN 9 THEN 'SQL 2005'  
 WHEN 10 THEN 'SQL 2008'  
END AS ProductVersion  
,SERVERPROPERTY('Edition') AS Edition  
,SERVERPROPERTY('ProductLevel') AS ProductLevel  

Causes for network related errors in SQL Server

Following are the main reasons for network related errors in SQL Server.


Firewall does not allow connections to SQL Server Service.
Solution: Add exception to Firewall to allow connections to SQL Server Service.


Firewall does not allow connections to SQL Browser Service.
Solution:  Add exception to Firewall to allow connections to SQL Browser Service.


Server/Instance name is wrong.
Solution: Check for the correct name of the Server/Instance and try to connect again.


TCP/IP is disabled on the Server Machine.
Solution: Enable TCP/IP
Steps to enable TCP IP On SQL Server
Open SQL Server Configuration Manager
- All programs ->(Microsoft SQL Server 2008 R2 (or) Microsoft SQL Server 2008 (or) Microsoft SQL Server 2005 ) -> SQL Server Configuration Manager
- Expand SQL Server Network configuration
- Click on protocols for MSSQLSERVER (If its an instance click on the respective instance name)
- RightClick on TCP/IP and click on enable


SQL Server Browser Service is not running on the Server Machine.
Solution: Start the SQL Server Browser service on the Server Machine.
Steps to start the SQL Server Browser Service:
- Run -> Services.msc
- RightClick on SQL Server Browser
- Click on Start


Remote connections are disabled on SQL Server
Solution: Allow remote connections on sql server
Steps:
- Connect to SQL Server on SSMS
- RightClick on Server
- Click On Properties
- In the Server Properties Popup Select Connections
- Check the Allow remote connections to Server and click on OK


SQL Server Service is not running on the Server Machine.
Solution: Start the SQL Server service on the Server Machine.
Steps to start the SQL Server Service:
- Run -> Services.msc
- RightClick on SQL Server (MSSQLSERVER)
- Click on Start


SQL Server instance Service is not running on the Server Machine.
Solution: Start the SQL Server instance service on the Server Machine.
Steps to start the SQL Server instance Service:
- Run -> Services.msc
- RightClick on SQL Server (instancename)
- Click on Start


Invalid Credentials
Solution: Fetch the latest creadentials to login into the server or ask the administrator to give your credentials necessary oermissions on the server.


Time out errors due to network latency
Solution:  Increase Remote Query Time out
Steps:
- Connect to SQL Server on SSMS
- RightClick on Server
- Click On Properties
- In the Server Properties Popup Select Connections
- Increase the Remote query timeout value and click on OK


Ref: http://sqlserverlearner.com/2012/causes-for-network-related-errors-in-sql-server