Scheduling DTS Jobs

[siena]

Hello all,
I moved all of our access tables to sql server database.
Right now, I am trying to schedule jobs to append data from access db to the same tables moved to sql server database.
I getting error that says duplicate insert not allowed.
Now, I thought I was only trying to append data, not fields to existing ones.
Giving that I am getting this error because of the unique primary key that I have, can anyone suggest a workaround to this issue.
We would like to append any new data from access to sql server on a daily basis.
Any suggestions would be greatly appreciated.

 

[JayKusch]

What the error is saying is that in your SQL Server table, that you are importing Access records to, there is a field that is set to be a Primary Key.

What is happening is that you are exporting records from Access to SQL Server that have already been sent!

So for a simple example we are sending 2 column records, ColumnA and ColumnB with columnA being the primary key(PK). SO let say we send ..

ColumnA(PK) ColumnB
----------- -------
1000 'this is a test 1'
1001 'this is a test 2'
1002 'this is a test 3'

SO our SQL Server table has 3 records w/ the above column data.

Now it would "seem" to be that when Access send the records in its next job run ... it is send a duplicate record like ...

ColumnA(PK) ColumnB
----------- -------
1003 'this is a test 1'
1004 'this is a test 2'
1001 'this is a test 1'

SO 1001 is the PK violator in this job run. Need to be sure you are grabbing the most currect set of records GREATER THAN the primary key of what the SQL Server table already has in it.

Hope this helps some!



Thanks

J. Kusch

 

[siena]

Thank you for the response.
I know the problem is as a result of the system crashing because an attempt was made to insert a duplicate record to a unique primary key.
The area I am having problem with is determining how to go about implementing your recommendation.
For instance, how do you go about handling this:

Need to be sure you are grabbing the most currect set of records GREATER THAN the primary key of what the SQL Server table already has in it

 

[JayKusch]

Since I am not familiar w/ what your package is doing, I am flying kind of blind. But if you need to know what the last PK was that was inserted into the SQL Server table, you could run a "pre-query" that looks something like this ...

SELECT MAX(ColumnA)
FROM MySQLTable

You could load a variable, if that would help, to hold the max PK value to be used against you Access DB export. That could be accomplished by this code ...

delcare @MaxPK Int

SELECT @MaxPK = MAX(ColumnA)
FROM MySQLTable

Once again, I am not sure how your Access to SQL Server DTS package looks like so I am just giving you examples of what you may do.




Thanks

J. Kusch