SQL SERVER – Fix : Error : 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

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)

About these ads

61 thoughts on “SQL SERVER – Fix : Error : 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

  1. 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

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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'
    —————————————–

    Like

  6. 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

    Like

  7. 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 ‘)’.

    Like

  8. 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

    Like

  9. 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

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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..

    Like

  17. 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

    Like

  18. 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

    Like

  19. 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.

    Like

  20. 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

    Like

  21. 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

    Like

  22. 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 ‘)’.

    Like

  23. 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

    Like

  24. 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

    Like

  25. 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

    Like

  26. 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.

    Like

  27. 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;

    Like

  28. 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

    Like

  29. 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’)

    Like

  30. 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’)

    Like

  31. 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.

    Like

  32. 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)

    );

    Like

  33. 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.

    Like

  34. 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??

    Like

  35. 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

    Like

  36. 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.

    Like

  37. 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…

    Like

  38. 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

    Like

  39. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s