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.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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment