SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS 2012 / mixed SQL Server 2008R2 and 2012 / stored procs which use #temp tables


SSIS 2012 / mixed SQL Server 2008R2 and 2012 / stored procs which use #temp tables

Author
Message
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53421 Visits: 21205
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10935 Visits: 7891
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

    Group: General Forum Members
    Points: 53421 Visits: 21205
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10935 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

    Group: General Forum Members
    Points: 53421 Visits: 21205
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10935 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Attachments
    sql2012temptest.PNG (225 views, 26.00 KB)
    Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

    Group: General Forum Members
    Points: 53421 Visits: 21205
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    Attachments
    MetaDataError.png (215 views, 50.00 KB)
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10935 Visits: 7891
    Thanks for taking the time to explain.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

    Group: General Forum Members
    Points: 53421 Visits: 21205
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

    Group: General Forum Members
    Points: 53421 Visits: 21205
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search