Tuesday, January 24, 2017

Troubleshooting SQL Server Connections Issue

I came across a challenging issue to solve SQL Server Connection Issue, I want to share the quick steps to resolve the issue in structured way.

Note: This blog is not how to troubleshoot, its all about what to troubleshoot.

Target Audience: SQL Server DBA, Windows System Administrator

Error Message:  Cannot connect to \.
ADDITIONAL INFORMATION: 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: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

Steps to Diagnose:

Server Machine:
At Configuration Level:
1) SQL Server Services should to up and running.
2) Enable the TCP Protocols via Configuration manager for required Instance
3) Make note or Configure port( default 1433) for TCP protocol
4) SQL Browser should be up and running.
5) Enable Firewall to allow connection on TCP Ports and Browser service ports.
At Database Instance Level:
Check following setting for required Instance using SQL Management Studio
1) Enable Mixed mode authentication(both Windows and SQL Authentication)
2) Ensure SA login is enabled
3) Ensure remote connections are allowed to connect 

Client Machine:
1) Ping the server using name or IP using Command prompt
2) Check is server machine file share is accessible
3) Ping the server with telnet command "telnet \  
4) Try with both windows and SQL authentication.

With all above list of action, i could able to resolve the connection issue in a timely fashion. 

Hope this helps!!


Thursday, February 23, 2012

CLR version.

These are some ways to find CLR version installed in any Windows PC.

1) CLRVer.exe
2) Existance of mscoree.dll file in %SystemTools%\system32

Happy programming :)

Undocumented sys function - fn_DBLog()

I come across interesting function sys.fn_dblog() and bit suprised, it clearly gives in depth info of Transaction log.

Here is the TSQL-query.

Select * from sys.fn_dblog(null,null)

Happy Programming.... :)

Tuesday, February 21, 2012

SQL Server Agent status via TSQL

Today, I just came across a requirement to find Status of SQL Server Agent via TSQL.
Here is it...

EXEC xp_servicecontrol querystate , N'SQLServerAGENT'

More info on http://www.mssqltips.com/sqlservertip/2036/monitor-start-and-stop-sql-server-services-using-xpservicecontrol/.

Happy programming.. :)

Creating table from other Table.- MS SQL Server

There is always an easy was to create table by having other table using below statement in MS SQL Server.

Select * into T1 from T2 where 1=2 

But big problem over here is if any column has BLOB data type(ntext, XML, varbinary) by default it stored in PRIMARY filegroup. If any situation we need to create table in different File group will end up with problem.
and I just solved using this Procedure created by myself.

IF OBJECT_ID('dbo.P_Arc_CreateTable') IS NOT NULL

DROP PROC dbo.P_Arc_CreateTable
GO
Create Proc dbo.P_Arc_CreateTable
(@SourceTableName nvarchar(100)
,@TargetTableName nvarchar(100)
,@FileGroup nvarchar(20)=NULL
,@PartitionSchemeFG nvarchar(200)=NULL
)
as
BEGIN
Declare @TableName as nvarchar(100)
DEclare @SQL as nvarchar(max)
Declare @ShemaName as nvarchar(10)
Declare @ErrorMessage as nvarchar(1000)

IF EXISTS (Select 1 from sys.columns where object_id = object_ID(@SourceTableName) and is_ansi_padded = 1)
SET @SQL = ' SET ANSI_PADDING ON ' +char(10)
ELSE
SET @SQL = ' SET ANSI_PADDING OFF ' +char(10)
SET @ShemaName = (Select SCHEMA_NAME(t.SCHEMA_ID) from sys.tables t where name = @SourceTableName)
SET @SQL =  @SQL + ' CREATE TABLE ['+@ShemaName+'].['+@TargetTableName+']('+ char(10)
Select 
@SQL = @sql 
+ '['+C.COLUMN_NAME  + '] [' + C.DATA_TYPE +']'
+ CASE WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ' ('+ rtrim(cast(C.CHARACTER_MAXIMUM_LENGTH as CHAR))+')' ELSE '' END
+ CASE WHEN Cl.user_type_id in (106,108) THEN ' ('+ rtrim(cast(C.NUMERIC_PRECISION as CHAR))+','+RTRIM(CAST( c.NUMERIC_SCALE AS CHAR))+ ')' ELSE '' END
+ case when cl.is_identity = 1 THEN ' IDENTITY(1,1) ' ELSE '' END
+ CASE WHEN len(C.COLUMN_DEFAULT )> 0 THEN ' DEFAULT ' + rtrim(CAST(C.COLUMN_DEFAULT AS CHAR))  ELSE '' END--THEN ' DEFAULT ' + c.COLUMN_DEFAULT ELSE '' END
+ ' ' + CASE WHEN C.COLLATION_NAME IS NOT NULL THEN ' COLLATE '+ C.COLLATION_NAME ELSE '' END 
+ ' '+ CASE WHEN C.IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END
+',' + CHAR(10)
from INFORMATION_SCHEMA.COLUMNS C 
inner join sys.columns cl
on cl.object_id = object_id(c.Table_Name) 
and cl.name = c.COLUMN_NAME 
where Table_Name = @SourceTableName
order by c.ORDINAL_POSITION 


SET @SQL = Substring(@SQL,0,Len(@SQL)-1)
IF (@PartitionSchemeFG IS NOT NULL AND @PartitionSchemeFG <> '')
SET @SQL = @SQL + char(10)+ ') ON '+@PartitionSchemeFG
ELSE
SET @SQL = @SQL + char(10)+ ') ON['+@FileGroup+ ']'
BEGIN TRY
IF OBJECT_ID(@TargetTableName) IS NOT NULL
BEGIN
PRINT 'Table - ' + @TargetTableName + ' already exists.'
RETURN
END
ELSE
BEGIN
EXEC (@SQL)
--print @SQL
PRINT 'Created Table - ' + @TargetTableName + ' successfully.'
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE(); 
PRINT 'Error: In creating table - '+ @TargetTableName+'.' 
RAISERROR(@ErrorMessage,16,1)
END CATCH
--Print @SQL
END
--dbo.P_Arc_CreateTable @SourceTableName = 'PromotionInstance',@TargetTableName = 'PromotionInstance_SWD' ,@FileGroup='FG1'
GO

It supports creating an table in Primary File Group, user defined filegroup  or in Partition Schema.

Happy Programming ..:)

Sunday, December 26, 2010

META tag info in SEO

We often miss to consider META tag in web development. Basically its very useful for Search Engine Optimization (SEO).

Content given in META tag :

Content returned by Search Engine :

Sunday, December 27, 2009

.NET 3.5 Features

I just started on reading .Net 3.5 features and impressed with following great things, and listing for guys who want to start exploring .net 3.5.

1) LINQ - Gives differant ways of writing Data Access code,With new programming style. .Net uses built-in to execute this.

2) JS Intellisense - This was delivered in VS 2008. Same like our Server-Side code (i.e c#), even in JS we have auto-complete of methods and properties available for an JS objects. Explore more on :http://weblogs.asp.net/scottgu/archive/2007/06/21/vs-2008-javascript-intellisense.aspx

3) JS Debugger - Even this delivered in VS 2008. As like server-side debugging we can put breakpoint on any javascript code block and VS will execute that block when page get rendered. Now no need to use 'debugger' keyword on any JS block.This will work ASp.net 2.0 web page edited in VS 2008. Expore more on : http://weblogs.asp.net/scottgu/archive/2007/07/19/vs-2008-javascript-debugging.aspx

4)Debugging .Net Fx Classes - Using VS 2008, we can debug .Net framework class libraries by simple pression F11, With previous version by doing same will take to a meta-data information, but now will take to source code. This is very great feature and give good knowledge of exploring Microsoft development standards. Bit excited with feature .. !!!. Explore more on http://weblogs.asp.net/scottgu/archive/2007/10/03/releasing-the-source-code-for-the-net-framework-libraries.aspx.

Will keep editing these post by reading feature one-by-one.

Thanks,
Guna.