Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jayaseelan
Starting Member
6 Posts |
 Posted - 2008-11-11 : 23:21:02
|
| I am creating a extract file by executing the procedure and outputting the results to a flat file. The procedure name in the given in the OLE DB source task. As i have used #table to retrieve the values from the procedure the columns are not exposed, so tried using SET NOCOUNT ON in the Procedure and SET FMTONLY OFF in the OLEDB task like below.SET FMTONLY OFF EXEC procedurenameWhen i execute the package i am getting the error [OLE DB Source [109]] Error:A rowset based on the SQL command was not returned by the OLE DB provider.I tried using the Table Variable in the procedure but i have around 100 million rows which hits the performance. Can anyone suggest me a different approach to get rid of this error |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-11 : 23:28:49
|
| try to make the procedure a table values function. then column info will be successfully taken by the SSIS metadata. |
 |
|
|
Jayaseelan
Starting Member
6 Posts |
Posted - 2008-11-12 : 08:30:08
|
| Vishak,Can you just give me a sample of how to write a table value function in a stored procedure. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 08:58:11
|
quote:
Originally posted by Jayaseelan
Vishak,Can you just give me a sample of how to write a table value function in a stored procedure.
You dont require to write table valued function inside a procedure. what i told was to convert your current procedure to table valued function.http://www.sqlteam.com/article/user-defined-functions |
|
|
|
|
|
|
OLEDB ERROR