3

I am getting a error when I execute the following query :

select MaterialTypes as 'Reasons(Polyesters. Unsaturated polyester resins are the simplest,Orthophthalic,Isophthalic,Vinyl ester,Phenolic, Epoxy, Gel coat)' from Material

as

Msg 103, Level 15, State 4, Line 9 The identifier that starts with 'Reasons(Polyesters. Unsaturated polyester resins are the simplest,Orthophthalic,Isophthalic,Vinyl ester,Phenolic, Epoxy, Gel coat)' is too long. Maximum length is 128.

I found similar issue How to resolve maximum length of 128 on a column alias when using FOR XML PATH?

However the solution dint help. Can anyone tell me how to increase the ALIAS length?

Community
  • 1
  • 1
mano
  • 308
  • 3
  • 19
  • 1
    Because the limit is imposed by the data engine there probably isn't a workaround. Can you amend the field names in the presentation layer instead? – David Rushton Nov 01 '16 at 09:38
  • 1
    @destination-data "amend the field names in the presentation layer instead" How do we do that? – mano Nov 01 '16 at 09:40
  • 1
    how would an alias that long be useful to anyone? it's ridiculous. – Tanner Nov 01 '16 at 09:42
  • @Tanner its clients requirement, mine is bit rigid – mano Nov 01 '16 at 09:43
  • How/where are you presenting your data? Is it displayed in an app or shared in a report? – David Rushton Nov 01 '16 at 09:43
  • @destination-data Its report which is generated via Query and nothing is involved in between. – mano Nov 01 '16 at 09:44
  • How is the report consumed? Does your client execute the query and read the results in Management Studio? Do you execute it and share via email? – David Rushton Nov 01 '16 at 09:52
  • destination-data sorry, the query is feed into CXGrid component in the application however the grid is generic one which is used for more than one report. – mano Nov 01 '16 at 09:54
  • 1
    You are attempting to do something that isn't possible, isn't a great idea and not a good fit for SQL of any flavour. The alias is generally meant to abbreviate but it can be used to provide "column headings". In your extreme case you want to convey too much information for a simple column heading. You just have to live within that 128 character limit. In truth I have never seen anyone need an alias this long and that's after several decades of SQL. abridge that v.long column heading more. You can do it. – Paul Maxwell Nov 01 '16 at 10:02
  • 2
    I see. How annoying that you are just 2 characters over the limit! 128 is a hard limit. This is because SQL Server stores column names in a [system table field with a data type of nVARCHAR(128)](https://msdn.microsoft.com/en-gb/library/ms188348.aspx). Aliases have to follow the same rules as columns names, for obvious reasons. – David Rushton Nov 01 '16 at 10:02
  • 3
    This is something you need to solve at the presentation layer. Tell your client that's the way it is, aliases or column names are restricted to 128 characters and that's that. If they can't change their report, that's tough on them then... – TT. Nov 01 '16 at 10:05
  • @destination-data Thanks i would try to convince the client about the Alias name. Some people here at SO dont try to understand other persons problem first and just try to "Down Vote" which is demotivating. Thanks for taking out time – mano Nov 01 '16 at 10:06
  • @TT Thanks Yes I would try to convince the client. – mano Nov 01 '16 at 10:07

0 Answers0