Hi there,
    I am wondering if anyone can help me solve this problem.Thanks you in advance!
Here is the table I am working on:
table user
user_id int(4),
user varchar(16),
password varchar(50)

Now I am trying to write a store procedure to implement follwoing tasks:


    -- Change size of [password] field to 255
    Alter Table sec_tmp
    alter column [password] varchar(255) NULL

    -- Insert a new column "password_tmp(varbinary)
    Alter Table dbo.sec_tmp ADD
    password_tmp varbinary(255) Null



    DECLARE upgrade CURSOR FOR
        SELECT [user id], [password] FROM sec_tmp

    OPEN upgrade

    FETCH NEXT FROM upgrade

    WHILE @@FETCH_STATUS = 0
        FETCH NEXT FROM upgrade
        update u
    set [password_tmp] = convert(varbinary(255),pwdencrypt([password]))
    from sec_tmp u

    CLOSE upgrade
    DEALLOCATE upgrade

    -- Delete password field
    -- Rename password_tmp to password
My problem is if I execute this store procedure, I always receive error msg saying "invalid column [password_tmp]".
I tried to first execute two "alter" commands, then refresh table, this store procedure works. My question is how
can I put those codes in a one or two store procedure to make it works automatically instead manually.

flyfish

__________________
flyfish

    What is U there? Is that you wanted to update sec_tmp table?

_________________________
- Vijay G
Strive for Perfection