ALTER Datatype of Column - SQL

I'm using SQL server 2000.
Can anyone provide me an alter script for changing the datatype of column from nvarchar(200) to binary(100) which allows NULL.

ALTER TABLE t1
ALTER COLUMN <col_name> binary(100) NULL

but this throws an error "Disallowed implicit conversion from data type nvarchar to data type binary". I even tried using convert function but it didn't wrk, help me out.....

gourmet wrote:

I'm using SQL server 2000.
Can anyone provide me an alter script for changing the datatype of column from nvarchar(200) to binary(100) which allows NULL.

ALTER TABLE t1
ALTER COLUMN <col_name> binary(100) NULL

but this throws an error "Disallowed implicit conversion from data type nvarchar to data type binary". I even tried using convert function but it didn't wrk, help me out.....

Tried using CAST? And have you read through the ALTER TABLE article fully in your local copy of Books Online?

Go to Sql Server Management studio and change it then tell it to generate a script. OR use SQL Management Objects and do it through the API.

Plz suggest valid inputs. I have tried using Convert and Cast functions it didn't wrkd out. Sql enterprise manager doesn't have options to generate a alter script

Use SQL Profiler to profile your database; in SQL Server Management Studio change the column type via the designer; get the statements from the captured profiler data.

gourmet wrote:

I'm using SQL server 2000.
Can anyone provide me an alter script for changing the datatype of column from nvarchar(200) to binary(100) which allows NULL.

ALTER TABLE t1
ALTER COLUMN <col_name> binary(100) NULL

but this throws an error "Disallowed implicit conversion from data type nvarchar to data type binary". I even tried using convert function but it didn't wrk, help me out.....

What would be the expected result of such a conversion regarding rows which contain data in the nvarchar(200) field?

Maybe there's an alternative or workaround if you describe your rationale of doing such a conversion.

gourmet wrote:

I'm using SQL server 2000.
Can anyone provide me an alter script for changing the datatype of column from nvarchar(200) to binary(100) which allows NULL.

ALTER TABLE t1
ALTER COLUMN <col_name> binary(100) NULL

but this throws an error "Disallowed implicit conversion from data type nvarchar to data type binary". I even tried using convert function but it didn't wrk, help me out.....

you will need to write a bit of sql script to do this...

when you use the sql GUI in enterprise manager to make this kind of alteration it does *NOT* just issue an alter table command.

it generaly creates a temp table and copies the data into it.
then it drops the old column.
then creates a new column with the new datatype.
 then it inserts the data from the temp table into the new coulmn with a convert / cast function if one is needed.

in older versions of MS-SQL and Sybase SQL you had to do this all manualy.  MSFT added the GUI method to make life less of a pain.

I got a change request to change the column as binary datatype. There is already some data available in the table and I need to execute a simple alter script which will change the existing values to binary format and will also store binary values that will be inserted here after from the application.

SQL Server might complain if reducing the size of the field would delete data already in the column.  Is there some reason why you can't just create a new column, copy & convert the field data with an UPDATE statement, then remove the old column? 

It sounds like an odd request; nvarchar is a flexible data type that also supports nulls.  Maybe you need to put on your "change management" hat