I am trying to handle exceptions using try catch with remote database.
I am writing the following code which works fine if login locally but when I am trying to do the same on linked server its not being caught in try..catch block.
The procedure I am writing to raiserror
create proc CustomError
as
RAISERROR ('db error', 16, 1);
I am calling the above procedure in local database using following code
BEGIN TRY
exec CustomError
END TRY
BEGIN CATCH
select ERROR_MESSAGE() as ERROR_MESSAGE;
END CATCH;
GO
and it works perfectly. I am able to catch the error in catch block.
and i m getting the following result
db error
but when i am trying to do the same on linked server the code doesn't take me to catch block. I am getting following error while executing the code
go
BEGIN TRY
exec [192.168.0.50].[BM].dbo.CustomError
END TRY
BEGIN CATCH
select ERROR_MESSAGE();
END CATCH;
GO
where [192.168.0.50] is a linked server name and BM is the database name and custom error is my sp which is on remote server.
its giving me following error
Msg 50000, Level 16, State 1, Procedure CustomError, Line 11
db error
I'm creating a class that will wrap a SqlException and will offer the developer a way of determining how to proceed in handling the exception by offering a suggestion of retrying, revalidating the data, and/or calling the entire process a no go.
One of the ways i've come up with to accomplish this is by using the severity (SqlException.Class) to assist in making a suggestion to the developer.
I'm hoping to get some feedback from the developers who use this forum if they feel severity is or is not the way to go, and also to offer any other ways of accomplishing that they can come up with.
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c# I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised: if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;} These two events are the only one's that have the ExceptionHandled and Exception properties. I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with Server Error in '/SourceCode' Application.
Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.... Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.public void get_issid(string cse_email, string tech_email, string subject, string issue_details, string response, string solv_date, out int issid) { // Establish ConnectionSqlConnection oConnection = GetConnection(); // build the commandSqlCommand oCommand = new SqlCommand("get_issid", oConnection); oCommand.CommandType = CommandType.StoredProcedure; // ParametersSqlParameter paracse_email = new SqlParameter("@cse_email", SqlDbType.VarChar, 50); paracse_email.Value =cse_email; oCommand.Parameters.Add(paracse_email); SqlParameter paratech_email = new SqlParameter("@tech_email", SqlDbType.VarChar,50); paratech_email.Value = cse_email; oCommand.Parameters.Add(paratech_email);SqlParameter parasubject = new SqlParameter("@subject", SqlDbType.VarChar, 50); parasubject.Value = subject; oCommand.Parameters.Add(parasubject);SqlParameter paraissue_details = new SqlParameter("@issue_details", SqlDbType.VarChar, 500); paraissue_details.Value = issue_details; oCommand.Parameters.Add(paraissue_details);SqlParameter pararesponse = new SqlParameter("@response", SqlDbType.VarChar, 500); pararesponse.Value = response; oCommand.Parameters.Add(pararesponse);SqlParameter parasolv_date = new SqlParameter("@solv_date", SqlDbType.DateTime); parasolv_date.Value = solv_date; oCommand.Parameters.Add(parasolv_date);SqlParameter paraissid = new SqlParameter("@issid", SqlDbType.Int);paraissid.Direction = ParameterDirection.Output; oCommand.Parameters.Add(paraissid); try { oConnection.Open(); oCommand.ExecuteNonQuery();issid = int.Parse(paraissid.Value.ToString()); }catch (Exception oException) {throw oException; } finally { oConnection.Close(); } }
the stored procedure is:
create proc [dbo].[get_issid](@tech_email varchar(50), @cse_email varchar(50),@subject varchar(50),@issue_details varchar(500),@response varchar(500),@solv_date datetime, @issid int output) as select @issid=tech_response.issue_id from tech_response,issue_details where tech_response.tech_email=@tech_email and tech_response.cse_email=@cse_email and tech_response.subject=@subject and tech_response.issue_details=@issue_details and response=@response and solv_date=@solv_date and tech_response.issue_id=issue_details.issue_id requested to help in this
I'm unsure how to handle an SQL Exception correctly when the database is unavailable/offline. I have my aspx file with the C# code-behind, but all of the SQL stuff is done in a separate code file in the App_Code directory.E.g. CODE-BEHINDDatabaseModifier.deleteUser(username); DATABASEMODIFIER.cspublic static void deleteUser(string username){ SqlConnection conn = SqlLogin.SqlConnect; SqlCommand command = new SqlCommand("DELETE FROM <table> WHERE Username = '" + username + "'", conn); conn.Open() command.ExecuteNonQuery(); conn.Close()} Now, that code works perfectly, however if the database I'm connecting to is offline, an SQLException is thrown and because the SQL is handled in my DatabaseModifier class, I'm not sure how to handle it correctly.If I use a Try/Catch block in my code-behind, it doesn't get thrown because the error occurs in my DatabaseModifier class. If I use a Try/Catch block in my DatabaseModifier class, what can I put in the catch block that will inform the user of the database being offline and/or how can I perform a url redirection? Any help is greatly appreciated.
Hi, Im Nithyananda working on a project with SQL server 6.0 Im having a stored procedure which inserts into a table . If I violate the primary key constraint on the table , I get a 2627 error. I would like to replace this error with my own.Could u tell me how to do it? Im an ORACLE guy and relatively new to SQL server. Is it possible for me to propogate the same error message to any front end? In my case ,Im using VC++ as front end. Could someone also tell me the way of calling a SQL stored procedure from VC++?
I m new to the sqlserver.In Oracle we can handle exceptions like this
declare name varchar(20); begin select ename into name from emp where eno=&eno; dbms_output.put_line(name); exception when no_data_found then dbms_output.put_line('No Entry'); end; /
We will get the message No Entry When corrsponding employee number dosent exists.
In Sqlserver how to handle these things.
Specifically my requirement is with select statement(Like above program)
Hello, I am trying to catch an error, but not able to. I am storing the value of @@Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT??). The Error that it throws it this:
Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157 Invalid object name '#TEMPO_'.
Can you please let me know whats going wrong here? Thanks a lot in advance.
SET @ErrNo = @@Error PRINT '@ErrNo is ' + ltrim(str(@ErrNo)) + '.' IF @ErrNo <> 0 Begin PRINT '@ErrNo is ' + ltrim(str(@ErrNo)) + '.' GOTO ROLLITBACK End
I am using exception handling and Transaction handling combined. The code is written to delete a record from 2 different table. The purpose of using exception and transaction handling combined is to make sure that the record is deleted in BOTH table. If there is error while deleting a record from one of the tables, it will rollback the database to the original and will stop deleting any record.
However, The Transaction handling is not working. When error occurs while deleting a record on table A, the code still delete a record from one table B. The transaction.RollBack() doesn't work. This is my code:
Try Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) Conn.Open() Trans = sConn.BeginTransaction
For Each row In GridView1.Rows Dim sql As String = "SELECT * FROM Member
Using Cmd As New SqlCommand(sql, Conn, Trans) SqlDataSource1.DeleteParameters("MemberID").DefaultValue = DropDownListMember.SelectedValue
SqlDataSource2.Delete() End Using End If Next sTrans.Commit() Label1.Text = "deleted." Catch ex As Exception If sTrans IsNot Nothing Then sTrans.Rollback() End If Label1.Text = "error" Return Finally If sConn IsNot Nothing Then sConn.Close() End If End Try
Hi guys, Does any one know how to detect when a SQL transaction has been rolled back in either a windows application or ASP.NET. My Transactions always run but when they are rolled back Visual Basic does not pick up any errors in the 'Try Catch SqlException'. Does any one know a way round this. Sorry for the lack of code. Im writing this post on a friends PC but i wil put up my code as soon as possible. Thanks in advance Matt
I'm trying to do exception handling in a stored procedure, such as IF (@@ERROR <> 0) execute and insert on to an error log table. I have set the volatile @@ERROR global to a variable, but the proc is still throwing a primary key exception; hence, it's not being trapped. My question is why isn't my exception being handled?
Here's my code, your help is much obliged. (my error proc is below the caller)
CREATE PROCEDURE [dbo].[INSERT_STATS_NO_GROUP_PROC] @stat_type_id int, @stat_delimited_file_id smallint, @time_interval datetime, @call_volume int, @Err int OUTPUT AS SET @Err = 0 DECLARE @Error int BEGIN TRANSACTION INSERT INTO DAILY_SUMMARY_STATISTICS ( stat_type_id, stat_delimited_file_id, ssda_customer_id, time_interval, call_volume ) VALUES ( @stat_type_id, @stat_delimited_file_id, NULL, @time_interval, @call_volume )
SET @Error = @@ERROR IF(@Error <> 0) BEGIN GOTO abort END COMMIT TRANSACTION
can I handle exception in SQL functions? how?? I am tring to migrate the Database from Oracle 9i to SQL server 2005 with SSMA which converts function in one procedure and wrapper function because of exception handling in Oracle.
in my package i'm using a for each loop container in order to process all files in a certain folder. everything works fine but of course when i start the service and there are no files available the package waits. is there a way to simply say skip the for each loop or semething like that?
I would like to capture any errors thrown by linked server in my stored procedure, How do I do this? eg: Select statement in sp to remote server fails if login credentials were changed for some reason on remote server. @@error is not able to capture this error and stored procedure is terminated when this type of error occurs.
Upon an exception being caught (i.e. when inserting a record, it violates integrity constraint), I would like to do my normal exception handling routine and then continue with the loop to the next record.
Is this possible? If so, how do I implement this behavior?
Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic. '((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'
My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer) http://i85.photobucket.com/albums/k71/Scionwest/table.jpg
Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.
Next is a snap shot of my startup form. http://i85.photobucket.com/albums/k71/Scionwest/form.jpg
Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.
account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.
financesDataSet.BankAccount.Rows.Add(account); //The next three lines where added while I was trying to get this to work. //I don't know if I really need them or not, I receive the error regardless if these are here or not.
catch (global:ystem.InvalidCastException e) { //Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'
throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);
I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
in my refreshDatabase() method it still failed.
When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Hello, I want the server to check validation rules and not the user application is this possible??? I want to send my own messages to the user and dont want the user to see the servers messages. Thank you in advance Eran
Hi I think by virtue of not being able to find reference to this I have my answer however.... You trap an error, check it and know that you are happy with it - it isn't an issue. Is it possible to prevent that specific error (number, message - the lot) being passed to the client so developers don't need to handle the error a second time? Ta db chucks :D
I have a field in one of my tables called renew_date. The renew_date is always the first of every month. My requirement is to have a query that's run on the first of every month to select records that have renew_date coming up in the next 150 days.(5 months from today).
HI all, if this issue has been solved, please provide me the post, or I've put this issue in a wrong category please let me know. The following is my code: DateTime dtBFWNow = DateTime.Today; String dtBFW_range1 = dtBFWNow.AddDays(-7).ToShortDateString(); String dtBFW_range2 = dtBFWNow.AddDays(3).ToShortDateString(); SqlDataSource1.SelectCommand = "SELECT * FROM [MORTGAGE] WHERE ([BringForwardDate] is not null) and ([BringForwardDate]>= '" + dtBFW_range1 + "' and [BringForwardDate] <= '" + dtBFW_range2 + "')"; lblHeader.Text = "Bring Forward Reminders Due From '" + dtBFW_range1 + "' To '" + dtBFW_range2 + "'"; lblHeader.ForeColor = System.Drawing.Color.FromName("#000000"); GridView1.DataBind(); When I compiled, the following exception occurred: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. and the exception points right at the line: GridView1.DataBind() I use the webhost4life as my server provider. My codes run fine with the server. I mean I uploaded my program to the server, set up and run the program online, everything is fine. But on my PC it gives me the above exception. I can't compile my code. There must be some sort of setting somewhere in my PC that can't handle the exception. Please help. Regards. Joey.
I'm thinking of storing a lot of images, Word documetns, PDF, and emails as media in SQL Server 2005. Is it equiped to handle a lot of media? Or is it still preferred to store media separately on a file server?
This is my code connect to SQL Server SqlConnection con = new SqlConnection("Data Source=OIT;Initial Catalog=big_db;User ID=sa; Password="); SqlDataAdapter cmd = new SqlDataAdapter("select * from myDB", con); SqlCommand sqlCmd = new SqlCommand(); DataTable dt = new DataTable(); cmd.Fill(dt); // It throw exception When myDB table have a lot of data, it throw exception like this : "It reached the time-out. Did the time-out period pass before completing the operation or the server doesn't respond. ". I config TCP/IP for SQL Server is Enable, but it throws SqlException too. How can I do? Help me please!!!
I was attampting to add application services to a remote server to my ASP database and received and exception.
Setup failed.
Exception: An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated.
---------------------------------------- Details of failure ----------------------------------------
INSERT INTO #aspnet_Permissions EXEC sp_helprotect
IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Setup_RestorePermissions') AND (type = 'P'))) DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions
SQL Exception: System.Data.SqlClient.SqlException: String or binary data would be truncated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
Hi, I have a website where the user uploads DBF files and then i am calling a sproc to scrub the data and put them from the staging database to the production database. If the folder size small may be less that 6 it transfers the data from the staging database to the production.. But if the size of the folder is big.. more the 6 mb i get an timeout exception.. But i know the data is been put in few tables in the production databse..
for eg... if the size of the file is 33mb it takes around 15 mins or so for the sproc to process . I have tried setting the connect and commd timeout whcih has not helped.. I tried increasing the server.script time and it fails too..
this is the error in my event viewer
Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
No Changes made to the ClientPlan table.
Inserting records(s) in ClientPlan table.
No Changes made to ClientPlan table.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, String spName, Object[] parameterValues)
at icc.BaseClasses.DL.DLImportPlan.Import(Int32 ClientId, DateTime StartDate, DateTime EndDate, Int32 UserId) in C:Documents and SettingskroslundMy DocumentskareniccPlanStatementsiccImportDLDLImportPlan.cs:line 120
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I'm taking the liberty to announce the availability of a suite of articles on my web site about error and transaction handling in SQL Server. In total there are three main parts and three appendixes.
The first part is a short jumpstart, while Part Two is a long in-depth discussion of what can happen in SQL Server in case of an error and what commands that are available. Part Three covers implementation and has lot of examples as well as a facility for logging and raising errors.
The appendixes cover special areas: linked servers, the CLR and Service Broker.