Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.
I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?
I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.
First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?
Second problem: What do I set the value of the parameter to?
The code looks like:
CallableStatement cstmt = myConnection.prepareCall(sQuery);
cstmt.registerOutParameter(1, Types.OTHER); // What is the right type?
cstmt.setNull(1, Types.OTHER); // What is the right type?
if (cstmt.execute()) {
ResultSet rs = cstmt.getResultSet();
}
Execution results in a NullPointerException from the driver.
What am I doing wrong?
Thanks for your assistance.
Jon Weaver
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug? Any workaround? Here is the pseudo code: create Procedure foo(@output varchar(max)) { set @foo = 'string that has more than 4000 characters...'; return; } Java code: CallableStatement cs = connection.prepareCall("{call foo ?}"); cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB. cs.execute(); String result = cs.getString(1); // The result is truncated to 4000 char. -- Also tried CLOB clob = cs.getClob(1); long len = clob.length(); // The result is 4000. Thanks, Eric Wang
View Replies !
Stored Procedure Parameter Sequence Through JDBC From SAP
Hi, I do not know if this is the right place, but do guide me if possible. I am using JDBC driver to send data from SAP XI to MS SQL 2000. The data is passed as an XML file, as below <?xml version="1.0" encoding="UTF-8" ?> - <ns0:MT_UPDATEPERSONACUSTOMERS xmlns:ns0="urndalesapcustomersupdatetopersona"> - <CallProc> - <UPDATE_EndCustomerFromSAP action="EXECUTE"> <table>mk_INSERT_ConsultantFromSAP_proc</table> <SAPID type="CHAR">0000206320</SAPID> <DateCreated hasQuot="No" type="DATE">2007-04-24</DateCreated> <FirstName type="CHAR">CAROL J</FirstName> <LastName type="CHAR">VERITY</LastName> <AccountGroup type="CHAR">Z001</AccountGroup> <CustomerStatus type="INTEGER">1</CustomerStatus> <Active type="INTEGER">1</Active> <Blocked type="INTEGER">1</Blocked> <AssocConsultant type="CHAR">NULL</AssocConsultant> <Street1 type="CHAR">112 EVERGREEN SQUARE SW</Street1> <Street2 type="CHAR">CHANGED ADDRESS LINE 1</Street2> <City type="CHAR">Saint John</City> <District type="CHAR">NULL</District> <Region type="CHAR">NB</Region> <PostalCode type="CHAR">E2L 4L1</PostalCode> <DayPhone type="CHAR">306-781-4923</DayPhone> <EveningPhone type="CHAR">306-761-2121</EveningPhone> <Email type="CHAR">test@lllll.com</Email> <CommunicationMethod type="CHAR">Day Phone</CommunicationMethod> </UPDATE_EndCustomerFromSAP> </CallProc> </ns0:MT_UPDATEPERSONACUSTOMERS> I know there is a way to pass paramters by name and hence sequence of parameters should not matter. So I was shocked and I still do not believe that when you call a stored procedure through JDBC adapter, the SEQUENCE of parameters have to be the same as defined in the stored procedure. I do not know if this is true for Oracle as well as MS SQL Server 2000, but I faced this problem on MS SQL 2k. example - if stored proc 1 has params a, b, c and stored proc 2 has param d, c, a . My destination data type looks like a, b, c, d. Now if i decide to call proc 2, the JDBC adapter in SAP generates XML like a,c,d. I did a trace on SQL server and found that the JDBC driver uses sp_execute command, which my DBA told uses positional mapping and not named mapping. I believe that SAP or the MS JDBC driver should support this. So does anyone know, if there is an additional parameter which will make the JDBC driver to work with name and not position of paramters.
View Replies !
Using Cursor As OUT Parameter In Stored Procedure
Hi guys, I have a serious problem. I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures : create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes; create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val; How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike
View Replies !
Stored Procedure && Output Parameter
I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have: IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL DROP PROCEDURE dbEvent GO CREATE PROCEDURE @Name varchar(200) ,@Location varchar(200) AS INSERT INTO Event ( NAME , LOCATION ) VALUES ( NAME , @LOCATION ) SELECT SCOPE_IDENTITY() And my code behind: public Int64 Insert(SqlConnection Conn) { try { using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output; Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName; Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location; if (Conn.State != ConnectionState.Open) Conn.Open(); Command.ExecuteNonQuery(); Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString()); return _requestId; } I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value. Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
View Replies !
Stored Procedure Output Parameter
I have two stored procedures one generates an output parameter that I then use in the second stored procedure. 1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4 5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14 15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut" 17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20 21 22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " " 30 myCommand.Parameters("@IDFieldOut").Value = 031 32 myCommand.ExecuteNonQuery()33 34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35 If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
View Replies !
Output Parameter Of A Stored Procedure
I have a stored procedure (named Insert_SRegister) that calls another stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one. The program goes like this: The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored procedure. The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission; The code of the first procedur is : Create Procedure dbo.Insert_SRegister @YGroup VarChar (3), @YDate VarChar (4), @YTerm Char(1), @SName VarChar(30), @SSecondName VarChar(30), @SFirstName VarChar(30), @SGender TinyInt, @SBDate DateTime, @SBPlace VarChar(50), @SOCountry VarChar(50), @SOPassCount VarChar(50), @PassNo VarChar(50), @SOLang VarChar(50), @MOLang VarChar(50), @MName VarChar(50), @MHAdd VarChar(50), @MHT1 VarChar(20), @MHT2 VarChar(20), @MHT3 VarChar(20), @MHMob VarChar(20), @MWAdd VarChar(50), @MWT1 VarChar(20), @MWT2 VarChar(20), @MExt VarChar(10), @MWMob VarChar(20), @FName VarChar(50), @FHAdd VarChar(50), @FHT1 VarChar(20), @FHT2 VarChar(20), @FHT3 VarChar(20), @FHMob VarChar(20), @FWAdd VarChar(50), @FWT1 VarChar(20), @FWT2 VarChar(20), @FExt VarChar(10), @FWMob VarChar(20), @EAdd VarChar(50), @ETele VarChar(20), @SchName VarChar(50), @SAdd VarChar(50), @FBNo Smallint, @FSNo Smallint, @FONo VarChar(50), @EMedical VarChar(1000), @FDetails VarChar(1000), @FRDetails VarChar(1000), @PName VarChar(50), @StudentStatus VarChar(50) OUTPUT, @ID VarChar (50) OUTPUT As Begin Declare @Exists Int, -- Return Value @StudentCode VarChar(50) ----------------------------------------- -- here I call the second proc and assgin it output to variable --@StudentCode ----------------------------------------------------------------------------------- exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT ----Do work and insert info in db End Code of the second stored procedure is: ALTER Procedure dbo.Generate_Student_Code @YGroup VarChar (3), @YDate VarChar (4), @newID VarChar (50) OUTPUT As Begin set nocount on Declare @Exists int, -- Return Value @Mv varchar(5), @IdLen int If Exists(Select SID From SRegisteration) Begin select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration Set @IdLen = DATALENGTH(@Mv) Set @Mv = case when @IdLen = 1 then '0000' + @Mv when @IdLen = 2 then '000' + @Mv when @IdLen = 3 then '00' + @Mv when @IdLen = 4 then '0' + @Mv else @Mv end Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv ---------Here I return the ID value select @newID -------------------------------------- Select @Exists = 1 End Else Begin Select @Exists = 0 Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001' ---------Here I return the ID value select @newID ------------------ Return @Exists End End
View Replies !
Output Parameter In Stored Procedure
can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter. ie insert record and get its id column value in output parameter. any one knows the way?
View Replies !
OUTPUT Parameter From Stored Procedure
Hi I should validate a USER and if the user is not a valid user it should return a message to the front end application for this I have a procedure like this . Basically I need to pass an output parameter to the front end application returned by this procedure. Create procedure test @userid VARCHAR(20) AS Declare c1 cursor AS Select userid from USERS_TBL Open Cursor C1 FETCH NEXT from C1 INTO @Temp While @fetch_status = 0 BEGIN If @Temp <> @Userid -- IF the USER is not a valid USER ...It should not execute the Futher code(statement) it should exit the procedure by returning the error statement. --- --- --- FETCH NEXT from C1 INTO @Temp END Thanks VENU
View Replies !
Help! !SQL Stored Procedure Output Parameter.
I am calling a procedure with two input parameters and one output parameter (third) in the following format. I'm receiving a database error on the return parameter. *result. What is the correct syntax for an output parameter. Code: CSTRING strSQL; strSQL.Format("BEGIN EQUIPMENT_UTILITIES.MOVE_EQUIPMENT('%s', '%s', '%d'); END;",(LPCSTR)lpEQNum,(LPCSTR)lpMoveTo, result); <-Failing at '%d' result. Thanks.
View Replies !
Parsing The Output Parameter Of A Stored Procedure
Hi, I am trying to cast the parameter returned from the stored procedure to an int32 type. But it is giving me an error message as " Input string is not in the correct format " my code is int valid = Int32.Parse(command.Parameters["@Valid"].Value.ToString()); the @Valid parameter is an output parameter and is declared as int in the stored procedure.... the command is a DbCommand object does anyone have an idea why this error is happening. Naveen
View Replies !
Stored Procedure Output Parameter Problem
I am trying to run a stored procedure in a C# class I built for this purpose, and get its output parameter. I get the following error: Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'sp_visitor_add' expects parameter '@visitor_id', which was not supplied.I've pasted the code below. The error occurs on line 24. I don't understand why I am getting this error because I am supplying the "@visitor_id" OUTPUT parameter in line 17. 1 public class SetSession 2 { 3 public object AddVisitor(object webSiteNumber, string sessionId) 4 { 5 string _conString = null; 6 SqlConnection _con = null; 7 SqlCommand _cmdSelect = null; 8 9 _conString = WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; 10 _con = new SqlConnection(_conString); 11 12 _cmdSelect = new SqlCommand("sp_visitor_add", _con); 13 _cmdSelect.CommandType = CommandType.StoredProcedure; 14 15 _cmdSelect.Parameters.Add("@web_site_number", SqlDbType.Int).Value = webSiteNumber; 16 _cmdSelect.Parameters.Add("@session_id", SqlDbType.VarChar, 32).Value = sessionId; 17 _cmdSelect.Parameters.Add("@visitor_id", SqlDbType.Int).Direction = ParameterDirection.Output; 18 19 int result = 0; 20 21 try 22 { 23 _con.Open(); 24 _cmdSelect.ExecuteNonQuery(); 25 result = (int)_cmdSelect.Parameters["@visitor_id"].Value; 26 } 27 finally 28 { 29 _con.Close(); 30 } 31 return result; 32 } 33 } Here is the top of my stored procedure for reference: CREATE PROCEDURE [dbo].[sp_visitor_add] @web_site_number int = NULL, @session_id varchar(50) = NULL, @visitor_id int OUTPUTAS ... Can anyone suggest a solution?
View Replies !
Return Output Parameter From Stored Procedure?
I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable? Below is the source code that I have:1 ' Insert question_text into Question table 2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 Dim QuestionID As Integer 5 6 ' Set connection to DB 7 cmd.Connection = conn 8 9 cmd.CommandText = "usp_QuestionResponse_ins" 10 cmd.CommandType = Data.CommandType.StoredProcedure 11 12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output 13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID)) 14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID)) 15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn)) 16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp)) 17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn)) 18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder)) 19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3)) 20 QuestionID = sp1.Value 21 cmd.Connection.Open() 22 cmd.ExecuteNonQuery() 23 24 conn.Close() 25 cmd.Dispose() 26 Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins 2 ( 3 @question_id int = null output, 4 @assessment_id int = null, 5 @domain_id int = null, 6 @question_text_en nvarchar(1000) = null, 7 @question_text_sp nvarchar(1000) = null, 8 @category_en nvarchar(500) = null, 9 @display_order smallint = null, 10 @question_template_id int = null 11 ) 12 As 13 BEGIN 14 DECLARE @Err Int 15 16 DECLARE @QuestionID INT 17 DECLARE @ReturnValue INT 18 SET @ReturnValue = 0 19 20 DECLARE @CategoryId int 21 SELECT @CategoryId = NULL 22 23 -- Check to see if an answer has already been inserted (i.e. revisiting) 24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0) 25 IF ( @CategoryId IS NOT NULL ) 26 BEGIN 27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0 28 SET @QuestionID = @ReturnValue 29 END 30 31 IF ( @QuestionID IS NOT NULL ) 32 BEGIN 33 DECLARE @question_locale_id int 34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0 35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0 36 37 END 38 39 RETURN @QuestionID 40 End What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine. Thanks
View Replies !
Retrieve Output Parameter From Stored Procedure
Hi, I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows: int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close(); my stored procedure: CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted) VALUES (@publicationid,@title,@categoryid,0) SELECT @slideshowid = SCOPE_IDENTITY()GO
View Replies !
NText To Stored Procedure Output Parameter
Greetings all! I'm writing a small content management program for a website that I'm putting together. One of the critical aspects of this projects is that users want to be able to cut n' paste from Word in to a FreeTextBox control and save it to the database. Well, we've run in to some problems. These entries are running well over 4000 characters, and I'm going to have to use an ntext datatype in order to the field to accomodate the amount of text they want to post. My input stored procedures work great and it's dumping it well in to the table. But the problem I'm having is with the output. Apparently, you can't pass the values to an output parameter or reference the field directly in a stored procedure. I've scoured the internet and purchased several books just to deal with this issue. Can someone point me in the right direction or offer some examples of getting ntext data out through a stored procedure? I'm in a real squeeze here. TIA Matt
View Replies !
Value Of Stored Procedure Output Parameter Is Blank
Here's my stored procedure... CREATE PROCEDURE [Get_WirelessProducts_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, ProductID int, ProductCategoryID nvarchar(50), ProductBandwidthKB int, ProductOverridePrice nvarchar(50), UseWirelessOverridePrice bit, DedicationTypeID int, DedicationTypeName nvarchar(50) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID, DedicationTypeName ) SELECT W.ProductID, W.ProductCategoryID, W.ProductBandwidthKB, W.ProductOverridePrice, W.UseWirelessOverridePrice, W.DedicationTypeID, D.DedicationTypeName FROM tblWirelessProducts W INNER JOIN tblDedicationTypes D ON W.DedicationTypeID = D.DedicationTypeID --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID DedicationTypeName FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter SELECT @TotalRecords = COUNT(*) FROM tblWirelessProducts GO Here is the relevant VB Code: Dim parmReturnValue As SqlParameter 'conProducts is already open cmdProducts = New SqlCommand("Get_WirelessProducts_By_Page", conProducts) cmdProducts.CommandType = CommandType.StoredProcedure cmdProducts.Parameters.Add("@CurrentPage", intCurrentPage) cmdProducts.Parameters.Add("@PageSize", dgrdProducts.PageSize) parmReturnValue = cmdProducts.Parameters.Add("@TotalRecords", SqlDbType.int) parmReturnValue.Direction = ParameterDirection.Output conProducts.Open dgrdProducts.DataSource = cmdProducts.ExecuteReader() If Not IsDBNull(cmdProducts.Parameters("@TotalRecords").Value) then Response.Write(cmdProducts.Parameters("@TotalRecords").Value) End IfThe rows are returned correctly, but the output parameter "@TotalRecords" doesn't return anything. Any ideas what I'm doing wrong? Thanks in advance for your help.
View Replies !
Value Of Stored Procedure Output Parameter Is Blank
Here's my stored procedure... --------------------------------------------------------- CREATE PROCEDURE [Get_WirelessProducts_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, ProductID int, ProductCategoryID nvarchar(50), ProductBandwidthKB int, ProductOverridePrice nvarchar(50), UseWirelessOverridePrice bit, DedicationTypeID int, DedicationTypeName nvarchar(50) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID, DedicationTypeName ) SELECT W.ProductID, W.ProductCategoryID, W.ProductBandwidthKB, W.ProductOverridePrice, W.UseWirelessOverridePrice, W.DedicationTypeID, D.DedicationTypeName FROM tblWirelessProducts W INNER JOIN tblDedicationTypes D ON W.DedicationTypeID = .DedicationTypeID --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID DedicationTypeName FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter SELECT @TotalRecords = COUNT(*) FROM tblWirelessProducts GO --------------------------------------------------------- Here is the relevant VB Code: --------------------------------------------------------- Dim parmReturnValue As SqlParameter 'conProducts is already open cmdProducts = New SqlCommand("Get_WirelessProducts_By_Page", conProducts) cmdProducts.CommandType = CommandType.StoredProcedure cmdProducts.Parameters.Add("@CurrentPage", intCurrentPage) cmdProducts.Parameters.Add("@PageSize", dgrdProducts.PageSize) parmReturnValue = cmdProducts.Parameters.Add("@TotalRecords", SqlDbType.int) parmReturnValue.Direction = ParameterDirection.Output conProducts.Open dgrdProducts.DataSource = cmdProducts.ExecuteReader() If Not IsDBNull(cmdProducts.Parameters("@TotalRecords").Value) then Response.Write(cmdProducts.Parameters("@TotalRecords").Value) End If --------------------------------------------------------- The rows are returned correctly, but the output parameter "@TotalRecords" doesn't return anything. Any ideas what I'm doing wrong? Thanks in advance for your help.
View Replies !
How To Execute A Stored Procedure With Output Parameter
Hi guys. I have a procedure with just one parameter. That's a output parameter. It's type is NVARCHAR I think there's no problem with the procedure. Now I want to execute it. Can I declare that variable passed by parameter to the procedure ? Must I use the Execute command ? thanks a lot.
View Replies !
Creating Stored Procedure With Output Parameter
Hi Being a rookie to stored procedures the following cry for help will be posted. The stored procedure below is supposed to create a dynamic string as an output parameter create procedure spGetFieldNameList @TableName varchar(256),@String varchar(8000) output As select @String = @String + sc.name + ',' from syscolumns as sc(nolock) join sysobjects as so(nolock) on sc.id = so.id and so.name = @TableName order by colid asc Creating the stored procedure does not trigger an error. Calling the stored procedure as: spGetFieldNameList 'OfferCondition'. Causes the following message: Server: Msg 201, Level 16, State 4, Procedure spGetFieldNameList, Line 0 Procedure 'spGetFieldNameList' expects parameter '@String', which was not supplied. Calling it this way: declare @String as varchar(8000) set @String = '' set @String = spGetFieldNameList 'OfferCondition'. Causes: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near 'OfferCondition'. Any help is highly appreciated. Thanks
View Replies !
Stored Procedure Output Parameter Issue
Hi, the stored procedure returns only the first letter of the ouput parameter i.e 'e'. spvr_ErrorDescription='error while updating data'. Actually It must return 'error while updating data'. can anybody help me to solve this issue.How to set the size of output parameter in dataaccess layer. Below is the actual code public string UserCostCenter_Upd(string spvp_Offering, string spvp_UserId, string spvp_CostCenter, DateTime spvp_StartDate, DateTime spvp_ExpiryDate, ref string spvr_ErrorDescription) { // The instance ds will hold the result set obtained from the DataRequest DataSet ds = new DataSet(); RequestParameter[] parms = new RequestParameter[7]; parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true); parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString); parms[1].Value = spvp_Offering; parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength); if (spvp_UserId == null) parms[2].Value = DBNull.Value; else parms[2].Value = spvp_UserId; parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString); if (spvp_CostCenter == null) parms[3].Value = DBNull.Value; else parms[3].Value = spvp_CostCenter; parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime); if (spvp_StartDate == null) parms[4].Value = DBNull.Value; else parms[4].Value = spvp_StartDate; parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime); if (spvp_ExpiryDate == null) parms[5].Value = DBNull.Value; else parms[5].Value = spvp_ExpiryDate; parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true); parms[6].Value = spvr_ErrorDescription; // Create an instance of DataSQLClient AirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ; // typDSRef holds the ref. to the strongly typed dataset DataSet typDSRef = (DataSet)ds; DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms); // Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSet DataHelper helper = new DataHelper(); if (!helper.MapResultSet(ref typDSRef, resultDataSet)) ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value; spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value); return spvr_ErrorDescription ; }
View Replies !
Reading An OUTPUT Parameter From A Stored Procedure Into A Variable
Hello, I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J. THE CODE I USE TO CALL THE SPROC Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty) THE STORED PROCEDURECREATE Proc [prs_VerifyUser @regid uniqueidentifier,@regemail nvarchar(250) OUTPUT ASBEGIN IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid) BEGIN SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid Return 1 END Return 2 END
View Replies !
Problem With Output Parameter From Stored Procedure Using SqlDataSource
I am having a problem getting the output parameter from a stored procedure that deletes a record. I use the EXACT same process for the insert and update procs and they work fine. For some reason, the delete proc is either not returning an output parameter, or the SqlDataSource (dsCompany) is not able to get the information in the dsCompany_Deleted method. I simply want the proc to return an output value so that when I eventually get around to checking if the record can be deleted due to foreign key restraints, I can deliver the appropriate message to the user.I have highlighted the line that is giving the problem. I also highlighted a default value in the parameter, that when removed causes the app to crash because it is getting a null value back. Thanks in advance to anyone who can help!STORED PROC (eventually I will add foreign key restraints) ALTER PROCEDURE CompanyDelete @iCompanyId int, @iOutput int OUTPUT AS BEGIN DELETE FROM tblCompany WHERE iCompanyId = @iCompanyId SELECT @iOutput = 1 END CODE BEHIND (abbreviated)protected void btnCompany_Click(object sender, EventArgs e) { dsCompany.InsertParameters["sCompany"].DefaultValue = txtCompany.Text; dsCompany.Insert(); txtCompany.Text = ""; txtCompany.Focus(); } protected void dsCompany_Inserted(object sender, SqlDataSourceStatusEventArgs e) { if ((int)e.Command.Parameters["@iOutput"].Value > 0) { lblMessage.Text = "Insert successful."; } else { lblMessage.Text = "Insert failed. Duplicate record would be created."; } } protected void dsCompany_Deleted(object sender, SqlDataSourceStatusEventArgs e) { if ((int)e.Command.Parameters["@iOutput"].Value > 0) { lblMessage.Text = "Delete successful."; } else { lblMessage.Text = "Delete failed. Other records use this value."; } } protected void dsCompany_Updated(object sender, SqlDataSourceStatusEventArgs e) { if ((int)e.Command.Parameters["@iOutput"].Value > 0) { lblMessage.Text = "Update successful."; } else { lblMessage.Text = "Update failed. Duplicate record would be created."; } } protected void gvCompany_RowEditing(object sender, GridViewEditEventArgs e) { lblMessage.Text = ""; } ASPX (abbreviated)<asp:SqlDataSource ID="dsCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="CompanyDelete" DeleteCommandType="StoredProcedure" InsertCommand="CompanyInsert" InsertCommandType="StoredProcedure" SelectCommand="CompanySelect" SelectCommandType="StoredProcedure" UpdateCommand="CompanyUpdate" UpdateCommandType="StoredProcedure" OnInserted="dsCompany_Inserted" OnDeleted="dsCompany_Deleted" OnUpdated="dsCompany_Updated"> <DeleteParameters> <asp:Parameter Name="iCompanyId" Type="Int32" /> <asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0"/> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="iCompanyId" Type="Int32" /> <asp:Parameter Name="sCompany" Type="String" /> <asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" /> </UpdateParameters> <SelectParameters> <asp:Parameter DefaultValue="1" Name="iZero" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="sCompany" Type="String" /> <asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" /> </InsertParameters> </asp:SqlDataSource>
View Replies !
How To Get The Output Parameter From An Internally Called Stored Procedure???
Hi all,i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane
View Replies !
Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example Select abc, cde, 'xyz' = Case When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter} When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter} end from MyTable Thanks Arcady
View Replies !
Stored Procedure Output Parameter's Issue In Dataaccess Layer
Hi,the stored procedure returns only the first letter of the ouput parameter i.e 'e'. Actually It must return 'error while updating data'. can anybody helpme to solve this issue.How to set the size of output parameter.spvr_ErrorDescription='error while updating data which I get from the output parameter of stored procedure. How to set the size of output parameter in dataaccess layer. Below is the actual code.public string UserCostCenter_Upd(string spvp_Offering,string spvp_UserId,string spvp_CostCenter,DateTime spvp_StartDate,DateTime spvp_ExpiryDate,ref string spvr_ErrorDescription){// The instance ds will hold the result set obtained from the DataRequestDataSet ds = new DataSet();RequestParameter[] parms = new RequestParameter[7];parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true);parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString);parms[1].Value = spvp_Offering;parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength);if (spvp_UserId == null)parms[2].Value = DBNull.Value;elseparms[2].Value = spvp_UserId;parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString);if (spvp_CostCenter == null)parms[3].Value = DBNull.Value;elseparms[3].Value = spvp_CostCenter;parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime);if (spvp_StartDate == null)parms[4].Value = DBNull.Value;elseparms[4].Value = spvp_StartDate;parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime);if (spvp_ExpiryDate == null)parms[5].Value = DBNull.Value;elseparms[5].Value = spvp_ExpiryDate;parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true);parms[6].Value = spvr_ErrorDescription;// Create an instance of DataSQLClientAirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ;// typDSRef holds the ref. to the strongly typed datasetDataSet typDSRef = (DataSet)ds;DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms);// Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSetDataHelper helper = new DataHelper();if (!helper.MapResultSet(ref typDSRef, resultDataSet))ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value;spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value);return spvr_ErrorDescription ;}
View Replies !
How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?
Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo
View Replies !
Retrieve An Output Parameter In Stored Procedure From Java Application
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View Replies !
How To Display Return Value From Stored Procedure Output Parameter In Query Analyzer
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure: CREATE PROCEDURE UserLogin ( @Email nvarchar(100), @Password nvarchar(50), @UserName nvarchar(100) OUTPUT ) AS SELECT @UserName = Name FROM Users WHERE Email = @Email AND Password = @Password GO If I run the query in Query Analyzer directly, I can display @UserName: DECLARE @UserName as nvarchar(100) SELECT @UserName = Name FROM Users WHERE Email = @Email AND Password = @Password Select @UserName But how can I display @UserName if I call stored procedure: exec UserLogin 'email', 'password', '' I believed it return int, right? Thanks in advance for help.
View Replies !
Calling A Stored Procedure That Inserts Records And Generates An Output Parameter
I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure. How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.
View Replies !
SQL Server 2005 JDBC Driver Output Parameter/Result Set Issue
I'm having an issue with the JDBC driver when I execute a stored procedure that both has a return value and also returns a result set. If I attempt to retrieve the return value (registered as an output parameter) after I execute the stored procedure, then any subsequent attempts to retrieve the result set always return null. Is this by design? If I use the result set first and then later get the return value that works; however, in my situation I need to first check the return value before I work on the result set. Am I'm I doing something wrong? Code: CallableStatement cs = connection.prepareCall("{? = call spGetCustomer(?, ?) }"); cs.registerOutputParameter(1, Types.INTEGER); cs.setString(2,"blahblahblah"); cs.setBoolean(3,false); cs.execute(); int retVal = cs.getInt(1); ResultSet rs = cs.getResultSet(); // Always returns null, even though the SP actually returns a result set.
View Replies !
Can I Create A 'Top N' Statement Within A Stored Procedure Using A Parameter?
In a 'Top n' type statement I wish to be able to insert the n valuefrom a parameter, within a stored precedure egHaving declared @pageSize as a parameter I want to run the followingtype of query :SELECT DISTINCT TOP @pageSize routeID, routeName FROMtblRoute_HeaderWhen I attempt to do so I get an error mesage indicating incorrectsyntax. I do not get an error message if I specify 'n' directly as inTOP 10Am I missing something or is this not possible within a storedprocedure?Best wishes, John Morgan
View Replies !
Execute SQL Task – Output Parameter On Stored Procedure Causes Task To Fail.
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
Using A Cursor Output From Stored Proc
Has anyone ever tried to use a cursor as an output variable to a stored proc ? I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc @parentId integer, @outputCursor CURSOR VARYING OUTPUT AS BEGIN TRAN T1 DECLARE parent_cursor CURSOR STATIC FOR SELECT parentTable.childId, parentTable. parentValue FROM parentTable WHERE parentTable.parentId = @parentId OPEN parent_cursor SET @outputCursor = parent_cursor DECLARE @childId int DECLARE @parentValue varchar(50) FETCH NEXT FROM parent_cursor INTO @childId, @parentValue WHILE @@FETCH_STATUS = 0 BEGIN SELECT childTable.childValue FROM childTable WHERE childTable.childId = @childId FETCH NEXT FROM parent_cursor INTO @childId, @parentValue END CLOSE parent_cursor DEALLOCATE parent_cursor COMMIT TRAN T1 GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself. My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter() da.SelectCommand = New SqlCommand("myStoredProc", conn) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int) paramParentId.Value = 1 Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor") paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas? Thanks Martin
View Replies !
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
I am working on an OLAP modeled database. I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff). I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails. The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output. What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column? I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it. Thanks in advance for any assistance you can provide.
View Replies !
How Do I Get A Procedure OUTPUT-parameter...
In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT... however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable... this is the sp: CREATE PROCEDURE spInsertNews @uidArticleId uniqueidentifier OUTPUT, @strHeading nvarchar(300), @strAbstract nvarchar(600), @strText nvarchar(4000), @dtDate datetime, @dtDateStart datetime, @dtDateStop datetime, @strAuthor nvarchar(200), @strAuthorEmail nvarchar(200), @strKeywords nvarchar(400) AS SET @uidArticleId = newid() INSERT INTO tblArticles VALUES(@uidArticleId ,@strHeading,@strAbstract,@strText,@dtDate,@dtDateStart,@dtDateStop,@strAuthor,@strAuthorEmail,@strKeywords) my asp code is something like this: ... SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon); sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strHeading", SqlDbType.NVarChar, 300)); sqlcmdInsertNewsArticle.Parameters["@strHeading"].Value = strHeading.Text; sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strAbstract", SqlDbType.NVarChar, 600)); sqlcmdInsertNewsArticle.Parameters["@strAbstract"].Value = strAbstract.Text; sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strText", SqlDbType.NVarChar, 4000)); sqlcmdInsertNewsArticle.Parameters["@strText"].Value = strText.Text; ... sqlcmdInsertNewsArticle.Connection.Open(); sqlcmdInsertNewsArticle.ExecuteNonQuery(); sqlcmdInsertNewsArticle.Connection.Close(); How do I do if I want to catch the OUTPUT-parameter (@uidArticleId)? anyone?
View Replies !
Multiple Output Parameter From Procedure
I have a sqlserver stored procedure with multiple output parameter like below. I am not sure how I can read the two output parameters at my VB.net code. I am trying to use ExecuteScalar from VB.net and it is throwing an exception. Procedure:CREATE PROCEDURE dbo.spGetUser @UserLoginId VARCHAR(80), 'E-mail of user @Password VARCHAR(50), 'Password in hash @UserId INT=NULL OUTPUT, 'system generated user id@UserType CHAR(2)=NULL OUTPUT 'type of the user AS BEGIN .......... END VB Code: Inside my business layer class =========================== TryWith objSQLCommand .CommandType = CommandType.StoredProcedure .CommandText = "spGetUser".Parameters.AddWithValue("@UserLoginId", UserLoginId) .Parameters.AddWithValue("@Password", Password).Parameters.AddWithValue("@UserId", UserId) .Parameters.AddWithValue("@UserType", UserType).Parameters("@UserId").Direction = ParameterDirection.Output .Parameters("@UserType").Direction = ParameterDirection.Output End With bolReturn = mobjDataAccess.Execute(objSQLCommand) If bolReturn Then GetDetail = True End If ========================== Inside my dataaccess layer class =========================== Public Function Execute(ByVal objSQLCommand As SqlCommand) As Boolean Execute = False Dim intReturn As Integer ' 0 = Success, Otherwise Error intReturn = 1 TryCall Connect()With objSQLCommand .Connection = mobjConnection .ExecuteScalar() End WithCall Disconnect() If intReturn = 0 Then Execute = TrueEnd If Catch ex As ExceptionCall Disconnect() ex.Source = "Execute" Execute = False End Try End Function
View Replies !
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source? Something like this: CREATE PROCEDURE TestHardDisk AS BEGIN DECLARE CURSOR HardDisk_Cursor FOR Exec xp_FixedDrives -- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source OPEN CURSOR HardDisk_Cursor FETCH NEXT FROM HardDisk_Cursor INTO @Drive, @Space WHILE @@FETCH_STATUS = 0 BEGIN ... END END
View Replies !
Creating A Store Procedure With Output Parameter
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
View Replies !
Output Parameter Not Fetched From Dynamic T-SQL Procedure
I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer. But when I try to get values in asp.net page it does not return anything. No execeptions or errors also. Below is the code snippet HDConn = CommonMethods.BuildConnection();SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);cmd1.CommandType = CommandType.StoredProcedure;// add parameters for Proccmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;try{cmd1.ExecuteNonQuery();//the below line does not print the valueResponse.Write(cmd1.Parameters["@rowidval"].Value.ToString());}My stored proc looks like :CREATE PROCEDURE dbo.GenerateRowids@TableName varchar(50),@ColumnName varchar(50),@rowidval varchar(30) outputASBegindeclare @sql nvarchar(4000), @Idval varchar(50) set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableNameexec sp_executesql @query = @sql,@params = N'@rowidval varchar OUTPUT', @rowidval = @rowidval OUTPUT ENDGO
View Replies !
Gettings Warnings Sent From A Stored Procedure Called With Jdbc
When running a stored procedure, how can i retrieve the warnings that are issued within the stored procedure? the code used is below, the jdbc is connecting fine, it is running the stored procedure, but when an error is raised in the stored procedure, it is not coming back into s.getwarnings() warning raised in stored proc with Code Block RAISERROR ('Error', 16, 1)with nowait; there are no results for the stored procedure, I am just wanting to get the warnings Code Block Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection con = DriverManager.getConnection("jdbc:sqlserver://localh...........); CallableStatement s = con.prepareCall("{call procedure_name}"); s.execute(); //running in seprate thread SQLWarning warn = s.getWarnings(); while (m.running) { if(warn == null) { warn = s.getWarnings(); } safeOut(warn); if(warn != null) warn = warn.getNextWarning(); m.wait(100); }the code is not complete, but should show what is happening it is continually outputting null for the warning, though the strored proc is definately raising errors, which is proven by running it in a query window in sql server. it is retreiving warning if the statement is a raiseerror instead of a call to the proc Code Block CallableStatement s = con.prepareCall("RAISERROR ('Error', 1, 1)with nowait;"); this thread is quite related, but doesnt offer a working solution Getting messages sent while JDBC Driver calls stored procedure any help much appreciated, thankyou Simon
View Replies !
Getting Messages Sent While JDBC Driver Calls Stored Procedure
Hi. How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver? I need to get my own debug messages (done through the print() function) and also standard server messages (such as "x row(s) affected" or results from SET STATISTICS TIME ON). Is this possible? Many thanks. Carlos
View Replies !
|
TRACKER
