Return value Stored Procedure SQL Task
-
Hi,
I'am have trouble executing a SQL task. I use the following simple SP:
ALTER PROCEDURE
dbo.sp_InsertDateImage
AS
BEGIN
SET NOCOUNT ON
Insert into DataImage (Image) values (null)
RETURN @@IDENTITY
END
I have set the following properties in the SQL task:
SQL statement: EXEC ?=dbo.sp_InsertDateImage
ResultSet: None
ConnectionType: OLE DB
In the Parameter Mapping section I have added a row with following values:
User::ImageKey, ReturnValue, Numeric, @pKey
When I run the script the following error appears:
[Execute SQL Task] Error: Executing the query "EXEC ?=dbo.sp_InsertDateImage" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Does anybody has a clue what the problem is.
-
Just change the name of the parameter to '0' (no quotes).
Check here from the BOL:http://msdn2.microsoft.com/en-us/library/ms141003.aspx
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I'm sorry but don't understand what you mean.
I have changed in the Parameter Mapping section the row with
User::ImageKey, ReturnValue, Numeric, 0
But still it doesn't work.
I'm going slighty mad.
-
I found the problem and it is a bug
But there is a workaround.http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQLTaskAndParameterOddities.html
-
I found the difference. I'm calling the procedure differently. You're using the TSQL standard of '? = EXEC proc' instead of '{? = CALL proc}'
I found out why that's a difference here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefsqlprovspec.asp
Try that out and we'll see. I added a section to the WIKI on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I'm beginning to the think the software has failed. I can't seem to post a reply to this topic properly.
The error is in how the proc is called. If you call it this way 'EXEC ? = proc' it fails. If you call it this way '{? = CALL proc}' it succeeds.
Here's the MS documentation on why: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefsqlprovspec.asp
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Yep. This also a solution.
Tnx again.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply