2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:

>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:

> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:

>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:

> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:

2005 -lock being held by an orphan transaction?

SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.Anyone?|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegroups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.
Anyone?
|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegr oups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>
|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.

2005 - linked server query

I had a database, stored procedure, and related job in 2000 that
checked other servers (via linked server functionality) for failed
jobs. Works fine in 2000. I migrated the database over to my local
2005 instance, and now the query does not work any longer. The below
query returns the error below. Both the 2000 instance and the 2005
instance use Windows Authentication. Does anyone have any ideas?
SELECT 'LinkedServername',
j.job_id,
j.name,
jh.sql_message_id,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time
FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
[LinkedServername].[msdb].[dbo].[sysjobs] j
WHERE jh.run_status = 0
and jh.job_id = j.job_id
and Not Exists (
Select *
From failedjobs f
Where jh.run_status = 0
and jh.job_id = j.job_id
and jh.job_id = f.job_id
and jh.run_date = f.run_date
and jh.run_time = f.run_time)
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
does not exist or the current user does not have permissions on that
table.Anyone?|||Hi
Do you have permissions on the DB and table? Security has been tightened up,
so things that worked in 2000 may not work in 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131561766.037644.219120@.z14g2000cwz.googlegroups.com...
>I had a database, stored procedure, and related job in 2000 that
> checked other servers (via linked server functionality) for failed
> jobs. Works fine in 2000. I migrated the database over to my local
> 2005 instance, and now the query does not work any longer. The below
> query returns the error below. Both the 2000 instance and the 2005
> instance use Windows Authentication. Does anyone have any ideas?
>
> SELECT 'LinkedServername',
> j.job_id,
> j.name,
> jh.sql_message_id,
> jh.sql_severity,
> jh.message,
> jh.run_date,
> jh.run_time
> FROM [LinkedServername].[msdb].[dbo].[sysjobhistory] jh,
> [LinkedServername].[msdb].[dbo].[sysjobs] j
> WHERE jh.run_status = 0
> and jh.job_id = j.job_id
> and Not Exists (
> Select *
> From failedjobs f
> Where jh.run_status = 0
> and jh.job_id = j.job_id
> and jh.job_id = f.job_id
> and jh.run_date = f.run_date
> and jh.run_time = f.run_time)
> Msg 7314, Level 16, State 1, Line 1
> The OLE DB provider "SQLNCLI" for linked server "LinkedServername" does
> not contain the table ""msdb"."dbo"."sysjobhistory"". The table either
> does not exist or the current user does not have permissions on that
> table.
>|||Using SQL authentication, I'm dbo/sa on everything. In windows
authentication, I'm in a SQL DBA group which is aliased over to
sa/dbo/Sys Admin. I include both b/c this stored procedure goes out to
lots of linked servers and some are windows, some are sql. It works on
my 2000 instance - same stored procedure, same query, same linked
servers.