0

I have a stored procedure that executes variously dynamic sql statements. In reality this Stored procedure will be fired from SSIS with various parameters. So there will be a lot of parallel executions.

This stored procedure uses temp tables during execution. I create the tables manually with a Select .. into statement and drop them at the end.

During parallel executions, the process ends up in error because of execution 2 is trying to create or use the same temp table as execution 1. This is giving errors..

I tried to resolve this using table variables. However this does not work in dynamic SQL. (How to use table variable in a dynamic sql statement?)

I tried to resolve this using local temp table. However SSIS and Stored Procedures using local temp tables is not a great marriage. I have never found a good working solution so far.. http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

I have an idea of how to create a temp table with the name of a GUID. And then use this so that it is unique in the Stored Procedure execution. However this would really make my dynamic SQL code much more difficult to maintain.

Does anyone see other options or am I over-complicating things? Is there a more viable solution?

Community
  • 1
  • 1
OverflowStack
  • 259
  • 1
  • 3
  • 17
  • 3
    creating these as local or global temps? http://stackoverflow.com/questions/892351/sql-server-2005-and-temporary-table-scope if global a local may work better. Is the parallel execution occurring though the same session? if so, then closing and opening different sessions should eliminate the problem (but at a cost of time). Also is connection pooling being used? (perhaps not correctly causing session not to terminate when released back and thus the problem.) – xQbert Oct 27 '14 at 17:39
  • 2
    Normal (local) temp tables already use the SPID GUID in their name and should not need anything like this. Nor should they behave like this, so I have to assume that you are using global temp tables, to which the answer is, as xQbert has already suggested, just use local temp tables instead. – RBarryYoung Oct 27 '14 at 17:52
  • Sorry Forgot to mention the following: SSIS and SP's with temp tables are really annoying! http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx (updated my first post) – OverflowStack Oct 27 '14 at 18:27
  • What are you trying to accomplish with SSIS such that local temporary tables do not work? Are you trying to share information between tasks that are separate connections? – Solomon Rutzky Oct 27 '14 at 19:21
  • 2
    I wouldn't recommend generating dynamic table names. Instead, create a staging table that includes a ProcesId. Each process can use this shared table, and clear out its own records when it's done. If you want, you can create this table in TempDB, you just need to make sure the SSIS package can re-create it if it doesn't exist. – Brandon Oct 27 '14 at 19:31
  • @Brandon : That's a nice creative solution. I will try this tommorow. thank you! – OverflowStack Oct 27 '14 at 20:36

1 Answers1

0

You can create the temp table as

Create table #tmp_execute ( id int, name varchar(50), ....... )

then, execute the dynamic query

insert into #tmp_execute select .... from tbl_Sample

If you are require to execute the dynamic columns, you can use ' alter table #tmp_execute add your_column int' with conditional statement.

MS Kesavan
  • 21
  • 3