NEW RELEASE: SQL PRETTY PRINTER VERSION 3.3.0

May 4th, 2012

We are pleased to let you know that SQL Pretty Printer V3.3.0 is available on May 4, 2012. This version begin to support MDX formatting. Add 12 new format options, now there are more than one hundred format options make sql pretty printer more flexible to achieve SQL layout you need.

Download SQL Pretty Printer:

Desktop version: http://www.dpriver.com/dl/sqlpp_desktop_v3_3_0.zip

Add-In for SSMS: http://www.dpriver.com/dl/ppforssms_3_3_0.zip

Add-In for Visual Studio: http://www.dpriver.com/dl/ppforvs_3_3_0.zip

1. support MDX formatting.

mdx drillthrough formatting

2. new format option:gfmtopt.AlignAndOrWithOnInJoinClause, able to align AND/OR keywords with ON in join clause.

select join clause align and with on

3. new format option:gfmtopt.expr_concat_string_max_length, format string concat expression into a more readable format.

sql formatting string concat expression

4. new format options: gfmtopt.CaseWhenInSamelineAsCase, gfmtopt.CaseThenExprInNewline,gfmtopt.CaseElseExprInNewline, able to set case expression into a new layout.

sql formatting case expression then else in newline

5. new format options: gfmtopt.BEStyle_createtable_leftBEIndentSize, gfmtopt.BEStyle_createtable_rightBEIndentSize,gfmtopt.Createtable_Fieldlist_Comma_option, gfmtopt.Createtable_Fieldlist_Style. Control layout for create table in a more flexible way, these format options apply to declare table as well.

sql formatting create table indent parenthesis

6. new format option: gfmtopt.Insert_Columns_Per_line. This format option make it much more easy to link value to column in insert statement. if there is a subquery in insert statement, select list also effected by this option no matter what’s value of Select_Columnlist_Style was set.

sql formatting insert column on each line

7. new format options: gfmtopt.WhereClauseAlignExpr, align expression in where clause.

sql formatting align expression in where clause

8. new format options: gFmtOpt.AlignAliasInFromClause, align table alias in from clause.

sql formatting align table alias in from clause

9. format privot table in a more readable layout

sql formatting pivot table

10. format AnalyticFunction in a more readable layout.

sql formatting analytic function

11. better support of –begin_no_format, –end_no_format tag to preserve original sql layout.

12. Improve internal parser to support more SQL syntax of different databases.

Download SQL Pretty Printer:

Desktop version: http://www.dpriver.com/dl/sqlpp_desktop_v3_3_0.zip

Add-In for SSMS: http://www.dpriver.com/dl/ppforssms_3_3_0.zip

Add-In for Visual Studio: http://www.dpriver.com/dl/ppforvs_3_3_0.zip

Posted in release, sql pretty printer | No Comments »

The error code 2869 while install/remove SQL Pretty Printer add-in for SSMS

March 28th, 2012

If you encounter error code 2869 while install/remove SQL Pretty Printer add-in for SSMS, this is mainly because you install this program as a standard user and just click ppforssmssetup.msi. This problem can be easily fixed by running setup.exe under the same directory. In problem still exists, These are the steps to solve the above mentioned problems:

1) Copy the ppforssmssetup.msi file to the root directory of your main hard drive (i.e. C:\).

2) Open Windows Notepad.

3) Copy this text into windows notepad:

msiexec /i C:\ppforssmssetup.msi

(You can write the total path of your .msi file in the system, after msiexec /i.)

4) Click File -> Save As…

Instead of saving it as a .txt file, change the file name to installer.bat.

Save the file to your desktop.

6) On your desktop, right click on the file and select Run as Administrator.

This will run the .msi file properly to install the application in the system.

If you need to remove SQL Pretty Printer add-in for SSMS, just replace command in step 3 with this:

msiexec /x C:\ppforssmssetup.msi

Posted in sql pretty printer | No Comments »

New release: SQL Pretty Printer Version 3.2.8

October 14th, 2011
  • Introduce 14 new format options in this version and there are more than 90 format options in SQL Pretty Printer.
  • Fix a bug that menu and toolbar disappeared in vs2010 after first installation.
  • About 2 times faster when format some big SQL scripts.
  • Fix a bug that can’t format join clause correctly in this sql: 
    SELECT
  *
FROM
  S1
  natural JOIN W3
  LEFT OUTER JOIN S2
    ON S1.S = S2.S
  • Improve format result if there is a comment after WHERE keyword directly.
  • Lots of minor bugs fixed.

Posted in release, sql pretty printer | No Comments »

How to add syntax colored and well formatted SQL in your wordpress blog

October 4th, 2011

People always post SQL code into blogs and forums need a tool to help them to prepare dull monochrome, badly-formatted SQL code for HTML-based publication in a nice clean style.

SQL Pretty Printer is able to output HTML in both <code> and <pre> blocks in a convenient way with code like this:

	<div><span style="font-family: Courier New; font-size: 10pt;">
	<span style="color: blue; ">WITH</span> <span style="color: maroon; ">mycte</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span>
	<br/>     <span style="color: blue; ">AS</span> <span style="color: maroon; ">(</span><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">=</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: red; ">'hello'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'a'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'b'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span><span style="color: maroon; ">)</span>
	<br/><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span>
	<br/><span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/><span style="color: blue; ">ORDER</span>  <span style="color: blue; ">BY</span> <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span>
	<br/>          <span style="color: maroon; ">x</span><span style="color: silver; ">;</span> 
	</span></div>

You can copy and embedded above HTML code into your blog to get a colored SQL like this:

WITH mycte(x)
     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)
         UNION ALL
         SELECT CONVERT(VARCHAR(1000), x + ‘a’)
         FROM   mycte
         WHERE  Len(x) < 10
         UNION ALL
         SELECT CONVERT(VARCHAR(1000), x + ‘b’)
         FROM   mycte
         WHERE  Len(x) < 10)
SELECT x
FROM   mycte
ORDER  BY Len(x),
          x; 

But in wordpress, the output will be looked like this that add additional lines.

WITH mycte(x)

     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘a’)

         FROM   mycte

         WHERE  Len(x) < 10

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘b’)

         FROM   mycte

         WHERE  Len(x) < 10)

SELECT x

FROM   mycte

ORDER  BY Len(x),

          x; 

It seems that editor of wordpress add an additional <br/> tag at the end of each line. this issue can be resolved by changing

function wpautop($pee, $br = 1) {

to

function wpautop($pee, $br = 0) {

in wp-includes/formatting.php

Posted in formatting, sql, wordpress | No Comments »

case(Capitalization) user defined identifier in SQL statement

October 4th, 2011

SQL Pretty Printer provides the ability to capitalize user defined identifier such as stored procedure name start with sp_ in SQL Server.

name: case_prefix_identitfer

setting path: capitalisation -> Prefix_ident

name: identifier_prefix_list

setting path: capitalisation -> Identifier prefix list

EXECUTE Sp_executesql
  @query,
  n’@age int’,
  @age = 25 

After setting case_prefix_identitfer to uppercase and identifier_prefix_list to sp_,

EXECUTE SP_EXECUTESQL
  @query,
  n’@age int’,
  @age = 25 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case variable name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of variable name in SQL statement, this feature helps you to find out variable in SQL statement quickly.

name: case_variable_name, default is no change.

setting path: capitalisation -> Variable

EXECUTE Sp_executesql
  @query,
  n’@age int’,
  @age = 25 

After setting case_variable_name to upper case,

EXECUTE Sp_executesql
  @QUERY,
  n’@age int’,
  @AGE = 25 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case alias name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of alias name in SQL statement, this feature helps you to find out alias in SQL statement quickly.

name: case_alias_name, default is no change.

setting path: capitalisation -> Alias

SELECT departmentname,
       Min(salary) AS min_salary
FROM   employees
GROUP  BY departmentname 

After setting case_alias_name to upper case,

SELECT departmentname,
       Min(salary) AS MIN_SALARY
FROM   employees
GROUP  BY departmentname 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case column name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of column name in SQL statement, this feature helps you to find out columns in SQL statement quickly.

name: case_column_name, default is no change.

setting path: capitalisation -> Column

SELECT department_id,
       Min(salary)
FROM   employees
GROUP  BY department_id 

After setting case_column_name to upper case,

SELECT DEPARTMENT_ID,
       Min(SALARY)
FROM   employees
GROUP  BY DEPARTMENT_ID 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case table name in SQL statement

October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of table name in SQL statement, this feature helps you to find out tables in SQL statement quickly.

name: case_table_name, default is no change.

setting path: capitalisation -> Table

SELECT department_id,
       Min(salary)
FROM   employees
GROUP  BY department_id 

After setting case_table_name to upper case,

SELECT department_id,
       Min(salary)
FROM   EMPLOYEES
GROUP  BY department_id 

Posted in format option, sql pretty printer, v238 | No Comments »

Case(capitalization) options used when formatting SQL statements

October 4th, 2011

There are 5 case options available while formatting SQL tokens in SQL Pretty Printer.

1. Uppercase(All caps): All letters are capital letters.

2. Lowercase: All letters are lower case.

3. InitCap: Only the first letter of the whole word capitalized.

4. No Change: Case of all letters in the word not changed.

5. InitCapEachWord: Also known also CamelCase or Pascal case, First letter of each word is capitalized.

TCaseOption = (coUppercase,coLowercase,coInitCap,coNoChange,coInitCapEachWord);

Posted in format option, sql pretty printer | No Comments »

« Older Entries

Gudu Software 2002-2011  Privacy Policy  Terms of Use  About Us