I have seen developers confused many times when they receive the following error message about Incorrect syntax near.
Msg 325, Level 15, State 1, Line 7
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
The reason for this error is when the user is trying to attempt to run a query or procedure or logic which is not compatible with the previous version of the SQL Server. When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to the next version. When database compatibility is set to the previous version and they are attempted with the procedure of the newer version they will throw the above error.
Fix/Workaround/Solution:
Change the database compatibility level using the following command.
For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90
For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100
I hope this will help you to fix the incorrect syntax near. You can reach out to me on Twitter.
Here are my few recent videos and I would like to know what is your feedback about them.
- Copy Database – SQL in Sixty Seconds #169
- 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
- Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
- 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
- Get Memory Details – SQL in Sixty Seconds #165
Reference: Pinal Dave (https://blog.sqlauthority.com)
87 Comments. Leave new
How would I or can I run this command on SSE (Sequel Server Express)?
Hi, Can anyone find the error of this query
SELECT ‘TNGModuleTNGExternal’ AS ‘Table’
GO
IF NOT EXISTS (SELECT name FROM syscolumns WHERE name=’cmt’ AND id=object_id(‘TNGModuleTNGExternal’))
EXEC sp_rename ‘[spider3].[TNGModuleTNGExternal].[comments]’, ‘cmt’, ‘COLUMN’
GO
Hello Pinal,
I am .net developer and I am having error while updating and inserting text data into sql server.
The error occurs when the text contains character ” ‘ “. Which is normal when we insert a long paragraph that has many apostrophe’s s symbol.
I get an error as invalid symbol near s
You need to double the single quotes
Madhivanan, Thank you so much! You have solved my problem with great insight!!
Can you help me please?
anybody correct this syntax for me plz ?
CREATE DATABASE 111 ON ( NAME = 111_data,
FILENAME =’D:\Project 2009\Web-Modules\111\db\Data_File.mdf’,
SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON ( NAME = ‘111_log’,
FILENAME = ‘D:\Project 2009\Web-Modules\111\db\Log_File.ldf’,
SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB )
when i run it, there is a error Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘111’. w
Hi Neven,
If the initial character of any identifier name is a number then cover it within square brackets. So change the name of database to [111].
Regards,
Pinal Dave
Ok, heres one.
SQL Server 2008, a new install.
This query errors
SELECT ID
FROM User
This query runs
SELECT ID
FROM [User]
Settings???
Hello Steve,
USER is a keyword and SQL Server take it as a keyword. While parsing the statement it founds the keyword at wrong place (FROM clause) and returns error. When we write [USER], it takes it as an identifier.
Regards,
Pinal Dave
Hi Pinal,
Would you please help me fixing this error.
“(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
(1 row(s) affected)
(1 row(s) affected)”
The Store proc is as follows
——————————————————
Create Database Bank
Go
Use Bank
go
Create table Account([ID] int, Name Varchar(100),
AccountNo varchar(100), Balance money)
Go
Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())
Go
Create procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query =’master.dbo.xp_cmdshell “dir ‘+@filepath+@pattern +’ /b”‘
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
–print @max1
–print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
–print @query
exec (@query)
insert into logtable (query) select @query
end
drop table #y
Executing the proc—-
Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'
—————————————–
Hi,
I am having kind of similar issue at my client. They are runnign their database on Microsoft Dynamics NAV with SQL server option. But when user tries to enter a sales order in NAV they get the following SQL error –
102,”42000″[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘{‘.
they are on SQL server 2008 and Microsoft dynamics NAV 4SP1 with NAV5.0 SP1 executables.
Thanks
Meenakshi
SELECT TOP (100) PERCENT b.bill_no, b.date, b.Amount, c.bill_no AS
Expr3, c.current_dat, em.date_adv_pay, em.adv_pay, ex.Date_of_expense,ex.Amount AS Expr5, ep.Date_of_Expense AS Expr6,
ep.Amount AS Expr7, en.bill_no AS Expr1,en.date AS Expr2, en.total, c.total AS Expr4
FROM bill_receive_entry b,
cus_pay_detail c,
emp_pay em,
expense_personnel ex,
expense_prod ep,
enlarge en
where b.date(+)=c.current_dat
and em.date_adv_pay=c.current_dat(+)
and ex.Date_of_expense = em.date_adv_pay(+)
and ep.Date_of_Expense = ex.Date_of_expense (+)
and en.date = ep.Date_of_Expense(+)
ORDER BY c.current_dat, c.bill_no, b.bill_no, em.date_adv_pay,ex.Date_of_expense, ep.Date_of_Expense, en.date
But the error is coming like, I am not able to detect the prob. Actually I am a fresher in sql. so plz do help it,s urgnt
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘)’.
It’s urgnt,
client is over-head!!!!!!!!!!
Is this website for demo only!!!!!!!!
what are u doing yaar
getting this error can u fix it ?
Incorrect syntax near ‘=’.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ‘=’.
Source Error:
Line 53: string del = “delete from tbluttam_allimage where id=” + DataList1.DataKeys[e.Item.ItemIndex];
Line 54: SqlCommand cmd = new SqlCommand(del, con);
Line 55: cmd.ExecuteNonQuery();
Line 56: heat();
Line 57:
Source File: c:inetpubvhostsuttamindustries.comhttpdocsadmin_uttammanageimage.aspx.cs Line: 55
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ‘=’.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +317
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
admin_uttam_manageimage.DataList1_DeleteCommand(Object source, DataListCommandEventArgs e) in c:inetpubvhostsuttamindustries.comhttpdocsadmin_uttammanageimage.aspx.cs:55
System.Web.UI.WebControls.DataList.OnDeleteCommand(DataListCommandEventArgs e) +108
System.Web.UI.WebControls.DataList.OnBubbleEvent(Object source, EventArgs e) +8736452
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.DataListItem.OnBubbleEvent(Object source, EventArgs e) +123
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
when I run command below
BACKUP DATABASE 1310_dB
TO DISK = ‘C:\Backuprestore\1310_db1.bak’;
WITH FORMAT;
GO
I am getting message as
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘1310’.
Please provide solution also let me know how to restore back up from file or file group
BACKUP DATABASE [1310_dB]
TO DISK = ‘C:Backuprestore1310_db1.bak’;
WITH FORMAT;
It is not a good practice to name an object name starting with a number
Hi Pinal,
Thanks for your help. Whenever I face any issue with Sql server and see your website, I will feel relaxed( I am sure I got the solution)
Thanks again
Vijay
Great,
It worked for me. I was trying to create a UDA using SQL clr and I was getting the exact same error and after executing the “EXEC sp_dbcmptlevel ‘db_name’, 100 ” I was able to deploy the UDA in VS successfully.
Thanks Pinal.
regards,
Bala
0 down vote favorite
I have created a store procedure for updating the patient name in multiple tables. When I execute it, shows the error like
Server: Msg 170, Level 15, State 1,
Line 1 Line 1: Incorrect syntax near ‘=’.
And my coding is
create procedure uppatname @pid varchar(150),@pname varchar(150)
as begin
declare @i as integer
declare @i1 as integer
declare @ttnm as varchar(100)
declare @tblnam as varchar(100)
drop table tbname
SELECT IDENTITY(int, 1,1) AS RowNumber, table_name
INTO tbname
FROM information_schema.columns
WHERE column_name = ‘pid’
AND table_catalog = ‘hospital’
AND table_name NOT LIKE ‘T%’
SET @i = (select count(*) from information_schema.columns
where column_name=’pid’ and table_catalog=’hospital’
and table_name not like ‘T%’)
SET @i1 = 1
WHILE @i1 <= @i
BEGIN
SET @tblnam = (select table_name from tbname where rownumber = @i1)
SET @ttnm = ('select * from ' + @tblnam + 'where pid = ' + @pid)
EXEC (@ttnm)
SET @i1 = @i1 + 1
END
END
kindly help me
Replace SET @ttnm = (‘select * from ‘ + @tblnam + ‘where pid = ‘ + @pid)
with
SET @ttnm = ‘select * from ‘ + @tblnam + ‘where pid = ‘ + @pid
create table products
(prodid int identity (100, 1) constraint products_pk primary key,
prodname varchar(30) constraint products_prodname_nn not null,
price money constraint products_price_chk check(price >= 0),
qoh int default 0 ,
remarks varchar(100),
catcode varchar(10) references categories (catcode) on delete casecade );
here iam getting error like this incorrect syntax near ‘ )’.
so please help me how to rectify this error
procedure or function expects a parameter which was not supplied
but i checked the procedure ,aspx.cs ,buisness layer and also data acess layer all are correct
one more problem in sql procedure which has one column of date type while executing 1st time all the values are passed
but for 2nd time an error is occured ie error on date type converting nvarchar to date type
how can we convert datatype while inserting values using stored procedure
use cast or convert function
Hi Pinaldave,
i read how we can remove the error that asking for upgrade etc but i am trying to copy one table from one database to other database on same SQL so how can we edit this syntax in the graphicaly
suppose two database are DB1, DB2
Insert into Table1
Select * from DB2..Table2
If table does not exist in db1
Select * into Table2 fromDB2..Table2