Hi there

I am having a strange problem - I am trying to transfer data from Access to
MSSQL 2K.

It is a very simple field->field copying tranformation task, so there
shouldn't be any funny stuff involved. There are no restrains/indexes or
anything on the destination table - and all fields allow NULL's.

If I execute the single task in the designer it works, but when I try
running the complete DTS package, the task fails - giving me a syntax error
on the first field.

The full package has worked, and I only inserted a couple of SQL-Task steps,
which handles creation of tables and SP's.

Any ideas?

Regards
Jesper Lauridsen
AKTANT A/S
Denmark

What is the exact error?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jesper Lauridsen" <jesper.lauridsen (AT) aktant (DOT) com> wrote

Whoops - sorry

The excact error is:

"The number of failling rows exceeds the maximum specified.
Line 1: Incorrect syntax near 'KID'"

KID is the first column name, of both the source and destination table.
KID is defined as a nvarchar(50) in both databases.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Are you just doing a TABLE-TABLE transfer? i.e no Query
SP level?

Can you give me the whole definition of the table?

Have you run Profiler to see what query is actually issued not that it
should differ when executed individually and as a whole package.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jesper Lauridsen" <jesper.lauridsen (AT) aktant (DOT) com> wrote

It is the simplest Transform data task you can make in the Designer (From
this table to that table)
No "SELECT something WHERE anotherthing = "

The destination table is created like this:
CREATE TABLE [dbo].[Access_Kunder] (
[KID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Gruppe] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Navn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Navn2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Adresse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[PostNr] [nvarchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Attention] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Tlf] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[KontaktNavn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[KontaktNavn2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
,
[KontaktAdresse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS
NULL ,
[KontaktPostNr] [nvarchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
,
[KontaktBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[KontaktAttention] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS
NULL ,
[Kontakt_Tlf] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Kontakt_Direkte] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS
NULL ,
[Kontakt_Fax] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Kontakt_Mobil] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
,
[Adm_Id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Arkiv] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[HomeDir] [nvarchar] (80) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[BrevSkema] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[BrevRapport] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[BrevSkemaAdm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
,
[BrevRapportAdm] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS
NULL ,
[InputSkema] [smallint] NULL ,
[Notat] [ntext] COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[NewPageOnNewPos] [bit] NULL ,
[Sagsnr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[Meteorologi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[ReadingsVia] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[ReportVia] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[FirstMonth] [smallint] NULL ,
[TilsynSMS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[TilsynEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[AlarmSMS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[AlarmEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[InputSkemaOrientation] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

All fieldnames are in danish (without special characters though).

Hope you can give me a clue


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Both boxes 2000?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jesper Lauridsen" <jesper.lauridsen (AT) aktant (DOT) com> wrote

From Access2000 to SQL2K


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Right sorry Access 2K.

OK What I did added a sample row of data to the SQL Server table pumped it
across to Access
I then turned it around and pumped back to SQL Server
No problems

I am using SP3 + Hotfixes.

If you want send me the Access MDB privately and the table def etc I will
gladly try to repro using your Actual data but my initial test says there is
no problem.

I did think collation was a problem but I cannot get that to fail either.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jesper Lauridsen" <jesper.lauridsen (AT) aktant (DOT) com> wrote

I can mail you the whole package, and the Access database, if that would be
of any use?

The whole thing is in Danish though - well not the code
Hmmm - the Access database takes up 77Mb


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Okay, got the Acess compressed down to 3.5 Mb

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote