Import stored procedure results into a table

  • Hi,

    Am attempting to import the results of stored procedure from one SQL server 2012 to another.

    The stored procedure accepts 3 varchar params, and will work with these params when I run it in a query window e.g.

    EXEC dbo.export_procedure '01-JAN-2013 00:00','24-FEB-2015 23:59',''

    However, I don't seem to be able to get it to work when I use the Import\Export Wizard from within Management Studio, or from within a Intergration Service project.

    The query will not Parse successfully in the Wizard, but DOES within the Integration Services Project.

    The error in the wizard is:

    TITLE: SQL Server Import and Export Wizard

    ------------------------------

    The statement could not be parsed.

    ------------------------------

    ADDITIONAL INFORMATION:

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. (Microsoft SQL Server Native Client 11.0)

    A similar error occurs in the Integration project when I attempt to Preview the data

    TITLE: Microsoft Visual Studio

    ------------------------------

    Exception from HRESULT: 0xC020204A

    Error at Data Flow Task [OLE DB Source [31]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.".

    Error at Data Flow Task [OLE DB Source [31]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

    I have attempted to declare some variables e.g.

    DECLARE @START AS VARCHAR(20)

    DECLARE @END AS VARCHAR(20)

    SET @START = '01-JAN-2013 00:00'

    SET @END = '24-FEB-2015 23:59'

    EXEC dbo.export_procedure @START,@END ,''

    without any luck.

    Am I doing something wrong? Do I have to use a prepared statement

    PS. As mentioned, the paramters work fine when I execute this statement in Management Studio.

  • Does the stored procedure have multiple result sets? Or multiple statements?

    Try using EXEC WITH RESULT SETS.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi,

    The SP only returns one result set, however there are a few statements setting up the parameters for the query ( the do contain select statements, and the parameters are SET into declared variables).

Viewing 3 posts - 1 through 2 (of 2 total)

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