SQL Prompt - Stored Procedures/Functions/Views

• 
  Hi Ken,
  
  Thanks for posting. I'm afraid I couldn't reproduce your problem. Could you tell me a bit more about your system? I.e. what's your OS, Framework version, language/regional settings, SQL Server versions, machine specs? I take it you are only using Query Analyzer?
  
  Regards,
  
  Tilman

  Tilman August 01, 2006 04:18

  - 0 +
• 
  Client: (SQL Prompt installed)
  OS - Windows XP Prof. SP2
  Microsoft SQL Server Management Studio 2005 Standard
  Region OS Setting - English
  Platform - Dell Latitude D800 Intel M 1.7GHz, 1Gb memory
  Framework - 1.1 & Hotfix, 2.0 & 2.0 SDK(ENU)
  
  Server: (SQL Prompt not installed)
  OS - Windows 2k SP2
  Microsoft SQL Enterprise Manager 2000
  Region OS Setting - English
  Platform - Dell PowerEdge Intel Xeo 2.8GHz, 2Gb memory
  Framework - 1.1 & Hotfix, 2.0
  
  Yes, I just started working with SQL Prompt and have only used it in query analyzer. Also the "Auto-Insert" doesn't work when I open a stored procedure from a solution-project and rarely when we type <ctl> + <space_bar> after key word "from " does a list of tables show in a stored procedure. These problems are also happening for a co-worker and we both have the same set-up. But when we open a new query analyzer, everthing works beautifully.
  
  Thanks in advance.

  ksrangsy August 01, 2006 10:08

  - 0 +
• 
  Hi Ken,
  
  I'm afraid I have no idea what could be causing your problem. Does this happen with every stored procedure? Does it also happen when you copy and paste the stored procedure to a new window? Do you think you could send me a copy of the sql where the problem happens? If you don't want to post it here, you can also pm it to me.
  
  Regards,
  
  Tilman

  Tilman August 02, 2006 03:43

  - 0 +
• 
  The parser in 2.0 requires that the SELECT that your FROM is part of should be delimited by blank lines.
  
  This won't work:
  
  EXEC SomeSP
  SELECT * FROM <Ctrl-Space>
  
  This will:
  
  SELECT * FROM <Ctrl-Space>
  
  Hope this helps.

  dm August 02, 2006 08:48

  - 0 +
• 
  Thanks for both replies. I tried opening up a stored procedure from a solution-project on my machine w/ SQL Studio 2005 and comment out the header and footer and SQL Prompt still doesn't kick in.
  
  Here some example code that I use for all the stored procedures:
  
  if (object_id('dbo.prTmp) is not null) drop proc dbo.prTmp
  go
  
  create proc dbo.prTmp
  @intEmployeeID int=Null
  , @intReturnCode int=NULL output
  , @chvErrorMessage varchar(255)=NULL output
  as
  set nocount on
  if (@intEmployeeID Is Null) Goto Help
  
  select lastName, firstName from employee (NoLock) where employeeID = @intEmployeeID
  
  @error
  
  Help:
  Exec sp_usage
  @objectName = 'dbo.prTmp'
  , @desc = '...'
  , @parameters='@intEmployeeID, ...'
  , @example='exec dbo.prTmp ;'
  , @returns='lastName, firstName'
  , @author='...'
  , @workfile='prTmp.sql'
  , @email='...'
  , @version='x', @revision='y'
  , @datecreated='...', @datelastchanged='...'
  return -1
  go
  
  grant execute on dbo.prTmp to execprocs
  go
  
  Also, when I copy a stored procedure into a new query analyzer and type select * from <ctl>+<space> "in the body" of the stored procedure, like in the example above, I get @intReturnCode (stored procedure parameter) instead of table names. But if I do it before I drop the procedure at the very beginning (example code above), SQL Prompt works perfectly from a stored procedure in a solution or new query.
  
  Again, thanks for everyones help.

  ksrangsy August 02, 2006 09:45

  - 0 +
• 
  I left out one thing from the example code, so I will post it again.
  
  if (object_id('dbo.prTmp) is not null) drop proc dbo.prTmp
  go
  
  create proc dbo.prTmp
  @intEmployeeID int=Null
  , @intReturnCode int=NULL output
  , @chvErrorMessage varchar(255)=NULL output
  /**********************************************************
  Object: dbo.prTmp
  Description: ...
  
  Usage: dbo.prTmp @intReturnCode output = 0 for success, otherwise there is an error, @chvErrorMessage output = error message if any
  
  Returns: lastName, firstName
  
  $Workfile: prTmp.sql $
  
  $Author: ... $. Email: ...
  
  $Revision: x.y $
  
  Example: exec dbo.prTmp;
  
  Created: .... $Modtime: ... $.
  **********************************************************/
  as
  set nocount on
  if (@intEmployeeID Is Null) Goto Help
  
  select lastName, firstName from employee (NoLock) where employeeID = @intEmployeeID
  
  @error
  
  Help:
  Exec sp_usage
  @objectName = 'dbo.prTmp'
  , @desc = '...'
  , @parameters='@intEmployeeID, ...'
  , @example='exec dbo.prTmp ;'
  , @returns='lastName, firstName'
  , @author='...'
  , @workfile='prTmp.sql'
  , @email='...'
  , @version='x', @revision='y'
  , @datecreated='...', @datelastchanged='...'
  return -1
  go
  
  grant execute on dbo.prTmp to execprocs
  go
  
  when executing just the stored procedure name, the message returned is what's in the comments directly after the parameters in the stored procedure, copy & paste. But if I copy one line at a time and create the newline characters myself, then SQL Prompt works perfectly. So I think it has something to do with the newline character when mass copying comments from the "Messages" result set.
  
  So I guess I'm going to have to change my commenting style, commenting style develope by my predicessor, or is there something else I can do ...

  ksrangsy August 02, 2006 10:17

  - 0 +
• 
  Hi,
  
  I just tried to use SQLPrompt in order to get the list of parameters from a SP with intellisense...
  
  Ex: EXEC dbaChangeObjectOwner -- this SP expects two parameters, but nothing shows....I then typed a snippet code (ssf + TAB), this pasted a SELECT * FROM ....the parameter list from the previous SP are now showing....Is this normal?
  
  thanks

  nick_70 August 02, 2006 14:05

  - 0 +
• 
  Hi Ken,
  
  I'm afraid I still can't reproduce your problem. I created some 'Messages' output and copied it to the comment, but it didn't break SQL Prompt 2.
  There's not a lot more i can do, I'm afraid. Hopefully, version 3 will fix your problem, as it will be a complete rewrite, including the parser.
  
  Regards,
  
  Tilman
  
  PS: nick_70, could you report your problem in a separate thread, please, because it doesn't really fit in here.

  Tilman August 03, 2006 04:28

  - 0 +

Add comment

Please sign in to leave a comment.