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)
12 Comments. Leave new
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.
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.
I am getting a same error while adding a value in smalldatetime field. All things are okay.. Can you please suggest me any solution?
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
Msg 128, Level 15, State 1,
I have a error while executing trigger.
Please help me
“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.’
Peter,
very well said.Thanks a lot for the info.
Cheers!!!
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….
hai,
when i installing service pack1 in sql server2005 i got error “unable to install windows installer” ..how is solve the problem….
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)
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.
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