SQL Server Performance Forum – Threads Archive
Field name "Description" problem in QA
I am unable to set the value for the field named "description" in the insert statement, but I can use the update statement to set it’s value in QA. (SQL 2k) Here are the statements that I am using: –insertInsert into IS_Input ( Workflow_name, Incident, staff_assigned, Description )
Values (‘any’,1022,’karen’,’Icebergs calved in the arctic ‘)
–check value
select * from dbo.IS_Staff_Input where incident =1022
–update
Update IS_Input set Description = ‘Icebergs calved in the arctic are generally 200-500 feet wide and extend about 200 feet above the surface of the water’
where incident =1022
–check value
select description from dbo.IS_Staff_Input where incident =1022 I have tried specifying the field name several ways:
as "Description"
as [Description]
as IS_Input.Description but none of these ways allows me to update the value in the insert statement. It does not make sense that it works in one statement and not the other. I’ve checked for keywords and reserved words and I cannot find "Description" listed in either. Any ideas how to correct this? (it’s always the little things…..) Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten
Did you check for any triggers that might be on insert that are not on update? Your syntax seems correct. I assume there would be some error message you would get if the column name was wrong or if you are somehow violating some check constraint. Is description of the varchar datatype? John
what is the output from the select after the insert this works for me (S2K, sp4)
Well, it looks like description is a keyword as of SP4. It turns blue for me in QA.
Exerpt from a document… **************************************************************************************** Local cubes now support the intrinsic member property DESCRIPTION for measures and dimensions. The DESCRIPTION keyword, added to the CREATE CUBE Multidimensional Expressions (MDX) statement, is used to support the use of the DESCRIPTION intrinsic member property. The following BNF clauses describe the changes made to the CREATE CUBE statement: <dimensions def> :: = DIMENSION <dimension name> [<time def>]
[DIMENSION_STRUCTURE <sub_type>] [<hidden def>]
[DESCRIPTION <description expression>]
<options def> <comma> <hierarchy def list>
<measures def> :: = MEASURE <measure name> <measure function def>
[<measure format def>] [<measure type def>] [<hidden def>]
[DESCRIPTION <description expression>] [<comma> <measures def>] **************************************************************************************** http://download.microsoft.com/downl…-4DE0-B36F-C44E06B0D2A3/ReadmeSql2k32asp4.htm John
i tried this:
create table IS_Input(Workflow_name varchar(100), Incident int, staff_assigned varchar(100), Description varchar(100))
Insert into IS_Input ( Workflow_name, Incident, staff_assigned, Description )
Values (‘any’,1022,’karen’,’Icebergs calved in the arctic ‘)
select * from IS_Input
drop table IS_Input and its working for me.
Is it something to do with service pack may be.
Which SP you have installed
Thank-you all for your help! I will continue looking at this. SQl is 2k sp4. The word ‘Description’ does turn blue in QA, that’s why I was looking at keywords, etc. I am testing changes to an insert trigger, which (summarized where I have an ellipsis) follows this note. This concatenates some of the fields together to create the description field. The code that builds @BigTemp and replaces the "description" field is detailed. The table and original trigger were created by someone else, I am only adding some new fields to the code. The code I included earlier is boiled down for testing purposes. Apparently my trigger change is not doing what I intended. If it would be helpfull, I will include the full text of the trigger. ——————
CREATE TRIGGER … AFTER INSERT AS DECLARE … SELECT @…fieldnames = …fieldnames from inserted if @InitName is NOT NULL set @BigTemp = ‘Init: ‘ + @InitName
if @InitPhone is NOT NULL set @BigTemp = @BigTemp + ‘ – ‘ + @InitPhone
if @ReqName is NOT NULL set @BigTemp = @BigTemp + ‘ / Req: ‘ + @ReqName
if @ReqPhone is NOT NULL set @BigTemp = @BigTemp + ‘ – ‘ + @ReqPhone
— add new fields to description field here
if @BigTemp is NOT NULL set @BigTemp = @BigTemp + ‘ ‘
if @Init_Dept_Name is NOT NULL set @BigTemp = @BigTemp + ‘Dept: ‘ + rtrim(@Init_Dept_Name)
if @Init_Cost_Center is NOT NULL set @BigTemp = @BigTemp + ‘ CC: ‘ + rtrim(@Init_Cost_Center)
if @CEAR is NOT NULL set @BigTemp = @BigTemp + ‘ CEAR: ‘ + rtrim(@CEAR)
if @Work_Location is NOT NULL set @BigTemp = @BigTemp + ‘ Loc: ‘ + rtrim(@Work_Location)
if @Device_Type is NOT NULL set @BigTemp = @BigTemp + ‘ Dev: ‘ + rtrim(@Device_Type)
if @PC_Asset_Tag is NOT NULL set @BigTemp = @BigTemp + ‘ PC: ‘ + rtrim(@PC_Asset_Tag)
if @Monitor_Asset_Tag is NOT NULL set @BigTemp = @BigTemp + ‘ Mon: ‘ + rtrim(@Monitor_Asset_Tag)
if @Printer_Asset_Tag is NOT NULL set @BigTemp = @BigTemp + ‘ Prtr: ‘ + rtrim(@Printer_Asset_Tag)
if @Printer_Name is NOT NULL set @BigTemp = @BigTemp + ‘ – ‘ + rtrim(@Printer_Name)
else
if @Printer_Name is NOT NULL set @BigTemp = @BigTemp + ‘ Prtr: ‘ + rtrim(@Printer_Name)
if @Desc is NOT NULL set @BigTemp = @BigTemp + char(13) + ‘Desc: ‘ + rtrim(@Desc) + isnull(rtrim(‘ ‘[email protected]_Descr2),”)+ char(13)
if @Mgr_Disapprove_Comments is NOT NULL set @BigTemp = @BigTemp + ‘Mgr Disapprove: ‘ + rtrim(@Mgr_Disapprove_Comments) — Update description
update IS_Staff_Input set Description = isnull(@BigTemp,”),…other fields
where Workflow_Name = @Workflow_Name and Incident = @Incident and Staff_Assigned = @Staff_Assigned
Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten
DUH!!! @BigTemp needs to be initialized in the trigger — I gave it a value of ‘ ‘ and the trigger works. If I do not give it an initial value, the trigger does not work. Thanks to all that took the time to help!! Have a GREAT weekend!! Karen "There are two documents in your in-box: tragedy and comedy. Your Pick." –Dale Dauten
]]>