SQL SERVER – Fix: Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Error Message:

Server: Msg 128, Level 15, State 1, Line 3
The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Causes:

This error occurs when using a column as the DEFAULT value of another column when a table is created.
CREATE TABLE [dbo].[Items] (
[OrderCount] INT,
[ProductAmount] INT,
[TotalAmount] DEFAULT ([OrderCount] + [ProductAmount])
)

Executing this CREATE TABLE statement will generate the following error message:

Server: Msg 128, Level 15, State 1, Line 5
The name ‘TotalAmount’ is not permitted in this context.
Only constants, expressions, or variables allowed here.
Column names are not permitted.

Solution/Workaround/Fix:

When setting the DEFAULT value of a column make sure to use only constants, expressions or variables and not another column.
System functions can be used as DEFAULT values. e.g. GETDATE(), NEWID()
Use Computed Column in place of Default keyword.
CREATE TABLE [dbo].[Items] (
[OrderCount] INT,
[ProductAmount] INT,
[TotalAmount] AS ([OrderCount] + [ProductAmount])
)

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 Security Best Practices – Operational and Administrative Tasks
Next Post
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

Related Posts

12 Comments. Leave new

  • Olasemo Oladipo
    August 10, 2007 5:24 am

    I got this error when trying to run a dynamic SQL Query in a Stored Procedure using the Execute function i.e
    Execute (‘insert into dbo.TimeSheetComments(TimeSheetId,’+@Dcomment+’) values (‘+@timeSheetId+’,’+@Comments+’)’))

    noticed that the culprit was the @comments variable. I still don’t know how to solve this problem. Please Help..

    Error:
    The name “XXXXX” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    Reply
  • You could try this one:

    Execute (’insert into dbo.TimeSheetComments(TimeSheetId,’+@Dcomment+’) values (’+@timeSheetId+’,”’+@Comments+’”)’))

    Notice that I’ve put 2 extra single quotes before and after the +@Comments+

    I’ve just encountered this problem right now and found the solution for it.

    Reply
  • Affordable web design and development India
    August 8, 2008 11:52 am

    I am getting a same error while adding a value in smalldatetime field. All things are okay.. Can you please suggest me any solution?

    Reply
  • Tx for the explanation.
    Actually I got the message while using the ssis script.
    I had a non numeric value and the insert didn’t like it:

    Public Overrides Sub InputCell_ProcessInputRow(ByVal Row As InputCellBuffer)
    MsgBox(Row.net)
    Dim mysql As String = “insert into ae_basedata.tKEY_Network (EntityID,Network) values (8,” + Row.net + “)”
    sqlCmd = New SqlCommand(mysql, sqlConn)
    Try
    sqlCmd.ExecuteNonQuery()
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    End Sub

    When (with your explanation) I realized that the error was not in the code but in the data, and entered a numeric value as specified in the table it worked!
    Thanks
    Omar

    Reply
  • Msg 128, Level 15, State 1,

    I have a error while executing trigger.
    Please help me

    Reply
  • “This error may also be caused by trying to include an apostrophe (‘) in inserted text surrounded by double quotes.” ‘Instead use single quotes to surround text and two single quotes (”) for the apostrophe.’

    Reply
  • Ankur Srivastava
    January 5, 2010 10:47 am

    Peter,

    very well said.Thanks a lot for the info.

    Cheers!!!

    Reply
  • well, i have a same problem…
    I HAVE FIXED it… and i realize that my error is really2 dummy!

    first, my query is INSERT INTO BILLING (TAKEN_BY, GUEST_NAME, ID_REG, TAGIHAN_FO, BILL_DATE, KETERANGAN, TAGIHAN_FB, TAGIHAN_EKSTRABED) VALUES(“+takenby.ToString()+”,”+guestname.ToString()+”,”+idreg.ToString()+”,”+float.Parse(total.ToString())+”,getdate(),’Room Charge’,’0′,’0′)”

    then i changed it into INSERT INTO BILLING (TAKEN_BY, GUEST_NAME, ID_REG, TAGIHAN_FO, BILL_DATE, KETERANGAN, TAGIHAN_FB, TAGIHAN_EKSTRABED) VALUES(‘”+takenby.ToString()+”‘,'”+guestname.ToString()+”‘,'”+idreg.ToString()+”‘,'”+float.Parse(total.ToString())+”‘,getdate(),’Room Charge’,’0′,’0′)”

    as you see, i only have to add a single quotes on it….

    Reply
  • hai,

    when i installing service pack1 in sql server2005 i got error “unable to install windows installer” ..how is solve the problem….

    Reply
  • chayan banerjee
    September 3, 2010 3:14 pm

    solved it–> my query was (in stored procedure i was using dynamic SQL to insert values)

    ALTER PROCEDURE [dbo].[usp_insertValues]
    @TABLENAME varchar(50),
    @txtQuestion varchar(max),
    @ansA nvarchar(3),
    @ansB nvarchar(3),
    @ansC nvarchar(3)
    AS
    DECLARE @query varchar(1000)
    SET @query=’INSERT INTO ‘+@TABLENAME+’
    VALUES (‘+@txtQuestion+’,’+@ansA+’,’+@ansB+’,’+@ansC+’)’
    exec(@query)

    it was giving error….then after some R&D i put two extra single quotes around txtQuestion without space,,,so—

    ALTER PROCEDURE [dbo].[usp_insertValues]
    @TABLENAME varchar(50),
    @txtQuestion varchar(max),
    @ansA nvarchar(3),
    @ansB nvarchar(3),
    @ansC nvarchar(3)
    AS
    DECLARE @query varchar(1000)
    SET @query=’INSERT INTO ‘+@TABLENAME+’
    VALUES (”’+@txtQuestion+”’,’+@ansA+’,’+@ansB+’,’+@ansC+’)’
    exec(@query)

    Reply
  • I got this error below codes. I can not find the problem

    USE [HW01]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE sp_prm(@P INT)
    AS
    BEGIN
    DECLARE @sqr_p INT;
    DECLARE @i INT;
    SET @sqr_p = CEIL(SQRT(P));
    END;

    INSERT INTO dbo.Name(N) VALUES(2);
    SET @i=3 ;

    WHILE i <= P
    INSERT INTO dbo.Name(N) VALUES(i);
    SET i=i+2;
    END WHILE;

    SET i=2;
    WHILE i i AND (N%i)=0;
    SET @i=i+1;
    END

    ERROR -The name ‘i’ is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    Reply
  • alter PROCEDURE general_select1 @tblname nvarchar(100),
    @key varchar(10),
    @userid int
    AS
    DECLARE @sql nvarchar(4000)
    –SELECT @sql = ‘ SELECT ‘+ COL_NAME(‘DI_ADS_userinfo’,@tblname) +’
    — FROM dbo.DI_ADS_userinfo
    — WHERE userid = ‘+@userid +”
    set @sql = ‘Select’ + @tblname + ‘ From DI_ADS_userinfo’
    –EXEC sp_executesql @sql, N’@key varchar(10)’, @key
    exec sp_executesql @sql

    print dbo.general_select1(cn,2)

    heare it saying cn is not permitted u have any idea .please reply me

    Reply

Leave a Reply

Menu