[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Msg 102 incorrect syntax near tempdev

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

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

 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.