linked servers with windows 2000 professional/sql server 2000

linked servers with windows 2000 professional/sql server 2000

Post by RZ » Thu, 17 Apr 2003 06:16:23



Hi,
I have 2 servers configured as linked servers.
One is ServerA and the other ServerB.
From ServerB I have dts packages that perform calls to
remote stored procedures is ServerA.
In one of my packages I have the following:
1. A connection object pointing to serverA
2. DDQ(Data Driven Query Task). In that DDQ , there is an
option tab called "lookups" where I created a new lookup
to perform a query in ServerA.
3. When I perform step # 2 I get the following error:
Server: 'ServerA' is not configured for DATA ACCESS

I check the configurations on the linked server properties
for this server and the Data Access flag is set in both
servers.

One difference that I think it might be the problem is
that serverA is running Windows 2000 server and Sql Server
2000
. Serverb is running Windows 2000 professional and sql
server 2000.
I can do 4 part queries from Query Analyzer from both
sides:
Example Select * from ServerA.TestDatabase.dbo.testtable

Any ideas?

Thanks,
RZ

 
 
 

linked servers with windows 2000 professional/sql server 2000

Post by Allan Mitchel » Thu, 17 Apr 2003 15:52:51


The times i see this is when trying to perform a linked server query against
the local server.
Basically it is over qualified.  it is a neat trick though to be able to run
SELECT statements from Stored procs.

You can change the option by executing

EXEC sp_serveroption '<<NAME OF SERVER>>','DATA ACCESS', True

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> Hi,
> I have 2 servers configured as linked servers.
> One is ServerA and the other ServerB.
> From ServerB I have dts packages that perform calls to
> remote stored procedures is ServerA.
> In one of my packages I have the following:
> 1. A connection object pointing to serverA
> 2. DDQ(Data Driven Query Task). In that DDQ , there is an
> option tab called "lookups" where I created a new lookup
> to perform a query in ServerA.
> 3. When I perform step # 2 I get the following error:
> Server: 'ServerA' is not configured for DATA ACCESS

> I check the configurations on the linked server properties
> for this server and the Data Access flag is set in both
> servers.

> One difference that I think it might be the problem is
> that serverA is running Windows 2000 server and Sql Server
> 2000
> . Serverb is running Windows 2000 professional and sql
> server 2000.
> I can do 4 part queries from Query Analyzer from both
> sides:
> Example Select * from ServerA.TestDatabase.dbo.testtable

> Any ideas?

> Thanks,
> RZ


 
 
 

linked servers with windows 2000 professional/sql server 2000

Post by RZ » Thu, 17 Apr 2003 22:15:39


Allan thanks for your response. I try that but it didn't
work , I'm still getting the error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Server 'ServerA' is not configured for
DATA ACCESS
Quote:>-----Original Message-----
>The times i see this is when trying to perform a linked

server query against
Quote:>the local server.
>Basically it is over qualified.  it is a neat trick

though to be able to run
>SELECT statements from Stored procs.

>You can change the option by executing

>EXEC sp_serveroption '<<NAME OF SERVER>>','DATA ACCESS',
True

>--

>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org



>> Hi,
>> I have 2 servers configured as linked servers.
>> One is ServerA and the other ServerB.
>> From ServerB I have dts packages that perform calls to
>> remote stored procedures is ServerA.
>> In one of my packages I have the following:
>> 1. A connection object pointing to serverA
>> 2. DDQ(Data Driven Query Task). In that DDQ , there is
an
>> option tab called "lookups" where I created a new lookup
>> to perform a query in ServerA.
>> 3. When I perform step # 2 I get the following error:
>> Server: 'ServerA' is not configured for DATA ACCESS

>> I check the configurations on the linked server
properties
>> for this server and the Data Access flag is set in both
>> servers.

>> One difference that I think it might be the problem is
>> that serverA is running Windows 2000 server and Sql
Server
>> 2000
>> . Serverb is running Windows 2000 professional and sql
>> server 2000.
>> I can do 4 part queries from Query Analyzer from both
>> sides:
>> Example Select * from ServerA.TestDatabase.dbo.testtable

>> Any ideas?

>> Thanks,
>> RZ

>.

 
 
 

1. Linked Server Configuration - SQL Server 2000/Windows 2000

Linked Server Configuration - SQL Server 2000/Windows 2000

Hello !!

Please advice.

I have a Win2000/SQL Server 2000 server on which I would
like to setup a  linked server.
I have the ip for the server to be linked.  I created a
linked server and specified sql server type.  Both the
source and the linked servers are sql server 2000, however
when I click on the linked servers tables I get the below
error.
Error: 7399. Ole DB Provider 'SQLOLEDB' reported an error.

Since I have only the ip please advice if it would be
possible to use the ip name in the transact sql.  Is there
any things I have to set before i do that.
Would this work
'Select * from linked-server-ip\db-name..tbl-name'.

Regards,
KK

2. CFP GIS workshop

3. D3 printing errors under Windows 2000 server and Windows 2000 professional

4. Subject: 8.1.7: Inserted rows not found for update within transaction.

5. SQL Server 2000 vs IIs on Windows 2000 Professional

6. Moving from access

7. Unable to Install SQL Server 2000 on windows 2000 Professional

8. Can't get Text ISAM to use SCHEMA.INI

9. Install Problem with SQL Server 2000 on Windows 2000 Professional

10. SQL Server 2000 on Windows 2000 Professional

11. Cannot install SQL Server 2000 Standard Edition on Windows 2000 Professional

12. SQL Server 2000 client on Windows 2000 professional

13. Update NT4.0 and SQL Server 7.0 to Windows 2000 and SQL Server 2000