Issue with Data Transform Task 1

[SajidAttar]

Can I use "Exec sp_MySP" in SQL Query box of Data Transform Task's Source Tab?

When I use "Select * from tbl_MyTable" it works FINE, but I want to use result from one of my Stored procedures.

I tried to use "Exec sp_MySP", it thrown an error like "INVALID POINTER".

I AM SURE YOU HAVE A SOLUTION FOR THIS, PLEASE SUGGEST.

Thanks in advance,

Sajid A.

 

[JayKusch]

See if adding the database name helps as in ...

EXEC MyDatabase.dbo.sp_MySP

Thanks

J. Kusch

 

[SajidAttar]

JayKusch,

I tried that also could not worked. It is giving the same error "Invalid Pointer".

What this error actually for . . .

Thanks,

Sajid A

 

[JayKusch]

So I can assume your Connection SOURCE object is a SQL Server instance ... what is your Connection DESTINATION object?

Thanks

J. Kusch

 

[SajidAttar]

Everything was correct.

When I tried with Select Statement it was working fine, but was not working with a particular SP.

My Coleague put the statement 'Set NoCount on' in SP and it worked fine.

Thanks,

Sajid A.

 

[Middlej]

Thanks for the input on this matter -- the SET NOCOUNT ON option worked for my problem -- outputting data from a stored proc to a semicolon-delimited text file.

Thank you,

Joe Middleton