3197 record deleted error

HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
Who know something about this ?If you are getting an error 3167 in Access then it's likely
an access error - can't tell without the error message you
get. But that's probably the case and you would want to post
your question in an access newsgroup.
-Sue
On 27 Oct 2005 08:05:38 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>HI,
>i have an access MDB linked by ODBC to SQL2k.
>I get a strange behaviour when i do an INSERT INTO to a local Mdb table
>from "LEFT OUTER JOINED" SQL tables.
>If the related table hasn't related record to the first table,
>it get an Error 3167.
>This behaviour is not present with MDB tables JOINS.
>Who know something about this ?|||well, if i use only mdb tables this error doesn't occour.
So i suppose it's an odbc problem|||Make sure you have a primary key or unique identifier
defined for your tables you are linking. Make sure the
primary key or unique identifier is not an approximate data
type (e.g. float). Try putting on timestamp data type column
on the tables you are linking.
-Sue
On 28 Oct 2005 00:25:17 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>well, if i use only mdb tables this error doesn't occour.
>So i suppose it's an odbc problem

3197 record deleted error

HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
Who know something about this ?
If you are getting an error 3167 in Access then it's likely
an access error - can't tell without the error message you
get. But that's probably the case and you would want to post
your question in an access newsgroup.
-Sue
On 27 Oct 2005 08:05:38 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>HI,
>i have an access MDB linked by ODBC to SQL2k.
>I get a strange behaviour when i do an INSERT INTO to a local Mdb table
>from "LEFT OUTER JOINED" SQL tables.
>If the related table hasn't related record to the first table,
>it get an Error 3167.
>This behaviour is not present with MDB tables JOINS.
>Who know something about this ?
|||well, if i use only mdb tables this error doesn't occour.
So i suppose it's an odbc problem
|||Make sure you have a primary key or unique identifier
defined for your tables you are linking. Make sure the
primary key or unique identifier is not an approximate data
type (e.g. float). Try putting on timestamp data type column
on the tables you are linking.
-Sue
On 28 Oct 2005 00:25:17 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>well, if i use only mdb tables this error doesn't occour.
>So i suppose it's an odbc problem

2nd Post Re: Subreport Shifts in HTML view...

I have inserted a linked subreport into a table. I have it formatted so
that it lines up perfectly with my main report, so the user shouldn't even
be able to tell that it is a subreport. When I render the report in the
.Net viewer, it's columns line up perfectly. When I export to .PDF, it
lines up fine as well. But when I run the report from Report Manager,
viewing in the default HTML view or export to Web Archive, the columns shift
like 25 pts to the left. What's up with that? Is there a way to fix it?
Thanks,
LisaHave you apready tried playing with the Padding?
"Lisa" wrote:
> I have inserted a linked subreport into a table. I have it formatted so
> that it lines up perfectly with my main report, so the user shouldn't even
> be able to tell that it is a subreport. When I render the report in the
> ..Net viewer, it's columns line up perfectly. When I export to .PDF, it
> lines up fine as well. But when I run the report from Report Manager,
> viewing in the default HTML view or export to Web Archive, the columns shift
> like 25 pts to the left. What's up with that? Is there a way to fix it?
> Thanks,
> Lisa
>
>|||I have tried that. If I pad it enough where it lines up in the HTML default
view, then it ends up off alignment in the other direction in all the other
formats.
This default HTML view is killing me. It seems like there are so many
tweaky problems with it. I just wish the default looked like my view in
Visual Studio. That would make things so much easier!
Lisa
"Rob 'Spike' Stevens" <RobSpikeStevens@.discussions.microsoft.com> wrote in
message news:24866FD7-34E1-4188-A3B3-8E4465610A25@.microsoft.com...
> Have you apready tried playing with the Padding?
> "Lisa" wrote:
> > I have inserted a linked subreport into a table. I have it formatted so
> > that it lines up perfectly with my main report, so the user shouldn't
even
> > be able to tell that it is a subreport. When I render the report in the
> > ..Net viewer, it's columns line up perfectly. When I export to .PDF, it
> > lines up fine as well. But when I run the report from Report Manager,
> > viewing in the default HTML view or export to Web Archive, the columns
shift
> > like 25 pts to the left. What's up with that? Is there a way to fix
it?
> >
> > Thanks,
> >
> > Lisa
> >
> >
> >

2005x64 to 2000x86 linked server question

This may be in the same class as the discussion entitled "SQL Server 2005
Linked Server Security"
But it is just different enough that I thought that I would start my own.
Situation:
I am trying to create a linked server between SQL Server 2005 SP1 Standard
Edition (x64) and SQL Server 2000 SP3 Enterprise Edition (x86)
The linked server appears to create; however, when I try to query it I
recieve the following message:
OLE DB provider "SQLNCLI" for linked server "report" returned message "Login
timeout expired".
OLE DB provider "SQLNCLI" for linked server "report" returned message "An
error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Environments:
Microsoft SQL Server 2005 - 9.00.2153.00 (X64)
May 9 2006 13:58:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
I have created an alias (TCP/IP) to access the SQL Server 2000 instance.
This alias works for SSMS and SSIS.
As per information that is already out there, I have run instcat.sql (SP3)
against the SQL Server 2000 instance. This has been sufficient to resolve
the issue in all of my test environments but not, of course, in the
environment that I really care about (sigh).
I have also ensured the remote connectivity has been enabled on 2005.
I have tried creating the linked sever both in SSMS and by Scripting, with
the same result.
Firewall changes had to be made to allow these machines to talk to each
other... could something be amiss there?
I am about to begin my SSIS workaround but I sure would prefer to get this
working. Any help would be appreciated.I had mentioned in my previous post that I had created an alias to connect to
the SQL Server 2000.
I had created a SQL Server Native Configuration (32 bit) alias because that
is the one that allowed me to connect to the server using SSMS.
I was testing the connection with OSQL when it struck me to add another
alias of the same name under SQL Server Native Configuration.
I can now query the linked server. I am going to test this a little bit
more and if I satisfy myself, I shall come back and close this post.|||Adding the alias has worked in all my affected environments

2005 local server name help

I have installed SQL Server 2005 and by mistake replace local server
name with linked server.
Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
After that I installed replication on same server and enable it as
distributor and subscriber.
It worked fime till server reboot :)
After reboot, the replication stopped working. I find out that this is
due to local server name not exists anymore (it's no record with id=0
in sys.servers view).
Now I have real big problem:
1. I can't drop linked and add a local server with same name since it
used in replication.
2. I can't drop replication because it's trying to get server by name
(apparently quering same view) and it returns NULL.
Any help , please.
Mikeyou have to add the server with
exec sp_addserver 'newname','local'
the key word 'local' will set the serverid to 0.
--
-oj
"Mike" <mguissine@.hotmail.com> wrote in message
news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...
>I have installed SQL Server 2005 and by mistake replace local server
> name with linked server.
> Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
> After that I installed replication on same server and enable it as
> distributor and subscriber.
> It worked fime till server reboot :)
> After reboot, the replication stopped working. I find out that this is
> due to local server name not exists anymore (it's no record with id=0
> in sys.servers view).
> Now I have real big problem:
> 1. I can't drop linked and add a local server with same name since it
> used in replication.
> 2. I can't drop replication because it's trying to get server by name
> (apparently quering same view) and it returns NULL.
> Any help , please.
> Mike
>|||I maybe didn't explained things correctly..
I created linked server with 'LocalServerName' thas my local server
(id=0) does not exists anymore. Now I want to put it back. The right
steps to do it would be:
1. drop linked server. sp_dropserver 'name', 'droplogins'
2. create local server. sp_addserver 'name' , 'local'
The problem is that the server is used in replication, so I can't drop
it hense can't add new server with the same name. I can't do anything
with replication either...:(
The only way I think is to hack into system tables and change id to 0
for the linked server so it will become local. But it looks like in
SS2005 i don't have that option...
Mike
oj wrote:
> you have to add the server with
> exec sp_addserver 'newname','local'
> the key word 'local' will set the serverid to 0.
> --
> -oj
>
> "Mike" <mguissine@.hotmail.com> wrote in message
> news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...
> >I have installed SQL Server 2005 and by mistake replace local server
> > name with linked server.
> > Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
> > After that I installed replication on same server and enable it as
> > distributor and subscriber.
> >
> > It worked fime till server reboot :)
> >
> > After reboot, the replication stopped working. I find out that this is
> > due to local server name not exists anymore (it's no record with id=0
> > in sys.servers view).
> >
> > Now I have real big problem:
> > 1. I can't drop linked and add a local server with same name since it
> > used in replication.
> > 2. I can't drop replication because it's trying to get server by name
> > (apparently quering same view) and it returns NULL.
> >
> > Any help , please.
> >
> > Mike
> >|||Ah. I see your delima. Dropping repl would be the only choice. Sorry.
--
-oj
"Mike" <mguissine@.hotmail.com> wrote in message
news:1151511040.077204.203160@.j72g2000cwa.googlegroups.com...
>I maybe didn't explained things correctly..
> I created linked server with 'LocalServerName' thas my local server
> (id=0) does not exists anymore. Now I want to put it back. The right
> steps to do it would be:
> 1. drop linked server. sp_dropserver 'name', 'droplogins'
> 2. create local server. sp_addserver 'name' , 'local'
> The problem is that the server is used in replication, so I can't drop
> it hense can't add new server with the same name. I can't do anything
> with replication either...:(
> The only way I think is to hack into system tables and change id to 0
> for the linked server so it will become local. But it looks like in
> SS2005 i don't have that option...
> Mike
>
> oj wrote:
>> you have to add the server with
>> exec sp_addserver 'newname','local'
>> the key word 'local' will set the serverid to 0.
>> --
>> -oj
>>
>> "Mike" <mguissine@.hotmail.com> wrote in message
>> news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...
>> >I have installed SQL Server 2005 and by mistake replace local server
>> > name with linked server.
>> > Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
>> > After that I installed replication on same server and enable it as
>> > distributor and subscriber.
>> >
>> > It worked fime till server reboot :)
>> >
>> > After reboot, the replication stopped working. I find out that this is
>> > due to local server name not exists anymore (it's no record with id=0
>> > in sys.servers view).
>> >
>> > Now I have real big problem:
>> > 1. I can't drop linked and add a local server with same name since it
>> > used in replication.
>> > 2. I can't drop replication because it's trying to get server by name
>> > (apparently quering same view) and it returns NULL.
>> >
>> > Any help , please.
>> >
>> > Mike
>> >
>

2005 Cluster linked server issue

We have a two node cluster with two SQL 2005 instances installed. For the
most part things work. This morning we had Instance1 on Node1 & Instance2 on
Node2. When I created a linked server on Instance1 pointed at Instance2
(under the aforementioned node conditions) & set the security context to use
the current login's security context, it didn't do that. Instead when linked
server commands were issued, it tried to connect to Instance2 using NT
AUTHORITY\ANONYMOUS LOGON.
When we put both Instance1 & Instance2 on Node1, everything works. When we
put both Instance1 & Instance2 on Node2, everything works. But when the
instances are on different nodes, the linked server stuff gets screwy.
Anyone have any ideas? Another thing I'll throw in is that I only get
the error when connecting to Instance1 from another machine (my
workstation, app server, etc.) & running a linked server query. When I
connect to Instance1 locally on that box, the linked server query
performs fine. It's almost as if when my windows credentials travel
from my workstation to Node1 and then to Node2, things get messed
up...
CB wrote:
> We have a two node cluster with two SQL 2005 instances installed. For the
> most part things work. This morning we had Instance1 on Node1 & Instance2 on
> Node2. When I created a linked server on Instance1 pointed at Instance2
> (under the aforementioned node conditions) & set the security context to use
> the current login's security context, it didn't do that. Instead when linked
> server commands were issued, it tried to connect to Instance2 using NT
> AUTHORITY\ANONYMOUS LOGON.
> When we put both Instance1 & Instance2 on Node1, everything works. When we
> put both Instance1 & Instance2 on Node2, everything works. But when the
> instances are on different nodes, the linked server stuff gets screwy.

2005 Cluster linked server issue

We have a two node cluster with two SQL 2005 instances installed. For the
most part things work. This morning we had Instance1 on Node1 & Instance2 o
n
Node2. When I created a linked server on Instance1 pointed at Instance2
(under the aforementioned node conditions) & set the security context to use
the current login's security context, it didn't do that. Instead when linke
d
server commands were issued, it tried to connect to Instance2 using NT
AUTHORITY\ANONYMOUS LOGON.
When we put both Instance1 & Instance2 on Node1, everything works. When we
put both Instance1 & Instance2 on Node2, everything works. But when the
instances are on different nodes, the linked server stuff gets screwy.Anyone have any ideas? Another thing I'll throw in is that I only get
the error when connecting to Instance1 from another machine (my
workstation, app server, etc.) & running a linked server query. When I
connect to Instance1 locally on that box, the linked server query
performs fine. It's almost as if when my windows credentials travel
from my workstation to Node1 and then to Node2, things get messed
up...
CB wrote:
> We have a two node cluster with two SQL 2005 instances installed. For the
> most part things work. This morning we had Instance1 on Node1 & Instance2
on
> Node2. When I created a linked server on Instance1 pointed at Instance2
> (under the aforementioned node conditions) & set the security context to u
se
> the current login's security context, it didn't do that. Instead when lin
ked
> server commands were issued, it tried to connect to Instance2 using NT
> AUTHORITY\ANONYMOUS LOGON.
> When we put both Instance1 & Instance2 on Node1, everything works. When w
e
> put both Instance1 & Instance2 on Node2, everything works. But when the
> instances are on different nodes, the linked server stuff gets screwy.

2005 Cluster linked server issue

We have a two node cluster with two SQL 2005 instances installed. For the
most part things work. This morning we had Instance1 on Node1 & Instance2 on
Node2. When I created a linked server on Instance1 pointed at Instance2
(under the aforementioned node conditions) & set the security context to use
the current login's security context, it didn't do that. Instead when linked
server commands were issued, it tried to connect to Instance2 using NT
AUTHORITY\ANONYMOUS LOGON.
When we put both Instance1 & Instance2 on Node1, everything works. When we
put both Instance1 & Instance2 on Node2, everything works. But when the
instances are on different nodes, the linked server stuff gets screwy.Anyone have any ideas? Another thing I'll throw in is that I only get
the error when connecting to Instance1 from another machine (my
workstation, app server, etc.) & running a linked server query. When I
connect to Instance1 locally on that box, the linked server query
performs fine. It's almost as if when my windows credentials travel
from my workstation to Node1 and then to Node2, things get messed
up...
CB wrote:
> We have a two node cluster with two SQL 2005 instances installed. For the
> most part things work. This morning we had Instance1 on Node1 & Instance2 on
> Node2. When I created a linked server on Instance1 pointed at Instance2
> (under the aforementioned node conditions) & set the security context to use
> the current login's security context, it didn't do that. Instead when linked
> server commands were issued, it tried to connect to Instance2 using NT
> AUTHORITY\ANONYMOUS LOGON.
> When we put both Instance1 & Instance2 on Node1, everything works. When we
> put both Instance1 & Instance2 on Node2, everything works. But when the
> instances are on different nodes, the linked server stuff gets screwy.

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.