Please help me in resoving this issue!

I have created a linked server between SQL Server 7.0 and
Access. I am trying to update and delete records in Access
database using cursors in a stored procedure. I am getting
Error Message: Server: Msg 7392, Level 16, State 2
The fix for this was suggested on the Microsoft support to
add: "Set XACT_ABORT ON" in the code. After adding that in
code, I am still getting the error message.
Please help me in getting this fixed.

The CODE is follows:
set XACT_ABORT ON

set transaction isolation level repeatable read
begin tran

DECLARE del_data cursor
keyset
for
select upper([id]) from test...[data table]

open del_data

BEGIN

begin

end

END

close del_data
deallocate del_data
commit tran
set transaction isolation level read committed

delete test...[data table]
from test...[data table] a
where (select count(*) from access_view b where b.[id] = a.[id]) = 0

AMB

Thanks for the quick response! it worked.
I also have to insert the data in Access table and I am
getting the same error message. Please let me know what I
can do to resolve the issue.

Thanks again!

CODE:

Set XACT_ABORT ON
set transaction isolation level repeatable read
begin tran

DECLARE ins_emp_data cursor
keyset
for
select emp_pega_Id from iVantage_pega.dbo. access_emp_view

open ins_emp_data

BEGIN



BEGIN

END

END

close ins_emp_data
deallocate ins_emp_data
commit tran
set transaction isolation level read committed

Raj

insert into test...[data table]
select (select max(isnull(entryid, 0)) from test...[data table]),
       b.emp_pega_Id,
       empl_last_name,
       emp_first_name,
       null,
       status_category_desc,
       business_card_title,
       sup_last_name,
       sup_first_name ,
       dept_desc,
       emp_region_code,
       'Inc.',
       emp_day_phone,
       emp_dayphone_ext,  
       emp_fax,
       emp_addr1,
       isnull(emp_addr2, space(1)),
       emp_city,
       emp_state,
       emp_zipcode,  
       emp_country,
       null,
       emp_home_phone,
       null,
       isnull(emp_pager_phone, 'N/A'),
       isnull(emp_mobile_phone, 'N/A',
       null,
       null
from access_view a
inner join
(
select distinct emp_pega_Id
from iVantage_pega.dbo.access_emp_view a
left join test...[data table] b on a.emp_pega_Id = b.[id]
where b.[id] is null
) b
on a.[id] = b.emp_pega_Id

What is this select for?

(select max(isnull(entryid, 0)) from test...[data table])

If you need max(entryid) + 1, I recommend using autonumber instead.

hope this help,

AMB

Thanks for the response!!!!!

I can not run the Insert statment, as I am getting the
following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Regular Full
Time' to a column of data type int.

I checked the table in the Access database, all the
columns are text type. I Used Cast and convert function
both to resolve the issue, it did not work.
I am using max(Entryid) to get the next value. The column
is Access is defined as Autonumber, if I remove the max
(EntryID), it is complaining. May be I have to define the
columns at the top in Insert statement.

I have another question , why I can not use cursor the way
I defined earlier, please let me know.

Thanks a lot!!!

Raj

Yes, remove the max(entryid) and do not include the autonumber field in the list.

insert into test...[data table] (fld2name, fld3name,....)  -- without autonumber field
       b.emp_pega_Id,
       empl_last_name,
       emp_first_nam,
       null,
       status_category_desc,
       business_card_title,
       sup_last_name,
       sup_first_name ,
       dept_desc,
       emp_region_code,
       'Inc.',
       emp_day_phone,
       emp_dayphone_ext,  
       emp_fax,
       emp_addr1,
       isnull(emp_addr2, space(1)),
       emp_city,
       emp_state,
       emp_zipcode,  
       emp_country,
       null,
       emp_home_phone,
       null,
       isnull(emp_pager_phone, 'N/A'),
       isnull(emp_mobile_phone, 'N/A',
       null,
       null
from access_view a
inner join
(
select distinct emp_pega_Id
from iVantage_pega.dbo.access_emp_view a
left join test...[data table] b on a.emp_pega_Id = b.[id]
where b.[id] is null
) b
on a.[id] = b.emp_pega_Id

Try to match the values from the select statement with the corresponding field in the table.

AMB