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
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
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
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.
Query to copy database diagrams
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.
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
2. Click on “Tools”
3. Click on “Installed SQL Server features discovery report”
The following report will be generated and opened in your browser.
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”
I hope you all find this useful…
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
Let us walkthrough the steps of using SQL Diag Configuration Tool
After installation, run the tool
Select your desired SQL Server version and then click on “Authentication Method” option available on left side
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
Select the Events Logs you want collect as part of performance data and then click on “Instance Specific Diagnostics”
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
Click “Enable SQL Tracing” to collect Profiler information and select a Trace Template.
Available Trace Templates are
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
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
Next Click on the “Analyzers” to enable the required Analyzers
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…