Linked server problem

Hi,

I am running following script on server A. Server B is configured as linked server to A.

SET xact_abort ON
GO
begin tran
INSERT INTO B.table1.dbo.CUSTOMER (CUSTOMER_ID,PLAN_CODE)
VALUES (1001,100)
rollback tran

The above script when run run on server A gives following error:-
Error..
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Pls. advise why this error occurs. How to insert records in linked server.

Is the Distributed Transaction Coordinator (MSDTC) service running on both computers?|||yes|||

change begin tran to BEGIN DISTRIBUTED TRANSACTION and see

Madhu

|||that also doesnot work. Is it because A is windows 2003 & B is windows 2000 server?|||

what is the service pack on these box

Madhu

|||

Hi Shah,

If you still have tht problem, here is the link for the solution.

http://support.microsoft.com/?kbid=873160

Linked server problem

Hi,

I am running following script on server A. Server B is configured as linked server to A.

SET xact_abort ON
GO
begin tran
INSERT INTO B.table1.dbo.CUSTOMER (CUSTOMER_ID,PLAN_CODE)
VALUES (1001,100)
rollback tran

The above script when run run on server A gives following error:-
Error..
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Pls. advise why this error occurs. How to insert records in linked server.

Is the Distributed Transaction Coordinator (MSDTC) service running on both computers?|||yes|||

change begin tran to BEGIN DISTRIBUTED TRANSACTION and see

Madhu

|||that also doesnot work. Is it because A is windows 2003 & B is windows 2000 server?|||

what is the service pack on these box

Madhu

|||

Hi Shah,

If you still have tht problem, here is the link for the solution.

http://support.microsoft.com/?kbid=873160

Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- HowardCan you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> results
being[vbcol=seagreen]
> from
> these
the[vbcol=seagreen]
the[vbcol=seagreen]
> over
> relationships
then[vbcol=seagreen]
> other
commonly[vbcol=seagreen]
linked[vbcol=seagreen]
>sql

Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard
Can you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard
|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>
|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the[vbcol=seagreen]
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> results
being[vbcol=seagreen]
> from
> these
the[vbcol=seagreen]
the[vbcol=seagreen]
> over
> relationships
then[vbcol=seagreen]
> other
commonly[vbcol=seagreen]
linked
>

Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- HowardCan you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> > Can you show us the query that you are using over the linked server?
> >
> > Have you tried using OPENQUERY?
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "H Lee" <hlee@.prodigy.net> wrote in message
> > news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> > Two SQL Server 2000 SP3a boxes are configured in a linked server
> > relationship. When performing a Profiler trace on one of them, the
> results
> > spit out a whole slew of what appears to be system stored procedures
being
> > called, each having Profiler Logical Disk Reads showing values ranging
> from
> > 2500 - 50000-- instant targets for possible optimization.
> >
> > The biggest performance offending stored procs are:
> >
> > "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> > "sp_indexes_rowset."
> >
> > The "ApplicationName" associated with the Profiler rows that contain
> these
> > sproc calls is "Microsoft SQL Server." The "HostName" value is one of
the
> > servers in a linked server relationship, and the "ServerName" value is
the
> > other server. These stored procedures are executed many times over and
> over
> > again.
> >
> > We're wondering if some component or aspect of the linked server
> > relationship, or MSDTC, may be causing these stored procedures to be
> > executed repeatedly without an end in sight. If linked server
> relationships
> > or MSDTC is in fact a very likely source of these stored procedures,
then
> > are they essential for the functionality of linked servers or MSDTC? If
> > anyone has experienced this behavior or know of it, what might be some
> other
> > standard causes, other than 3rd-party application calls, that is
commonly
> > known to execute these sprocs? And, given that the cause is indeed
linked
> > servers, or MSDTC, or some other functionality built into SQL Server, is
> > there any chance that the frequency of these calls can be lessened or
> > eliminated?
> >
> > Thanks,
> > - Howard
> >
> >
> >
>

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 connections

I have a server in my office which is configured as a linked server on
another one.
How can I configure the connections between them to be killed after a period
of non-useness. As the connections are not killed after some time the limit
of connections in my server is exceeded. The connections appear as dormant
(their status).
Cintia,
Your connections are exceeded? Sounds like you are using a desktop sql
server, which is not intended for production use. (If this is correct, then
you should upgrade to Developer Edition or Standard Edition depending on
what you are doing.)
The MSDN makes this comment about 'dormant': After the connection has been
reset, the SPID indicates that it is in a "dormant" state in the
sysprocesses system table. If you do not use the connection for a period of
time a background process closes the connection. Such a SPID should not be
holding on to any locks or resources that would affect other users.
So, the process will close out eventually. It is just taking too long for
your purpose. You can - sometimes - kill these, but that is not a
recommended procedure since you could have one task killing a SPID at the
same moment that another task is trying to use it.
RLF
"Cintia Goncalves" <Cintia Goncalves@.discussions.microsoft.com> wrote in
message news:F230DFB6-C64D-4F9B-A074-A58C1F5047D6@.microsoft.com...
> I have a server in my office which is configured as a linked server on
> another one.
> How can I configure the connections between them to be killed after a
period
> of non-useness. As the connections are not killed after some time the
limit
> of connections in my server is exceeded. The connections appear as dormant
> (their status).