• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

changing field from read to read/write

I am trying to import some data into a table for testing purposes, one of the table's fields is a read only field -
I need to change this so that I can import the data and test the stored proc for reports.
Can someone tell me how to do this hard coding or otherwise
I am using 2008Server
0
Tagom
Asked:
Tagom
  • 10
  • 10
  • +2
2 Solutions
 
Chris MangusDatabase AdministratorCommented:
Please script out the structure of the table and upload it.  I suspect if you are getting some information that says it's read-only that you are looking at a table with a calculated column, which you can't INSERT to.
0
 
TagomAuthor Commented:
USE [acisql2]
GO

/****** Object:  Table [dbo].[CC_CalloutLog]    Script Date: 10/04/2011 13:11:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CC_CalloutLog](
      [CLID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [CLIndex] [int] NOT NULL,
      [CLChannelStatus] [varchar](50) NOT NULL,
      [CLCrewName] [uniqueidentifier] NOT NULL,
      [CLEmpID] [varchar](50) NOT NULL,
      [CLInfo1] [varchar](50) NOT NULL,
      [CLInfo2] [varchar](50) NOT NULL,
      [CLInfo3] [varchar](50) NOT NULL,
      [CLInfo4] [varchar](50) NOT NULL,
      [CLStartCallTime] [datetime] NOT NULL,
      [CLNextCallTime] [datetime] NOT NULL,
      [CLEndCallTime] [datetime] NOT NULL,
      [CLAttempts] [smallint] NOT NULL,
      [CLResultofLastAttempt] [varchar](50) NOT NULL,
      [CLAttemptsString] [varchar](100) NOT NULL,
      [CLAccepted] [varchar](3) NOT NULL,
      [CLPhone1] [varchar](50) NOT NULL,
      [CLPhone2] [varchar](50) NOT NULL,
      [CLPhone3] [varchar](50) NOT NULL,
      [CLPhone4] [varchar](50) NOT NULL,
      [CLCallInfo1] [varchar](50) NOT NULL,
      [CLCallInfo2] [varchar](50) NOT NULL,
      [CLCallInfo3] [varchar](50) NOT NULL,
      [CLCallInfo4] [varchar](50) NOT NULL,
      [CLPIN] [varchar](50) NOT NULL,
      [CLCallingSequence] [uniqueidentifier] NOT NULL,
      [CLScript] [uniqueidentifier] NOT NULL,
      [CLDistrict] [varchar](50) NOT NULL,
      [CLUserID] [varchar](50) NOT NULL,
      [CLCrewCode] [tinyint] NOT NULL,
      [CLPriority] [varchar](15) NOT NULL,
      [CLUtilityID] [uniqueidentifier] NULL,
      [CLMessage] [int] NOT NULL,
      [clPagerString] [varchar](200) NOT NULL,
      [CLAtWork] [datetime] NULL,
      [CLCNCrewName] [varchar](50) NOT NULL,
      [CLScriptsName] [varchar](50) NOT NULL,
      [CLCSSequenceName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CC_CalloutLog] PRIMARY KEY CLUSTERED
(
      [CLID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF_CC_CalloutLog_CLID]  DEFAULT (newid()) FOR [CLID]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInd__08AB2BC8]  DEFAULT ((0)) FOR [CLIndex]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCha__099F5001]  DEFAULT ('') FOR [CLChannelStatus]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLEmp__0A93743A]  DEFAULT ('') FOR [CLEmpID]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0B879873]  DEFAULT ('') FOR [CLInfo1]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0C7BBCAC]  DEFAULT ('') FOR [CLInfo2]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0D6FE0E5]  DEFAULT ('') FOR [CLInfo3]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0E64051E]  DEFAULT ('') FOR [CLInfo4]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLEnd__0F582957]  DEFAULT (getdate()) FOR [CLEndCallTime]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLAtt__104C4D90]  DEFAULT ((0)) FOR [CLAttempts]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLRes__114071C9]  DEFAULT ('') FOR [CLResultofLastAttempt]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLAtt__12349602]  DEFAULT ('') FOR [CLAttemptsString]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLAcc__1328BA3B]  DEFAULT ('') FOR [CLAccepted]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__141CDE74]  DEFAULT ('') FOR [CLPhone1]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__151102AD]  DEFAULT ('') FOR [CLPhone2]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__160526E6]  DEFAULT ('') FOR [CLPhone3]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__16F94B1F]  DEFAULT ('') FOR [CLPhone4]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__17ED6F58]  DEFAULT ('') FOR [CLCallInfo1]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__18E19391]  DEFAULT ('') FOR [CLCallInfo2]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__19D5B7CA]  DEFAULT ('') FOR [CLCallInfo3]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__1AC9DC03]  DEFAULT ('') FOR [CLCallInfo4]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPIN__1BBE003C]  DEFAULT ('') FOR [CLPIN]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLDis__1CB22475]  DEFAULT ('') FOR [CLDistrict]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLUse__1DA648AE]  DEFAULT ('') FOR [CLUserID]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCre__1E9A6CE7]  DEFAULT ((1)) FOR [CLCrewCode]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPri__1F8E9120]  DEFAULT ('') FOR [CLPriority]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLMes__29F710FA]  DEFAULT ((0)) FOR [CLMessage]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__cc_callou__clPag__2454DCEB]  DEFAULT ('') FOR [clPagerString]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_CALLOU__CLCNC__53A4C8B1]  DEFAULT ('') FOR [CLCNCrewName]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_CALLOU__CLScr__5498ECEA]  DEFAULT ('') FOR [CLScriptsName]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_CALLOU__CLCSS__558D1123]  DEFAULT ('') FOR [CLCSSequenceName]
GO

0
 
TagomAuthor Commented:
the column that hangs first for read only is the [CLIndex] column
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ralmadaCommented:
a) Please show us the insert query you're using and also indicate where you're using it (Is it in a trigger/stored procedure, etc...?)

b) Are you trying to insert to this table through a view?
0
 
TagomAuthor Commented:
I am using the "import wizard" in ms sql studio management
transferring data from one database to another...
basically need to combine the databases so if there is another way, i am all about it!
I need to merge the databases somehow. All table structures are the same in the database.
0
 
ralmadaCommented:
well, first of all make sure your account have adequate privileges on both databases

you can run the following in Management studio.

insert database1.dbo.yourtable
select *
from database2.dbo.yourtable

0
 
TagomAuthor Commented:
these are the errors

Validating (Error)
Messages
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ACIndex".
 (SQL Server Import and Export Wizard)
 
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
 (SQL Server Import and Export Wizard)
 
Error 0xc004706b: Data Flow Task 1: "component "Destination - CC_ActiveCallouts" (121)" failed validation and returned validation status "VS_ISBROKEN".
 (SQL Server Import and Export Wizard)
 
Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
 (SQL Server Import and Export Wizard)
 
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
 (SQL Server Import and Expor
0
 
ralmadaCommented:
if more than one table (and assuming the name is the same
use database1
exec sp_MSforeachtable 'insert [?]
			select * 
			from database2.dbo.[?]'

Open in new window

0
 
ralmadaCommented:
don't use the wizard, use management studio to run the query directly
0
 
TagomAuthor Commented:
This is the message I get now:
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'acisql2.dbo.CC_ActiveCallouts' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
ralmadaCommented:
ok, the problem is that you're trying to insert a value in an identity column

you might want to try

set indentity_insert acisql2.dbo.CC_ActiveCallouts  on

and then run the insert

finally make sure you reset it to off again

set indentity_insert acisql2.dbo.CC_ActiveCallouts  off
0
 
TagomAuthor Commented:
'indentity_insert' is not a recognized SET option.
0
 
ralmadaCommented:
type (extra n)

set identity_insert acisql2.dbo.CC_ActiveCallouts  on

0
 
TagomAuthor Commented:
lost me, if i just copy paste the above code i get an error...says incorrect syntax near 'extra'
0
 
Ephraim WangoyaCommented:

if you are going to use the import wizard, make sure to select the option for writing your own query.
For the query, exclude the first two fields of your table

select  [CLChannelStatus],
           [CLCrewName],
           [CLEmpID],
           [CLInfo1],
           ....
from [dbo].[CC_CalloutLog

If you are to use a query, again simple exclude the two fields

insert into [dbo].[CC_CalloutLog2(
           [CLChannelStatus],
           [CLCrewName],
           [CLEmpID],
           [CLInfo1],
           ....)
select  [CLChannelStatus],
           [CLCrewName],
           [CLEmpID],
           [CLInfo1],
           ....
from [dbo].[CC_CalloutLog

If you want the fields to be exactly the same, then set identiy insert to on as suggested above, eg

set identity_insert  [dbo].[CC_CalloutLog2  on
insert into [dbo].[CC_CalloutLog2
select * from [dbo].[CC_CalloutLog]
set identity_insert  [dbo].[CC_CalloutLog2  off


BTW: I dont understand exactly why [CLCrewName] is a unique identifier
0
 
ralmadaCommented:
You need to enter

set identity_insert acisql2.dbo.CC_ActiveCallouts on
0
 
TagomAuthor Commented:
hand coding worked! my concern is, will this delete the rows already in the table or will it append to what is already there!
0
 
Anthony PerkinsCommented:
It depends.
0
 
ralmadaCommented:
>>will this delete the rows already in the table or will it append to what is already there! <<

My understanding is that it will NOT delete the rows already in the table. Now what could happen is that your identity column might get corrupted if you're trying to insert rows with an existing identity number in the receiving table. Remember that an identity column is meant to be unique.
0
 
ralmadaCommented:
hmmm, I'm not sure why this disposition. You've mentioned you've gone with the hand coding, and that's what I've mentioned in my comment http:#a36912761 plus I've guided you to identify the problem which was the fact that you were trying to insert a value into an identity column.

Can you please advise?
0
 
TagomAuthor Commented:
I actually thought I split the points up. Yes you were correct in your answer, however the example of how to do so is more what I needed. I did not have to turn anything on or off, and all the data inserted once I wrote the insert code correctly. I have no problem with a request to reassign points. If I new how or what to do to make this happen. I would
0
 
ralmadaCommented:
>>I have no problem with a request to reassign points. If I new how or what to do to make this happen. I would <<

click on the request attention link above and ask for a moderator to re open the questions to reassign the points.

Thanks.
0
 
TagomAuthor Commented:
done, thank you for your help
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 10
  • 10
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now