Capture Return Value From Stored Procedure, Use Same In Code Behind Page
Apr 18, 2007
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 Procedure
CREATE 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 @@rowcount
BEGIN
-- See if a contact with the same name and zip code exists
IF EXISTS (Select * FROM Info
WHERE ID = @ID)
BEGIN
RETURN 1
END
ELSE
BEGIN 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
END
GO
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>
View 2 Replies
ADVERTISEMENT
Jun 2, 2006
how do i return the identity from stored procedure to asp.net code behind page?
CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name)
DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber
code behind:
public void _Insert(
string _Name,
{
DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId);
db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null);
db.ExecuteNonQuery(dbCommand);
int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber");
whats wrong with to the above code?
View 2 Replies
View Related
May 19, 2006
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
Thanks in advance
Dave
View 6 Replies
View Related
Apr 12, 2005
Does anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
Thank you all!
View 6 Replies
View Related
Feb 12, 2008
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
View 2 Replies
View Related
Jan 18, 2008
Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.
View 4 Replies
View Related
Jun 28, 2006
Hi
I was transferring objects of my existing database from remote to local and I recieved this error during the DTS process. It was on the transfer of the following stored procedure. I tried to create this SP manually but its giving me the same error and doesn't create it. I don't know how to fix it.
I wonder if somebody can help me! Thanks in Advance
[450] Code page translations are not supported for the text data type. From: 1252 To: 1256.
CREATE PROCEDURE dbo.Novasoft_DL_UpdateDownloadsRating @RatingID int, @DownloadID int, @UserID int, @Rating int, @Comment text, @ReviewDate datetime, @CommentName nvarchar(50) ASUPDATE dbo.Novasoft_DL_DownloadsRating SET [DownloadID] = @DownloadID, [UserID] = @UserID, [Rating] = @Rating, [Comment] = @Comment, [ReviewDate] = @ReviewDate, [CommentName] = @CommentNameWHERE [RatingID] = @RatingID
GO
View 1 Replies
View Related
Sep 19, 2007
My stored proceddure "My Programs" includes a parameter @meid.How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?I tried the following codes, but the error message indicated it can not findthe Stored Procedure. How do I pass the EmpID as a Stored Procedure parameter?
<%meid = EmpIdcmd.CommandText = "MyPrograms meid"cmd.CommandType = CommandType.StoredProcedurecmd.Connection = sqlConnection2sqlConnection2.Open()reader3 = cmd.ExecuteReader(meid)While reader3.Read()sb.Append(reader3(i).ToString() + "......<BR> <BR /> ")End While%>
TIA,Jeffrey
View 3 Replies
View Related
May 1, 2007
Hi !
I have a problem with the unique identifier and don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
Thanks!!
View 2 Replies
View Related
Jun 11, 2015
We are executing a SSIS package using a xp_cmdshell command in a SP as shown below. This package does consumes time to execute almost 90 minutes and does get executed successfully too. But the strange thing is we don't get the result in @result variable just because somehow the next sql statement after the below highlighted statement doesn't get executed at all. Â After checking execution stats for the SP using the query attached below we observed that somehow the SP vanishes out of the execution stats for the server.
 SELECT @cmd = 'dtexec /FILE "D:Program FilesMicrosoft SQL Server100DTSPackages.....PopulateReport.dtsx"'     Â
 SELECT @cmd = @cmd + ' /Decrypt T@!0er '     Â
 SELECT @cmd = @cmd + ' /set package.variables[vAppID].Value;' + CONVERT(VARCHAR(10),@appId)     Â
 SELECT @cmd = @cmd + ' /set package.variables[vDBName].Value;' + '"' + @db + '"'     Â
 SELECT @cmd = @cmd + ' /set package.variables[vBuildMFF].Value;' + CONVERT(VARCHAR(10),@BuildMFF)     Â
Â
[code]....
View 6 Replies
View Related
Jan 28, 2008
Is there a way to capture Stored Procedure Name along with the Parameter passed in SQL 2005 or SQL2000 when a stored procedure is executed?
Thanks !
View 6 Replies
View Related
Dec 5, 2006
Greetings,
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.
Thanks,
BCB
View 6 Replies
View Related
Dec 19, 2007
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.
View 2 Replies
View Related
May 30, 2007
Greetings,
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?
Thanks,
BCB
View 7 Replies
View Related
Oct 1, 2007
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
View 4 Replies
View Related
Sep 28, 2007
Hi all!!
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)".
View 3 Replies
View Related
Jan 19, 2015
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.
View 2 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Jun 6, 2008
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
View 2 Replies
View Related
Feb 24, 2008
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
View 2 Replies
View Related
Oct 30, 2005
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
View 7 Replies
View Related
May 30, 2007
I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:
Code Snippet
Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.
Task failed: SendDataToAs400 1
Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Load_ELEP.dtsx" finished: Failure.
I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.
View 3 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Aug 19, 2015
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
View 17 Replies
View Related
Nov 20, 2007
Hi guys I know this is a really common question, and I have read loads of replies on it but everything I try does not work. I have written a small stored procedure in SQL server to upload images to a table and return the new ID using scope_identity. I have tested it and it works fine. here it is:******* @siteID numeric(18,0), @imgNum numeric(18,0), @title NVarchar(50), @MIMEtype nchar(10), @imageData varbinary(max)ASBEGINSET NOCOUNT ONdeclare @imageID intINSERT INTO [site_images] ([img_siteID], [img_num], [img_title], [img_MIME], [Img_Data]) VALUES (@siteID, @imgNum, @Title, @MIMEType, @ImageData) SET @imageID = SCOPE_IDENTITY()RETURN @imageIDSET NOCOUNT OFF************If I run this in management studio express it runs fine and returns the ID under 'return value'. The problem I have is trying to actually call that return value in VB. If I try using these lines:Dim returnParam As SqlParameterreturnParam = New SqlParameter("@imageID", SqlDbType.UniqueIdentifier)returnParam.Direction = ParameterDirection.OutputcmdTest.Parameters.Add(returnParam)withcnBKTest.Open()cmdTest.ExecuteNonQuery() imageIDparam = returnParam.value.toStringcnBKTest.Close() I get the error "procedure has too many arguments specified"And if I try to access the return value like this: imageIDparam = cmdTest.Parameters("@return_value").ValueI get the error "@return_value is not contained by this sqlparametercollection" What am I doing wrong? Any help would be greatly appreciated. Robsa
View 3 Replies
View Related
Nov 28, 2007
I have written this stored procedure but I get no return value (neither 0 nor 1). What I hope is when the transaction successful, return value 1. If fails, return value 0.1 set @TransactionOk = 0
2
3 BEGIN TRAN
4
5 UPDATE WhiteList_IMEI SET WhiteList_IMEI_Used = 1, Whitelist_IMEI_UsedDate = getdate()
6 WHERE WhiteList_IMEI_Code = @IMEICode_New
7
8 IF @@ERROR <> 0
9 BEGIN
10 ROLLBACK TRAN
11
12 PRINT ('Error. Contact Software Engineer.')
13 RETURN
14 END
15
16 COMMIT TRAN
17 set @TransactionOk = 1
View 6 Replies
View Related
May 16, 2008
Hi,
I have been trying to this this for quite a while with no joy can someone please tell me the error of my ways. I am trying to add a new record by stored procedure, this I can do, but my problem lies with the returnvalue part of the procedure. I cannot get it to work. When I debug it tells me that the "Specified cast is not valid" see C# code as i comment the line where it errors. I enclose a sample stored procedure and its c# code. Please can someone tell me where I am going wrong? as this is annoying me alot
SQL:create procedure SPUAddVehicleInsert
@VehicleDetailsRegistrationNumber varchar(50),
@VehicleDetailsMake varchar(50),
@VehicleDetailsModel varchar(50),
@NID bigint =null
as
insert into tblvehicledetails
(
VehicleDetailsRegistrationNumber,
VehicleDetailsMake,
VehicleDetailsModel,
)
values
(
@VehicleDetailsRegistrationNumber,
@VehicleDetailsMake,
@VehicleDetailsModel,
);
select @NID = scope_identity();c# code on sqldatasource: protected void dsAddVehicle_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
Int64 VID = (Int64)e.Command.Parameters["NID"].Value; //errors with specified cast is invalid
Response.Redirect("details.aspx?VID=" + VID.ToString());
}
protected void dsAddVehicle_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
SqlParameter p = new SqlParameter("NID", SqlDbType.BigInt);
p.Direction= ParameterDirection.ReturnValue;
e.Command.Parameters.Add(p);
}
sql datasource:<asp:SqlDataSource ID="dsAddVehicle" runat="server" ConnectionString="<%$ ConnectionStrings:National %>"
InsertCommand="SPUAddVehicleInsert" InsertCommandType="StoredProcedure" SelectCommand="SPUAddVehicleSelect"
SelectCommandType="StoredProcedure" OnInserted="dsAddVehicle_Inserted" OnInserting="dsAddVehicle_Inserting">
<InsertParameters>
<asp:Parameter Name="VehicleDetailsRegistrationNumber" Type="String" />
<asp:Parameter Name="VehicleDetailsMake" Type="String" />
<asp:Parameter Name="VehicleDetailsModel" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
View 4 Replies
View Related
Jun 20, 2005
I have a stored procedure that returns an integer value, declared as:Public Function MyProc(..) As Int32 . . Return <integer>End FunctionI return an integer value, and can do this and get the value returned from the method, as such:declare @rc intexec @rc = dbo.MyProc <params>select @rcThis returns the value; how do I return the value to code and get the value; I've been debugging and that is my problem, I can't get the value to return.Thanks a lot.
View 2 Replies
View Related
Nov 20, 2005
Hello all of members, I have written a Stored Procedure.that creates a new account and then returns a value witch displays a result to me.if result is 1 "Username already exists" or 2 "E-Mail already exists".I did it with "Return" instruction.But, I don't know how can I get the returned value in ASP.NET(VB.NET)? Please help me. Thanks in advance
View 3 Replies
View Related
Nov 25, 2005
Hi all,Is there anyway to get a returned value from a called Stored Procedure from within a piece of SQL? For example, I have the following code...DECLARE @testval AS INTSET @testval = EXEC u_checknew_dwi_limits '163'IF (@testval = 0)BEGIN PRINT '0 Returned'ENDELSEBEGIN PRINT '1 Returned'END...which
as you can see calls a SP called 'u_checknew_dwi_limits'. This SP
(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want to
assign that value to the '@testval' variable (as you can see in my
code) - but Query Analyser is throwing an error at me. Is this the
correct way to do this?ThanksTryst
View 2 Replies
View Related
Apr 28, 2006
I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end: Return Scope_Identity()
I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.
I have intReturn defined as an integer
I've tried :Dim retValParam As OleDbParameter = cmd.Parameters.Add("@RETURN_VALUE", OleDbType.Integer)retValParam.Direction = ParameterDirection.ReturnValueintReturn=cmd.Parameters("@RETURN_VALUE").Value
whenever I add this section - I get an error that there are too many arguments for the sProc.
I've tried:intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.
What is the best way to do this in the code part (VB.Net)?
Thanks ahead of time
View 2 Replies
View Related