Msg 102 incorrect syntax near tempdev.
Applies to: Microsoft SQL Server, Hyper-V Windows Server 2008 R2 SP1
Problem Description.
One of my students was trying to add some files to TempDB for an instance
installed on a VM using below T-SQL code.
USE master;
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = n'tempdev2',
FILENAME = n'D:\Databases\tempdb_file2.ndf' ,
SIZE = 20MB,
FILEGROWTH = 10MB,
MAXSIZE = UNLIMITED );
ALTER DATABASE tempdb
ADD FILE ( NAME = n'tempdev3',
FILENAME = n'D:\Databases\tempdb_file3.ndf' ,
SIZE = 20MB,
FILEGROWTH = 10MB,
MAXSIZE = UNLIMITED );
ALTER DATABASE tempdb
ADD FILE ( NAME = n'tempdev4',
FILENAME = n'D:\Databases\tempdb_file4.ndf' ,
SIZE = 20MB,
FILEGROWTH = 10MB,
MAXSIZE = UNLIMITED );
GO
The student copied the above code from a lab manual but when he tried to execute
received the following error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'tempdev2'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'tempdev3'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'tempdev4'.
Cause.
The student used the Hyper-V Virtual Machine Connection console and the
Type Clipboard Text menu option of the console to copy statements from the lab
manual to the VM but the statements pasted by the student showed the SQL
National character (N) before the strings changed to lower case. That N in lower
case created those syntax errors.
Solution.
Changing all National characters to upper case allowed the T-SQL code to be
successfully parsed and executed.
USE master;
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev2',
FILENAME = N'D:\Databases\tempdb_file2.ndf' ,
SIZE = 20MB,
FILEGROWTH = 10MB,
MAXSIZE = UNLIMITED );
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev3',
FILENAME = N'D:\Databases\tempdb_file3.ndf' ,
SIZE = 20MB,
FILEGROWTH = 10MB,
MAXSIZE = UNLIMITED );
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev4',
FILENAME = N'D:\Databases\tempdb_file4.ndf' ,
SIZE = 20MB,
FILEGROWTH = 10MB,
MAXSIZE = UNLIMITED );
GO
![[Company Logo Image]](images/SQLCofee.jpg)
