I have seen developer confused many times when they receive following error message.
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 user is tring to attempt to run query or procedure or logic which is not compatible with 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 next version. When database compatibility is set to previous version and they are attempted with procedure of newer version they will throw above error.
Fix/Workaround/Solution:
Change the database compatibility level using following command.
For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90
For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100
Reference : Pinal Dave (http://blog.SQLAuthority.com)
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
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:\inetpub\vhosts\uttamindustries.com\httpdocs\admin_uttam\manageimage.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:\inetpub\vhosts\uttamindustries.com\httpdocs\admin_uttam\manageimage.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:\Backuprestore\1310_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
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
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