Error creating temp table based on IF logic

  • Hello,

    Was working on something I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running.

    I would like to avoid a global temp table if possible. Here's what I've tried:

    sp_executesql creates a table outside of the scope of my session:

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @StageTable NVARCHAR(1024) = N''

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    IF @PrVersNum < 11.00

    BEGIN

    SET @StageTable = N'

    CREATE TABLE #stage

    (

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );'

    EXEC sys.sp_executesql @StageTable

    END

    ELSE IF @PrVersNum >= 11.00

    BEGIN

    SET @StageTable = N'

    CREATE TABLE #stage

    (

    UnitID INT,

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );'

    EXEC sys.sp_executesql @StageTable

    END

    SELECT * FROM #stage

    Msg 208, Level 16, State 0, Line 90

    Invalid object name '#stage'.

    Calling non-dymnamic SQL seems to attempt to create *both* tables.

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    IF @PrVersNum < 11.00

    BEGIN

    CREATE TABLE #stage

    (

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    END

    ELSE IF @PrVersNum >= 11.00

    BEGIN

    CREATE TABLE #stage

    (

    UnitID INT,

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    END

    SELECT * FROM #stage

    Msg 2714, Level 16, State 1, Line 29

    There is already an object named '#stage' in the database.

    Any suggestions?

    Thanks

  • The temp table in the dynamic sql is created in a different context and not available to the calling context. e.g.

    DECLARE @sql nvarchar(4000) = N'create table #foo(a int)';

    EXEC sp_executesql @sql

    SELECT * FROM #foo

    fails just like your code.

    Gerald Britton, Pluralsight courses

  • g.britton (11/13/2014)


    The temp table in the dynamic sql is created in a different context and not available to the calling context. e.g.

    DECLARE @sql nvarchar(4000) = N'create table #foo(a int)';

    EXEC sp_executesql @sql

    SELECT * FROM #foo

    fails just like your code.

    Try calling the table ##stage instead.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (11/13/2014)


    g.britton (11/13/2014)


    The temp table in the dynamic sql is created in a different context and not available to the calling context. e.g.

    DECLARE @sql nvarchar(4000) = N'create table #foo(a int)';

    EXEC sp_executesql @sql

    SELECT * FROM #foo

    fails just like your code.

    Try calling the table ##stage instead.

    I considered that, but I would like to avoid using a global temp table if possible.

  • Your second approach is the best one. Execute it in sections and see if you can isolate where the failure occurs.

    Gerald Britton, Pluralsight courses

  • What about creating the table and adding or dropping columns as needed?

    Here's an example dropping the column to preserve the order and prevent confusing the Intellisense.

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    CREATE TABLE #stage

    (

    UnitID INT,

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    IF @PrVersNum < 11.00

    ALTER TABLE #stage DROP COLUMN UnitID ;

    SELECT * FROM #stage

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Nice!

    Gerald Britton, Pluralsight courses

  • Luis Cazares (11/13/2014)


    What about creating the table and adding or dropping columns as needed?

    Here's an example dropping the column to preserve the order and prevent confusing the Intellisense.

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    CREATE TABLE #stage

    (

    UnitID INT,

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    IF @PrVersNum < 11.00

    ALTER TABLE #stage DROP COLUMN UnitID ;

    SELECT * FROM #stage

    That's so simple it hurts.

    Thanks, Luis.

  • Quick suggestion, not as elegant as Luis's but should work

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @StageTable NVARCHAR(1024) = N''

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    SET @StageTable = N'

    CREATE TABLE #stage

    (

    ' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,

    ' END + 'FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    SELECT * FROM #stage'

    EXEC sys.sp_executesql @StageTable

  • Eirikur Eiriksson (11/13/2014)


    Quick suggestion, not as elegant as Luis's but should work

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @StageTable NVARCHAR(1024) = N''

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    SET @StageTable = N'

    CREATE TABLE #stage

    (

    ' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,

    ' END + 'FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    SELECT * FROM #stage'

    EXEC sys.sp_executesql @StageTable

    That would work, but the problem is I need to hit that table outside of the dynamic SQL. :hehe:

  • How about this

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @StageTable NVARCHAR(1024) = N''

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    SET @StageTable = N'

    CREATE TABLE #stage

    (

    ' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,

    ' END + 'FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    INSERT INTO #stage

    EXEC (''DBCC LOGINFO'')

    SELECT * FROM #stage'

    EXEC sys.sp_executesql @StageTable

  • Eirikur Eiriksson (11/13/2014)


    How about this

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @StageTable NVARCHAR(1024) = N''

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    SET @StageTable = N'

    CREATE TABLE #stage

    (

    ' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,

    ' END + 'FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    INSERT INTO #stage

    EXEC (''DBCC LOGINFO'')

    SELECT * FROM #stage'

    EXEC sys.sp_executesql @StageTable

    I think that since the next part of this script (below) does some further processing and iterates over all DBs on a server, it's still probably a bit less complicated to use Luis' method. I don't even need the additional column for anything. Heh.

    EXEC sp_foreachdb @command = N'Use ?;

    Insert Into #stage

    Exec sp_executeSQL N''DBCC LogInfo(?)'';

    Insert Into #results

    Select DB_Name(), Count(*)

    From #stage;

    Truncate Table #stage;';

  • You are right on this, Luis's approach is better as it doesn't switch session contexts, just wanted to make the point that there are always alternatives.;-)

    😎

  • And there's another alternative based on "best practices" 🙂

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @command NVARCHAR(4000)

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    IF OBJECT_ID('tempdb..#results') IS NOT NULL

    DROP TABLE #results;

    CREATE TABLE #results(

    db_name nvarchar(128),

    counts int

    );

    CREATE TABLE #stage

    (

    UnitID INT,

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    SET @command = 'Use ?;

    Insert Into #stage (

    ' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID,

    ' END + 'FileID,

    FileSize ,

    StartOffset,

    FSeqNo,

    [Status],

    Parity,

    CreateLSN

    )

    Exec sp_executeSQL N''DBCC LogInfo(?)'';

    Insert Into #results

    Select DB_Name(), Count(*)

    From #stage;

    Truncate Table #stage;';

    EXEC sp_MSforeachdb @command;

    SELECT *

    FROM #results;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/13/2014)


    And there's another alternative based on "best practices" 🙂

    DECLARE @PrVers NVARCHAR(128)

    , @PrVersNum DECIMAL(10,2)

    , @command NVARCHAR(4000)

    SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )

    PRINT @PrVers

    PRINT @PrVersNum

    IF OBJECT_ID('tempdb..#stage') IS NOT NULL

    DROP TABLE #stage;

    IF OBJECT_ID('tempdb..#results') IS NOT NULL

    DROP TABLE #results;

    CREATE TABLE #results(

    db_name nvarchar(128),

    counts int

    );

    CREATE TABLE #stage

    (

    UnitID INT,

    FileID INT ,

    FileSize BIGINT ,

    StartOffset BIGINT ,

    FSeqNo BIGINT ,

    [Status] BIGINT ,

    Parity BIGINT ,

    CreateLSN NUMERIC(38)

    );

    SET @command = 'Use ?;

    Insert Into #stage (

    ' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID,

    ' END + 'FileID,

    FileSize ,

    StartOffset,

    FSeqNo,

    [Status],

    Parity,

    CreateLSN

    )

    Exec sp_executeSQL N''DBCC LogInfo(?)'';

    Insert Into #results

    Select DB_Name(), Count(*)

    From #stage;

    Truncate Table #stage;';

    EXEC sp_MSforeachdb @command;

    SELECT *

    FROM #results;

    Son of a...

    Also, I'm not crazy. I swear.[/url]

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply