Avoid dollar sign in table name

Dollar sign ($) is allowed to be used in temparory table name in SQL Server. E.g. run the following script in SSMS:
CREATE TABLE #AA$(c1 int)
INSERT #AA$(c1) VALUES (1)
SELECT * FROM #AA$
DROP TABLE #AA$
GO

The script works fine. However, if you save the sacrit to a file (C:\test.sql), and run it in sqlcmd from command line:
 
C:\>sqlcmd -SYourServer -dtempdb -iC:\test.sql
You will get this error message:
Sqlcmd: Error: Syntax error at line 1 in file ‘C:\test.sql’.
 
Or if you run it in SQLCMD mode in SSMS, you will get "Query completed with errors"
This is because dollar sign and the brackets are used as sqlcmd script variable. Run the following script in SQLCMD mode in SSMS:
 
:Setvar tablename master.dbo.spt_values
SELECT * FROM $(tablename)
 
In the runtime, SQLCMD will replace the variable "tablename" with master.dbo.spt_values and returns all the records in the table.
This is also true for table variables and physical tables, e.g. the following works in SSMS:
 
DECLARE @AA$ TABLE (c1 int)
INSERT @AA$(c1) VALUES (1)
SELECT * FROM @AA$
DROP TABLE @AA$
GO

 

But will fail in sqlcmd or SQLCMD mode in  SSMS:

A fatal scripting error occurred.
Variable c1 is not defined.

So avoid using $ as the last character when creating tables, temporary tables, and table variables though it is allowed by SQL Server.
Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

3 Responses to Avoid dollar sign in table name

  1. I am working on creating a meta-database of public data for research purposes, and since I’m putting a lot of sources of data together into a single database, I want to have table names being based upon the original data source, and I have been thinking of using dollar signs $ to delineate the parts in the ultimate table name. So for instance if the original database was named “foo” and the original table was named “bar”, I thought about using “foo$bar” as the meta-database table name. Do you think the problem you mention in this blog entry will be a problem with doing this? Is there a way around the problem, by using quotes or escape characters?

    • phe1129 says:

      It is not a problem to have “$” in table name unless it is the last character. When it is the last character, it will be used together with “(” in inserting data and caused SQLCMD confused.

      You can always quote the table name using brackets, e.g. [foo$bar],[AA$]
      The following script will fail in SQLCMD mode if the table name is not quoted:

      CREATE TABLE [AA$](c1 int);
      INSERT [AA$](c1) VALUES (1);
      SELECT * FROM AA$;
      DROP TABLE AA$;
      GO

  2. Thank you so much for the information. It truly was helpful!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s