Error: Server: MSG 7392, Level 16, State 2 
Author Message
 Error: Server: MSG 7392, Level 16, State 2

Hi,

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



Sun, 24 Apr 2005 22:54:37 GMT
 Error: Server: MSG 7392, Level 16, State 2

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

AMB



Sun, 24 Apr 2005 23:37:56 GMT
 Error: Server: MSG 7392, Level 16, State 2
Hi Mesa,

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

Quote:
>-----Original Message-----
>delete test...[data table]
>from test...[data table] a
>where (select count(*) from access_view b where b.[id] =
a.[id]) = 0

>AMB



Mon, 25 Apr 2005 02:53:59 GMT
 Error: Server: MSG 7392, Level 16, State 2

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



Mon, 25 Apr 2005 03:33:18 GMT
 Error: Server: MSG 7392, Level 16, State 2
Hi Mesa,

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

Quote:
>-----Original Message-----
>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



Mon, 25 Apr 2005 05:30:06 GMT
 Error: Server: MSG 7392, Level 16, State 2

Quote:
>>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.<<

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

Quote:
>>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.<<

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

AMB



Mon, 25 Apr 2005 22:57:40 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Error Message: Server: Msg 7392, Level 16, State 2

2. Server: Msg 7399, Level 16, State 1, Line 1 error on .mdb as linked server

3. Server: Msg 7399, Level 16, State 1, Line 1 error on .mdb as linked server

4. HELP - SQL Server 7.0 Error - Msg 542, Level 16, State 1

5. Server: Msg 107, Level 16, State 3 Error

6. Select error:Server: Msg 8120, Level 16, State 1, Line 1

7. Error Msg: 7343, Level 16, State 4 (linking Database)

8. SQLServer Msg 2506, Level 16, State 3 Error - Help Needed

9. Msg 5159, Level 16, State 1 OS Error 38

10. Getting Error Msg 7343, Level 16, State 4

11. Msg 207, Level 16, State 1 - bizarre error


 
Powered by phpBB® Forum Software