Post reply

Stored procedure output to Excel

  • March 23, 2006 at 11:19 am

    #67243

    Is there a way to take the output of a stored procedure using the data transform task, or do I have to execute it in a SQL task and create an intermediate table to make this work?

    The temp table is probably the answer.  It just seems klunky to have to create a table when the sp gives me a nice result set.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • March 23, 2006 at 11:47 am

    #628439

    Have you looked into using a FOR XML EXPLICIT stored proc to create the resultset in a XML stream.  With Active-X you could capture the stream and save it to a FSO.Textfile.  This would be a quick and filthy way to get the dataset in to an Excel readable format.

    -Mike Gercevich

  • March 23, 2006 at 11:50 am

    #628441

    No

    Create a source connection.

    An excel destination.

    Transform data task.

    Select sql query.

    Put in stored procedure name with parameter

    Select the destination tab, DTS will interrogate the proc for the resultset, and create a definition for you. You can arrange your transformations if you need to.

    and thats it.

     

  • March 23, 2006 at 1:42 pm

    #628459

    I would have thought that would work.  It complains mightily that table #results doesn't exist.  So, it should work if I can get it to output the result set without using a temp table?  Of course, I'm using a temp table because of the complexity of the query.

    *sigh*

    Well, if this stuff were easy, anyone could do it.  Right?

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • March 24, 2006 at 5:06 am

    #628564

    Is it any better with a table var??


    Thanks Jeet

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

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