[SQLSTATE 42000] (Error 8198)).

Running SQL 2000 & Win 2000.
My main server was upgraded/replaced today. We stopped
SQL on the ond server and copied the dBs to the new
server, which has the same name as old server. I receive
the following message on all my subscriptions from my
remote locations.
The job failed. Unable to determine if the owner
(domain.COM\user) of job sqlname-StoreMain-
vCost,vItem,PLU-remotesqlSERVER-StoreMain-D80E140D-1D56-
4A0A-96D3-E33335441348 has server access (reason: Could
not obtain information about Windows NT
group/user 'domain.COM\user'. [SQLSTATE 42000] (Error
8198)).
I have done some research and found I need to change the
owner of the job.
How do I do that?
HELP!!!!
Larry...
locate the job in the jobs subfolder of the management folder. Right click
on it, select properties and modify it there.
You can also use sp_update_job to make this change.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:515a01c42cb9$00956860$a401280a@.phx.gbl...
> Running SQL 2000 & Win 2000.
> My main server was upgraded/replaced today. We stopped
> SQL on the ond server and copied the dBs to the new
> server, which has the same name as old server. I receive
> the following message on all my subscriptions from my
> remote locations.
> The job failed. Unable to determine if the owner
> (domain.COM\user) of job sqlname-StoreMain-
> vCost,vItem,PLU-remotesqlSERVER-StoreMain-D80E140D-1D56-
> 4A0A-96D3-E33335441348 has server access (reason: Could
> not obtain information about Windows NT
> group/user 'domain.COM\user'. [SQLSTATE 42000] (Error
> 8198)).
> I have done some research and found I need to change the
> owner of the job.
> How do I do that?
>
> HELP!!!!
> Larry...
|||Larry,
the job owner is only validated in AD and not actually used as the job runs
as SQL Agent. With that in mind, you can change the job owner to sa to fix
this type of problem.
Regards,
Paul Ibison

@@rowcount in SQL server 2000

Hi,

We have migrated a stored procedure from SQL 7 to SQL 2000 . We made some changes to remove the Set rowcount and replaced it by rowcount function. But @.@.ROWCOUNT does not return the correct value. It returns 0 when it should return 1.

Here is a section of the code:
DECLARE @.no_of_rows int
DECLARE @.curr_prnt_cmp_convert char(5)

SELECT @.local_cmps_loop = 1,
@.master_child_loop = 1,
@.acct_list_loop = 1,
@.master_child2_loop = 1,
@.no_of_rows = 0,
@.curr_prnt_cmp_convert = ' '

SET NOCOUNT ON

/* -- Temporary Tables Creation -- */

CREATE TABLE #local_cmps (cmp_int smallint, org_flg tinyint, pflg tinyint)

CREATE TABLE #master_child(bus_int smallint, prnt_cmp_int smallint, chld_cmp_int smallint, pflg tinyint)

CREATE TABLE #acct_list (bus_int smallint, prnt_cmp_int smallint, acct_int int)

/* -- Insert all Local Components -- */

INSERT INTO #local_cmps (cmp_int, org_flg, pflg)
(SELECT DISTINCT cmp_int, origin_flg, 0
FROM tbl_sd_components
WHERE origin_flg = 1)

/* -- Get Fiscal Year from Data Base -- */

SELECT @.fiscal_year=num_data
FROM tbl_sd_sys_control
WHERE sys_ctl_typ = 1

/* -- Start Buiding Tables for Local Components -- */

WHILE (@.local_cmps_loop = 1)
/* WHILE ((SELECT COUNT(*) FROM #local_cmps WHERE pflg = 0) > 0) */
BEGIN

/*LEAPMIGRA : Commented and added TOP 1 to subsequect SELECT ; set rowcount 1 */

/* -- Select the Unprocessed Parent Component -- */

SELECT TOP 1 @.curr_prnt_cmp = cmp_int
FROM #local_cmps
WHERE pflg = 0

SELECT @.no_of_rows = @.@.ROWCOUNT

IF (@.no_of_rows = 0)
BREAK /* WHILE (@.local_cmps_loop = 1)*/

The value of @.no_of_rows should be 1. It gives 0
Please advise.

Thanks and Regards,
Paritamaybe a simpler test is in order...

DECLARE @.no_of_rows int
set nocount on
select top 1 au_id,au_lname,phone,contract from pubs.dbo.authors
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows TOP 1'

select au_id,au_lname,phone,contract from pubs.dbo.authors
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows'

and more to your code...
declare @.no_of_rows int
declare @.Tbl table(au_id varchar (11), au_lname varchar (40), phone char (12), contract bit)

set nocount on

insert into @.Tbl
select au_id,au_lname,phone,contract from pubs.dbo.authors
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows inserted'

select top 1 * from @.Tbl
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows TOP 1'

select * from @.Tbl
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows'|||Hi Paul,
Thanks for your reply. I did test this procedure by executing some print commands after fetching @.@.rowcount.

I also executed a dummy procedure with @.@.ROWCOUNT and it worked. But the migrated code (i posted earlier) does not give the right result.

Maybe you could give me some idea about situations where @.@.rowcount could fail.

Thanks and Regards,
Parita|||I honestly have never seen @.@.rowcount fail.

I have seen code where the developer thinks @.@.rowcount faild but it turned out that the query was not returning or updateing the number of rows the developer thought would be effected.

The other situation I have seen is that @.@.rowcount is not caputered right after the query is executed or @.@.rowcount is captured and the developer printed @.@.rowcount rather than the local variable holding the rowcount for the query in question!

here is what I would do to start debugging this problem:
DECLARE @.no_of_rows int
DECLARE @.curr_prnt_cmp_convert char(5)

-- ---------------------------
-- added by psy
-- ---------------------------
declare @.Debug tinyint
, @.RowCount int
-- ---------------------------

SELECT @.local_cmps_loop = 1,
@.master_child_loop = 1,
@.acct_list_loop = 1,
@.master_child2_loop = 1,
@.no_of_rows = 0,
@.curr_prnt_cmp_convert = ' '

-- ---------------------------
-- added by psy
-- ---------------------------
set @.Debug = 1
-- ---------------------------

SET NOCOUNT ON

/* -- Temporary Tables Creation -- */
CREATE TABLE #local_cmps (cmp_int smallint, org_flg tinyint, pflg tinyint)
CREATE TABLE #master_child(bus_int smallint, prnt_cmp_int smallint, chld_cmp_int smallint, pflg tinyint)
CREATE TABLE #acct_list (bus_int smallint, prnt_cmp_int smallint, acct_int int)

/* -- Insert all Local Components -- */
INSERT INTO #local_cmps (cmp_int, org_flg, pflg)
SELECT DISTINCT cmp_int, origin_flg, 0
FROM tbl_sd_components
WHERE origin_flg = 1

-- ---------------------------
-- added by psy
-- ---------------------------
set @.RowCount = @.@.rowcount

if (@.Debug <> 0)
raiserror('Records inserted #local_cmps @.RowCount:%d',0,1,@.RowCount) with nowait
-- ---------------------------

/* -- Get Fiscal Year from Data Base -- */
-- ---------------------------
-- This can be unpredictable! @.fiscal_year will hold the first value found in the num_data
-- attribute where sys_ctl_typ = 1. There is no guarente you will get the same result twice
-- if there are several records that meet the where clause!
-- ---------------------------
SELECT @.fiscal_year = num_data
FROM tbl_sd_sys_control
WHERE sys_ctl_typ = 1

-- ---------------------------
-- added by psy
-- ---------------------------
set @.RowCount = @.@.rowcount

if (@.Debug <> 0)
raiserror('After Select @.fiscal_year @.RowCount:%d',0,1,@.RowCount) with nowait
-- ---------------------------

/* -- Start Buiding Tables for Local Components -- */
WHILE (@.local_cmps_loop = 1)

/* WHILE ((SELECT COUNT(*) FROM #local_cmps WHERE pflg = 0) > 0) */
BEGIN

/*LEAPMIGRA : Commented and added TOP 1 to subsequect SELECT ; set rowcount 1 */
/* -- Select the Unprocessed Parent Component -- */

-- ---------------------------
-- added by psy
-- ---------------------------
set @.RowCount = @.@.rowcount

if (@.Debug <> 0)
raiserror('@.local_cmps_loop:%d, @.master_child_loop:%d, @.acct_list_loop:%d, @.master_child2_loop:%d, @.no_of_rows:%d, @.curr_prnt_cmp_convert:%s',
0,1,@.local_cmps_loop, @.master_child_loop, @.acct_list_loop, @.master_child2_loop, @.no_of_rows, @.curr_prnt_cmp_convert) with nowait
-- ---------------------------

SELECT TOP 1 @.curr_prnt_cmp = cmp_int
FROM #local_cmps
WHERE pflg = 0
SELECT @.no_of_rows = @.@.ROWCOUNT

IF (@.no_of_rows = 0)
BREAK /* WHILE (@.local_cmps_loop = 1)*/

-- ---------------------------
-- added by psy
-- ---------------------------
end
-- ---------------------------

I would bet the problem is some where in your code or your assumptions of the data or how the code is working! I don't see anything inherently wrong with your code but you have not posted the entire sp either.|||Hi Paul,

I have attached here the procedure code. The solution i found was to have a select count query instead of @.@.rowcount and it is working now.

But why @.@.rowcount would not work is a big dilemma.

Thanks

Parita