Why Stored Procedure throws exception during transaction?

%3CLINGO-SUB%20id%3D%22lingo-sub-1204743%22%20slang%3D%22es-ES%22%3EWhy%20Stored%20Procedure%20throws%20exception%20during%20transaction%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1204743%22%20slang%3D%22es-ES%22%3E%3CP%3EHello%20everyone%3F%3C%2FP%3E%3CP%3EI%20will%20post%20in%20english%20but%20I%20am%20an%20spanish%20speaker!!%20So%20let's%20try%20to%20do%20this.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20stocked%20with%20an%20issue%20working%20with%20ado.net%20and%20sql%20server2009.%3C%2FP%3E%3CP%3Ethe%20problem%20is%20when%20i%20call%20a%20Store%20procedure%20that%20try%20to%20insert%20data%20into%20a%20column%20with%20a%20check%3C%2FP%3E%3CP%3Econstrait.%20the%20insert%20is%20into%20a%20transaction%20block%2C%20but%20when%20i%20call%20the%20sp%20via%20ado.net%20and%20expect%20to%20receive%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20return%20value%20so%20that%20i%20can%20manage%20the%20process%20behaviour%20and%20send%20appropriate%20message.%3C%2FP%3E%3CP%3Ebut%20instead%2C%20after%20the%20sql%20command%26gt%3B%20executenonquery()%20method%20it%20throws%20the%20check%20column%20exception%20from%20sql%20server.%3C%2FP%3E%3CP%3Eas%20it%20is%20into%20a%20transaction%2C%20i%20was%20expecting%20not%20to%20have%20an%20axception%2C%20and%20can%20filter%20via%20my%20return%20value.%3C%2FP%3E%3CP%3Ewith%20this%20exception%2C%20users%20receive%20very%20confidential%20information%20from%20the%20database%20structure%2C%20and%20even%20i%20can%20not%20indicate%20that%20the%20problem%20was%20just%20incorrect%20data%20format%20for%20that%20column%20value.%3C%2FP%3E%3CP%3Edoes%20anyone%20knows%20how%20should%20i%20configure%20sql%20server%20or%20app%20server%20with%20ado.net%20in%20order%20to%20not%20receive%3C%2FP%3E%3CP%3E(or%20throw...)%20that%20check%20constraint%20exception%3F%3F%3C%2FP%3E%3CP%3Ei%20have%20to%20add%20that%20this%20is%20an%20academy%20project%2C%20so%20that%20i%20am%20mandatory%20to%20use%20some%20architectures%20and%20technologies%2C%20but%20as%20i%20am%20stocked%2C%20i%20want%20to%20hear%20new%20options.%3C%2FP%3E%3CP%3Ethanks%20in%20advance!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1213481%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20Stored%20Procedure%20throws%20exception%20during%20transaction%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213481%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572437%22%20target%3D%22_blank%22%3E%40_Mmesa1984%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3Bthe%20answer%20is%20in%20your%20description%3A%20attend%20to%20the%20user%20input%20and%20output%20only%20the%20error%20description%20-if%20you%20have%20to-%20and%20not%20the%20exception%20details%20to%20the%20end-user.%20Exceptions%20should%20be%20logged%20for%20the%20developer%20and%20not%20for%20end-users.%20Instead%2C%20try%20to%20inform%20the%20user%20on%20the%20wrong%20input%20or%20use%20proper%20input%20controls%20to%20validate%20the%20user%20input%20which%20has%20always%20been%20the%20Achilles%20heel%20of%20every%20application%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20don't%20forget%20to%20put%20your%20code%20inside%20try-catch-finally%20blocks%20to%20eliminate%20the%20application%20exceptions%20and%20hanging%20of%20your%20app.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%2C%3CBR%20%2F%3EVassilis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1213500%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20Stored%20Procedure%20throws%20exception%20during%20transaction%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213500%22%20slang%3D%22en-US%22%3E%3CP%3EBTW%20there%20is%20no%20%22sql%20server2009%22%20%3B)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1213694%22%20slang%3D%22es-ES%22%3ERe%3A%20Why%20Stored%20Procedure%20throws%20exception%20during%20transaction%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213694%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F14476%22%20target%3D%22_blank%22%3E%40Vassilis%20Ioannidis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20man!!%3C%2FP%3E%3CP%3EBut%20you're%20wrong!!%20Yes%20there%20is....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Mmesa1984_0-1583507964785.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F175641i8CCF32901DC3F74A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22_Mmesa1984_0-1583507964785.png%22%20alt%3D%22_Mmesa1984_0-1583507964785.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1214129%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20Stored%20Procedure%20throws%20exception%20during%20transaction%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1214129%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572437%22%20target%3D%22_blank%22%3E%40_Mmesa1984%3C%2FA%3E%3C%2FP%3E%3CP%3EI'm%20not%20wrong%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3EThe%20instance%20you%20are%20showing%20is%20version%20%3CSTRONG%3E10.50.1600%3C%2FSTRONG%3E%20%3D%26gt%3B%20SQL%20Server%20%3CSTRONG%3E2008%20R2%3C%2FSTRONG%3E.%26nbsp%3BI%20hope%20it%20makes%20sense%20to%20you%2C%20now.%3C%2FP%3E%3CP%3EIf%20not%20check%20%3CA%20href%3D%22https%3A%2F%2Fsqlserverbuilds.blogspot.com%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20for%20a%20comprehensive%20list%20of%20all%20SQL%20Server%20builds%20and%20versions%20available.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVassilis%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone?

I will post in english but I am an spanish speaker!! So let's try to do this.....

 

I am stocked with an issue working with ado.net and sql server2009.

the problem is when i call a Store procedure that try to insert data into a column with a check

constrait. the insert is into a transaction block, but when i call the sp via ado.net y expect to receive 

the return value so that i can manage the process behaviour and send appropriate message.

but instead, after the sql command> executenonquery() method it throws the check column exception from sql server.

as it is into a transaction, i was expecting not to have an axception, and can filter via my return value.

with this exception, users receive very confidential information from the database structure, and even i can not indicate that the problem was just incorrect data format for that column value.

does anyone knows how should i configure sql server or app server with ado.net in order to not receive

(or throw...) that check constraint exception??

i have to add that this is an academy proyect, so that i am mandatory to use some architectures and technologies, but as i am stocked, i want to hear new options.

thanks in advance!!

 

4 Replies

Hi @_Mmesa1984,

 the answer is in your description: attend to the user input and output only the error description -if you have to- and not the exception details to the end-user. Exceptions should be logged for the developer and not for end-users. Instead, try to inform the user on the wrong input or use proper input controls to validate the user input which has always been the Achilles heel of every application :)

 

Also, don't forget to put your code inside try-catch-finally blocks to eliminate the application exceptions and hanging of your app.

 

HTH,
Vassilis

BTW there is no "sql server2009" ;)

@Vassilis Ioannidis 

 

Thanks man!!

But you're wrong!! Yes there is....

 

_Mmesa1984_0-1583507964785.png

 

Hi @_Mmesa1984

I'm not wrong :)

The instance you are showing is version 10.50.1600 => SQL Server 2008 R2. I hope it makes sense to you, now.

If not check here for a comprehensive list of all SQL Server builds and versions available.

 

Vassilis