Error:Calling procudure outside Management Studio

Question

Error:Calling procudure outside Management Studio

MastahYoda

Valued Member

Points: 61
More actions
March 15, 2010 at 8:28 am

#219871

Hi
I have a procedure which works perfect when I call it via Management Studio. However when I try to execute it for example as a job or inside SSIS-package, I get the following error: (this error is from SSIS, but almost the same error appears when I run it as a job)
[Execute SQL Task] Error: Executing the query "
DECLARE @rc int
EXECUTE @rc = [db_name].[dbo].[proc_name]
GO
[..." failed with the following error: "Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why executing a procedure which doesn't include any subqueries gives this error?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

IT would seem that your job has a subquery that returns more than one record in a place where that's not allowed.

Perhaps you could post the query and some DDL and sample data for the tables in question? See the first link in my signature.

When your procedure is called from a job or SSIS you are more than likely passing in different parameters than when you call it from Management studio, hence causing the issues with the subquery.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

Lukes right...that proc is returning more than one value;

run the proc all by itself, or try this as a proof of concept:

EXECUTE [db_name].[dbo].[proc_name]

--or capture the results to a table:

DECLARE @rc TABLE(MyVal int)

INSERT INTO @rc

EXECUTE [db_name].[dbo].[proc_name]

SELECT * FROM @rc --multiple rows?!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MastahYoda Valued Member Points: 61 More actions · Answer 3

Thanks a lot for answers! There was a multivalue set operation to one of local variables after all. I fixed that and now procedure call works fine as a job and as in SSIS packege. I'm still a bit confused why Management Studio was able to process also the original procedure. But that doesn't really matter so much anymore.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

MastahYoda (3/16/2010)
Thanks a lot for answers! There was a multivalue set operation to one of local variables after all. I fixed that and now procedure call works fine as a job and as in SSIS packege. I'm still a bit confused why Management Studio was able to process also the original procedure. But that doesn't really matter so much anymore.

If you're still interested in why, the easiest way would be to take a trace of it run in SSMS and once as the SSIS Package. You'll most likely see that your parameters were different therefor causing your subquery to return different data.

In short you weren't making an apples to apples comparison but rather perhaps apples and pitchforks.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]