odbc is probably timing out.

how long does the query take?

increase odbc timeout to larger value or consider revising query so it is faster.

did you upgrade the server or move the DBs to a new server?

does that table exist?

who is owner?

are server logins and db users in synch?

consider multiple segments for data (mdfs).

then you could partition tables or seperate indexes from table data

killing restore will probably put db in bad state.

to stop the restore you should be able to kill the process running the restore or stop the sql service or restart...

indexes should speed things up.

on the main table with ID, make the index clustered

and unique if possible

on the temp table

create index ix_tmpid on ##TMP(id)

join should be faster then the IN...

just use the sps -

sp_help_job

sp_help_jobhistory

...

sp_help_maintenance_plan

...

if only difference is

and UBNR = '001'

I would guess it is table scanning UBNR

add an index on that column (and all others in the "where" clause)

are deadlocks during inserts?

perhaps lower fill factor.

if clustered pk has to move a lot of pages to insert new rows it may take a while.

sometimes good not to have the...

what is the dbcc, maybe write an sp that duplicates the dbcc function?

it is fairly simple -

old server:

export logins or copy via dts

backup dbs (use a script) or detach and move files

script jobs

script replication

note linked servers (not aware of a script...

either add transaction log backups to the maintenance plan or change the db to simple.

if log has grown, probably shrink it after the data used decreases.

have you tried increasing the size of tempdb to see what happens?

maybe up to 200 or 500 mb if you have drive space.

also the tables that the queries run against...

dynamic sql is not good for a number of reasons. stay away from it if you can. sounds like the dynamic sql is different then the working non-dynamic...

I was having problem.

was able to fix by exec xp_stopmail

and then xp_sendmail

do not xp_startmail before xp_sendmail