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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy