Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Angry Unanswered: Changing Data Types in SqlServer

    I have upsized my access database to Sql Server - Fine. Everything exported in order, but..

    I haven't had any success changing the data types and sizes
    Ex. [nvarchar] [255] to [varchar] [1000].

    I have tried in Sql Server to:

    ALTER TABLE dbo.products
    ALTER COLUMN description type [varchar] [1000]

    Obviously this isn't a correct statement, however, not obvious to me..

    Thanks much in advance for help.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: Changing Data Types in SqlServer

    Q1 I haven't had any success changing the data types and sizes Ex. [nvarchar] [255] to [varchar] [1000]. I have tried in Sql Server to:

    ALTER TABLE dbo.products
    ALTER COLUMN description type [varchar] [1000]

    Obviously this isn't a correct statement, however, not obvious to me..
    A1 If the column name is "description type", sic, consider changing it to DescriptionType, or possibly description_type, to avoid all sorts of similar issues in the future; if this is not possible, more success is likely using:

    a) [description type] (sic)

    rather than the unbracketed form

    b) description type

    In any event the general syntax is:
    Alter Table [Table_Name]
    Alter Column [Column_Name]

    For Example:
    ALTER TABLE [dbo].[products]
    ALTER COLUMN [description type] [varchar] (1000)

    For a demonstration example, run the following:

    Use TempDB
    Go

    if exists (select * from dbo.sysobjects where id = object_id(N'[Demo_Information]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Demo_Information]
    GO

    CREATE TABLE [dbo].[Demo_Information] (
    [DemoPrimaryKey] [int] IDENTITY (1, 1) NOT NULL ,
    [AlterTypeTargetData] [nvarchar] (255) NULL ,
    CONSTRAINT [PK_Demo_Information] PRIMARY KEY CLUSTERED
    (
    [DemoPrimaryKey]
    )
    )
    Go

    INSERT INTO
    [dbo].[Demo_Information]
    ([AlterTypeTargetData])
    VALUES
    ('012342578901234257890123425789012342578901234257 89')

    INSERT INTO
    [dbo].[Demo_Information]
    ([AlterTypeTargetData])
    VALUES
    ('01234257890123425789')

    Select 'Note Type_Name and Precision:' As 'BEFORE Alter Table statement'
    Exec sp_Columns Demo_Information
    Go

    Alter Table [Demo_Information]
    Alter Column [AlterTypeTargetData] [Varchar] (1000) Null
    Go

    Select 'Note Type_Name and Precision:' As 'AFTER Alter Table statement'
    Exec sp_Columns Demo_Information
    Go

    -- To Change the type back to nVarChar (255)
    -- Alter Table [Demo_Information]
    -- Alter Column [AlterTypeTargetData] [nVarchar] (255) Null
    -- Go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •