Post reply

Error when update 2 extra coloums in temp table

  • November 26, 2004 at 1:39 pm

    #84275

    Hi,

    I have stored procedure which has a transaction and the code below is within the transaction.

    What I'vd done was:

    1. select into a temptable.

    2. add two extra fields

    3. update one of the two extra fields.

    Sqlserver complains the new fields I added was not found when I tried to update one of them. It then complains about the transaction count does not match.

    Note: the stored procedure works fine if I do not update. See code and error message below.

    Can any tell me why it does not like the update statement

    Thanks

    SELECT

    @NewCollectionId AS collection_id,

    subject_id,

    user_id,

    dateenrolled,

    collectionsubjecttitle_id,

    note,

    newid() AS id,

    @default_timestamp AS bhtimestamp,

    @bhtriggerid AS bhtriggerid

    INTO #tmp_CollectionSubject

    FROM CollectionSubject

    WHERE collection_id = @OrigCollectionId

    alter table #tmp_CollectionSubject add newsubject_id uniqueidentifier null

    alter table #tmp_CollectionSubject add myField varchar(99) null

    update #tmp_CollectionSubject

    set myField = 'ABC'

    ERROR:

    Server: Msg 207, Level 16, State 1, Procedure wbcsp_util_copydeal_collection, Line 299

    Invalid column name 'myField'.

    Server: Msg 266, Level 16, State 1, Procedure wbcsp_util_copydeal_collection, Line 427

    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

  • November 27, 2004 at 7:23 am

    #531648

    It's much easier to have all the columns in the table, right from the start, by modifying the SELECT INTO statement, like this:

    SELECT 
	@NewCollectionId AS collection_id, 
	subject_id, 
	user_id, 
	dateenrolled, 
	collectionsubjecttitle_id, 
	note, 
	newid() AS id, 
	@default_timestamp AS bhtimestamp, 
	@bhtriggerid AS bhtriggerid,
	CONVERT(uniqueidentifier, NULL) as newsubject_id,
	CONVERT(varchar(99), NULL) as myField



INTO #tmp_CollectionSubject 
FROM CollectionSubject 
WHERE collection_id = @OrigCollectionId

    Razvan

  • November 28, 2004 at 5:29 am

    #531655

    Hi Razvan,

    I tried that, created a table first then insert records. SQLServer said it can not find newsubject_id field. That is very strange.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply