I'm trying to generate some reports from our AS400 mainframe using OLE
DB. The problem is some of the fields have a name like josel#. For
some reason, the Report builder in VS.net 2003 adds a space between
the # and the characters. I've tried adding [] around the field but
the as400 does not like that. Has anyone had any experience with this
problem?
BartIs this happening in generic query designer?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<Bart> wrote in message news:nahu915fhecll9qe7hvdpmm82uhsak6md8@.4ax.com...
> I'm trying to generate some reports from our AS400 mainframe using OLE
> DB. The problem is some of the fields have a name like josel#. For
> some reason, the Report builder in VS.net 2003 adds a space between
> the # and the characters. I've tried adding [] around the field but
> the as400 does not like that. Has anyone had any experience with this
> problem?
> Bart|||have you set the language for the report to the correct language?
Showing posts with label names. Show all posts
Showing posts with label names. Show all posts
Thursday, March 29, 2012
Field names must be CLS-compliant identifiers.
While creating a report using 'CreateReport' API method for Report Services
2000, I am getting the following error.
//
A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
--> A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
//
The error occurs due to the space on the computed field name. If I
concatinate the words with '_' (for example) then I do not get an error.
I would like know how can I create report that has field with the name like
â'Count Borrower Nameâ', etc?
Thanks.
-SueThe CLS-compliance rule for field names comes from the fact that in vast
majority of scenarios fields in expressions are referenced using the
following syntax: =Fields!FieldName.Value
If a field name is not CLS-compliant (for example "Field Name") then the
syntax above would result in VB compilation error: =Fields!Field Name.Value
is not a valid VB snippet.
RDL does not support field names with spaces. Although if you data source
returns non-CLS-compliant name you designer will build
<Field name="non_CLS_Compliant_Name">
<DataField>non CLS Compliant Name</DataField>
</Field>
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E82BA30A-FD8B-48E9-BF24-28EA9408301D@.microsoft.com...
> While creating a report using 'CreateReport' API method for Report
Services
> 2000, I am getting the following error.
> //
> A field in the data set 'MainDataSet' has the name 'Count Borrower Name'.
> Field names must be CLS-compliant identifiers.
> --> A field in the data set 'MainDataSet' has the name 'Count Borrower
Name'.
> Field names must be CLS-compliant identifiers.
> //
>
> The error occurs due to the space on the computed field name. If I
> concatinate the words with '_' (for example) then I do not get an error.
> I would like know how can I create report that has field with the name
like
> 'Count Borrower Name', etc?
> Thanks.
> -Sue
>
2000, I am getting the following error.
//
A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
--> A field in the data set â'MainDataSetâ' has the name â'Count Borrower Nameâ'.
Field names must be CLS-compliant identifiers.
//
The error occurs due to the space on the computed field name. If I
concatinate the words with '_' (for example) then I do not get an error.
I would like know how can I create report that has field with the name like
â'Count Borrower Nameâ', etc?
Thanks.
-SueThe CLS-compliance rule for field names comes from the fact that in vast
majority of scenarios fields in expressions are referenced using the
following syntax: =Fields!FieldName.Value
If a field name is not CLS-compliant (for example "Field Name") then the
syntax above would result in VB compilation error: =Fields!Field Name.Value
is not a valid VB snippet.
RDL does not support field names with spaces. Although if you data source
returns non-CLS-compliant name you designer will build
<Field name="non_CLS_Compliant_Name">
<DataField>non CLS Compliant Name</DataField>
</Field>
"Sue" <Sue@.discussions.microsoft.com> wrote in message
news:E82BA30A-FD8B-48E9-BF24-28EA9408301D@.microsoft.com...
> While creating a report using 'CreateReport' API method for Report
Services
> 2000, I am getting the following error.
> //
> A field in the data set 'MainDataSet' has the name 'Count Borrower Name'.
> Field names must be CLS-compliant identifiers.
> --> A field in the data set 'MainDataSet' has the name 'Count Borrower
Name'.
> Field names must be CLS-compliant identifiers.
> //
>
> The error occurs due to the space on the computed field name. If I
> concatinate the words with '_' (for example) then I do not get an error.
> I would like know how can I create report that has field with the name
like
> 'Count Borrower Name', etc?
> Thanks.
> -Sue
>
Field Names from SQL Statement
Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?
For example:
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK
resulting field names:
FIELD1
FIELD2
FIELD3
ANOTHERNAME
Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.
Just a shot in the dark because I don't know if anyone has already done something like this before.
Thank you in advance,
JeffI'm not quite sure what you are after... but, assuming that all columns are aliased... it would be the 'word' preceding the commas and the FROM.
Where are you trying to consume this from? ADO.NET will expose these field names to you, or are you looking to somehow consume them in just SQL Server?
--Mike|||What I am trying to do is build a report editor whereby the user simply enters a SQL statement on one tab. The second tab would then contain a datagrid with the field names as one column and some other columns that are formatting the report such as column width, forecolor, etc. for each field. All of this information is then written to a database table to be used later to restore that report definitions which are used to build a dynamic datagrid to display the results of the query.
Unfortunately, not all fields are aliased so I cannot simply use those words that precede a comma. Additionally, there are also times when fields are concatenated and include commas such as TABLE1.LAST_NAME + ', ' + TABLE1.FIRST_NAME AS FULL_NAME.
Field names
Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick
|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>
|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick
|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>
|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
Field names
Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
Field Names
I want to build a report that from an OLAP data source that uses a named set
that is a rolling 12 month view. The problem is I don't like the field names
that are displayed. For Example,
'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
All I want is August 2003. I know I can edit the field name, but what
happens when the fields change due to the nature of the rolling view? Any
ideas?
--
Michael Hardy
ETL Developer
Visit our web pages:
www.atlantis.com
www.oceanclub.com
www.oneandonlyresorts.com
www.kerzner.comOpuspocus wrote:
> I want to build a report that from an OLAP data source that uses a named set
> that is a rolling 12 month view. The problem is I don't like the field names
> that are displayed. For Example,
> 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> All I want is August 2003. I know I can edit the field name, but what
> happens when the fields change due to the nature of the rolling view? Any
> ideas?
We uses this technique, coudl be more out there...
WITH
Member [Measures].[TimeMemberUniqueName] as
'[Time].currentmember.UniqueName'
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
Time].[2003].[Q4].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
{ [SelectedPeriod]} on 1
FROM XX_YY
In the dynamic version we get the "lag" and actual date from parameters
and code functions. We then get a static field_name
"Measures_TimeDisplayName" to be used in the report.
Regards
// Jonas Montonen|||I'm trying to work out the syntax. I get a 'Query failed: Syntax error in
axis definition' for the following MDX. Can't figure out why.
WITH
Member [Measures].[TimeMemberUniqueName] as
'[ArrivalDate].currentmember.UniqueName',
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[ArrivalDate].[All ArrivalDate].[2003].[3] :
[ArrivalDate].[All ArrivalDate].[2003].[3].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[RsrvtnCount] } ON Axis(0)
{ [SelectedPeriod]} on ROWS
FROM [RsrvtnSummary]
"Jonas Montonen" wrote:
> Opuspocus wrote:
> > I want to build a report that from an OLAP data source that uses a named set
> > that is a rolling 12 month view. The problem is I don't like the field names
> > that are displayed. For Example,
> >
> > 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> > All I want is August 2003. I know I can edit the field name, but what
> > happens when the fields change due to the nature of the rolling view? Any
> > ideas?
> We uses this technique, coudl be more out there...
> WITH
> Member [Measures].[TimeMemberUniqueName] as
> '[Time].currentmember.UniqueName'
> Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
> SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
> Time].[2003].[Q4].Lag(3)'
> SELECT
> { [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
> { [SelectedPeriod]} on 1
> FROM XX_YY
> In the dynamic version we get the "lag" and actual date from parameters
> and code functions. We then get a static field_name
> "Measures_TimeDisplayName" to be used in the report.
> Regards
> // Jonas Montonen
>sql
that is a rolling 12 month view. The problem is I don't like the field names
that are displayed. For Example,
'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
All I want is August 2003. I know I can edit the field name, but what
happens when the fields change due to the nature of the rolling view? Any
ideas?
--
Michael Hardy
ETL Developer
Visit our web pages:
www.atlantis.com
www.oceanclub.com
www.oneandonlyresorts.com
www.kerzner.comOpuspocus wrote:
> I want to build a report that from an OLAP data source that uses a named set
> that is a rolling 12 month view. The problem is I don't like the field names
> that are displayed. For Example,
> 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> All I want is August 2003. I know I can edit the field name, but what
> happens when the fields change due to the nature of the rolling view? Any
> ideas?
We uses this technique, coudl be more out there...
WITH
Member [Measures].[TimeMemberUniqueName] as
'[Time].currentmember.UniqueName'
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
Time].[2003].[Q4].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
{ [SelectedPeriod]} on 1
FROM XX_YY
In the dynamic version we get the "lag" and actual date from parameters
and code functions. We then get a static field_name
"Measures_TimeDisplayName" to be used in the report.
Regards
// Jonas Montonen|||I'm trying to work out the syntax. I get a 'Query failed: Syntax error in
axis definition' for the following MDX. Can't figure out why.
WITH
Member [Measures].[TimeMemberUniqueName] as
'[ArrivalDate].currentmember.UniqueName',
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[ArrivalDate].[All ArrivalDate].[2003].[3] :
[ArrivalDate].[All ArrivalDate].[2003].[3].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[RsrvtnCount] } ON Axis(0)
{ [SelectedPeriod]} on ROWS
FROM [RsrvtnSummary]
"Jonas Montonen" wrote:
> Opuspocus wrote:
> > I want to build a report that from an OLAP data source that uses a named set
> > that is a rolling 12 month view. The problem is I don't like the field names
> > that are displayed. For Example,
> >
> > 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> > All I want is August 2003. I know I can edit the field name, but what
> > happens when the fields change due to the nature of the rolling view? Any
> > ideas?
> We uses this technique, coudl be more out there...
> WITH
> Member [Measures].[TimeMemberUniqueName] as
> '[Time].currentmember.UniqueName'
> Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
> SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
> Time].[2003].[Q4].Lag(3)'
> SELECT
> { [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
> { [SelectedPeriod]} on 1
> FROM XX_YY
> In the dynamic version we get the "lag" and actual date from parameters
> and code functions. We then get a static field_name
> "Measures_TimeDisplayName" to be used in the report.
> Regards
> // Jonas Montonen
>sql
Field names
Hi
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Is there a preference in sql server 2005 as to how fields/objects are
>> named such as Client_Address, Client Address, Client-Address or
>> ClientAddress?
>> Also what are the reserved words that should be avoided in field names?
>> Thanks
>> Regards
>>
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
>> OK, well that was just an example. What about answers to my questions?
>> Regards
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
Is there a preference in sql server 2005 as to how fields/objects are named
such as Client_Address, Client Address, Client-Address or ClientAddress?
Also what are the reserved words that should be avoided in field names?
Thanks
Regards"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
If this is a client table, why would you include the word client in the
column definition...
That is like working for the department of redundancy department. <wink>
Rick|||OK, well that was just an example. What about answers to my questions?
Regards
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ueqSuvBnFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> Is there a preference in sql server 2005 as to how fields/objects are
>> named such as Client_Address, Client Address, Client-Address or
>> ClientAddress?
>> Also what are the reserved words that should be avoided in field names?
>> Thanks
>> Regards
>>
> If this is a client table, why would you include the word client in the
> column definition...
> That is like working for the department of redundancy department. <wink>
> Rick
>|||"John" <John@.nospam.infovis.co.uk> wrote in message
news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
> OK, well that was just an example. What about answers to my questions?
> Regards
>
Oops. I mean to include the following link:
http://vyaskn.tripod.com/object_naming.htm
Regards,
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks. Funny it mentions ClientAddress as a valid example.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OdLk%23iCnFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:euImh$BnFHA.3408@.tk2msftngp13.phx.gbl...
>> OK, well that was just an example. What about answers to my questions?
>> Regards
> Oops. I mean to include the following link:
> http://vyaskn.tripod.com/object_naming.htm
>
> Regards,
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||On Mon, 8 Aug 2005 14:06:29 +0100, John wrote:
>Hi
>Is there a preference in sql server 2005 as to how fields/objects are named
>such as Client_Address, Client Address, Client-Address or ClientAddress?
>Also what are the reserved words that should be avoided in field names?
>Thanks
>Regards
>
Hi John,
Here are the rules for identifiers from the 2000 version of Books
Online:
(start quote)
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z
and from A through Z, in addition to letter characters from other
languages.
* The underscore (_), "at" sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have special
meaning in SQL Server. An identifier beginning with the "at" sign
denotes a local variable or parameter. An identifier beginning with
a number sign denotes a temporary table or procedure. An identifier
beginning with double number signs (##) denotes a global temporary
object.
Some Transact-SQL functions have names that start with double at
signs (@.@.). To avoid confusion with these functions, it is
recommended that you do not use names that start with @.@..
2. Subsequent characters can be:
* Letters as defined in the Unicode Standard 2.0.
* Decimal numbers from either Basic Latin or other national scripts.
* The "at" sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
4. Embedded spaces or special characters are not allowed.
When used in Transact-SQL statements, identifiers that fail to comply
with these rules must be delimited by double quotation marks or
brackets.
(end quote)
A list of reserved words can also be found in Books Online (use the
index; find reserved keyword, listed).
I don't have the 2005 version, but I don't think that the rules for
identifiers will be changed. And I'm sure that the 2005 version of BOL
will also feature a reserved words list.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
Take a look at the topic "Identifiers" in Books Online. It lists the
requirements/restrictions for object names in SQL Server.
> Also what are the reserved words that should be avoided in field names?
You should avoid keywords reserved by SQL Server. If you use a reserved
word, you'll always need to delimit it with quotes or brackets in order to
avoid syntax errors. This is explained in the Identifiers topic, but also
see the topic Reserved Keywords (Transact-SQL) in Books Online for a list of
reserved words.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
> named such as Client_Address, Client Address, Client-Address or
> ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>|||Hi,
As I now ClientAddress Coloum Namewe can use because the special
character are not allowed in Coloum Name.
Regards,
-Ajay Lonare
"John" <John@.nospam.infovis.co.uk> wrote in message
news:#bYyAoBnFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a preference in sql server 2005 as to how fields/objects are
named
> such as Client_Address, Client Address, Client-Address or ClientAddress?
> Also what are the reserved words that should be avoided in field names?
> Thanks
> Regards
>
Tuesday, March 27, 2012
Field Aliases
I don't know if that is the correct term or not but here is what I want to do:
I have a table in a sql server db that has generic field names (form1, form2, etc). I want the users to be able to define specific names for each field unique to them. Then when the 'table' is displayed to them(via a web page and asp/ado), I want them to see 'their' names for each field rather than the generic ones. Any ideas?
Thanks!!Maybe you can create a users table and a field names table. In the field names table, you create a foreign key to your original generic table and the users table. In your field names table, you create the same number of columns as in your generic table. For every user you can now store the field names.|||I'd recommend against doing this in the first place, but if it is absolutely necessary then the problem falls under data presentation, and is thus best handled by the interface (using cookies, config files, or whatever). The database server is not best suited to perform this task.
blindman|||Thanks for your input. What I did was simply create a table of 'Aliases' that each client could determine. Then, when they opened the original table, instead of seeing the actuall table headings, they see their customized version.sql
I have a table in a sql server db that has generic field names (form1, form2, etc). I want the users to be able to define specific names for each field unique to them. Then when the 'table' is displayed to them(via a web page and asp/ado), I want them to see 'their' names for each field rather than the generic ones. Any ideas?
Thanks!!Maybe you can create a users table and a field names table. In the field names table, you create a foreign key to your original generic table and the users table. In your field names table, you create the same number of columns as in your generic table. For every user you can now store the field names.|||I'd recommend against doing this in the first place, but if it is absolutely necessary then the problem falls under data presentation, and is thus best handled by the interface (using cookies, config files, or whatever). The database server is not best suited to perform this task.
blindman|||Thanks for your input. What I did was simply create a table of 'Aliases' that each client could determine. Then, when they opened the original table, instead of seeing the actuall table headings, they see their customized version.sql
Subscribe to:
Posts (Atom)