January 15, 2008 at 2:38 pm
Hello all
I am still rather new at this and i am trying to write a query on some tables that start with a number. However, i get an incorrect syntax when writing a query against a table that starts with a number. This was my first web application and i didnt know any better i guess, but is there anyway besides renaming all 500 tables with a letter before the number?
for example if i rename the table from 11rsadmin to a11rsadmin the query is successful.
can i set some sort of variable or something at the top of my query to let the query analyzer know that my tables start with an integer and not a character?
Thank You
January 15, 2008 at 2:47 pm
Select * From [1invalidtablename]
January 15, 2008 at 2:48 pm
Table names shouldn't start with a number. They fall into the category of identifiers which , per Books Online, must conform to the following:
The rules for the format of regular identifiers depend on the database compatibility level. This level can be set by using sp_dbcmptlevel. When the compatibility level is 90, the following rules apply:
The first character must be one of the following:
A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also 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. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.
Subsequent characters can include the following:
Letters as defined in the Unicode Standard 3.2.
Decimal numbers from either Basic Latin or other national scripts.
The at sign, dollar sign ($), number sign, or underscore.
The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words.
Embedded spaces or special characters are not allowed.
Supplementary characters are not allowed.
Otherwise - put brackets around the names.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2008 at 3:15 pm
Thank You both very much
love the signatures...lol "A lack of planning on your part does not constitute and emergency on mine!" <-- I have that same sticker behind my desk. 😛
I am actually going to try and redo the database off only 2 tables, one will be the good data and one will be the errors. Then write queries based on if you are 11rs, or 57rs, or 99rs...etc
what it actually is: a database that imports a script output that i wrote. In an effort to clean up or SAN and illegal/old files i thought it would be easiest and fastest for the computer managers out there to just look at the webpage versus actually right clicking every file which we all know will not happen...haha
Thanks again!
Joe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply