Home » 2012 » September (Page 2)

Monthly Archives: September 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

21 Different Ways to Connect to SQL Server Instance

Today, A weird though come into mind to check the different way to connect to SQL Server instance. Diffrent strings, I can pass in Server Name in SSMS to connect.

I am surprised but I got 21 different way to connect & may be there are many more ways. I am connecting to the default instance of SQL Server from the same system where it is installed.

1. Connecting using computer name

2. Connecting using system IP

3. Connecting using loopback IP 127.0.0.1

4. Connecting using localhost

5. Connecting using (local)

6. Connecting using just a dot (.)

7. Connecting using \\.\pipe\sql\query

8. Connecting using \\.\pipe\SQLLocal\MSSQLSERVER

9. Connecting using computer name with prefix np: (to force to use named pipe protocol)

10. Connecting using \\.\pipe\SQLLocal\MSSQLSERVER with prefix np: (to force to use named pipe protocol)

11. Connecting using (local) with prefix np: (to force to use named pipe protocol)

12. Connecting using just a dot (.) with prefix np: (to force to use named pipe protocol)

13. Connecting using \\.\pipe\sql\query with prefix np: (to force to use named pipe protocol)

14. Connecting using localhost with prefix np: (to force to use named pipe protocol)

15. Connecting using computer name with prefix tcp: (to force to use TCP protocol)

16. Connecting using system IP with prefix tcp: (to force to use TCP protocol) – Connect using TCP only without prefix also

17. Connecting using localhost with prefix tcp: (to force to use TCP protocol)

18. Connecting using (local) with prefix tcp: (to force to use TCP protocol)

19. Connecting using just a dot (.) with prefix tcp: (to force to use TCP protocol)

20. Connecting using loopback IP 127.0.0.1 with prefix tcp: (to force to use TCP protocol) – Connect using TCP only without prefix also

21. Connecting using specifying port no.

SQL Server || Query Execution plan from XML to Graphical View

1) We can get queries execution plan from below query

SELECT

–st.text, qs.EXECUTION_COUNT

qs.*, cp.*

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle)AS st

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp

2) Column query_plan is having execution plan in XML form

3) When we click hyperlink in query_plan column, in SQL 2005,2008 & 2008 R2 it open as XML but in SQL 2012 it converted to graphical view

4) To convert XML to graphical view, save XML file with extension .sqlplan & open it with SSMS

5) Now plan will show in graphical view

%d bloggers like this: