Problem connecting one SQL Server server to another SQL Server server

Problem connecting one SQL Server server to another SQL Server server

Post by Pragnesh_Ratho » Wed, 20 Mar 2002 02:59:35



My objective is to make a trigger that does the following:

I have two SQL Server 2k servers, A and B. On each I have a database, lets
call the one on A as X and the one on B as Y. My objective is, whenever I
insert data into table X, a trigger is fired that deletes the old data in
table Y and populates it with the same data in X. I have the SQL script and
I know how to make triggers. I've tested it with two different databases in
the same server, but I cannot do it with two different databases in two
different servers. When I try to make the trigger, I come across the
following error:

Error 18456: 'Login failed for users NT Authority\Anonymous Logon'.

To over come this, I just ran a stored procedure sp_grantlogin for NT
Authority\Anonymous Logon, and it didn't throw that error anymore. But, it
gives another error, shown below:

Error 7314: OLE DB provider [servername 'B'] does not contain table "Y". The
table either does not exist or the current user does not have permissions on
that table.

I looked it up in the MS Knowledge base and found that it has something to
do with lowercase letters and service packs. Well, I tried changing the
table name to all Upper Case, but no success.

Any suggestions????

PR

 
 
 

Problem connecting one SQL Server server to another SQL Server server

Post by chri » Wed, 20 Mar 2002 04:43:33


Does the user that your connection as have permissions to the table???

Quote:> My objective is to make a trigger that does the following:

> I have two SQL Server 2k servers, A and B. On each I have a database, lets
> call the one on A as X and the one on B as Y. My objective is, whenever I
> insert data into table X, a trigger is fired that deletes the old data in
> table Y and populates it with the same data in X. I have the SQL script
and
> I know how to make triggers. I've tested it with two different databases
in
> the same server, but I cannot do it with two different databases in two
> different servers. When I try to make the trigger, I come across the
> following error:

> Error 18456: 'Login failed for users NT Authority\Anonymous Logon'.

> To over come this, I just ran a stored procedure sp_grantlogin for NT
> Authority\Anonymous Logon, and it didn't throw that error anymore. But, it
> gives another error, shown below:

> Error 7314: OLE DB provider [servername 'B'] does not contain table "Y".
The
> table either does not exist or the current user does not have permissions
on
> that table.

> I looked it up in the MS Knowledge base and found that it has something to
> do with lowercase letters and service packs. Well, I tried changing the
> table name to all Upper Case, but no success.

> Any suggestions????

> PR


 
 
 

Problem connecting one SQL Server server to another SQL Server server

Post by Pragnesh_Ratho » Wed, 20 Mar 2002 05:12:28


I want to set this thing up...so tell me whether I need to have a separate
account just to let the two machines talk to each other or should both the
machines have the same login and password? Please let me know.

Thanks


> Does the user that your connection as have permissions to the table???


> > My objective is to make a trigger that does the following:

> > I have two SQL Server 2k servers, A and B. On each I have a database,
lets
> > call the one on A as X and the one on B as Y. My objective is, whenever
I
> > insert data into table X, a trigger is fired that deletes the old data
in
> > table Y and populates it with the same data in X. I have the SQL script
> and
> > I know how to make triggers. I've tested it with two different databases
> in
> > the same server, but I cannot do it with two different databases in two
> > different servers. When I try to make the trigger, I come across the
> > following error:

> > Error 18456: 'Login failed for users NT Authority\Anonymous Logon'.

> > To over come this, I just ran a stored procedure sp_grantlogin for NT
> > Authority\Anonymous Logon, and it didn't throw that error anymore. But,
it
> > gives another error, shown below:

> > Error 7314: OLE DB provider [servername 'B'] does not contain table "Y".
> The
> > table either does not exist or the current user does not have
permissions
> on
> > that table.

> > I looked it up in the MS Knowledge base and found that it has something
to
> > do with lowercase letters and service packs. Well, I tried changing the
> > table name to all Upper Case, but no success.

> > Any suggestions????

> > PR

 
 
 

Problem connecting one SQL Server server to another SQL Server server

Post by chri » Wed, 20 Mar 2002 07:49:47


See "security" under Linked Servers in BOL.

> I want to set this thing up...so tell me whether I need to have a separate
> account just to let the two machines talk to each other or should both the
> machines have the same login and password? Please let me know.

> Thanks



> > Does the user that your connection as have permissions to the table???


> > > My objective is to make a trigger that does the following:

> > > I have two SQL Server 2k servers, A and B. On each I have a database,
> lets
> > > call the one on A as X and the one on B as Y. My objective is,
whenever
> I
> > > insert data into table X, a trigger is fired that deletes the old data
> in
> > > table Y and populates it with the same data in X. I have the SQL
script
> > and
> > > I know how to make triggers. I've tested it with two different
databases
> > in
> > > the same server, but I cannot do it with two different databases in
two
> > > different servers. When I try to make the trigger, I come across the
> > > following error:

> > > Error 18456: 'Login failed for users NT Authority\Anonymous Logon'.

> > > To over come this, I just ran a stored procedure sp_grantlogin for NT
> > > Authority\Anonymous Logon, and it didn't throw that error anymore.
But,
> it
> > > gives another error, shown below:

> > > Error 7314: OLE DB provider [servername 'B'] does not contain table
"Y".
> > The
> > > table either does not exist or the current user does not have
> permissions
> > on
> > > that table.

> > > I looked it up in the MS Knowledge base and found that it has
something
> to
> > > do with lowercase letters and service packs. Well, I tried changing
the
> > > table name to all Upper Case, but no success.

> > > Any suggestions????

> > > PR

 
 
 

1. Problems connecting sql server 6.5 and sql server 7

Hello,
(Excuse me for my bad english, I'm italian)

I have to call stored procedure from a SqlServer 6.5 and a SqlServer 7 and
vice-versa.
The problem is that the two servers are in two different domains and there
is a firewall between.
There isn't a trust relationship between this two domains.

I have tried with two sqlserver 7 (in the same conditions) and it go.

I have tried with a sqlserver 7 and a sqlserver 6.5 in the same domain and
it go.

Using sp_addserver in sql server 7 I can use the IP address of the server.
With Sqlserver 6.5 this isn't possible. Maybe this the problem?

Thanks for any suggestion.

2. latest date

3. how to backup a sql server 65 server from a sql server 2000 server

4. How to deploy SQL XML to another server?

5. Moving alerts from one server to another server in sql server 6.5

6. Data Junction for Windows?

7. Having problems connecting my SQL Server to an Oracle Server

8. PROGRAMMERS NEEDED

9. Problem connecting to SQL Server from MTS Server

10. MOVE everything on one SQL server to a new SQL server

11. Transfer database from one SQL Server 6.5 to another SQL Server 6.5

12. Consolidating multiple SQL servers into one SQL server.

13. transfer database from one SQL Server 6.5 to another SQL Server 6.5