Janet - unless you change to simple recovery - your TLOG is going to grow no matter how small or large the transaction. It's going to keep growing until...

select t.id,t.code,t.pri

from table1 t left outer join

table1 u on t.id=u.id and t.pri=u.pri+1

where u.id is null and t.id>1

Should return all of the rows with a gap in priority BEFORE it.

That's an insane number. An hour to create a non clustered index? I'd have to say that there IS something wrong. Sounds like resource starvation of some...

The concept's the right one - but it sounds like you might not be pushing it "far enough". Meaning - you need to track what's inserted/updated, and what's deleted.

Inserted/updated...

Murray - there's an IDENTITY field setting available to use for just this purpose. If you set this up - you don't need any special code to increment the...

Do you have a good read on what step of the process IS taking most of the time and/or has been increasing? What's happening at that moment.

Since it's running...

You're using Access SQL to do this... use the NZ syntax instead

i.e.

SELECT

nz(LastDate,date()) AS Expr1,

tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.PersonnelNumber

FROM tblSicknessEpisode LEFT JOIN tblSMSStaff ON tblSicknessEpisode.PersonnelNumber = tblSMSStaff.[Personal Number]

GROUP BY tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.PersonnelNumber;

Careful there....

In Access' version of SQL - ISNULL takes only one parameter and tests the value to see if it IS null...

The Access equivalent of SQL's ISNULL function is NZ.

Well - given the lack of detail you're giving, I suppose the answer to your question is...

"Yes"

Be more specific - it will be easier to be more helpful that way....

hehe...If we threw every cranky person off of here....This would be one quiet place........:w00t:

what error do you get? if no error - what happens?

A few questions:

- Assuming you're using the DTS GUI to set these up - what do you have in the transfer task as far as Fetch size? Do...

Since you're in Access - it might depend on where you are.

Access' syntax on that would be...

...nz(mydatefield,date())....

SQL Server's syntax on that would be...

...isnull(mydatefield,getdate())......

Foreign key errors mean that you are trying to insert children into a "parent-child" set of tables.

For example - if you had InvoiceMain and and InvoiceDetail tables (the invoice detail...

Have you checked that that server's language property is set to British English? (SSMS, server properties, advanced?)