Tech Off Thread

9 posts

ALTER Datatype of Column - SQL

Back to Forum: Tech Off
  • User profile image
    gourmet

    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.....

  • User profile image
    W3bbo

    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?

  • User profile image
    odujosh

    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.

  • User profile image
    gourmet

    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

  • User profile image
    TommyCarlier

    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.

  • User profile image
    mawcc

    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.

  • User profile image
    figuerres

    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.

  • User profile image
    gourmet

    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.

  • User profile image
    dske

    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 Wink

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.