linked server problem

I have two SQL server A and B and I want to access data from one to the other server.
I setup a linked server named 'LinkB' on A to link server B, I can see tables and views of B when I click the linked server node in EM. I can also execute select or SP of B in Query analyzer, It works well.

My question is, when I try to built a linked server 'LinkA' on B to link A, It seems no problem occur, but when I click the linked server node, I can not see any tables or views of A, and I can't execute SQL like 'select * from A.database.dbo.table' in Query analyzer.

I use 'sa' to connect the two SQL server.

who can tell me the real reason?
thankscan u give more imformation about servers?|||Originally posted by a1ex
can u give more imformation about servers?
the two server are all Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
I can connect to A and B in EM use same userid 'sa' and password.

Thanks very much!!|||who can help me with this problem? thanks!|||Originally posted by shine_law

My question is, when I try to built a linked server 'LinkA' on B to link A, It seems no problem occur, but when I click the linked server node, I can not see any tables or views of A

i noticed that you mentioned "linkA" and then said "link A" i thi sa typo or is this how you called it?|||I placed 'LinkA' on server B, 'LinkA' is the name of the lined server, which is used to access data on server A. Actully,I can call it any other name, suck as 'LinkFromBtoA'

thankssql

Linked server issues for named instances.

Howdy all. Ive configured linked servers plenty of times, but now Im
wondering if Ive ever done it on a named instance. I've got an SQL 2000 SP2
box that I can link up to default instances with no issue. But when I try to
link it to a named instance (various editions and service packs) it blows up
with:
"SQL Server does not exist or access is denied. "
I know it's not a login/ permissions issue as I've went so far as to use SA
on both the "source" and "destination" boxes. BOL says I can happen, so Im
not understanding what the problem is?
TIA, ChrisR
Hello,
Can you aricreate a server alias for the named instance using Client network
utility and then use this alias to create a linked server.
Thanks
Hari
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>
|||A couple items to check:
1. Check the MDAC version. Updating to the latest might help resolve the
issue.
2. Check your firewall. The named instances are assigned dynamic port for
communication and you firewall might be dropping the packets. Normally a
good idea to assign a static port for the named instance and to configure
your firewall.
3. It is worth updating SQL Server to the latest SP.
Here are some useful links to troubleshoot the issue:
http://support.microsoft.com/kb/328306/
http://support.microsoft.com/kb/287932
http://support.microsoft.com/kb/827422/
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>
sql

Linked server issues for named instances.

Howdy all. Ive configured linked servers plenty of times, but now Im
wondering if Ive ever done it on a named instance. I've got an SQL 2000 SP2
box that I can link up to default instances with no issue. But when I try to
link it to a named instance (various editions and service packs) it blows up
with:
"SQL Server does not exist or access is denied. "
I know it's not a login/ permissions issue as I've went so far as to use SA
on both the "source" and "destination" boxes. BOL says I can happen, so Im
not understanding what the problem is?
TIA, ChrisRHello,
Can you aricreate a server alias for the named instance using Client network
utility and then use this alias to create a linked server.
Thanks
Hari
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>|||A couple items to check:
1. Check the MDAC version. Updating to the latest might help resolve the
issue.
2. Check your firewall. The named instances are assigned dynamic port for
communication and you firewall might be dropping the packets. Normally a
good idea to assign a static port for the named instance and to configure
your firewall.
3. It is worth updating SQL Server to the latest SP.
Here are some useful links to troubleshoot the issue:
http://support.microsoft.com/kb/328306/
http://support.microsoft.com/kb/287932
http://support.microsoft.com/kb/827422/
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>

Linked server issues for named instances.

Howdy all. Ive configured linked servers plenty of times, but now Im
wondering if Ive ever done it on a named instance. I've got an SQL 2000 SP2
box that I can link up to default instances with no issue. But when I try to
link it to a named instance (various editions and service packs) it blows up
with:
"SQL Server does not exist or access is denied. "
I know it's not a login/ permissions issue as I've went so far as to use SA
on both the "source" and "destination" boxes. BOL says I can happen, so Im
not understanding what the problem is?
TIA, ChrisRHello,
Can you aricreate a server alias for the named instance using Client network
utility and then use this alias to create a linked server.
Thanks
Hari
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>|||A couple items to check:
1. Check the MDAC version. Updating to the latest might help resolve the
issue.
2. Check your firewall. The named instances are assigned dynamic port for
communication and you firewall might be dropping the packets. Normally a
good idea to assign a static port for the named instance and to configure
your firewall.
3. It is worth updating SQL Server to the latest SP.
Here are some useful links to troubleshoot the issue:
http://support.microsoft.com/kb/328306/
http://support.microsoft.com/kb/287932
http://support.microsoft.com/kb/827422/
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
If you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||I tried setting a specific security user id and password and that, also,
provided the same error.
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>
sql

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.comIf you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I tried setting a specific security user id and password and that, also,
provided the same error.
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>

Linked Server Issue

I am trying to set up a link between 2 SQLServer databases. One is on
server named ACT-S91 and the other is on server named ACT-S92. I ran the
following statement on ACT-S91 - which said it worked.
sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
So, at this point I should have ACT-S91 and ACT-S92 linked. But when I run
a query from ACT-S91 trying to access the data on ACT-S92 I get the
following error:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'SQLOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
returned 0x8007007f].
I assume I am missing something in setting up the link. But from the
documentation I can't determine what that something might be. Any help?
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.comIf you look at the properties of the linked Server in Enterprise Manager,
under Security, what context is the connection to the linked server being
made.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I used the one that says Be made using the login's current security context.
Since I can get into both servers I assumed I would have no problems. I
have no specific local logins defined. We use windows authentication on
both machines. So, if I can get into both machines shouldn't this have been
right?
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I tried setting a specific security user id and password and that, also,
provided the same error.
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:hXKEeD%23UEHA.2616@.cpmsftngxa10.phx.gbl...
> If you look at the properties of the linked Server in Enterprise Manager,
> under Security, what context is the connection to the linked server being
> made.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Figured out the problem appears to be that you can't use the SQLServer
option. But on our server there are no providers loaded so we are
researching why this is and how to fix it. Anyone have any ideas how to get
providers loaded? We have 2 other servers and the providers show up there
fine. The server on which they don't show up is a Server2003 server running
SQLServer2000 (was recently upgraded to Server2003).
--
Edward Fultz
Sr. Software Engineer
Pamet Systems
Acton, Ma
www.pametsystems.com
"Ed Fultz" <efultz@.yahoo.com> wrote in message
news:%235FnB88UEHA.4092@.TK2MSFTNGP11.phx.gbl...
> I am trying to set up a link between 2 SQLServer databases. One is on
> server named ACT-S91 and the other is on server named ACT-S92. I ran the
> following statement on ACT-S91 - which said it worked.
> sp_addlinkedserver N'ACT-S92', ' ', N'SQLOLEDB', N'ACT-S92'
> So, at this point I should have ACT-S91 and ACT-S92 linked. But when I
run
> a query from ACT-S91 trying to access the data on ACT-S92 I get the
> following error:
> Server: Msg 7302, Level 16, State 1, Line 1
> Could not create an instance of OLE DB provider 'SQLOLEDB'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for SQLOLEDB
> returned 0x8007007f].
>
> I assume I am missing something in setting up the link. But from the
> documentation I can't determine what that something might be. Any help?
>
> --
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>

linked server from SQL2000/WIN2003 to SQL2000/WIN2000

Hi NG,
I have a problem with distributed transactions between 2 SQL2000-Servers.
2 SQL2000-Server (details see below), one runs at Win2000 as named instance,
the other at Win2003 as default, both in the same domain controlled by a
Win2000-Server
=> distributed transaction works fine in both directions in
4-Part-Naming-Convention, including UPDATE and INSERT.
I didn't change anything in any way on these 2 Servers. The only thing to
mention is that the Sysads changed the DC-Server from Win2000 to Win2003.
Since this upgrade I get the following error (in both directions):
SQL-Error 7391 [blabla...] [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]
The SQL:
UPDATE localtbl
SET field = 1
FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON rtbl.fk_id
= localtbl.id)
But I have no problems with the following SELECT:
SELECT *
FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON
localtbl.id = rtbl.fk_id)
I tested the same sqls successfully between the Win2000-SQL2000 from above
and another Win2000-SQLServer2000.
I executed INSCAT.SQL on the Win2000-System with no effect. On Win2003 I
can't do this yet because it is a production server. I may do so on weekend.
Concerning my DTs, my special need is Win2003 as the leading Server.
Here the results from @.@.version and the MDAC-Version from the registry:
Win2003
MDAC: 2,80,1022,0
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: )
Win2000
MDAC: 2,71,9040,2
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.0 (Build 2195: Service Pack
3)
Is there anybody with some hints?
Thanks a lot
PatrickHI
Verify in Add or Remove Programs-->Windows Comonents-->Application Server-->
Details
The Enable network DTC access must installed. (Not default!)
JBandi|||Hi
"Andras Jakus" <andras.jakus@.vodafone.hu> schrieb im Newsbeitrag
news:C2B10F53-781A-4360-93BF-689532E32469@.microsoft.com...
> HI
> Verify in Add or Remove Programs-->Windows Comonents-->Application
Server--> Details
> The Enable network DTC access must installed. (Not default!)
> JBandi
it is installed, on both Server.
As described before everything worked fine just to the moment when the
DC-Server get an upgrade to Win2003.|||Hello NG,
I solved the problem myself.
The win2003-server had a problem with the changed/upgraded domain
controller.
Detaching the server from the domain and re-attaching it to the domain with
deleting the computeraccount in ADS solved my problem!
However, the win2003-server didn't recognize the DC-Change "fully",
something got wrong and this caused the described problems with distributed
transaction. The sysads can't explain this behavior, nor do I.
You will mention by the way i describe this "solution" that i doesn't really
understand it.
I don't know which of those things like authentification, trusting,
locator-service etc. has what influence on the SQLOLEDB provider or the
MSDTC.
I hope there is anybody out there with a brief explanation concerning the
function of linked server / MSDTC / Network!? That would be great!
Best regards
Patrick
"Patrick Haubold" <phaubold@.zeb.de> wrote in
news:c49r3k$2geour$1@.ID-152493.news.uni-berlin.de...
> Hi NG,
> I have a problem with distributed transactions between 2 SQL2000-Servers.
> 2 SQL2000-Server (details see below), one runs at Win2000 as named
instance,
> the other at Win2003 as default, both in the same domain controlled by a
> Win2000-Server
> => distributed transaction works fine in both directions in
> 4-Part-Naming-Convention, including UPDATE and INSERT.
> I didn't change anything in any way on these 2 Servers. The only thing to
> mention is that the Sysads changed the DC-Server from Win2000 to Win2003.
> Since this upgrade I get the following error (in both directions):
> SQL-Error 7391 [blabla...] [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]
> The SQL:
> UPDATE localtbl
> SET field = 1
> FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON
rtbl.fk_id
> = localtbl.id)
> But I have no problems with the following SELECT:
> SELECT *
> FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON
> localtbl.id = rtbl.fk_id)
> I tested the same sqls successfully between the Win2000-SQL2000 from above
> and another Win2000-SQLServer2000.
> I executed INSCAT.SQL on the Win2000-System with no effect. On Win2003 I
> can't do this yet because it is a production server. I may do so on
weekend.
> Concerning my DTs, my special need is Win2003 as the leading Server.
> Here the results from @.@.version and the MDAC-Version from the registry:
> Win2003
> MDAC: 2,80,1022,0
> 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: )
> Win2000
> MDAC: 2,71,9040,2
> 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.0 (Build 2195: Service Pack
> 3)
> Is there anybody with some hints?
> Thanks a lot
> Patrick
>sql

linked server from SQL2000/WIN2003 to SQL2000/WIN2000

Hi NG,
I have a problem with distributed transactions between 2 SQL2000-Servers.
2 SQL2000-Server (details see below), one runs at Win2000 as named instance,
the other at Win2003 as default, both in the same domain controlled by a
Win2000-Server
=> distributed transaction works fine in both directions in
4-Part-Naming-Convention, including UPDATE and INSERT.
I didn't change anything in any way on these 2 Servers. The only thing to
mention is that the Sysads changed the DC-Server from Win2000 to Win2003.
Since this upgrade I get the following error (in both directions):
SQL-Error 7391 [blabla...] [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]
The SQL:
UPDATE localtbl
SET field = 1
FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON rtbl.fk_id
= localtbl.id)
But I have no problems with the following SELECT:
SELECT *
FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON
localtbl.id = rtbl.fk_id)
I tested the same sqls successfully between the Win2000-SQL2000 from above
and another Win2000-SQLServer2000.
I executed INSCAT.SQL on the Win2000-System with no effect. On Win2003 I
can't do this yet because it is a production server. I may do so on weekend.
Concerning my DTs, my special need is Win2003 as the leading Server.
Here the results from @.@.version and the MDAC-Version from the registry:
Win2003
MDAC: 2,80,1022,0
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: )
Win2000
MDAC: 2,71,9040,2
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.0 (Build 2195: Service Pack
3)
Is there anybody with some hints?
Thanks a lot
Patrick
HI
Verify in Add or Remove Programs-->Windows Comonents-->Application Server--> Details
The Enable network DTC access must installed. (Not default!)
JBandi
|||Hi
"Andras Jakus" <andras.jakus@.vodafone.hu> schrieb im Newsbeitrag
news:C2B10F53-781A-4360-93BF-689532E32469@.microsoft.com...
> HI
> Verify in Add or Remove Programs-->Windows Comonents-->Application
Server--> Details
> The Enable network DTC access must installed. (Not default!)
> JBandi
it is installed, on both Server.
As described before everything worked fine just to the moment when the
DC-Server get an upgrade to Win2003.
|||Hello NG,
I solved the problem myself.
The win2003-server had a problem with the changed/upgraded domain
controller.
Detaching the server from the domain and re-attaching it to the domain with
deleting the computeraccount in ADS solved my problem!
However, the win2003-server didn't recognize the DC-Change "fully",
something got wrong and this caused the described problems with distributed
transaction. The sysads can't explain this behavior, nor do I.
You will mention by the way i describe this "solution" that i doesn't really
understand it.
I don't know which of those things like authentification, trusting,
locator-service etc. has what influence on the SQLOLEDB provider or the
MSDTC.
I hope there is anybody out there with a brief explanation concerning the
function of linked server / MSDTC / Network!? That would be great!
Best regards
Patrick
"Patrick Haubold" <phaubold@.zeb.de> wrote in
news:c49r3k$2geour$1@.ID-152493.news.uni-berlin.de...
> Hi NG,
> I have a problem with distributed transactions between 2 SQL2000-Servers.
> 2 SQL2000-Server (details see below), one runs at Win2000 as named
instance,
> the other at Win2003 as default, both in the same domain controlled by a
> Win2000-Server
> => distributed transaction works fine in both directions in
> 4-Part-Naming-Convention, including UPDATE and INSERT.
> I didn't change anything in any way on these 2 Servers. The only thing to
> mention is that the Sysads changed the DC-Server from Win2000 to Win2003.
> Since this upgrade I get the following error (in both directions):
> SQL-Error 7391 [blabla...] [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]
> The SQL:
> UPDATE localtbl
> SET field = 1
> FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON
rtbl.fk_id
> = localtbl.id)
> But I have no problems with the following SELECT:
> SELECT *
> FROM localtbl INNER JOIN remotesrv.remotedb.dbo.remotetbl rtbl ON
> localtbl.id = rtbl.fk_id)
> I tested the same sqls successfully between the Win2000-SQL2000 from above
> and another Win2000-SQLServer2000.
> I executed INSCAT.SQL on the Win2000-System with no effect. On Win2003 I
> can't do this yet because it is a production server. I may do so on
weekend.
> Concerning my DTs, my special need is Win2003 as the leading Server.
> Here the results from @.@.version and the MDAC-Version from the registry:
> Win2003
> MDAC: 2,80,1022,0
> 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: )
> Win2000
> MDAC: 2,71,9040,2
> 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.0 (Build 2195: Service Pack
> 3)
> Is there anybody with some hints?
> Thanks a lot
> Patrick
>

linked server for a named instance

sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you dont wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion to
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statement;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:

> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-"
> 's in it. I have two instances of sql on it. So lets say I have the names of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>
|||First, I create the Alias in the Client Network Utility. Then I:
declare @.p_alias varchar(128)
DECLARE @.v_sql VARCHAR(500)
set @.p_alias = 'LSTestDest'
SeT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
Thanks Joseph this fixed it, but I don't understand why? I don't see how
this would be adding a linked server any differently than I did in EM?
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:81DF5536-7CD4-473C-9A71-A0732BDAE2E6@.microsoft.com...[vbcol=seagreen]
> Create a stored procedure with servername, loginuser, login_password and
> an
> alias to call the server(which can be null). Then write a small portion
> to
> drop link server if it exist:
> IF EXISTS (SELECT srvname
> FROM master.dbo.sysservers (NOLOCK)
> WHERE srvname = @.p_alias)
> BEGIN
> EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
> EXEC master.dbo.sp_dropserver @.p_alias
> Then make sure you have declare your variable to excute dynamic sql
> statement;
> DECLARE @.v_sql VARCHAR(500)
> And then use this peace to actually link the server.
> /* Add linked server */
> SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
> '@.server = ''' + @.p_alias + ''', ' +
> '@.srvproduct = ''SQLServer OLEDB Provider'', ' +
> '@.provider = ''SQLOLEDB'', ' +
> '@.datasrc = ''' + @.p_servername + ''' ' +
> 'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''',
> ''rpc
> out'', ''TRUE'' '
> EXEC(@.v_sql)
> /* Add login for linked server */
> EXEC master.dbo.sp_addlinkedsrvlogin
> @.rmtsrvname = @.p_alias,
> @.useself = 'false',
> @.rmtuser = @.p_user,
> @.rmtpassword = @.p_pw
> GO
> This is the easy way to do it using TSQL and all credit for this one has
> to
> go to my mentor.
> Jared
> Now you can link a server in a different way but this script just does all
> the work for you.
> Hope this helps.
>
> "ChrisR" wrote:

linked server for a named instance

sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you don´t wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion to
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statement;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-"
> 's in it. I have two instances of sql on it. So lets say I have the names of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>

linked server for a named instance

sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you dont wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion t
o
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statemen
t;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:

> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-
"
> 's in it. I have two instances of sql on it. So lets say I have the names
of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>|||First, I create the Alias in the Client Network Utility. Then I:
declare @.p_alias varchar(128)
DECLARE @.v_sql VARCHAR(500)
set @.p_alias = 'LSTestDest'
SeT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
Thanks Joseph this fixed it, but I don't understand why? I don't see how
this would be adding a linked server any differently than I did in EM?
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:81DF5536-7CD4-473C-9A71-A0732BDAE2E6@.microsoft.com...[vbcol=seagreen]
> Create a stored procedure with servername, loginuser, login_password and
> an
> alias to call the server(which can be null). Then write a small portion
> to
> drop link server if it exist:
> IF EXISTS (SELECT srvname
> FROM master.dbo.sysservers (NOLOCK)
> WHERE srvname = @.p_alias)
> BEGIN
> EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
> EXEC master.dbo.sp_dropserver @.p_alias
> Then make sure you have declare your variable to excute dynamic sql
> statement;
> DECLARE @.v_sql VARCHAR(500)
> And then use this peace to actually link the server.
> /* Add linked server */
> SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
> '@.server = ''' + @.p_alias + ''', ' +
> '@.srvproduct = ''SQLServer OLEDB Provider'', ' +
> '@.provider = ''SQLOLEDB'', ' +
> '@.datasrc = ''' + @.p_servername + ''' ' +
> 'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''',
> ''rpc
> out'', ''TRUE'' '
> EXEC(@.v_sql)
> /* Add login for linked server */
> EXEC master.dbo.sp_addlinkedsrvlogin
> @.rmtsrvname = @.p_alias,
> @.useself = 'false',
> @.rmtuser = @.p_user,
> @.rmtpassword = @.p_pw
> GO
> This is the easy way to do it using TSQL and all credit for this one has
> to
> go to my mentor.
> Jared
> Now you can link a server in a different way but this script just does all
> the work for you.
> Hope this helps.
>
> "ChrisR" wrote:
>

linked server can not connect using Named Pipes

Push Transactional Replication

Replication was running fine, we switched one of the servers in the local lan to named pipes.

We can still connect to the database with Query Analyzer (QA) and Enterprise Manager (EM) but replication failed.

We realized that the linked server on the distributer for that subscriber could not connect.

We switched back to TCP and all is well again.

We can issue a Net Use \\server\ipc$ with success so it seems the pipe is ok, and we can still connect with QA and EM.

Why would the protocol make a difference. Why can't we connect a linked server with Named Pipes when it connects fine with TCP, and why even though the linked server can not connect can swe till connect with QA or EM.

More info please:

What agent is failing, distribution agent? Is logreader/snapshot agent functioning properly?

Which machine was configured to use named pipes, distributor or subscriber?

Did you change this at the client level or server level?

When you use QA/EM, are you connecting locally or remotely?

What error are you getting?

Linked Server B/w two SQL SERVER 05 instances (on differnt pc;s) VERY URGENT

I created a linked server named CONFUSION (as the machine name with perticular IP say a.b.c.d)

TESTED the connection by

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

CREATE TABLE #foo
(
pingResult SYSNAME NULL
);

INSERT #foo
EXEC master..xp_cmdshell
'ping CONFUSION';

IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END

DROP TABLE #foo;

I executed the above code and the result was :::

(13 row(s) affected)
Feel free to use linked server.

THAT means my linked server is perfect :::

but when I write the query

select * from CONFUSION.coindb.dbo.test

output is

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'pss2600'.
- INFO --
pss2600 is a user in both sql servers (with all previlages) and I am loged in as root on both machines, while creating a linked server I choosed the option "be made using the security context" user : pss2600 and pwd: XXXX.

What am I doing wrong ?
in both the servers I have the coindb database with the table test.

ITS VERY VERY URGENT

Are you using SQL Server authentication?

...on both servers?

If so does the user have the same password on both servers?

|||You haven't mentioned the SQL server version being used here, I believe there was some issue previous version of SQL.|||I am using SQL Server 2005 on both servers and also authentication mode is ( both ) that is window + SQL Server authentication mode.

user:

I can login into the machine in two ways:

1. As admin (of that machine - local)
2. user of the whole network (which includes UNIX machines + Windows + Mac)

In this perticular case, I logged in into the machies (both ) as admin and when I created a linked server I used the user name and password by which I can login into any machine (network user)

I also created the same user in sql server too (with same password I use to loggin into the network).

Thanks for your replies please let me know if you want any other details too

Pardeep|||

Sorry this isn't helping...

If pss2600 is a Login (servers have logins, databases have users) on both servers

and

pss2600 is created as a Login with SQL Server Authentication on both servers.

and

pss2600 has the same SQL Server Authentication password on both servers

and

you use the "Be made using this security context " option as shown in this quote from Books Online:

Be made using this security context

Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

You should not see the error.

The login you used to create the Linked Server is unimportant.

|||Even I have seen many articles and issues like these, but this is weired.

Can you tell me any way by which I can check (with or without liked server) if I can connect to say server A (SQL Server 2005) from machine say server B.

kinda of remote login (right that what we say). There could be the problem that, server A does not accept remote connection ? how to check this ? and what could be the solution if that what the case is.

Thanks for your response, but its still not working !!|||

I would expect to see a no remote connections allowed error before a Login failed error.

You could try this adhoc alternative to a linked server:
OPENROWSET (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190312.aspx

The Ping test you show should work with any server, linked or not.

Can you use your linked server with any account, say sysadmin or Windows account that is a Login on both servers.

Can you login to both servers from SSMS on both servers?

|||Dear LKH

YOU WROTE

"pss2600 is created as a Login with SQL Server Authentication on both servers."

Though, I selected the option "both SQL Server Authentication and Window Authentication" as previously it was on Window Authentication mode, I actually did not made a separate user with the same login ID and PWD in SQL Server Authentication mode.

THANK A LOTS !!!

now the query

select * from CONFUSION.coindb.dbo.test UNION select * from test

WORKS perfectly !!!!!
-
NOW I have to build an C# application over it (just to demonstrate my senior that it works)
I will get back to you, If you had any problem .. THANKS a lots again !!!

Pardeep
|||Hello lkh

NOW, we have a third server in the picture that is Oracle 10g XE in the same LAN.
I created a same table test(name varchar(10)) in Oracle 10g XE by login pss2600 in windows as well as in 10g. (machine name on which 10 g XE is 'don'

I created a linked server to 10g as follow

1. Right click on linked server, create new linked server
Linked Server : SERVER2
other data source : Microsoft OLEDB DB Provider for Oracle
Data sourse : don
provider string : OraOLEDB.Oracle

security :
be made using security context
username : pss2600 , pwd : same every where
Server options : rcp true, rpc out true, data access true

and if I run this now :

CREATE TABLE #foo
(
pingResult SYSNAME NULL
);

INSERT #foo
EXEC master..xp_cmdshell
'ping SERVER2';

IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END

it shows :

(2 row(s) affected)
Linked server not available.

every thing else is same, like firewall is turned off ..

I will really appreciate if you can get me out of this too.

Pardeep|||

For testing the linked server connectivity you can use the following stored procedure

sp_testlinkedserver [ @.servername ] = servername.

If this command works then there should not be any problem in accessing the data from linked server.

|||Hi ,

sp_testlinkedserver @.server = SERVER2

doest work. What I think is its because I am not setting up the Linked Server as it is supposed to be with Oracle 10g XE.

if you know any good articles or anyone got into same problem, that will aslo be help

I have already seen http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1040216&SiteID=1

but I was unable to make it working !!!!

Thanks for replying Rajesh|||

I copied your ping test to my machine and I can successfully ping any server in my domain even though it is not a linked server. So, if you get Linked server not available that tells me that the two servers can't talk regarless of their being linked.

Here is a link on Oracle linked servers in case you haven't already found it:

http://support.microsoft.com/kb/280106

My next check would be correct premissions in Oracle but it has been a few years since I worked with 9g

Rajesh is correct about sp_testlinkedserver [ @.servername ] = servername, with this caution in Books Online:

"No permissions are checked; however, the caller must have the appropriate login mapping."

Linked Server and Server Registration

I am trying to set up a linked server from a SQL 7 sp3 box
to a SQL 2000 sp3 named instance server. I keep getting
the following error.
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
I can however connect from a SQL Server 7 sp4 box with no
problem. We have checked connectivity and other options
with no luck. Is there a problem with SQL 7 sp3 connecting
to a SQL 2000 named instance box? Any suggestions?It may have to do with your MDAC version. Can you make an OSQL connection
from the SQL7 box to the SQL2K box?
If you have an older version of MDAC you may need to create an alias with
cliconfg and use the alias when you create the linked server. That's
because older versions of MDAC don't know about named instances.
For instance, if your named instance is SQL1\Inst1, on the SQL7 box run
cliconfg.exe and create an alias named SQL1_Alias.
Point SQL1_Alias to SQL1\Inst1. Then in your linked server query specify
SQL1_Alias.
270126 PRB: How to Manage Client Connectivity to Both SQL Server 2000
Virtual
http://support.microsoft.com/?id=270126
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.