April 23, 2009 at 3:22 am
I have inherited an access database from my predecessor which does a comparison on changes in address data.
Unfortunately the access database vba module that does the comparison doesn't seem to work anymore and as such i have decided to create my own system rather than try and fix the old. It's always hard fixing other people's code.
Anyway: What I am trying to do is import the data from the access database to have as legacy data because I will need to compare against this legacy data for the first comparison before I regularly import the new data.
I am having problems doing such.
I have created a linked server to the access database using:
EXEC master.dbo.sp_addlinkedserver @server = N'CENTRAL_ADDRESS_TABLE', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'#:\###\Central Address Data\Central Address Table.mdb'
GO
WHERE # is drive and path of undisclosed length.
I am able to query the data quite happilly:
SELECT * FROM CENTRAL_ADDRESS_TABLE...[central address data]
However when I try to run the code below to insert the data into the table I have created called dbo.LegecyCentralAddressData I get an error.
Code:
USE [AddressData]
GO
INSERT dbo.LegecyCentralAddressData
SELECT * FROM CENTRAL_ADDRESS_TABLE...[central address data]
Error:
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.
The statement has been terminated.
I have tried changing the specification of the table dbo.LegecyCentralAddressData without success.
The current spec is:
USE [AddressData]
GO
/****** Object: Table [dbo].[LegecyCompleteAddressData] Script Date: 23/04/2009 09:58:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LegecyCentralAddressData](
[UPRN] nvarchar NULL,
[MainId] nvarchar NULL,
[MainType] nvarchar NULL,
[MainStatus] nvarchar NULL,
[SystemDMA] nvarchar NULL,
[ACORN] nvarchar NULL,
[OccType] nvarchar NULL,
[GrndLevel] nvarchar NULL,
[NormalOpHead] nvarchar NULL,
[MaxHead] nvarchar NULL,
[SubUnit] nvarchar NULL,
[Building] nvarchar NULL,
[Road] nvarchar NULL,
[Locality] nvarchar NULL,
[Town] nvarchar NULL,
[County] nvarchar NULL,
[PostCode] nvarchar NULL,
[SupplyType] nvarchar NULL,
[Easting] nvarchar NULL,
[Northing] nvarchar NULL,
[SPID] nvarchar NULL,
[SRID] nvarchar NULL,
[FileDMA] nvarchar NULL,
[AddressID] bigint NOT NULL,
[DateAdded]datetime NULL,
[Status] nvarchar NULL,
[StatusDate] datetime NULL,
[TempStatus] nvarchar NULL,
[CompleteAddress] nvarchar(max) NULL,
[RecordType] nvarchar NULL,
[FixedID] bigint NOT NULL,
[RoadID] nvarchar NULL,
[WMSInactive] nvarchar NULL,
[ResZone] nvarchar NULL,
[System] nvarchar NULL
CONSTRAINT [PK_LegecyCompleteAddressData] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I have one very large field called CompleteAddress which the longest length of the string is 249 characters. Apart from that field all others are not very long.
Has anyone got any further ideas. Thanks
April 23, 2009 at 4:15 am
Sorry everyone I have solved the problem myself. I found that when i looked at the table in design view i.e. 'Modify' I found that the nvarchar which I hadn't specified a length for had defaulted to nvarchar(1) which obviously is way to short.
I have stopped being lazy and specified the lengths more precisely in the table design.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply