What are the challenges you face when working across database platforms? Take the survey
Options

DEFECT 5.3.2: Table Variable not prompted

PDinCAPDinCA Posts: 642 Silver 1
edited May 14, 2012 8:57PM in SQL Prompt Previous Versions
SP has only 250 lines, including many comments, so the <ParserLookBackDistance> engine setting is not in play. I took the precaution of setting it to 15000 as I have couple of humongous SPs, but as soon as I restarted SSMS it bombed saying "object not set to an instance of an object" for the first connection to a User database...

BTW, I set the <ParserLookAheadDistance> to 13000 from its default 3000 and the entire SSMS exceptioned when restarted! Couldn't even bring up the shell - forget that! Moved on to just the look-back...

These TWO exceptions aren't the subject of this post, though...

The SP I'm editing has TWO table variables @METER and @DELETE. As soon as I attempt to reference anything to do with @METER after the declaration of @DELETE, I get absolutely NO suggestions. This is a defect.

The @METER TV is declared near the top of the code. @DELETE is only declared when it is determined that there are deletes to be performed. So if there are > 0 rows in @METER, @DELETE is declared and populated via an OUTPUT INTO within the DELETE FROM.

Environs:
Using SSMS2008R2 connected to a remote server running SS2005EE under Win2008Server. Local box is Win2003Server.
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...

Comments

  • Options
    Thanks for your post.

    I've haven't been able to reproduce this in a test case, so would you be able to post an example that reproduces this?

    I've tried the following, which prompted me for @Meter at the <cursor>
    DECLARE @Meter TABLE &#40;id INT, text VARCHAR&#40;30&#41;&#41;
    DECLARE @Upper int;
    DECLARE @Lower int
    
    SET @Lower = 1
    SET @Upper = 999
    
    INSERT INTO @Meter &#40;id&#41; SELECT Round&#40;&#40;&#40;@Upper - @Lower -1&#41; * Rand&#40;&#41; + @Lower&#41;, 0&#41;
    
    IF &#40;select TOP 1 id from @Meter&#41; &gt; 500 
    BEGIN
    	DECLARE @Delete TABLE &#40;text VARCHAR&#40;30&#41;&#41;
    	INSERT INTO @Delete &#40;text&#41; SELECT 'Im Deleting stuff'
    	SELECT * FROM @Delete AS D
        SELECT * FROM &lt;cursor&gt;
    END
    
    IF &#40;select TOP 1 id from @Meter&#41; &lt;= 500
    BEGIN
    	UPDATE @Meter SET text = 'Im not deleting stuff'
    	SELECT * FROM @Meter
    END
    
    Chris
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    Entire DB schema emailed with pointer to the SP I was editing at the time.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    Further to our email thread, I bumped the look-back to 50000 and restarted SSMS (2008R2 vintage under WinServer2003EE).
    &lt;ParserLookBackDistance&gt;50000&lt;/ParserLookBackDistance&gt;
      &lt;ParserLookAheadDistance&gt;3000&lt;/ParserLookAheadDistance&gt;
    

    I edited the usp_CacheDelete SP and at the penultimate line shown below tried to add to the JOIN - NO SUGGESTIONS from m.
    /* -----------------------------------------------------------------------------
          ** Delete t_meter_agg rows
          ** -------------------------------------------------------------------------- */
          DECLARE @DELETED TABLE
                &#40; Site_ID      int         NOT NULL
                , Tag_ID       int         NOT NULL
                , Input_ID     int         NOT NULL
                , StartTime    datetime    NOT NULL
                , EndTime      datetime    NOT NULL
                , IntervalCode nvarchar&#40;5&#41; NOT NULL
                &#41;
    
          DELETE ma
          OUTPUT DELETED.agg_site_id
               , DELETED.agg_tag_id
               , DELETED.agg_input_id
               , DELETED.agg_start_time
               , DELETED.agg_end_time
               , DELETED.agg_interval_code
            INTO @DELETED
            FROM dbo.t_meter_agg ma
                 INNER JOIN @METER m
                         ON m.SiteID   = ma.agg_site_id
                        AND m.TagID    = ma.agg_tag_id
                        AND m.
           WHERE ma.agg_end_time &gt; m.StartTime
    
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    PDinCA wrote:
    Further to our email thread, I bumped the look-back to 50000 and restarted SSMS (2008R2 vintage under WinServer2003EE).
    &lt;ParserLookBackDistance&gt;50000&lt;/ParserLookBackDistance&gt;
      &lt;ParserLookAheadDistance&gt;3000&lt;/ParserLookAheadDistance&gt;
    
    Having bumped the lookback to 100,000 BYTES, I now see the suggestions AND the SP's Parameters.

    TWO things:
      1. If having the DEFAULT set to a pitifully low 5000 eventuates in so much frustration, not only for me, WHY is it set so LOW? 2. Given this is an SP of a mere 250 LINES, and my largest, due to it's inescapable complexity, is currently over 2600 LINES (175,773 CHAR), with other very complex SPs at 1400+ LINES, what should my lookback value be set at, please? I absolutely NEED the entire SP to be covered by the abilities of SQL Prompt's suggestions mechanism...

    Given that, as you said Chris, in your email, UTF-8 vs. UTF-16 makes a difference, please:
      1. Enhance SQL Prompt so it recognizes the encoding and sets a default lookback value based on 1- or 2-bytes per visible character. 2. Consider significantly increasing the default lookback to support at least a 250 LINE SP, that, in my case, uses DOS\Windows encoding and is ONLY 8705 characters in total length. 3.
    Fix what appears to be a lookback BUG that requires me to set the value to 100,000 BYTES for a file that can only be 2x8705 BYTES raw max size. Perhaps SQL Prompt's lookback needs to consider what the SSMS script editor does to files presented to it, including what a user's custom background color may entail, so that the byte-count is realistic. To replicate my SSMS environment, I use a custom color background of RGB 252/253/204, which is much more restful on the eyes than stark white, IMO, and Lucida Console 8pt. Even at 50,000 BYTES lookback, my 8705 CHAR file still couldn't elicit a suggestion at line 183, CHARACTER 6,768, which appears to point to a defect in SQL Prompt somewhere...
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    Sorry for the delay, I was on holiday last week.

    I seem to get almost the opposite of what you're finding in point 3. With your script, I only stop getting suggestions when I set the value to below 7100, which is nowhere near the original declaration based on 1 byte per character. You seem to need to set nearly 4 bytes per character on your system.

    There might be some significant differences in our systems, but I would think it should still be more consistent.

    I'd like to see if I can replicate the problem you're having, so could you tell me:

    1) Your OS and service packs
    2) Exact version of SSMS
    3) The encoding of a .SQL file when you save a script in SSMS.

    For your reference, I've logged this issue as bug SP-4394.
    Chris
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    Local OS is Windows Server 2003 R2 Standard, SP2

    SSMS:
    Microsoft SQL Server Management Studio      10.50.2500.0
    Microsoft Analysis Services Client Tools    10.50.1600.1
    Microsoft Data Access Components &#40;MDAC&#41;     3.86.3959
    Microsoft MSXML                             2.6 3.0 4.0 5.0 6.0 
    Microsoft Internet Explorer                 8.0.6001.18702
    Microsoft .NET Framework                    2.0.50727.3625
    Operating System                            5.2.3790
    

    How does one ascertain the encoding, please, as there's nothing in the tools==> options dialog that I can see?

    Thanks for picking this up, Chris.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    Sorry, I've been on holiday and missed your reply.

    The easiest way to check the encoding is to save the file to disk, and then open it in notepad++. You can then find the encoding through the 'encoding' menu item.
    Chris
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    UCS-2 Little Endian (whatever that is :? )
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    Hi Chris,

    I just created an SP in SSMS and saved per my usual encoding. Having tested it, I dragged it into SQL Multi Script and made one change, from ALTER to CREATE, then ran it against my remaining 5 servers.

    I then hit Ctrl+Z to revert to ALTER and, just to keep SQL Multi Script happy, saved it.

    Upon return to SSMS I received the customary "file has changed - reload" prompt, which this time I said "Yes" to. The result was an unreadable file, a snippet of which looks like this:
    &#21333;&#8261;&#30811;&#28771;&#29279;&#26213;&#3421;&#18186;&#3407;&#21258;&#21573;&#16672;&#21326;&#24393;&#21838;&#19532;&#8275;&#20047;&#2573;&#17747;&#8276;&#21841;&#21583;&#17477;&#18783;&#17732;&#21582;&#17993;&#17737;&#8274;&#20047;&#2573;&#20295;&#2573;&#19521;&#17748;&#8274;&#21072;&#17231;&#17477;&#21077;&#8261;&#29275;&#28773;&#29295;&#29556;&#11869;&#30043;&#28787;&#20319;&#28784;&#29295;&#30068;&#26990;&#31092;&#28500;&#25936;&#25710;&#28265;&#23911;&#2573;††&#8232;&#20288;&#28784;&#29295;&#30068;&#26990;&#31092;&#17481;††&#28277;&#29033;&#25973;&#25705;&#28261;&#26996;&#26982;&#29285;&#2573;††&#8236;&#21312;&#24948;&#30068;&#18803;&#8260;††††&#28265;&#3444;&#8202;†&#11296;&#16416;&#25938;&#30065;&#29541;&#26996;&#26478;&#29525;&#29285;†&#28192;&#24950;&#25458;&#24936;&#8306;&#13608;&#10544;&#2573;††&#8236;&#17472;&#29541;&#26996;&#24942;&#26996;&#28271;&#26964;&#29285;†&#26996;&#31086;&#28265;&#8308;†††††&#8253;&#3377;&#8202;†&#11296;&#16416;&#17732;&#21826;&#8263;†††††&#25120;&#29801;†††††††&#15648;&#12320;&#2573;††&#3369;&#16650;&#3411;&#12042;&#8234;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#3389;&#10762;&#8234;&#30017;&#26740;&#29295;&#11808;&#11808;&#11808;&#14880;&#21280;&#25972;&#26736;&#28261;&#19488;&#8238;&#28225;&#27763;&#30575;&#2573;&#10794;&#17184;&#25970;&#29793;&#8293;&#24932;&#25972;†›&#12338;&#12849;&#12333;&#11573;&#13361;&#2573;&#10794;&#17440;&#29541;&#29283;&#28777;&#26996;&#28271;†›&#26947;&#25458;&#28021;&#25974;&#29806;&#29728;&#25960;&#28192;&#29295;&#24941;&#8300;&#29811;&#29793;&#29557;&#29741;&#24946;&#29550;&#29801;&#28521;&#29550;&#25632;&#26213;&#28265;&#25701;&#26912;&#8302;&#26740;&#3429;&#10762;&#8234;†††††††&#28448;&#28784;&#29295;&#30068;&#26990;&#31092;&#29472;&#24948;&#30068;&#8307;&#24948;&#27746;&#8293;&#28257;&#8292;&#26213;&#25958;&#29795;&#24864;&#8302;&#28009;&#25965;&#26980;&#29793;&#8293;&#28525;&#25974;&#26144;&#28530;&#8301;&#26740;&#3429;&#10762;&#8234;†††††††&#18720;&#25956;&#10081;&#12147;&#28751;&#28528;&#29810;&#28277;&#29801;&#10105;&#8307;&#30051;&#29298;&#28261;&#8308;&#29811;&#29793;&#29557;&#29728;&#8303;&#17744;&#17486;&#20041;&#11847;&#2573;&#10794;&#2573;&#10794;&#20000;&#29807;&#29541;†&#8238;&#8238;&#8238;›&#28233;&#28515;&#29550;&#29545;&#25972;&#29806;&#25632;&#29793;&#8289;&#26982;&#27749;&#29540;&#24864;&#25970;&#20000;&#19541;&#17740;&#11844;&#2573;&#10794;††††††††&#26708;&#8293;&#29779;&#29793;&#29557;&#17440;&#29793;&#8293;&#29545;&#24864;&#30572;&#31073;&#8307;&#24941;&#25956;&#8736;&#28532;&#24932;&#8825;&#3374;&#10762;&#8234;†††††††&#21536;&#25960;&#25632;&#26213;&#30049;&#29804;&#21536;&#25961;&#8306;&#29545;&#12576;&#8236;&#26743;&#28261;&#29216;&#29541;&#29797;&#26996;&#26478;&#24864;&#8302;&#25673;&#24933;&#24864;&#28514;&#25974;&#21536;&#25961;&#11634;&#8241;&#2573;&#10794;††††††††&#26740;&#29793;&#18720;&#8275;&#19521;&#17746;&#17473;&#8281;&#20041;&#20512;&#20037;&#18756;&#18254;&#21280;&#16724;&#21844;&#11347;&#29728;&#25960;&#21792;&#25971;&#8306;&#25959;&#29556;&#24864;&#25376;&#28520;&#25449;&#8293;&#26223;&#2573;&#10794;††††††††&#25956;&#29811;&#28265;&#29793;&#28521;&#8302;&#26964;&#29285;&#8236;&#24930;&#25971;&#8292;&#28271;&#29728;&#25960;&#17184;&#28015;&#24944;&#31086;&#25376;&#28271;&#26982;&#30055;&#24946;&#26996;&#28271;&#3374;&#10762;&#8234;†††††††&#21536;&#25960;&#18464;&#29545;&#28532;&#31090;&#26912;&#8307;&#27747;&#24933;&#25970;&#8292;&#26223;&#28192;&#8303;&#28524;&#26478;&#29285;&#29216;&#27749;&#30309;&#28257;&#8308;&#29541;&#24931;&#24940;&#26996;&#28271;&#24864;&#25710;&#2573;&#10794;††††††††&#28526;&#26996;&#26982;&#24931;&#26996;&#28271;&#29216;&#25445;&#29295;&#29540;&#10272;&#31092;&#25968;&#8307;&#11312;&#12832;&#8236;&#8243;&#28257;&#8292;&#12337;&#11817;&#2573;&#10794;&#2573;&#10794;&#19744;&#25711;&#26217;&#25449;&#29793;&#28521;&#8302;&#28492;&#14951;&#2573;&#10794;&#15648;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#3389;&#10762;&#8234;&#8316;†&#24900;&#25972;††&#31776;&#22304;&#28520;&#31776;†&#27476;&#8308;&#8316;&#28493;&#26980;&#26982;&#24931;&#26996;&#28271;&#2573;&#10794;&#31776;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11563;&#11565;&#11565;&#11563;&#11565;&#11565;&#11053;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#2573;&#10794;&#31776;&#12832;&#12592;&#11570;&#13616;&#12589;&#8244;&#8316;&#19539;&#8257;&#8316;&#12594;&#14643;&#31776;&#18720;&#26990;&#26996;&#27745;&#30240;&#29285;&#26995;&#28271;&#3374;&#10762;&#8234;&#11644;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#3373;&#10762;&#8234;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#15677;&#10784;&#3375;&#17418;&#17221;&#16716;&#17746;&#16416;&#29253;&#28530;&#20082;&#28021;&#25954;&#8306;††††&#26912;&#29806;&#2573;†††&#8236;&#17728;&#29298;&#29295;&#25933;&#29555;&#26465;&#8293;††††&#30318;&#29281;&#26723;&#29281;&#19752;&#22593;&#3369;&#12042;&#8234;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#3373;&#10762;&#8234;&#30789;&#29545;&#26996;&#26478;&#20256;&#28784;&#29295;&#30068;&#26990;&#31092;&#29472;&#24948;&#30068;&#8307;&#30061;&#29811;&#25120;&#8293;&#28271;&#8293;&#26223;&#29728;&#28520;&#25971;&#29472;&#28789;&#28528;&#29810;&#25701;&#25120;&#8313;&#26740;&#29545;&#21280;&#11856;&#2573;&#10794;&#11552;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#11565;&#8237;&#12074;&#2573;&#17993;&#20000;&#21583;&#17696;&#18776;&#21587;&#8275;&#8232;&#17747;&#17740;&#21571;&#12576;&#2573;†††††††††&#21062;&#19791;&#30752;&#26723;&#28257;&#25959;&#25646;&#28514;&#29998;&#26212;&#18271;&#29797;&#29761;&#29300;&#25193;&#29813;&#22117;&#27745;&#25973;&#25939;&#10356;&#11313;&#11313;&#20263;&#28784;&#25936;&#25710;&#28265;&#21351;&#24948;&#30068;&#19571;&#29545;&#10100;&#8233;
    

    When I dragged the file to Notepad++, the Encoding is "ASCII".

    Obviously, this is highly undesirable...

    Having closed the file in SSMS and then re-opening it, it is now readable, just like normal .sql files. If I then save it in SSMS and accept Notepad++'s offer to reload a changed file, the visual style in Notepad++ doesn't change - the SQL Language is still recognized, but when I click to see the Encoding, there is none at all - the usual dot-beside-the-encoding is entirely absent.

    Does this give you any more clues about the loss of prompting and the Encoding being a participant?

    Earlier in the day, for another ticket, now solved, I uninstalled 5.3.0.3 and installed SQL Prompt 5.3.2 and left the defaults as-is.

    Also, while editing the SP, I lost parameter-prompting at line 358, which is an EXEC of an SP that lives in the dbo schema of the same database as the SP I was editing that lives in the reports schema. The parameters referred to are the inputs to the SP being edited that would not appear in the suggestion list while matching @param = @param for the EXEC. This was prior to my drag-change-exec-revert, etc. described above.
    EXEC xcp_ref.dbo.usp_ProcessOpportunityNotification @NotificationType   = N'S%'
                                                                     , @OpportunityID      = @OpportunityID
                                                                     , @SiteID             = @SiteID
                                                                     , @StatusID           = @PendingStatus
                                                                     , @EscalationPrepend  = NULL
    
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.