Tag: SQL Server 2008 R2

Error Handling in SQL Server 2008 R2–Questions Answered


With reference to the Question posted by Hima Bindu Vejella on http://himabinduvejella.blogspot.com/2011/06/how-did-you-handle-errors-in-sqlserver.html, I have posted these answers, I hope you all find this usefull

How did you handle errors in SqlServer 2008 R2?

We handle errors using

  1. BEGIN TRY
  2. BEGIN TRANSACTION
  3. /* Insert or Update or Delete statements */
  4. COMMIT TRANSACTION
  5. END TRY
  6. BEGIN CATCH
  7. INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease)
  8. SELECT ERROR_NUMBER() AS ErrorNumber,
  9. ERROR_SEVERITY() AS ErrorSeverity,
  10. ERROR_STATE() as ErrorState,
  11. ERROR_LINE () as ErrorLine,
  12. ERROR_PROCEDURE() as ErrorProcedure,
  13. ERROR_MESSAGE() as ErrorMessage
  14. IF (XACT_STATE()) = -1
  15. BEGIN
  16. ROLLBACK TRAN
  17. END
  18. ELSE IF (XACT_STATE()) = 1
  19. BEGIN
  20. COMMIT TRAN
  21. END
  22. RAISEERROR(‘Application Error Occurred. Please contact administrator’, 16, -1)
  23. END CATCH()

What are the different ways of handling errors in SQL Server?

1. We can handle errors by checking @@ERROR, if it’s not equal to 0, then we can use RAISERRROR to return error message to application

2. We can handle error by checking @@ERROR and @@ROWCOUNT in combination and then use RAISERROR or RETURN to return error message or code to application

3. We can use TRY..CATCH method

Which is the optimized way to handle errors?

The optimized way to handle errors is to create Error Log table with following columns (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, UserName, HostName, ErrorDateTime, ApplicationName) and then create a Stored Procedure to get these columns as Input Parameter except ErrorDateTime, for which we can use GetDate() and then call this Stored Procedure inside BEGIN CATCH..END CATCH block

What are the Built-In Objects available related to error handling?

The following are the built in objects related to Error Handling and Information

ERROR_NUMBER() – Returns Error Number of the Error that caused the CATCH block of TRY…CATCH to run

ERROR_SEVERITY() – Returns severity of Error that caused the CATCH block of TRY…CATCH to run

ERROR_STATE() – Returns State Number of the Error that caused the CATCH block of TRY…CATCH to run

ERROR_LINE () – Returns the line number at which an error occurred that caused the CATCH block of TRY…CATCH to run

ERROR_PROCEDURE() – Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_MESSAGE() – Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run

@@ERROR – Returns the Error number of the last T-SQL Statement run

RAISERROR – Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

Sys.Messages – This is a Catalog view which contains the list of system defined and user defined messages

SP_AddMessage – To define a new user-defined error message in a SQL Server instance

sp_dropmessage – To drop a specified user-defined error message from a SQL Server instance

xp_logevent – Logs a user defined message in SQL Server log file and in the Windows Event Viewer. It can be used to send an alert in the background to administrators without showing a message to client

Print – To simply display the message, we can use PRINT statement followed with Error message

How can return Error information to Application ?

We can return error information back to application using RAISERROR Or RETURN

RAISERROR (‘Application Error Occurred’, 16, -1)

RETURN -1 –In Application we have to check for Return value and display corresponding message

Advertisement

SQL Server 2008 R2 Database Diagrams Questions Answered


What are the database diagrams in SQL Server?

SQL Server Database Diagram is visual model of a database. We can use only Tables in SQL Server database diagrams. Any changes made outside Database Diagram designer will reflect automatically in the diagram and vice versa.

How did you create them?

We can create them using built-in SQL Server Database Diagram designer tool in SQL Server Management Studio. There is no Undo and Redo option, so we need to be careful when deleting columns or removing relationships

Do you know where exactly the database diagrams are stored in SQL Server 2008 R2?

Database Diagrams are stored in dbo.sysdiagrams system table in the database we are creating diagram. This table will be created automatically, when we create the first database diagram in the database.

The structure of this table is as follows

clip_image001

 

Is there any way how you copy one database diagram to another database?

Yes, we can copy the diagram by copy the data of dbo.sysdiagrams table from source database to destination database, but the tables in the diagram should exist in the destination database or else while viewing or modifying the diagram, we will get the below error and corresponding tables will be removed from the diagram

“Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.

clip_image001[5]

Query to copy database diagrams

  1. INSERT INTO [DESTINATIONDB].[dbo].[sysdiagrams]
  2. ([name]
  3. ,[principal_id]
  4. ,[version]
  5. ,[definition])
  6. SELECT [name]
  7. ,[principal_id]
  8. ,[version]
  9. ,[definition]
  10. FROM [SOURCEDB].[dbo].[sysdiagrams]

 

For copying to remote servers, we can use Linked Servers and use Four part name or use SSIS to transfer data

How will you insert database diagrams into your design document?

We can use “Edit -> Copy Diagram to Clipboard” or “Database Diagram – Copy Diagram to Clipboard” menu option to copy the diagram and then paste it in the document required. If the diagram is bigger, we can paste it to Paint Brush or any image editor and then select specific portions and copy one by one in document.

We can also the print the database diagram as PDF, if we have PDF Print drivers such as BioPDF or CutePDF installed.

How to identify what features has been installed in your SQL Server ?


One of my friend recently asked me a question, How to view the list of features installed in the SQL Server easily without going through “Programs and Features” or “Services”.

Yes, there is an easy way

SQL Server 2008 / 2008 R2 / Denali provides “Installation Center” using which we can view features of all locally installed SQL Server versions 2000 / 2005 / 2008 / 2008 R2 / Denali including the SQL Express editions

Please follow the below steps to do that

1. Click on “Start” –> “All Programs” –> “Microsoft SQL Server 2008 R2” –> “Configuration Tools” –> “SQL Server Installation Center

image

2. Click on “Tools”

image

3. Click on “Installed SQL Server features discovery report”

image

The following report will be generated and opened in your browser.

image

The report displays the SQL Server version Name, Instance Name and its ID, Feature Name, Installed Language ID, SQL Server Edition, Version Number and whether its clustered or not.

Output from SQL Server “Denali”

image

I hope you all find this useful…

SQL Diag Configuration Tool


SQL Diag Configuration Tools is an easy to use tool which will help you in configuring SQLDiag.xml to collect performance data from SQL Server 2005/2008/2008 R2

SQL Diag Configuration Tool is a free tool available on Codeplex. You can download it from the following link

http://sdct.codeplex.com/ 

Let us walkthrough the steps of using SQL Diag Configuration Tool

After installation, run the tool

image

Select your desired SQL Server version and then click on “Authentication Method” option available on left side

image

Specify the Server name and SQL Server Instance Name on which you are planning to use the SQLDiag.xml and then click “Machine Wide Diagnostics” option

image

Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”

image

If you want to collect Blocking information, select “Blocking” and the specify the Maximum File Size of log and the maximum number of files and the polling interval on which the Blocking information should be collected from SQL Server

Next Click on “Profiler" Options

image

Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.

Available Trace Templates are

image

After selecting, you can also add further Events you want to collect and then specify the maximum file size, maximum number of files and polling interval on which profiler information should be collected from SQL Server

Next click “Perfmon Options” to proceed

image

Select the Performance Counters which you want to collect or select the predefined template available and then specify Maximum File Size and the polling interval on which performance counter data should be collected

Next Click on “Producers” to enable the Producers options

image

Next Click on the “Analyzers” to enable the required Analyzers

image

Click “Save” to SQLDiag1050.xml and use it as input for SQLDiag to collect SQL Server Performance Data

You can use the  “Open” option reload the saved SQLDiag xml file and modify the profiler or perfmon or other above specified options.

I hope you find this useful…

%d bloggers like this: