Welcome to the CodeSmith Community!

Trouble generating result types in PLINQO for stored procedures that use Temp Tables.

Support Forums

Feel free to ask any questions about CodeSmith here.

Trouble generating result types in PLINQO for stored procedures that use Temp Tables.

  • rated by 0 users
  • This post has 5 Replies |
  • 2 Followers
  • Hey everybody.  I wanted to post this real quick.  If you are using the PLINQO templates against stored procedures that use temp tables, you will likely not see the associated result types in your Models folder.  This happened to me yesterday.  From a prior discussion with Support, I know that when generating result types for stored procedures, the procedure is exected using SQL's FMTONLY setting.  This is a special setting used in SQL server to return metadata to the client without actually executing the query.

    This mode is absolutely necessary for obvious reasons: if CodeSmith (or anyone else) execute stored procedures just to find out what the result types are, they could unintentially execute inserts, updates, and/or deletes.  BAD!!!

    However, the mode is also a bit of a pain to work with.  It sometimes behaves in ways that you would not expect.  For example, while it ignores if statements (to better determine what all result formats might look like) it still assigns variable values.  This can be a problem if you use logic statements like this:

    IF (Condition)
        BEGIN
            @MyVar = 1
        END
    ELSE IF (Condition)
        BEGIN
            @MyVar = 2
        END

    If you run this code block in FMTONLY mode, the variable @MyVar will always wind up being 2.

    More specifically (and difficult to deal with) I ran into a bigger problem.  While in FMTONLY mode, SQL Server will not execute statements that create temp tables.  I have a lot of complex procedures that make use of them, and this ultimately turned out to be the reason why CodeSmith was not creating the return types.

    To figure this out, I ran the Procedure in a T-SQL statement the same way that CodeSmith would:

    SET FMTONLY ON
        EXEC [Schema].[Procedure]
        NULL
    SET FMTONLY OFF

    What I got was the following error:
    Msg 208, Level 16, State 0, Procedure [ProcedureName], Line ###
    Invalid object name '#tmpTable'.

    The procedure errors out at the point where I'm trying to access the temp table, and never returns a result.  When CodeSmith tries to do this, it gets the same result, and cannot infer a return type for any SELECT statements I have after that point.

    Looking at the documentation for FMTONLY yielded little information:
    http://msdn.microsoft.com/en-us/library/ms173839.aspx
    http://social.msdn.microsoft.com/Search/en-us?query=SET+FMTONLY+ON

    FMTONLY's actual behaviour is not well documented as far as I can find.  What I wound up finding that was helpful was this post, but I'll summarize below if you don't want to read the whole discussion:
    http://www.eggheadcafe.com/software/aspnet/32579522/invalid-object-name-for-temp-table-when-executing-a-sp.aspx

    The solution it turns out, is pretty simple, but not at all (IMHO) straightforward.  It is one of those things where you just have to understand how FMTONLY actually behaves.  In this case it 1) Ignores IF statements and 2) Does not create temp tables.

    To solve the problem of temp talbes not being created in FMTONLY mode we can exploit the behaviour of FMTONLY ignoring IF statements.  The whole process looks like this:

    1) Create a variable to store whether FMTONLY was on use when the procedure was called, and initialize it as false.
    2) Write an IF block with a condition that is never true.
    3) Inside the IF block, set your variable to true.  Remember, this line WILL be executed in FMTONLY mode because it ignores IF statements.
    4) If FMTONLY is in use, turn it off.
    5) Create any temp tables that you need.  Create them all, even if you will only conditinally need them.
    6) Turn FMTONLY back on.

    A quick T-SQL example looks like this:

     

    -- This value gets set to 1 if FMTONLY is on, because
    -- FMTONLY does not care about if statements

     

     

    DECLARE
    @fmtonlyon BIT = 0 ;
    IF (1 = 0)
     
    SELECT @fmtonlyon 1

    -- We always turn FMTONLY off to create temp tables
    SET FMTONLY OFF

     -- Create temp tables
    CREATE TABLE #tmpTable1 ( [Column1] INT )
    CREATE TABLE #tmpTable2 ( [Column3] INT )
    -- Etc...

    -- Turn FMTONLY back on
    SET FMTONLY ON

     

    Anyway, I hope you guys find this helpful.  Cheers!

  • Also worth mentioning... I worded the first few sentances poorly.  This problem is intrinsic to FMTONLY and not a PLINQO or CodeSmith issue.  It will arise any time a Stored Procedure needs to create a temp table in FMTONLY mode.

  • Great Eric, i learned alot here. I noted what could be a bug here. Probably should the last statement (#6) be:

    IF @fmtonlyon = 1 SET FMTONLY ON

    /Lennart

  • Hello,

    Good catch, Eric do you want to update this?

    Thanks

    -Blake Niemyjski

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • @lennartg is correct.  However, when I try to edit my post and make the correction, I wind up on an error page.  This is true in several browsers.

    Error Page.

  • Hello,

    Would it be possible for you to create a new post with the updated content as well as paste the content you are trying to update the old post with and I'll take a look.

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

Page 1 of 1 (6 items)