DEFECT 5.3.2: Table Variable not prompted

PDinCA

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.

Chris Auckland

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 (id INT, text VARCHAR(30))
DECLARE @Upper int;
DECLARE @Lower int

SET @Lower = 1
SET @Upper = 999

INSERT INTO @Meter (id) SELECT Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)

IF (select TOP 1 id from @Meter) > 500 
BEGIN
	DECLARE @Delete TABLE (text VARCHAR(30))
	INSERT INTO @Delete (text) SELECT 'Im Deleting stuff'
	SELECT * FROM @Delete AS D
    SELECT * FROM <cursor>
END

IF (select TOP 1 id from @Meter) <= 500
BEGIN
	UPDATE @Meter SET text = 'Im not deleting stuff'
	SELECT * FROM @Meter
END

PDinCA

Entire DB schema emailed with pointer to the SP I was editing at the time.

PDinCA

Further to our email thread, I bumped the look-back to 50000 and restarted SSMS (2008R2 vintage under WinServer2003EE).

<ParserLookBackDistance>50000</ParserLookBackDistance>
  <ParserLookAheadDistance>3000</ParserLookAheadDistance>

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
            ( 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(5) NOT NULL
            )

      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 > m.StartTime

PDinCA

PDinCA wrote:

Further to our email thread, I bumped the look-back to 50000 and restarted SSMS (2008R2 vintage under WinServer2003EE).

<ParserLookBackDistance>50000</ParserLookBackDistance>
  <ParserLookAheadDistance>3000</ParserLookAheadDistance>

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...

Chris Auckland

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.

PDinCA

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 (MDAC)     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.

Chris Auckland

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.

PDinCA

UCS-2 Little Endian (whatever that is :? )

PDinCA

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:

单⁅硛灣牟晥൝䜊൏匊呅䄠华彉啎䱌⁓乏਍䕓⁔啑呏䑅䥟䕄呎䙉䕉⁒乏਍佇਍䱁䕔⁒剐䍏䑅剕⁅牛灥牯獴⹝畛灳佟灰牯畴楮祴潔敐摮湩嵧਍††
佀灰牯畴楮祴䑉††湵煩敵摩湥楴楦牥਍††‬區慴畴䥳⁄††††湩൴ †Ⱐ䀠敒畱獥楴杮獕牥†渠慶捲慨⁲㔨⤰਍††‬䑀獥楴慮楴湯楔牥†楴祮湩⁴†††††‽റ †Ⱐ䀠䕄啂⁇†††††戠瑩†††††††㴠〠਍††ഩ䄊൓⼊‪㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽ഽ⨊‪畁桴牯⸠⸠⸠㨠匠整桰湥䰠‮湁汳睯਍⨪䌠敲瑡⁥慤整†›〲㈱〭ⴵ㐱਍⨪䐠獥牣灩楴湯†›楃捲浵敶瑮琠敨渠牯慭瑳瑡獵琭慲獮瑩潩獮搠晥湩摥椠桴൥⨊‪†††††††漠灰牯畴楮祴猠慴畴⁳慴汢⁥湡⁤晥敦瑣愠浩敭楤瑡⁥潭敶映潲桴൥⨊‪†††††††䤠敤❡⽳灏潰瑲湵瑩❹⁳畣牲湥⁴瑳瑡獵琠䕐䑎义⹇਍⨪਍⨪丠瑯獥†‮‮‮›湉潣獮獩整瑮搠瑡⁡楦汥獤愠敲丠䱕䕌⹄਍⨪††††††††桔⁥瑓瑡獵䐠瑡⁥獩愠睬祡⁳慭敤∠潴慤≹മ⨊‪†††††††吠敨搠晥畡瑬吠敩⁲獩ㄠ‬桷湥爠獥瑥楴杮愠摉慥愠潢敶吠敩⵲‱਍⨪††††††††桴瑡䤠⁓䱁䕒䑁⁙义倠久䥄䝎匠䅔啔ⱓ琠敨唠敳⁲敧獴愠挠潨捩⁥景਍⨪††††††††敤瑳湩瑡潩楔牥‬慢敳⁤湯琠敨䌠浯慰祮挠湯楦畧慲楴湯മ⨊‪†††††††吠敨䠠獩潴祲椠⁳汣慥敲⁤景渠潬杮牥爠汥癥湡⁴獥慣慬楴湯愠摮਍⨪††††††††潮楴楦慣楴湯爠捥牯獤⠠祴数⁳ⰰ㈠‬″湡⁤〱⸩਍⨪਍⨪䴠摯晩捩瑡潩潌㩧਍⨪㴠㴽㴽㴽㴽㴽㴽㴽ഽ⨊‪⁼†慄整††簠圠潨簠†歔⁴⁼潍楤楦慣楴湯਍⨪簠ⴭⴭⴭⴭⴭⴭ⴫ⴭⴭ⴫ⴭⴭ⬭ⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭ਍⨪簠㈠㄰ⴲ㔰ㄭ‴⁼䱓⁁⁼ㄲ㤳簠䤠楮楴污瘠牥楳湯മ⨊‪⵼ⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭഭ⨊‪㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽㴽⨠യ䐊䍅䅌䕒䀠牅潲乲浵敢⁲††††椠瑮਍†††‬䕀牲牯敍獳条⁥††††癮牡档牡䴨塁ഩ⼊‪ⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭഭ⨊‪硅獩楴杮传灰牯畴楮祴猠慴畴⁳畭瑳戠⁥湯⁥景琠潨敳猠灵潰瑲摥戠⁹桴獩匠⹐਍⨪ⴠⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭⴭ‭⼪਍䙉丠呏䔠䥘呓⁓
䕓䕌呃ㄠ਍†††††††††剆䵏砠档湡敧搮潢甮晤䝟瑥瑁牴扩瑵噥污敵敓⡴ⰱⰱ伧灰敐摮湩卧慴畴䱳獩❴


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