January 17, 2006 at 6:28 am
Hi,
I posted a while back about a problem I had getting SSIS to read a stored procedure from SQL 2000 - well I never solved the problem, I just worked around it, but then I got to thinking so I did some experiments.
The conclusion I have come to is that 2005 will not read the results from a stored procedure if the result set is returned from a temp table in SQL 2000/7.0
Which leads me to the question that perhaps I should have been asking in the first place. I made the assumption that if you let a stored procedure create a temp table then by default it will use the data types that you're feeding it, I'm now wondering if there is more going on here.
Any ideas?
Thanks
Rich
January 17, 2006 at 7:14 am
I seem to remember that varchar sizes are set as the default size which may cause truncated strings, try using create table. Anyhow as far as SSIS goes try turning on set nocount on in the sp, I have encountered ado etc. interpreting row counts as recordsets, of course you should do this in sps anyway for performance.
Phil Nicholas
January 18, 2006 at 7:59 am
A similar issue affects Reporting Services. The workaround there (not sure if it applies to SSIS) is to use this as the SQL statement:
SET FMTONLY OFF EXEC YourProcName
(Typically the stored proc will be executed as SET FMTONLY ON by the designer in order to get the resultset column defs, which doesn't work with temp tables)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply