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 (http://blog.SQLAuthority.com)

About these ads

12 thoughts on “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.

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  6. hai,

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

    Like

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

    Like

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

    Like

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

    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