RE: Validating stored proc return columns SSIS

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Can you post the code that doesn't work?

Josh


"Ron Ruble" wrote:

We've noticed that in SQL Server 2005, you can't return results from a temp
table in a stored procedure to an SSIS OLEDB source; it fails the validation
step.

As a workaround, we create a persistent table that we use as a temporary
table, truncating it and loading it in the stored procedure.

However, I just discovered SSIS won't validate against a stored proc that
uses a temp table, -even if the resultset is not selected from a temp table-,
but from a persistent table.

This seems to me to be a bug; I can't see any reason that validation should
fail when the resultset the procedure returns is just a simple select from a
permanent table. Is this going to be fixed in a newer version?

It's a real annoyance; the temp table is just cached to return the results
of a query at the start of the procedure, that replaces a view that can't be
indexed (requires select distinct). I can see many areas in our data
warehouse where we might use a temp table to speed up various queries.


.

Relevant Pages

  • Re: SQLserver and the WHERE x IN y
    ... to a stored procedure - that would only work if you introduced subtyping into ... the temp table from the calling procedure (the temp table is still in scope, ... INSERT INTO #MyTempTable EXECUTE YourPopulateProc 123 ... INSERT INTO @MyTableVar VALUES ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedures in SE 7.3
    ... My end goal to write a stored procedure to be used by Lotus Enterprise ... Integrator (LEI). ... into temp t1; ... from bmdata, t1 ...
    (comp.databases.informix)
  • RE: Strange issue while executing sql server sp
    ... field is the one where truncation is occurring as my temp table field size is ... I have a simple stored procedure in sql server 2005 express version. ... ,finishdate datetime ...
    (microsoft.public.sqlserver.programming)
  • Re: How to navigate recursive stored procedures results
    ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Changed all objects from dbo to non dbo, now cannot send parameters
    ... use SQL authentication if you have to. ... However, when i run the adp, it fails. ... Dim rst As New ADODB.Recordset ... ' Set CommandText equal to the stored procedure name. ...
    (microsoft.public.access.adp.sqlserver)