Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #489

SSMS Query Designer chokes on ANSI Analytic Functions. Any Workaround?

Show all headers | View raw

SUMMARY:
SSMS Query Designer chokes on analytic functions.  Is there any
workaround for this?


EXAMPLE:
This query runs fine:
 SELECT
 *
 ,ROW_NUMBER() OVER (PARTITION BY TABLE_SCHEMA, TABLE_TYPE ORDER BY
TABLE_SCHEMA,  TABLE_TYPE, TABLE_NAME DESC) AS row_selector_nr
FROM
INFORMATION_SCHEMA.TABLES

But highlight the query, right click and choose "Design Query in
Editor . . "

You will get an error dialog box that says:
"The text that you have selected cannot be represented graphically
using the Query Designer. Please select just the text necessary to
design a SELECT, DELETE, INSERT or UPDATE statement."

The query designer is choking on the ROW_NUMBER() function.


DETAIL:
I generally prefer to simply write my SQL queries, but a lot of
clients like to use the Query Designer.  And for just dashing off a
quick join, re-aliasing, etc, the designer is quite nice.  I would
like to switch between the two. Edit the code to fix the designer's
mistakes, but then paste the code into the designer to take advantages
of its strengths.

When my needs involve an analytic function, this issue keeps me from
using the designer.   Does anyone know if there is a way to make the
designer accept this function?

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

SSMS Query Designer chokes on ANSI Analytic Functions.  Any Workaround? bill <billmaclean1@gmail.com> - 2011-07-01 11:17 -0700
  Re: SSMS Query Designer chokes on ANSI Analytic Functions. Any Workaround? Erland Sommarskog <esquel@sommarskog.se> - 2011-07-01 23:17 +0200
    Re: SSMS Query Designer chokes on ANSI Analytic Functions. Any Workaround? bill <billmaclean1@gmail.com> - 2011-07-02 01:05 -0700
      Re: SSMS Query Designer chokes on ANSI Analytic Functions. Any Workaround? Erland Sommarskog <esquel@sommarskog.se> - 2011-07-02 10:30 +0200
        Re: SSMS Query Designer chokes on ANSI Analytic Functions. Any Workaround? bill <billmaclean1@gmail.com> - 2011-07-07 12:05 -0700
    Re: SSMS Query Designer chokes on ANSI Analytic Functions. Any Workaround? "Fred." <ghrno-google@yahoo.com> - 2011-07-04 16:31 -0700

csiph-web