November 21, 2016 at 1:42 pm
I have an Execute Sql Task where I am using this SP.
CREATE PROCEDURE [dbo].[InsData]
@DB VARCHAR(20),
@FeT VARCHAR(20),
@SType VARCHAR(50),
@Id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Emp(FeT,DB,SType,StrtDate)
VALUES(@FeT, @DB, @SType, getdate())
SELECT @Id = SCOPE_IDENTITY()
RETURN @Id
END
At the time of executing execute sql task I am facing the error as " input string was not in correct format but execution method was successful its going and inserting into Emp table..
In Genral tab under sql statement of Execute sql task I am using
Exec dbo.InsData ?,?,?,? OUTPUT
In Paramter mapping - created varaibles for first 3 paramters and marked direction as input and last using User::id make I marked direction as output
Not sure where I am doing mistake any inputs please?
November 21, 2016 at 2:14 pm
mcfarlandparkway (11/21/2016)
I have an Execute Sql Task where I am using this SP.CREATE PROCEDURE [dbo].[InsData]
@DB VARCHAR(20),
@FeT VARCHAR(20),
@SType VARCHAR(50),
@Id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Emp(FeT,DB,SType,StrtDate)
VALUES(@FeT, @DB, @SType, getdate())
SELECT @Id = SCOPE_IDENTITY()
RETURN @Id
END
At the time of executing execute sql task I am facing the error as " input string was not in correct format but execution method was successful its going and inserting into Emp table..
In Genral tab under sql statement of Execute sql task I am using
Exec dbo.InsData ?,?,?,? OUTPUT
In Paramter mapping - created varaibles for first 3 paramters and marked direction as input and last using User::id make I marked direction as output
Not sure where I am doing mistake any inputs please?
I suggest that you change this
SELECT @Id = SCOPE_IDENTITY()
RETURN @Id
to this
SET @Id = SCOPE_IDENTITY()
You don't need to RETURN it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 21, 2016 at 3:03 pm
We need this Id to return we are using in another Sp as imput. to update Emp table with this Id.
November 21, 2016 at 3:06 pm
I changed from Select to SET.
Still its showing error
"Input string was not in a correct format.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
November 21, 2016 at 4:20 pm
mcfarlandparkway (11/21/2016)
We need this Id to return we are using in another Sp as imput. to update Emp table with this Id.
I understand that, but OUTPUT parameters in a proc do not need the RETURN keyword, all you need to do is set their value within the proc.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 21, 2016 at 4:23 pm
mcfarlandparkway (11/21/2016)
I changed from Select to SET.Still its showing error
"Input string was not in a correct format.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
If you call this proc from SSMS, does it work OK?
If yes, try setting a breakpoint on the ExecuteSQL task and then, when execution pauses, check the actual values of all the parameters. Try feeding these values into SSMS, as above.
If this works, there's something about the config of the ExecSQL task which has gone awry, but please start with these two tasks.
--Edit: fixed typo
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 21, 2016 at 4:49 pm
I didn't understood this point
I understand that, but OUTPUT parameters in a proc do not need the RETURN keyword, all you need to do is set their value within the proc. Do I need to comment Return Statement in my Proc then?
This proc from SSMS is working fine. but to see what values passing in these paramters in package how to debug it?
How to debug Execute Sql task I mean how I can keep breakpoint there I don't see any options?
November 21, 2016 at 5:14 pm
mcfarlandparkway (11/21/2016)
I didn't understood this pointI understand that, but OUTPUT parameters in a proc do not need the RETURN keyword, all you need to do is set their value within the proc. Do I need to comment Return Statement in my Proc then?
This proc from SSMS is working fine. but to see what values passing in these paramters in package how to debug it?
How to debug Execute Sql task I mean how I can keep breakpoint there I don't see any options?
As per my earlier post, yes, remove the RETURN.
Click once on the ExecSQL task and then press F9. You should see a red dot appear on the task - this is, by default, a pre-execute breakpoint. It will pause execution just before the task executes and allow you to view the runtime contents of local variables.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 21, 2016 at 6:27 pm
Yeah, TY
I have commented that return statement and kept a break point on Execute sql task. I can see all the values which I am passing to that SP for Id its returning the value as 0.
But for Task which I kept in sql statement as well as in parameter mapping is fine? because its facing that error.
In result set I am not using any variable.
November 22, 2016 at 5:41 am
mcfarlandparkway (11/21/2016)
Yeah, TYI have commented that return statement and kept a break point on Execute sql task. I can see all the values which I am passing to that SP for Id its returning the value as 0.
But for Task which I kept in sql statement as well as in parameter mapping is fine? because its facing that error.
In result set I am not using any variable.
I'm sorry, but I do not understand what you are trying to describe here.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply