December 28, 2006 at 1:19 pm
having a weird issue. i have an SSIS package that needs to change a data type on a column before and after importing some data. In the past i just right clicked on the table and did it manually. trying to make a package to do everything in one click
when i run
alter table tbldetail alter column dedteeffdate varchar(8)
i get the following
Msg 5074, Level 16, State 1, Line 1
The object 'DefaultBlank' is dependent on column 'dedteeffdate'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN dedteeffdate failed because one or more objects access this column.
when i right click on the table and modify the data type that way, it works fine
what i do is change it to varchar, import data from some files and then change it back to smalldatetime. i tried the data conversion transform with no luck
December 28, 2006 at 3:14 pm
May your coulumn has constraint, so you need to drop the constraint before changing the data type of the coulumn...
create
table tbldetail
(
col
int not null primary key,
dedteeffdate
smalldatetime default getdate()
)
alter
table tbldetail alter column dedteeffdate varchar(8)
Msg 5074
, Level 16, State 1, Line 2
The object
'DF__tbldetail __dedteeffdate__251C81ED' is dependent on column 'dedteeffdate'.
Msg 4922
, Level 16, State 9, Line 2
ALTER
TABLE ALTER COLUMN dedteeffdate failed because one or more objects access this column.
alter
table tbldetail
drop
constraint DF__tbldetail __dedteeffdate__251C81ED
alter
table tbldetail alter column dedteeffdate varchar(8)
drop
table tbldetail
MohammedU
Microsoft SQL Server MVP
January 3, 2007 at 4:49 am
When you right click and Modify SQL does a sequence of sql commands upon saving the changes (table name and columns removed) :
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TableName (
YourColumns) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_TableName ON
GO
IF EXISTS(SELECT * FROM dbo.TableName)
EXEC('INSERT INTO dbo.Tmp_TableName(YourColumns)
SELECT YourColumns FROM dbo.TableName WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TableName OFF
GO
DROP TABLE dbo.TableName
GO
EXECUTE sp_rename N'dbo.Tmp_TableName, N'TableName', 'OBJECT'
GO
COMMIT
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply