February 24, 2015 at 5:58 pm
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.
February 25, 2015 at 12:57 am
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
February 25, 2015 at 2:43 pm
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