Format SQL Error

• 
  Hi @Superz
  
  Can you disable formatting for blocks of code: https://documentation.red-gate.com/sp9/formatting-your-code
  
  If problem persists, can you share a sample script?
  
  Thanks.

  Tianjiao_Li July 19, 2018 15:38

  - 0 +
• 
  I already submitted a comment in response and I got a message saying the message needed to be reviewed after I edited it. I don't know where it went but this site is broken.

  Superz August 03, 2018 20:39

  - 0 +
• 

  First off it isn't that I don't know how to use SQL format. We actually use it all the time for formatting code to standardize it before it gets committing to our code repository. But maybe there is something in the settings that I'm not seeing. 
  
  My problem is that I when I try to format just a small piece of a code that is among lot of other code, which might not be error free, I get an error when formatting the small piece of code that I want to format. For example, when writing code for SSIS packages I write the code in SSMS first and then copy and paste it into Visual Studio (VS). Before I copy the code into VS I like to format it. In this case there can be lots of other code in this script file because this one script file can be used many times over for research, testing, and for scripts for other pieces of my SSIS package. It’s a scratch pad or playground essentially and some of the other code can be broken. I don’t want to go through a whole file and comment out or fix those other pieces of code just to get one chunk of code formatted.

  In my example below the code that I want to format is just below the first comment and before the second comment. If I highlight that and do the SQL format I get an error because of the error below the second comment.

   

   

  <p></p><p>--Code to format is below before next comment. I only</p><p>--want to format the declare and first select in this script.</p><p><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">DECLARE</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"> @Temp </span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">TABLE</span><br></p><p>(</p><p><span>&nbsp;&nbsp;&nbsp; id </span>INT,</p><p><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">&nbsp;&nbsp;&nbsp; FirstName </span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">VARCHAR</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">(</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">100</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">)</span></p><p>);</p><p><br></p><p>SELECT id,<span>&nbsp;FirstName </span>FROM @Temp;</p><p><br></p><p>--The following statement below is broken and causes the above to not </p><p><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">--format </span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">when just the above is selected for formatting regardless of the</span></p><p>--rest of the script.</p>
  <p>SELECT id,<span>&nbsp; FirstName</span></p><p>FRO @Temp;</p><p>&nbsp;</p>

   

  
  

  Superz August 03, 2018 20:42

  - 0 +
• 
  This works for me. Note that your post has the "DECLARE @... TABLE" on the commented line. I assume that's not the case in SSMS.

  
  

  Highlight the code, then format.
  

  
  

  Prompt 9.1.10

  
  

  
  
  

  way0utwest August 17, 2018 14:23

  - 0 +

Add comment

Please sign in to leave a comment.