SSIS Script Component Call Stored Procedure returns -1

Question

I tried implementing a call to Stored proc and the proc returns ID which will used later. Everytime I execute I get the out parameter as -1. Below is my sample code:

        OleDbCommand sqlStrProc = new OleDbCommand();
        sqlStrProc.Connection = dbConn;
        sqlStrProc.CommandText = "dbo.insert_test";
        sqlStrProc.CommandType = CommandType.StoredProcedure;

        sqlStrProc.Parameters.Add("@p_TestID", OleDbType.Integer, 255).Direction = ParameterDirection.Output;

        sqlStrProc.Parameters.Add("@p_TestName", OleDbType.VarChar).Value = "Test";
        sqlStrProc.Parameters.Add("@p_CreatedBy", OleDbType.VarChar).Value = "Test";


        int personID = sqlStrProc.ExecuteNonQuery();
        Row.outPersonID = personID;

personID is always -1. What am I doing wrong here. Please help..!! Below is the stored proc code

CREATE PROCEDURE [dbo].[INSERT_TEST]
     @p_TestID int OUTPUT,
     @p_TestName varchar (50),
     @p_CreatedBy varchar (100)
AS
SET NOCOUNT ON


INSERT INTO Test(
    TestName,
    CreatedBy)
VALUES
(    @p_TestName,
    @p_CreatedBy)

SELECT @p_TestID = SCOPE_IDENTITY()

Does the SP actually insert a record as expected? To shed some light (and learn something new), follow the instructions here to observe your stored procedure in action. http://www.techrepublic.com/article/step-by-step-an-introduction-to-sql-server-profiler/ You should be able to verify that your code is sending the correct parameters, and your stored procedure is executing correctly. — Nick.Mc, Mar 19 '14 at 01:57

score 0 · Answer 1 · answered Mar 18 '14 at 17:38

-1 could mean that the stored procedure failed to execute as desired and the transaction was rolled back. You may want to look for any truncation issues since you have different sizes for the 2 input parameters but are using the same input. Also I assume you have proper code to open and close connections etc?

score -1 · Answer 2 · answered Mar 19 '14 at 09:52

-1 returned value is an error produced during the execution of your SP, this is due to the following reasons:

SP Structure: everytime you are executing the SP it tries to create it again while it already exists. so you have to either make it an ALTER PROCEDURE instead of CREATE PROCEDURE or do the following:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[INSERT_TEST]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[INSERT_TEST] GO

CREATE PROCEDURE [dbo].[INSERT_TEST] @p_TestID int OUTPUT, @p_TestName varchar (50), @p_CreatedBy varchar (100) AS
Database Connection (Table Name and Location): you have to specify withe the OLEDB the ConnectionString that connects you to the write DB. try to test the full Table path; like the following;

INSERT INTO [DATABASENAME].[SHCEMA].[TABELNAME]( Name, CreatedBy) VALUES ( @p_TestName, @p_CreatedBy)
Define your SP as :

CREATE PROCEDURE [NAME]

AS BEGIN END

thought it is not a problem, but it is a proper way to write your SPs in terms of connection transactions,

Let me know if it works fine with you :)

Regrads, S.ANDOURA

I respectfully disagree with most of those points. 1. The is calling `dbo.INSERT_TEST`. The CREATE/ALTER issue described here has nothing to do with the return value. 2. Yes this could conceivably fix the error 3. This is misleading. `BEGIN` `END` has to do with code blocks and has nothing to do with 'connection transactions' (do you just mean transactions here?) Perhaps you are getting confused with `BEGIN TRAN` — Nick.Mc, Mar 20 '14 at 02:24
Hey @ElectricLlama, I do appreciate your kind feedback, thank you, the post will be edited. and regarding what you said, yes POINT 2 is the solution for it. — Ando, Mar 20 '14 at 15:36

SSIS Script Component Call Stored Procedure returns -1

2 Answers2