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

• Phil Parkin

  SSC Guru

  Points: 246686

  More actions

  July 8, 2013 at 9:59 am

  #148179

  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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 8, 2013 at 5:04 pm

  #1630611

  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
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]