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
too good post…
I am fresher and use to read many article from your site…
thx for posting :)
How do I fix this error ‘Incorrect syntax near ‘@errno’ in my stored procedure if I’m using a script to run the program? I am using sql server 2012 and the compatiblility is already set to a level of 110.
st.executeUpdate(“create table AutomobileDBnew as AutomobileDB”);
getting error as java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword ‘as’. can any one help me??
create table AutoDBnew as AutomobileDB
but getting error — Incorrect syntax near the keyword ‘as’
i have executed EXEC sp_dbcmptlevel ‘DatabaseName’, 100 command but getting same error
create table Auto as AutomobileDB
getting error — Incorrect syntax near the keyword ‘as’
also execute command
EXEC sp_dbcmptlevel ‘DatabaseName’, 100
but same error occurring… plz help me.
Hi Pinal,
I have used .mdf file in my project I need to SET COMPATIBILITY_LEVEL for using my .net project code as i have done as per follows
query = string.Concat(“USE master; ALTER DATABASE myDBName SET COMPATIBILITY_LEVEL = 100;”);
SqlHelperNew.ExecuteNonQuery(connectionString, CommandType.Text, query);
But I have got following error “User does not have permission to alter database ‘myDBName’, the database does not exist, or the database is not in a state that allows access checks.
ALTER DATABASE statement failed. Changed database context to ‘master’. ”
So how can I set COMPATIBILITY_LEVEL for my .mdf file using sql query.
Please help me.. thanks…
It worked for me.
Thank you very much.
I am getting following error when i try to delete a record from database. can anyone help me
Must declare the scalar variable “@CustomerID”.
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: Must declare the scalar variable “@CustomerID”.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Must declare the scalar variable “@CustomerID”.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1789270
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5340622
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +869
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +378
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +568
System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +84
System.Web.UI.WebControls.DetailsView.HandleDelete(String commandArg) +780
System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +519
System.Web.UI.WebControls.DetailsView.RaisePostBackEvent(String eventArgument) +196
System.Web.UI.WebControls.DetailsView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +9702098
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724
Hi Pinal,
Thanks a lot for solution.
But when I’m trying to execute the query in Post Deployment script of SQL Server Database Project. It’s not working. Have you come across the scenario? Or if you know the solution, please help.
My code:
–Upgrading database version, if necessary.
–Without this Merge Query fails
DECLARE @DataBaseName nvarchar(128)
SET @DataBaseName = DB_NAME()
EXEC sp_dbcmptlevel @DataBaseName, 100
Thanks in advance.
Regards,
Kaushal.
Thank you very much
Your welcome Shanil.
i have a custom sync class in (ASP.net application) which syncs the data between two servers it was running fine for a while but recently it threw an error saying incorrect syntax near. the problem is that it doesnt say near what . it just says “incorrect syntax near”, i double checked my queries, ran them directly in SSMS and they all run fine.
i should mention that Database servers are same they are both SQL server 2008 R2. there is no compatibility updgrading issue here because i havent upgraded anything. i know this is a little confusing but can you please provide some insight on this.
can this error be coming from anything else apart from SQL statements? like C# code or anything else.
Its very urgent , kindly respond.
capture profiler and find the query which is causing problem. If its urgent, you may want to try other options.
Hi Dave, I am getting this error “Invalid syntax near “VALUES” and don’t know what is wrong with my insert statement::
cmd.CommandText = “INSERT INTO NewResource SELECT Name + 1 FROM NewResource ” + “VALUES (‘” + ((TextBox)row.FindControl(“Name”)).Text + “‘)”;
Insert …Select …Values is a invalid syntax. Can you shared table schema?
Hi, I am getting this error
“getdate() expects parameter 1 to be long, string given in /home/fashion1/public_html/funblogging/common.php on line 70”
in common.php at line 70 coding is like this
function DatetimeFormat($timeStamp)
{
$date = getdate($timeStamp);
return ($date[“mon”].”/”.$date[“mday”].”/”.$date[“year”]);
// return date(“F j, Y, g:i a”,$timeStamp);
}
When I run a SQL query it said—
MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘getdate() expects parameter 1 to be long, string given in /home/fashion1/public_’ at line 1
My SQL server version is 5.5.42-37.1
Can you help me sort out the problem ?
You question is about MySQL not SQL Server. These are two different products.
Dear Pinal
I developed a solution that uses both a SQL server and one MDF file, according to the user’s choice.
However, when you use the MDF file in the “C: \ programdata \” we get a data conversion error (Error converting data type varchar to date), which is not displayed when you use the server.
It could be a compatibility error?
Thank you
i have not seen compatibility causing “converting data type varchar to date” error. You need to run profiler and find exact statement. mostly its a issue with data in the table and the query.
Private Sub Tsb_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Tsb_save.Click
If Tsb_save.Text = “save” Then
If cmb_busnam.Text “” Then
Dim cmd As New SqlCommand
cmd.Connection = connection()
cmd.CommandType = CommandType.Text
cmd.CommandText = “insert into registration (Busname,Start,Dest,passengername,age,sex,Fare) values(‘” + cmb_busnam.Text + “‘, ‘” + cmb_frm.Text + “‘, ‘” + desti.Text + “‘, ‘” + txt_nam.Text + “‘, ” + txt_age.Text + “, ‘” + cmb_sex.Text + “‘, ” + far.Text + “)”
cmd.ExecuteNonQuery()
MsgBox(“save success”, MsgBoxStyle.Information, “saving”)
incorrect syntax near ‘)’
cmd.executenonquery
plz help to solve dis
Hi Pinal,
I’ve written a procedure that purges data on certain date range. When I execute the procedure , it works fine but when I run it in a job , it fails with the following message :
Executed as user: NT SERVICE\SQLSERVERAGENT. Incorrect syntax near ‘,’. [SQLSTATE 42000] (Error 102). The step failed.
Store procedure:
USE [Pegasus]
GO
/****** Object: StoredProcedure [dbo].[sp_Purge_Pegasus] Script Date: 15/11/2015 19:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Purge_Pegasus]
AS
BEGIN
DECLARE @DATE_RANGE INT
SELECT @DATE_RANGE = 180
DECLARE @DATELIMIT DATETIME
SELECT @DATELIMIT = CONVERT(varchar(10), getdate()-@DATE_RANGE, 120)
begin tran
delete surveysurvey where surveydate < @DATELIMIT
commit tran
begin tran
delete ordersdetails where orderdetailcreated < @DATELIMIT
commit tran
begin tran
delete ORDERSDISCOUNTS where ORDERIDLINK in (select orderid from orders where orderdate < @DATELIMIT)
commit tran
begin tran
delete orders where ordercreated < @DATELIMIT
commit tran
begin tran
delete sessions where sessiondatestart < @DATELIMIT
commit tran
begin tran
delete customersvisits where visitdatestart < @DATELIMIT
commit tran
begin tran
delete log where logdate< @DATELIMIT
commit tran
begin tran
delete commlog where commlogdate < @DATELIMIT
commit tran
begin tran
delete hhlog where hhlogdate < @DATELIMIT
commit tran
END
GO
—————-
Compatibility level: SQL Server 2012 (110)
Please help me solve this problem as soon as possible.
Thanks
Hi All,
I am trying to execute a sql job through linked server but i am getting a below error message, I marked as bold those lines are showing me a error in red lines.please help me on this.
Incorrect syntax near the keyword ‘SELECT’. [SQLSTATE 42000] (Error 156) Incorrect syntax near ‘)’. [SQLSTATE 42000] (Error 102). The step failed.
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N’tempdb..#tempinsertstable’)
)
BEGIN
DROP TABLE #TEMPINSERTSTABLE –drop temptables if they exist
END
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N’tempdb..#tempodstable’)
)
BEGIN
DROP TABLE #TEMPODSTABLE –drop temptables if they exist
END
CREATE TABLE #TEMPINSERTSTABLE
(
[MasterItemID] [int] NOT NULL PRIMARY KEY,
[ItemNumber] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[LegacyPartNumber] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
[Description] [nvarchar](240) COLLATE Latin1_General_CI_AI NULL,
[LoadDate] [datetime] NULL,
)
CREATE TABLE #TEMPODSTABLE
(
[MasterItemID] [int] NOT NULL PRIMARY KEY,
[ItemNumber] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[LegacyPartNumber] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
[Description] [nvarchar](240) COLLATE Latin1_General_CI_AI NULL,
[LoadDate] [datetime] NULL,
)
— import everything from linked table to a temp table
INSERT INTO #TEMPODSTABLE (MasterItemID, ItemNumber, LegacyPartNumber, Description, LoadDate)
SELECT * FROM OPENQUERY(ODS, SELECT PART_info_ID, ITEM_NUMBER, LEGACY_PART_NUMBER,
DESCRIPTION, Creation_date FROM FPTODS.SQT_PART_INFO_T where rec_source = ‘AO’)
–insert records that are not in MasoneilanLINK.MasoneilanLINK.Masteritems but are in temp ods table to another temp table.
INSERT INTO #TEMPINSERTSTABLE (MasterItemID, ItemNumber, LegacyPartNumber, Description, LoadDate)
SELECT #TEMPODSTABLE.MasterItemID, #TEMPODSTABLE.ItemNumber, #TEMPODSTABLE.LegacyPartNumber, #TEMPODSTABLE.Description, #TEMPODSTABLE.LoadDate
FROM #TEMPODSTABLE left join MASONEILANLINK.MASONEILANLINK.MASTERITEMS
ON #TEMPODSTABLE.MasterItemID = MASONEILANLINK.MASONEILANLINK.MASTERITEMS.MasterItemID
WHERE MASONEILANLINK.MASONEILANLINK.MASTERITEMS.MasterItemID is null
–insert records from temp table to MasoneilanLINK.MasoneilanLINK.Masteritems
INSERT INTO MASONEILANLINK.MASONEILANLINK.MASTERITEMS
SELECT * FROM #TEMPINSERTSTABLE
–update records in MasoneilanLINK.MasoneilanLINK.Masteritems that have a load date different from temp ods table.
UPDATE [MASONEILANLINK].[MASONEILANLINK].[MASTERITEMS]
SET ItemNumber = #TEMPODSTABLE.ItemNumber,
LegacyPartNumber = #TEMPODSTABLE.LegacypartNumber,
Description = #TEMPODSTABLE.Description,
LoadDate = #TEMPODSTABLE.LoadDate
FROM #TEMPODSTABLE
WHERE [MASONEILANLINK].[MASONEILANLINK].[MASTERITEMS].MasterItemID = #TEMPODSTABLE.MasterItemID
AND
[MASONEILANLINK].[MASONEILANLINK].[MASTERITEMS].LoadDate #TEMPODSTABLE.LoadDate
–drop temp tables.
DROP TABLE #TEMPINSERTSTABLE
DROP TABLE #TEMPODSTABLE
Hi Pinal,
I have a similar error when trying to update statisitics on all user-defined tables within the database using a stored procedure. The stored proc works for all databases except one database. I am using SQL Server 2008 R2. I checked the compatability level and it is set to 100. I checked the compatability level of other databases which work and that is also the same. The error message is
Incorrect syntax near ‘\’.
There is no ‘\’ in my code, so I don’t know what to investigate. As it works on other databases I think this is something to do with the database settings.
Could you please share your ideas?
Thanks
Robby
I am getting error “Incorrect syntax near ‘TAKE’. (Source: MSSQLServer, error number: 170)”
on sql server 2008 for below statement,
DENY TAKE OWNERSHIP ON [dbo].[table_name] TO [user_name] AS [dbo]
(Transaktionssequenznummer: 0x00E11A4D000010C8001000000001, Befehls-ID: 5)
Please help
Never seen that earlier. does it work on other server?
Hi Pinal,
I m just trying to execute the search page using SQL Server in adf. I m stuck with the query for search.. can you help on this..
How can I help you?
For Example in Oracle Toad we create a bind variable by VO =:pVO query to create a search form. I m looking for the replacement of bind variable in SQL server since the Oracle Query doesn’t work in SQL.
In SQL variable use “@” symbol
Declare @str varchar(10)
Select * from MyTable where ID = @str