No Hints for Variable

JohnHWkJohnHWk Posts: 3 Bronze 2
edited August 1, 2017 9:21AM in SQL Prompt Previous Versions
I key the following SQL:
DECLARE
	@StartDate	datetime,
	@EndDate	datetime,
	@RepID		varchar(25)

SELECT
	@StartDate = '2009-01-01',
	@EndDate = '2009-03-31 23:59:59',
	@
I get no suggestions at all (I should get a suggestions to complete @ RepID). This worked fine in 3.9x, but not now. Any help much appreciated.

Regards,

John Hopkins

Comments

  • Many thanks for your post.

    We have logged your issue and will have it addressed in our future releases. Reference number for the issue is SP-2789.

    Sorry for the inconvenience caused by this issue.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • Hello,

    This issue is now fixed with the latest update build.

    For more details, kindly visit the following post:)
    http://www.red-gate.com/MessageBoard/viewtopic.php?t=9517

    Regards,
    Tanya
    Project Manager
    Red Gate Software Ltd
  • First off, please put the release numbers in the posts about new versions.

    Second - version 4.0.2.53 - it is still erratically picking up declared variables.


    ALTER proc [dbo].[Matcher_PostBuildingKeyAndScore]
    	@Matcher_Output_pk INT = null,
    	@Building_fk INT = NULL,
    	@BuildingMatchScore INT = NULL,
    	@BuildingPass VARCHAR(5)
    as
    
    set nocount on;			
    
    if @Matcher_Output_pk is not null
    
    	UPDATE
    		dbo.Matcher_Output 
    		SET
    			Building_fk = @Building_fk,
    			BuildingMatchScore = @BuildingMatchScore,
    			BuildingPass = @BuildingPass
    	WHERE 
    		Matcher_Output_pk = @Matcher_Output_pk
    

    @BuildingPass did not pick up when I typed it.

    I hit refresh cache and suddenly it picked it up. I duplicated this in another window and nothing makes it work.

    Here's the other proc - I have refreshed the cache, backed up and retyped the @ several times. Nothing.
    ALTER proc [dbo].[Matcher_PostBusinessKeyAndScore]
    
    @Matcher_Output_pk int = null,
    @Business_fk INT = NULL,
    @BusinessMatchScore INT = NULL,
    @BusinessPass VARCHAR(5)
    as
    
    set nocount on;			
    
    if @Matcher_Output_pk is not null
    
    
    	UPDATE
    		dbo.Matcher_Output 
    		SET
    			Business_fk = @Business_fk,
    			BusinessMatchScore = @BusinessMatchScore,
    			BusinessPass = @
    	WHERE 
    		Matcher_Output_pk = @Matcher_Output_pk
    		
    return(@@rowcount)
    

    Is it possible to get the install for version 3.x? I really feel llike 4.x was released too early - some of the core functionality isn't working correctly. I like where it's headed, but I'd rather wait until the bugs are ironed out.
  • I am having the same problem.
    It does not matter if I am in SSMS 2008 or VS 2008.

    I will be going back to ver 3.9 until the version after 4.0.2.53 comes out.
    Thanks
    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143
    [email protected]

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92
  • Hello,

    @nhustak - Thank you for giving us the code. We tried to replicate the issue and having troubles in doing the same in our environment with similar or same code. If you are able to consistently replicate the issue it would be of help if you can let us know the following:
    • On manually invoking the suggestions list using CTRL+SPACE after typing @ can you let us know what are the top 5 objects you see?
    • After manually typing in @BuildingPass, does hovering over the object display the object tooltip that reads @BuildingPass (Parameter) if you have not unchecked object definitions in the options menu?
    Sorry about the missing build no in the forum post.

    @wsoranno - Do you have any sample code wherein you are able to consistently replicate the issue?

    Sincere apologies for the trouble caused due to this issue.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
  • I have gone back into the procs and reset them. Unfortunately (?), it works though I do have to manually invoke.

    For what it's worth, I do always hit manual invoke when nothing pops up and that usually works (I also am very quick to rebuild the cache via hotkey). If I'm having problems, it will show a list of internal function calls. From my memory, what is intersting is it shows none of the variables. I say that because the first time it happened, I immediately went into the options to change the 'look behind' distance to all - except that option has been removed in 4.0. I assume it's look at all code now always.

    The next time this happens, I will be sure to manually invoke per your request and gather information.

    Regards,
    Nick
  • TSchwabTSchwab Posts: 80
    edited September 15, 2009 10:23AM
    @RowCount etc., only. Even after CTRL-SHIFT-D.


    SQL-Prompt 3.9 does not have this problem.
    Regards,

    Thorsten
  • Tanya
    I sent you an email with a script attached.
    I experienced the error starting on line 482.
    That is where I was making an update to the script.
    Thanks
    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143
    [email protected]

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92
  • Unfortunately, with the new release I am still seeing this behavior.
  • Any new info about this problem? :?:
    Regards,

    Thorsten
  • Ok guys, this product cost far too much for the basics to still be non-functioning. The level of frustration I am dealing with EVERYDAY is starting to get to me.

    Please post the latest 3.x version for download - I need a version that works. (NOLOCK) pretty much rendering it inoperative is making sql prompt useless for me. 90% of our stored procesdures use nolock. This is a serious problem for me. I am very dependent on SQL Prompt - I'm a contractor and I regularly work with large databases with what amounts to random fieldnames. So EVERYDAY I get more and more angry as SQL Prompt fails to work. This isn't something I use once a week - it's something I use all day long.

    I apologize for the tone of this post but I really feel like I am beta testing an application at this point and I didn't sign up for that. I want to revert to 3.x until this thing is working.

    As it stands, I feel like we all deserve an extension on our support. For me, it's nearly unusable with the current bugs. If it weren't part of the package I purchased, I'd be returning it for a refund. THAT is how frustrated I am at this point.

    This post is a product of the last two days of SQL Prompt 4 constantly failing due to the fact I am debugging stored procedures that contain NOLOCK. 4 is certainly faster than 3 - but that speed doesn't help when it constantly fails to function.

    Regards,
    Nick Hustak
  • Hi Nick,

    We totally understand and my sincere apologies for the trouble caused with the NOLOCK issue.
    We are working on it and will have a public release out that includes the NOLOCK fix by end of this year. Meanwhile, I will send you the link for v3.9 installer.
    Does that help?

    Sorry for the inconvenience caused.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
  • Yes the v3.9 installer would help immensely.

    Thank you,
    Nick
  • Previous versions of many Red Gate products can be found here:

    http://www.red-gate.com/supportcenter/G ... rsions.htm

    Kind regards,

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • Thanks David - I had no idea that was there.
  • PDinCAPDinCA Posts: 642 Silver 1
    edited October 28, 2009 12:47PM
    Post removed - dumb user error!
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Thanks for your post.

    Can you please remove the last 'GO' command and check if SQL Prompt suggests the variable?
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: [email protected]
  • PDinCAPDinCA Posts: 642 Silver 1
    edited December 11, 2009 12:20PM
    Well, when one forgets that GO puts the variable out of scope one must expect it to be missing from the suggestions - DUH!

    Apologies... Note to self, "GO is a pain to remember!" :oops:
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • I don't think this issue has ever gotten fully identified, here is a sample that I think will help:
    DECLARE 
    @Name VARCHAR(1000),
    @PageCount VARCHAR(1000);
    
    SELECT 
    	@PageCount = backed_up_page_count<CURSOR>
    FROM msdb.dbo.backupfile;
    
    SELECT<cursor2>
    

    If you select <CURSOR> and type a comma and hit ENTER and then @ you will get no suggestions unless you hit ctrl-space.

    However if you instead type a B you will get suggestions for field name automatically.

    At cursor2 if you type a space you get no suggestions, but you should get the two variables suggested.
  • I am still having this problem (with version 4.0.3.11). It really is annoying, and hurts the usability of SQL Prompt.

    I declare a bunch of variables. Then I declare a cursor, open the cursor, and do a fetch.

    If I try to add variables to the Fetch ... "INTO @variable1&quot;, etc. I get no prompting at all showing me any of the declared variables, even with Ctrl-Space. If I add a statement like:

    set @variable ...

    before the FETCH statement, SQL Prompt will prompt me once I type the "@", but in the INTO clause of the fetch, or anywhere after it will not...

    Stuart Naftel
Sign In or Register to comment.