April 26, 2011 at 5:52 am
I have a stored procedure
create procedure ErrorMail_sp
@JobName varchar(50),
@TableName varchar(50),
@Issue varchar(MAX)
AS
BEGIN
declare @desc varchar(MAX)
set @desc= ' Job Name : ' + @JobName + ' Table Name : '+ @TableName+ ' Issue Description : '+ @Issue
EXECUTE msdb.dbo.sp_notify_operator @name=N'Mail_Operator',@subject=N'Alert - Error Record(s) found',@body=@desc
END
GO
exec ErrorMail_sp 'SomeText',''SomeText','SomeText' --> works find in Management studio.
But I am trying to execute this procedure through an oledb command in Data Flow task like this :
exec ErrorMail_sp ?,?,?
But when I refresh I get the following error in oledbcommand task:
An oledb record is available. Source: "Microsoft SQL Server Native Client 10.0".HResult: 0x80004005 Description: "Could not find Stored procedure".
Unable to retrieve destination column descriptions from the parameters of the sQL command SSIS oledb command sql stored procedure
The stored procedure exists. because even if I miss one '?' for parameter, it prompts for paramete correctly. Which means it recognizes the stored procedure, but there is some other problem which I am not able to rectify. :crying:
Any ideas from you?
Thnks in Advance.
April 26, 2011 at 6:04 am
I never had that issue. I would like to see the code you are using to call the sp.
Also varchar(50) for job name and table name is too short. I'd use sysname for both of them to never have any problem and then nvarchar(max).
Also have you tried calling the sp like this? >>> dbname.owner.spname
April 26, 2011 at 6:10 am
I get the error even if I try to execute a stored procedure without any parameters.
Yes I have tried the way you have mentioned.
April 26, 2011 at 6:28 am
I have nothing more to offer... I'm sure someone else will figure it out for you...
good luck.
April 26, 2011 at 8:05 am
BackT0Work (4/26/2011)
I have a stored procedurecreate procedure ErrorMail_sp
@JobName varchar(50),
@TableName varchar(50),
@Issue varchar(MAX)
AS
BEGIN
declare @desc varchar(MAX)
set @desc= ' Job Name : ' + @JobName + ' Table Name : '+ @TableName+ ' Issue Description : '+ @Issue
EXECUTE msdb.dbo.sp_notify_operator @name=N'Mail_Operator',@subject=N'Alert - Error Record(s) found',@body=@desc
END
GO
exec ErrorMail_sp 'SomeText',''SomeText','SomeText' --> works find in Management studio.
But I am trying to execute this procedure through an oledb command in Data Flow task like this :
exec ErrorMail_sp ?,?,?
But when I refresh I get the following error in oledbcommand task:
An oledb record is available. Source: "Microsoft SQL Server Native Client 10.0".HResult: 0x80004005 Description: "Could not find Stored procedure".
Unable to retrieve destination column descriptions from the parameters of the sQL command SSIS oledb command sql stored procedure
The stored procedure exists. because even if I miss one '?' for parameter, it prompts for paramete correctly. Which means it recognizes the stored procedure, but there is some other problem which I am not able to rectify. :crying:
Any ideas from you?
Thnks in Advance.
Never encountered this problem, however can you check the 'External columns' (Input and Output Columns) of your OLEDB Component? You should find three parameters...
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
April 27, 2011 at 12:02 am
Nope. Thats the problem. No parameters are created in Input/Output columns. 🙁
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply