quote:

---

Andy writes "Server- Sql Server 2000
Os- Windows 2000 Pro
Hello, I'm trying to build a store procedure that changes all columns, which are on User tables, and datatypes: char,varchar,text,nchar,nvarchar,ntext

I made a cursor which stores all columns, their type, if it's null, and their size (the size of the Unicode columns is divided by 2)
After that, I do a fetch from the cursor and build a string which stores the procedure, this is a example of what he builds:
"ALTER TABLE [mytable] ALTER COLUMN mycolumn varchar(12) COLLATE database_default NOT NULL"

Generally this is what I want to do with all the columns.

I'm having a few problems:
1- If the column is from a primary key, he doesnt let me change it's collation, and I'm having trouble saving the keys on temp vars, dropping the keys, changing the collation, and then return the keys.
2- I execute each of them in a transaction, but I can't manage to catch the error to make it continue to the next fetch, if it failed on the current string.
3- After all this works, I want to make from all the strings, a big one, so then I can make a big execution from a transaction, so I can rollback everything if something failed. But I can't store a string of over 8000 chars into any variable and it doesnt let me use text variables as a local variable.

I hope you can help me

Best Regards

Andy Kohn
Programmer
Retalix"

---