Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS 2012 / mixed SQL Server 2008R2 and 2012 / stored procs which use #temp tables Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 5,047, Visits: 11,799
Apologies in advance for the longish post…
We have the following environment:
1) SSIS 2012
2) Mixed SQL Server 2008R2 and 2012

We have instances of databases installed on both 2008R2 and 2012 – these databases contain independent data, but have the same DDL. We want to keep the DDL and SSIS packages the same across all environments.

We have come across a problem when it comes to the use of OLEDB datasources which use stored procedures containing one or more references to #temp tables.
In SS2012, when executing a proc which makes any use of temp tables, it’s easy (and necessary) to modify the OLEDB source to include the ‘WITH RESULT SETS’ option to define the columns and datatypes which the proc will return. I like that, but executing it on anything before SS2012 gives a syntax error, because ‘WITH RESULT SETS’ is new in SS2012.

The way that we have been getting round this issue is by using table variables inside the procs rather than temp tables. This works, but the performance of table variables is not so good when there are lots of rows to be processed, as here.

[A question for another day is why do (local) temp tables give a problem when table variables do not – they’re not that different.]

We have plans to upgrade everything to 2012, which will mean that we can resolve the issue as described above. But it will not be for a while and I need to find an interim fix.

(I was quite hopeful about this idea) I tried creating a physical ‘work’ table and having the proc truncate it, populate it and then select from it, but still the fact that I am INSERTing into temp tables earlier on in the proc still causes problems, even though no result sets are being returned until the physical table at the very end of the proc.

I have tried to think of a simple way of handling the differences in the SSIS package which calls the proc – perhaps by passing in the OLEDB source SQL command string as a parameter – but it’s a level of complexity we don’t want to introduce. So I haven’t tried this.

I do have another idea which would definitely work:
1) Add an ExecuteSQL task to truncate/populate a physical work table.
2) Select from this work table in the OLEDB source.

This does, however, come with some negatives:
a) For every proc which needs to use temp tables, we need to create additional objects (a physical work table and a proc which truncates and populates the work table)
b) We need to introduce extra complexity into the SSIS package to call the ‘populate’ proc before selecting from the work table.
c) We need to be careful that no more than one instance of the ‘populate’ proc can ever be executing in the same db.

Does anyone have any ideas about how we can resolve this? Thanks in advance.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1471259
Posted Monday, July 8, 2013 5:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 1,787, Visits: 5,697
If I understand what you are seeing correctly, I think that adding a dummy select with the correct structure cures it.

e.g you have a stored proc that returns a result set with 3 columns, one int, one date, one varchar, but it uses temp tables.

Place a select that will never execute at the start before any temp table usage...

create proc foo
as
set nocount on;
if 1=0
select cast(0 as int) column1, cast(0 as date) column2, cast(0 as varchar(10)) column2
...
blah blah blah

This can provide the structural information that is needed by the oledb driver without affecting your code.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1471392
    Posted Tuesday, July 9, 2013 12:17 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:23 AM
    Points: 5,047, Visits: 11,799
    Thank you, MM, for taking the time to read and answer my post.

    The solution you propose works fine if the SQL Server database engine is 2008R2 or below.

    But it fails from 2012 onwards with an error along the lines of:

    The metadata could not be determined because statement '[first select statement in proc to use a temp table]' uses a temp table.


    This is the crux of the problem - one technique works for 2008R2 and lower, a different technique works in 2012 (and, 2014, I expect).

    The two techniques are not compatible with each other:

    -- The 2008R2-and-lower technique appears to be ignored when executed on 2012.

    -- The 2012 technique fails on 2008R2 and lower.



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1471451
    Posted Tuesday, July 9, 2013 6:16 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 10:01 AM
    Points: 1,787, Visits: 5,697
    Ah right, sorry - I hadn't realised that didn't work any more in 2012, and you hadn't mentioned trying that technique...

    I don't have any other suggestions, sorry.


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1471575
    Posted Tuesday, July 9, 2013 6:21 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:23 AM
    Points: 5,047, Visits: 11,799
    mister.magoo (7/9/2013)
    Ah right, sorry - I hadn't realised that didn't work any more in 2012, and you hadn't mentioned trying that technique...

    I don't have any other suggestions, sorry.


    Sorry about that - I was so wrapped up and frustrated trying numerous alternatives yesterday that I was not at my most lucid.

    We have decided, for now, to go down the route of using an ExecuteSQL task to call a proc which truncates/repopulates a physical 'work' table and then using a simple SELECT from that work table in the OLEDB source. A bit messy, but we did not have any better ideas.



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1471576
    Posted Tuesday, July 9, 2013 4:11 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 10:01 AM
    Points: 1,787, Visits: 5,697
    Hi Phil,

    This has been playing on my mind as I know it will be an issue for me at some point...so I tried it and could not get the error you had using temp tables in SS2012.

    Could you give me a bit of detail about what task you were using and how you had it configured?

    This was my simple test:



    using this stored proc:

    create proc testProcWithTempTables
    as
    if 1=0 select 1 as id;
    create table #t1(id int identity(1,1));
    insert #t1 default values;
    select id
    from #t1;



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw



  •   Post Attachments 
    sql2012temptest.PNG (122 views, 26.57 KB)
    Post #1471890
    Posted Wednesday, July 10, 2013 12:44 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:23 AM
    Points: 5,047, Visits: 11,799
    Hi MM

    The error occurs when you use the proc in an OLEDB Source, within a data flow. ExecuteSQL tasks are not affected, as far as I know.

    I did this, using your proc. Then, after clicking on 'Columns', the meta data error is displayed, as per the attached screen shot.




    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.


      Post Attachments 
    MetaDataError.png (104 views, 50.19 KB)
    Post #1471969
    Posted Wednesday, July 10, 2013 12:48 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 10:01 AM
    Points: 1,787, Visits: 5,697
    Thanks for taking the time to explain.

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1471972
    Posted Wednesday, July 10, 2013 12:52 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:23 AM
    Points: 5,047, Visits: 11,799
    To fix the problem (for SS2012 only), use the following T-SQL instead:

    exec dbo.testProcWithTempTables
    with result sets((id int));




    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1471973
    Posted Wednesday, July 10, 2013 12:53 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 11:23 AM
    Points: 5,047, Visits: 11,799
    mister.magoo (7/10/2013)
    Thanks for taking the time to explain.


    NP - I feel like the subject matter expert on this topic after the last few days!



    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1471974
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse