exporting error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

exporting error

Author

Topic

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 11:56:20

Im trying to move data from one table to another but im getting an error

Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only column "id".
(SQL Server Import and Export Wizard)

Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task: "component "Destination - tblStaffDirectory_Area" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)

Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)

Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)

Any ideas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:32:29

check if the relevant column exists on destination? seems like metadata is out of date. have you changed struiucture of destination recently?

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 12:40:12

nope ..its complaining about the primary key, its cant import it

Do you know why

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:41:44

quote:
Originally posted by ikeguluo

nope ..its complaining about the primary key, its cant import it

Do you know why

is it an identity field? If yes,have you selected enable identity insert option?

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 12:45:29

yes i did that but then it compains that the primary key field is a read only field, im confused

this is the create table query SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblStaffDirectory_Area](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NOT NULL,
[is_geographical] [nvarchar](max) NOT NULL,
[active] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_tblStaffDirectory_Area] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

where am i going wrong ..thanks 4 your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:50:34

can you try implicitly inserting some testvalues to table? something like

SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] ON
INSERT INTO [dbo].[tblStaffDirectory_Area]
VALUS(...)
SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] OFF

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 12:53:44

Hi again

SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] ON
INSERT INTO [dbo].[tblStaffDirectory_Area]
VALUS(...)
SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] OFF

the values do i put the columns valuees for example

SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] ON
INSERT INTO [dbo].[tblStaffDirectory_Area]
VALUS(id,name, graphics, color)
SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] OFF

thanks 4 this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:55:29

did it got inserted or you still got any error? also is there any trigger defined on table?

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 12:55:51

Visakh16
please note the destination table is tblStaffDirectory_Area
and the source table is testing

Many thanks

Obi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:58:12

quote:
Originally posted by ikeguluo

Visakh16
please note the destination table is tblStaffDirectory_Area
and the source table is testing

Many thanks

Obi

yup...i got that. thats why i asked if it got inserted.

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 13:07:32

visakh16

SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] ON -destination table
INSERT INTO [dbo].[tblStaffDirectory_Area] destination table
VALUS(id,name, graphics, color)
SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] OFF

when do i declare the source table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:16:43

didnt you understand what i told you. that statement was given to try to insert explicitly values to table and see if it allows explicit insertion to identity. you should do insert query after create query you posted. also replace the columns id,name,... with actual values. then post back if insert was successfull or not.

ikeguluo
Starting Member

15 Posts

Posted - 2008-10-27 : 13:25:21

Hi Visakh

Ran the query SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] ON
INSERT INTO [dbo].[tblStaffDirectory_Area]
VALUES(id, name is_geographical, active)
SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] OFF

and got the error sg 128, Level 15, State 1, Line 3
The name "id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:30:43

quote:
Originally posted by ikeguluo

Hi Visakh

Ran the query SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] ON
INSERT INTO [dbo].[tblStaffDirectory_Area]
VALUES(id, name is_geographical, active)
SET IDENTITY_INSERT [dbo].[tblStaffDirectory_Area] OFF

and got the error sg 128, Level 15, State 1, Line 3
The name "id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Can you PLEASE understand what i posted. i told you to REPLACE id,name,... with some random values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources