How Does The .net App Capture Raiserror Values From A Stored Proc
Oct 28, 2006
I have been using return values to check the status of my stored procedures called from an application. But how does one read the new raiserror values that are passed with sql server 2005 and using ado.net 2.0. Are they returned as parameters or as a dataset or what?
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I have a stored proc with a query which checks whether an identical value is already in the database table. If so, it returns a value of 1. How do I caputure this value in an asp.net page in order to display a message accordingly? (using ASP.NET 1.1)
Currently my stored proc looks something like this (snippet only): If Exists( SELECT mydoc WHERE.... ) Return 1 Else ...INSERT INTO.... code here.
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings: RESULTSET: Single Row PARAMETER MAPPING: User::giBatchID DIRECTION: OUTPUT DATATYPE: LONG PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog @OutSuccess INT AS
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON DECLARE @RowCountCAT INT DECLARE @RowCountLOG INT
---these totals should match SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT) SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG) --PRINT @RowCountCAT --PRINT @RowCountLOG BEGIN IF @RowCountCAT <> @RowCountLOG --PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file' --RETURN 1 SET @OutSuccess = 1 END GO
The following SP gives an error of:Server: Msg 245, Level 16, State 1, Procedure spSelectSEICData, Line26Syntax error converting the varchar value '@' to a column of data typeint.In the Procedure I am using the Select * for testing purposes.Here is the proc.CREATE PROCEDURE spSelectSEICData(@IndivNo int,@CommType SmallInt,@BeginDate as SmallDateTime)ASDeclare @SqlStr as char(1)Set @SqlStr = ''If ((@BeginDate <> ' ') and (@CommType <> ' '))BeginSet @SqlStr = '@IndivNo AND [SEIC-COMMENT-TYPE] = @CommType'EndIf ((@BeginDate <> ' ') and (@CommType = ' '))BeginSet @SqlStr = '@IndivNo AND [SEIC-COMMENT-DATE-MCYMD] =@BeginDate'EndIf ((@BeginDate = ' ') and (@CommType <> ' '))BeginSet @SqlStr = '@IndivNo AND [SEIC-COMMENT-DATE-MCYMD] = @BeginDateAND[SEIC-COMMENT-TYPE] = @CommType 'EndIf ((@BeginDate = ' ') and (@CommType = ' '))BeginSet @SQlStr = '@IndivNo 'EndSELECT *FROM SEICWHERE [SEIC-INDIVIDUAL-NO] = @SqlStrGOThe optional values are the @CommType and the @BeginDate. Where did Igo wrong or is there a better way of doing this?Thanks in advanceBill
HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
I have 3 paramaters@value1, @value2,@value3 being passed into a stored proc and each of these parameters can be blank. If one of them is blank and the rest of them have some valid values, then I should just exclude the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would do this select * from tblName where column1 like @value1 and column2 like @value2 and column3 like @value3
else if I have one of the parameter being passed as empty, I should ignore that parameter like if@value1 is empty then my sql should be
select * from tblName where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue. I want it through a stored procedure only.
Also, all the three columns can have null values in the table. Please let me know what is the best possible way to do this. Thanks in advance !.
Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd) begin print 'Date Lies Between Boundary Limits' select * from membership where uid=@uid end else begin if(@pipe=1) begin if(@plan_id=1) begin print 'Monthly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid select * from membership where uid=@uid end else begin print 'Weekly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid select * from membership where uid=@uid end end end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish
I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.
I have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!
I use Try ... catch blok in my activation stored procedure. When SQL Server raise error (e.g. Primary key violation) in Try blok, XACT_STATE in Catch blok has value 1 = commitable transaction and I can use rollback transaction to savepoint. But when I use Raiserror() in Try blok, XACT_STATE in Catch blok has value -1 = uncommitable transaction and I can't use rollback transaction to savepoint. When I drop automatic activation for given queue and I run this stored procedure with Raiserror(), XACT_STATE has value 1 = commitable transaction.
What a problem may cause this different behavior ?
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I have a stored procedure that does updates. The sproc is called from an €œExec SQL Task€?. The sproc returns the number of rows that were updated using this technique:
RETURN @@ROWCOUNT
How do I capture the sproc€™s return value (number of rows) in a package-level variable? I eventually want to log this value. The sproc is not returning a result set. I€™m new to SSIS so any general guidance would be appreciated.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter named NewId to my ASP.NET server. I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName; 1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.
I am creating a package that has many SQL tasks. Each task executes a stored procedure. I need to capture any error messages returned by the stored procedures. Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.
I'm not sure where or how I can access a stored procedure message. What is the best way?
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted. Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varchar(50) AS DECLARE @myERROR int -- local @@ERROR , @myRowCount int --local @@rowcountBEGIN -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info WHERE ID = @ID) BEGIN RETURN 1END ELSEBEGIN TRAN INSERT INTO Info(IDNumber, ID, LName, FName) VALUES (@IDNumber, @ID, @LName, @FName) SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR COMMIT TRAN RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @myERROR ENDGO asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>" SelectCommand="SELECT * FROM TABLE_One" UpdateCommand="UPDATE TABLE_One WHERE ID = @ID" InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)... ...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.