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
declare @ls_sql as varchar(500)
declare @ls_value as varchar(10)
set @ls_value = ”
set @ls_sql = ‘select ‘ + @ls_value
execute(@ls_sql)
why this is isn’t working? tnx for the help..
Are you trying to return empty string?
Use this
declare @ls_sql as varchar(500)
declare @ls_value as varchar(10)
set @ls_value = ”””
set @ls_sql = ‘select ‘ + @ls_value
execute(@ls_sql)
Also refer this to understand how single quotes work in dynamic sql
Hi Pinal,
I have checked with the DB compatibility.. its 100 (using SQL Server 2008)
but still getting the same err : Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
Can you pls help!! Below is my query..
ALTER PROCEDURE [Audit].[uspGetCardParameterAuditDtl]–3,123,126,122,129,’01-11-2009′,’15-11-2010′,”,0,10,0
@EntityTypeID INT,
@DailyLimitID INT,
@CreditLimitID INT,
@MaxTrnLimitID INT,
@BalanceAmtID INT,
@StartDate NVARCHAR(20),
@EndDate NVARCHAR(20),
@ModifiedByUser NVARCHAR(30),
@PageIndex INT,
@PageSize INT,
@TotalRecord INT OUTPUT
AS
BEGIN
DECLARE @LowerBound INT,@UppperBound INT
DECLARE @Query NVARCHAR(4000),@ModifiedByQuery NVARCHAR(200),@ParamVal NVARCHAR(1000)
SELECT @LowerBound = @PageIndex * @PageSize
SELECT @UppperBound = @PageSize – 1 + @LowerBound
SELECT @StartDate=DATEADD(day,DATEDIFF(day,0,@StartDate),0)
SELECT @EndDate=DATEADD(day,DATEDIFF(day,0,@EndDate),0)+1
IF(LEN(@ModifiedByUser)>0)
BEGIN
SET @ModifiedByQuery=’ AND U.UserName like ”%’+@ModifiedByUser+’%”’
END
ELSE
BEGIN
SET @ModifiedByQuery=”
END
SELECT @Query=’DECLARE @TmpTable TABLE (SNo INT, CardNumber BIGINT,DailyLimit NVARCHAR(100), CreditLimit NVARCHAR(100),
MaxTrnLimit NVARCHAR(100),CCMSBalance NVARCHAR(100),ModifiedBy NVARCHAR(512),ModifyOn DATETIME);
DECLARE @CTEParams TABLE(ParameterName NVARCHAR(100),ParameterId INT,ParameterValue NVARCHAR(100),EntityID INT);
INSERT INTO @CTEParams
Select P.ParameterName , P.ParameterId , PE.ParameterValue, PE.EntityID
from EntityParameterValues PE WITH(NOLOCK)
inner join Parameter P WITH(NOLOCK) ON PE.ParameterId = P.ParameterId
where PE.EntityTypeID=@EntityTypeIDVal;
INSERT INTO @TmpTable
Select ROW_NUMBER() OVER (ORDER BY CardNumber ASC) AS SNo
, C.CardNumber
, DailyLimit = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@DailyLimitIDVal and EntityId = C.CardId )
, CreditLimit = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@CreditLimitIDVal and EntityId = C.CardId)
, MaxTrnLimit = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@MaxTrnLimitIDVal and EntityId = C.CardId)
, CCMSBalance = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@BalanceAmtIDVal and EntityId = C.CardId)
,U.UserName AS ModifiedBy
,C.ModifiedDate AS ModifyOn
From [Audit].[CardAudit] C WITH(NOLOCK)
INNER JOIN aspnet_Users U WITH(NOLOCK)
ON C.ModifiedBy=U.NumericUserId
WHERE (C.CreatedDate>=@StartDateVal AND C.CreatedDate<@EndDateVAl)'+@ModifiedByQuery+';'+
'SELECT * FROM [@TmpTable] WHERE SNo BETWEEN @LowerBoundVal AND @UppperBoundVal ORDER BY CardNumber;'
SET @ParamVal='@EntityTypeIDVal INT,@StartDateVal NVARCHAR(20),@EndDateVal NVARCHAR(20),@LowerBoundVal INT,@UppperBoundVal INT,
@DailyLimitIDVal INT,@CreditLimitIDVal INT,@MaxTrnLimitIDVal INT,@BalanceAmtIDVal INT, ,@TotalRecordsOut INT OUT'
Print @Query
EXECUTE sp_executeSQL @Query,@ParamVal,@EntityTypeIDVal=@EntityTypeID,@StartDateVal=@StartDate,@EndDateVal=@EndDate,
@DailyLimitIDVal=@DailyLimitID,@CreditLimitIDVal=@CreditLimitID,@MaxTrnLimitIDVal=@MaxTrnLimitID,
@BalanceAmtIDVal=@BalanceAmtID,@LowerBoundVal=@LowerBound,@UppperBoundVal=@UppperBound,
@TotalRecordsOut=@TotalRecord OUTPUT
SELECT @TotalRecord
END
Hi Pinal,
Here is my SQL Script to Create TESTDB. I Try to Create DB on SQL Server 2008 R2 But got Lots error Can you tell me what’s wrong. I am not expert on SQL Server. I can create DB Desing but I am tring to create DB Run-time on my Program. Here are code….
DECLARE @InstanceName varchar(100),
@InstanceLocation varchar(100),
@InstancePath varchar(100)
SELECT @InstanceName = convert(varchar, ServerProperty(‘InstanceName’))
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’,
@value_name=’SQLEXPRESS’,
@value=@InstanceLocation OUTPUT
SELECT @InstanceLocation = ‘Software\Microsoft\Microsoft SQL Server\’+@InstanceLocation+’\Setup’
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=@InstanceLocation,
@value_name=’SQLPath’,
@value=@InstancePath OUTPUT
SELECT @InstancePath
USE [master]
GO
/****** Object: Database [TestDB] Script Date: 12/23/2011 04:56:32 ******/
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N’TestDB’, FILENAME = N” + @InstancePath + N’\DATA\TestDB.mdf’ , SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’TestDB_log’, FILENAME = @InstancePath + N’\DATA\TestDB_log.ldf’ , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [TestDB].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TestDB] SET AUTO_CLOSE ON
GO
ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestDB] SET DISABLE_BROKER
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TestDB] SET READ_WRITE
GO
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestDB] SET MULTI_USER
GO
ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestDB] SET DB_CHAINING OFF
GO
USE [TestDB]
GO
Error List
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘@InstancePath’.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database ‘TestDB’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
Thanks You Pinal
I have also get this error incorrect syntax near ‘,’. It took me lot of errors and checks to solve this issue.
I am using DSN for SQL Server. While creating DSN, i have selected provider there as SQL Server Native Client 10.0.
After so many checks i have changed this provider to SQL Server Only and then created the DSN then the issue is solved for me.
But thanks for this site also.
CREATE TRIGGER CustomerUpdateMail
— and the table is CustomerInfo
ON CustomerInfo
— trigger is fired when an update is made for the table
FOR UPDATE
AS
— holds the CustomerID so we know which Customer was updated
declare @CustomerID varchar(10)
— holds the body of the email
declare @body varchar(2000)
— holds the old customer name which has been changed
declare @CustomerName varchar(10)
— holds the new customer name
declare @CustomerNewName varchar(10)
— gets the previous customer first name that was deleted
SELECT @CustomerID = Customer_id,
@CustomerName = d.First_Name
FROM deleted d
— gets the new customer first name
SELECT @CustomerNewName = First_Name
FROM inserted
SET @body = ‘Customer with ID=’ @CustomerID ‘ has been updated
with previous First Name is ‘ @CustomerName ‘
and the new First Name is ‘ @CustomerNewName
–xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = ‘ali_raza_shaikh@datasprings.com’,
@subject = ‘Customer Information Updated’,
@message = @body
GO
i got an error like this
Msg 102, Level 15, State 1, Procedure CustomerUpdateMail, Line 28
Incorrect syntax near ‘@CustomerID’.
Can u tell me where i have to change the values in it
when ever we exeute stored procedure in sql server 2005,
i return value parameter is also attached. I writer procedure
create procedure pname
as
select * from tablename;
but when i execute it
the sql server generte code
use dbname
go
declare @return_value int
exec @return_value=dbo.pname
select ‘Return Value’=@return_value
go
how to avoid this @return_value
SELECT * FROM (select datename(month,docdate)as ‘MONTH’ ,sum(doctotal)AS ‘TOTAL_SALES’,
YEAR(DOCDATE)AS ‘YEAR’,DATENAME(quarter, DOCDATE) AS ‘QTR’
from oinv where convert(varchar(4),year(docdate)) = CONVERT(VARCHAR(4),YEAR(GETDATE()))-4
group by datename(month,docdate),YEAR(DOCDATE),DATENAME(quarter, DOCDATE)) Q1
LEFT OUTER JOIN
(
SELECT TOTAL_SALES,YEAR FROM (select datename(month,docdate)as ‘MONTH’ ,sum(doctotal)AS ‘TOTAL_SALES’,
YEAR(DOCDATE)AS ‘YEAR’,DATENAME(quarter, DOCDATE) AS ‘QTR’
from oinv where convert(varchar(4),year(docdate)) = CONVERT(VARCHAR(4),YEAR(GETDATE()))-3
group by datename(month,docdate),YEAR(DOCDATE),DATENAME(quarter, DOCDATE))) Q2
ON Q1.MONTH = Q2.MONTH
order by q1.qtr
but i get an error like
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ‘)’.
This part is wrong.
SELECT * FROM (select datename(month,docdate)as
brace is missing. Also why are you using subquery?
Hello,
Here is my C@ code:
private void btn_stergeclient_Click(object sender, EventArgs e)
{
if (txt_idclient.Text!= “” && txt_numeclient.Text!= “” && txt_prenume.Text!= “” && txt_varsta.Text !=””)
{
cn.Open();
comand = cn.CreateCommand();
comand.CommandText = “delete from Clienti where Id_client='” + txt_idclient.Text + “and Nume='” + txt_numeclient.Text + ” Prenume= ‘” + txt_prenume.Text + “and Varsta='” + txt_varsta.Text;
comand.ExecuteNonQuery();
MessageBox.Show(“Client sters”);
cn.Close();
}
}
When I run it I have this error:
Incorrect syntax near “3”
Please help me with it,
Cosmina
that 3 boxes put the value at run time den display result madam i think its a crct solution
when i run the form in access 2010 ,it will automically update the table but at the end of processing it generates error on
sp.update
please help me
harry
Hi,
I have a issue in SQL Server 2012, changing a database from sql server 2005 to 2012.
Already tried to change the compatibility level but I was out of luck.
The “incorrect syntax near @errno” error remains.
Can anyone help me, please?
Thanks
Hi Pinal,
I have a linked server that has a hyphen on its name. When I run a query using the linked server, this returns “Incorrect syntax near ‘-‘”..
Could you help me with this? Thanks.
Hi mara,
Even I’m facing the same issue, If u found out the solution, kindly share it with me also.
you need to use profiler and find where is error.
Hi,
I’m getting incorrect syntax near ; in @tableHTML.Can you please help me to solve.
use master
DECLARE @SQL nvarchar(max),@SQL1 nvarchar(max),@SQL3 int,
@tableHTML NVARCHAR(MAX) ,@dbName sysname;
DECLARE DBcursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’tempdb’,’model’,’msdb’,’AdventureWorksDW’)
AND DATABASEPROPERTYEX(name,’status’)=’ONLINE’ ORDER BY name;
OPEN DBcursor; FETCH DBcursor INTO @dbName;
WHILE (@@FETCH_STATUS = 0) — loop through all db-s
BEGIN
–DECLARE @dbContext nvarchar(256)=@dbName+’.dbo.’+’sp_executeSQL’
SET @SQL = ‘SELECT ”Database: ‘+ @dbName +
‘ table count” = COUNT(*) FROM’+’ ‘+ @dbName+’..sessionsummary where’+’ ‘+ @dbName+’..sessionsummary.usn>2′ ;
SET @SQL1 = ‘SELECT ”Database: ‘+ @dbName +
‘ table count” = COUNT(*) FROM’+’ ‘+ @dbName+’..sessionsummary where’+’ ‘+ @dbName+’..sessionsummary.usn=0′ ;
–SET @SQL3= convert(int,@sql)
PRINT @SQL
print @SQL1;
— SELECT ‘Database: AdventureWorks table count’ = COUNT(*) FROM sys.tables
–SELECT CONVERT(int, ‘@sql’, 0) AS [Style 0, character to int];
–select @sql= convert(@sql as int)
EXEC sp_executeSQL @SQL;
EXEC sp_executeSQL @SQL1;
—
–set @sql1= cast(@sql1 as int) ;
if ((@sql)>0 AND (@SQL1)=0)
begin
declare @Message nvarchar(100)
–declare @time varchar(20);
–declare @date datetime;
–set @date=CONVERT(VARCHAR(19), CAST(s.lastsyncdate AS DATETIME), 107)
–set @time=@date
declare @fullbody nvarchar(1000)
set @Message = ‘[All markets] – ‘+CAST(@SQL as varchar)+’ Users not performedthe DB reset’
SET @tableHTML =
‘N”’ +
‘N”h2, body {font-family: Geneva, calibri;} table{font-size:12px; border-seperate:seperate;} td{background-color:#B22222; padding:3px;} th{background-color:#800080 ;}’ +
‘N”Not performed the DB reset Users’ +
‘N”’ +
‘N”’ +
‘N” ‘ +
‘N” ‘ +
‘N”’ +
‘N”RepnameRepID’ +
‘N”Max USN countLast DB Reset countLast sync date DATELast DB Reset DATE
‘ +
CAST ( ( ‘SELECT
td = R.Repname, ”,
td = R.RepID, ”,
td = S2.USN, ”,
td = S1.USN, ”,
td = ‘+CONVERT(VARCHAR(25), CAST(s2.lastsyncdate AS DATETIME), 107)+’,”,
td = ‘+CONVERT(VARCHAR(25), CAST(s1.lastsyncdate AS DATETIME), 107)+’
from’+”+ @dbname+’..sessionsummary s1
inner join’+”+ @dbname+’..sessionsummary s2 on ‘+”+@dbname+’..S2.Repid=’+”+@dbname+’..S1.Repid
inner join’+”+ @dbname+’..Rep_table R on ‘+@dbname+’..R.repid=’+@dbname+’..S2.Repid
AND ‘+@dbname+’..S2.lastsyncdate=(select max (‘+@dbname+’..s2.lastsyncdate) from ”’+@dbname+’..sessionsummary s2
where’+ @dbname+’..s2.usn>3 and ‘+@dbname+’..s2.repid=s1.repid)AND ”’+@dbname+’..s1.lastsyncdate=(select max(”’+@dbname+’..s1.lastsyncdate) from ‘+@dbname+’..sessionsummary s1
where ‘+@dbname+’..s1.usn=0 and ‘+@dbname+’..s1.repid=’+@dbname+’..s2.repid)’+
‘FOR XML PATH (”tr”), TYPE
) AS NVARCHAR(MAX))’ +
‘N’+” ;–>getting error here
print @tableHTML
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”Pinal profile”,
@recipients=”xxxx@gmail.com”,
@importance = ”HIGH”,
@subject = ”Action Required :: users need to perform DB reset”,
@body = ”@tableHTML”,
@body_format = ”HTML” ;
end
FETCH DBcursor INTO @dbName;
END; — while
CLOSE DBcursor; DEALLOCATE DBcursor;
Incorrect syntax near ‘unsigned’.?????
hi
i have a following code:
Alter PROCEDURE p_details
(@P_id int
,@P_name NVARCHAR(50)
,@P_age nvarchar(50)
,@P_gender nvarchar(50)
,@StatementType nvarchar(50) =”)
AS
BEGIN
IF @StatementType = ‘Insert’
BEGIN
insert into Table_1 (P_id,P_name,P_age,P_gender) values( @P_id,@P_name,@P_age,@P_gender)
END
IF @StatementType = ‘Select’
BEGIN
select * from Table_1
END
IF @StatementType = ‘Update’
BEGIN
UPDATE Table_1 SET
P_name = @P_name,P_age = @P_age,P_gender = @P_gender
WHERE P_id = @P_id
END
ELSE IF @StatementType = ‘Delete’
BEGIN
DELETE FROM Table_1 WHERE P_id = @P_id
END
END
GO
I shows error on execution
Msg 208, Level 16, State 6, Procedure p_details, Line 25
Invalid object name ‘p_details’.
please tell me what’s going wrong
and my stored procedure is also nt show in the list of stored procedures
check database that using if correct then Replace ALTER by CREATE
Hi Pinal,
Would you please help me fixing this error.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘=’.
SELECT tblSubCategory1.Sub_Cat1, tblSubCategory1.Description, tblSubCategory1.Picture
FROM tblSubCategory1, tblItemMaster, tblItemQty
WHERE (tblSubCategory1.Sub_Cat1 = tblItemMaster.Sub_Cat1 AND tblItemMaster.ItemCode = tblItemQty.ItemCode)
ORDER By (tblItemQty.DepCode = ‘NEWJ’)
AND (tblSubCategory1.Code = ‘FLT’)
AND (tblCategory.Type = ‘Sell’ tblCategory.Type = ‘Both’)
Hi Pinal,
Would you please help me fixing this error.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘=’.
SELECT tblSubCategory1.Sub_Cat1, tblSubCategory1.Description,
tblSubCategory1.Picture
FROM tblSubCategory1, tblItemMaster, tblItemQty
WHERE (tblSubCategory1.Sub_Cat1 = tblItemMaster.Sub_Cat1
AND tblItemMaster.ItemCode = tblItemQty.ItemCode)
ORDER By (tblItemQty.DepCode = ‘NEWJ’)
AND (tblSubCategory1.Code = ‘FLT’)
AND (tblCategory.Type = ‘Sell’ tblCategory.Type = ‘Both’)
Hi Pinal,
We have a SQL Server Database on 2008 version which was restored from 2005 Version.
We are using OLEDB from .net Code to connection to SQL Sever.
Problem is, during load testing of our application we are getting errors like “Incorrect Syntax near” while it is working fine in normal scenario.
thank u. :)
Thanks, Its worked for me….
this is error,can you fixed it
CREATE TABLE Passenger(
Passenger_ID char(20),
FristName varchar(35) not null,
MiddleName varchar(35) not null,
LastName varchar(35) not null,
Address varchar(40) not null,
Passport_ID char(8) not null,
DateofBirth int not null,
Age int not null,
Email varchar(50) not null,
constraint P_ID_pk primary key(Passenger_ID),
constraint pid_ck check (Passport_ID like ‘[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’),
constraint em_ck check (Email like ‘[A-Za-z0-9_-.”]%@[A-Za-z0-9_-]%.[A-Za-z]%’),
constraint ag-ck check(year(currentdate)-year(DateofBirth)=Age)
);