Linkedservername variable

We are calling stored procedures over a linkedserver from a client and we
want to use these client stored procedures in a test and production system
with different server names.
Also we don' t want to have to kinds of sps (for the test and production
system). Our Clients have a MSDE 7.0 and the Server is a MSSQL 2000.
For us it is not possible to use dynamik sql and substitute the linked
servername.
Does any body have an solution to this problem? We think that it may
possible to configure a linkedserver to access two different servernames?If you need to access different servers for the linked
server in the environments, why not just changed the linked
server in each environment? In test, linked server named
YourLinkedServer references the test box. In production,
change the linked server YourLinkedServer to reference the
production environment. You use the same code, same linked
server name but the linked server in the different
environments points to different servers.
-Sue
On Thu, 2 Jun 2005 06:06:04 -0700, "Pille"
<Pille@.discussions.microsoft.com> wrote:

>We are calling stored procedures over a linkedserver from a client and we
>want to use these client stored procedures in a test and production system
>with different server names.
>Also we don' t want to have to kinds of sps (for the test and production
>system). Our Clients have a MSDE 7.0 and the Server is a MSSQL 2000.
>For us it is not possible to use dynamik sql and substitute the linked
>servername.
>Does any body have an solution to this problem? We think that it may
>possible to configure a linkedserver to access two different servernames?
>|||Hi Sue. Is it so easy? We call our SPs from the client like "exec
MyLinkedServer.MyDB.MyUser.MySP". I think you said that is possible to acces
s
to different servers without changing the linked servername "MyLinkedServer"
.
How can I do this? I thought that there is a 1:1 relationship on the linked
servername and the real servername.
We add the linked server on the client like this:
exec master.dbo.sp_addlinkedserver 'MyLinkedServer', N'SQL Server'
exec master.dbo.sp_addlinkedsrvlogin 'MyLinkedServer'
How can I configure the realservername of the linked server
Thx... Pille...
"Sue Hoegemeier" wrote:

> If you need to access different servers for the linked
> server in the environments, why not just changed the linked
> server in each environment? In test, linked server named
> YourLinkedServer references the test box. In production,
> change the linked server YourLinkedServer to reference the
> production environment. You use the same code, same linked
> server name but the linked server in the different
> environments points to different servers.
> -Sue
> On Thu, 2 Jun 2005 06:06:04 -0700, "Pille"
> <Pille@.discussions.microsoft.com> wrote:
>
>|||Use sp_setnetnamesql

Linkedservername variable

We are calling stored procedures over a linkedserver from a client and we
want to use these client stored procedures in a test and production system
with different server names.
Also we don' t want to have to kinds of sps (for the test and production
system). Our Clients have a MSDE 7.0 and the Server is a MSSQL 2000.
For us it is not possible to use dynamik sql and substitute the linked
servername.
Does any body have an solution to this problem? We think that it may
possible to configure a linkedserver to access two different servernames?
If you need to access different servers for the linked
server in the environments, why not just changed the linked
server in each environment? In test, linked server named
YourLinkedServer references the test box. In production,
change the linked server YourLinkedServer to reference the
production environment. You use the same code, same linked
server name but the linked server in the different
environments points to different servers.
-Sue
On Thu, 2 Jun 2005 06:06:04 -0700, "Pille"
<Pille@.discussions.microsoft.com> wrote:

>We are calling stored procedures over a linkedserver from a client and we
>want to use these client stored procedures in a test and production system
>with different server names.
>Also we don' t want to have to kinds of sps (for the test and production
>system). Our Clients have a MSDE 7.0 and the Server is a MSSQL 2000.
>For us it is not possible to use dynamik sql and substitute the linked
>servername.
>Does any body have an solution to this problem? We think that it may
>possible to configure a linkedserver to access two different servernames?
>
|||Hi Sue. Is it so easy? We call our SPs from the client like "exec
MyLinkedServer.MyDB.MyUser.MySP". I think you said that is possible to access
to different servers without changing the linked servername "MyLinkedServer".
How can I do this? I thought that there is a 1:1 relationship on the linked
servername and the real servername.
We add the linked server on the client like this:
exec master.dbo.sp_addlinkedserver 'MyLinkedServer', N'SQL Server'
exec master.dbo.sp_addlinkedsrvlogin 'MyLinkedServer'
How can I configure the realservername of the linked server
Thx... Pille...
"Sue Hoegemeier" wrote:

> If you need to access different servers for the linked
> server in the environments, why not just changed the linked
> server in each environment? In test, linked server named
> YourLinkedServer references the test box. In production,
> change the linked server YourLinkedServer to reference the
> production environment. You use the same code, same linked
> server name but the linked server in the different
> environments points to different servers.
> -Sue
> On Thu, 2 Jun 2005 06:06:04 -0700, "Pille"
> <Pille@.discussions.microsoft.com> wrote:
>
>
|||Use sp_setnetname

Linked Servres and deadlocks

We are running a SQL 2000 production server with MSDE 2000 clients for local
storage. The 60+ clients are set up as linked servers back to the productio
n server. The application calls stored procedures on the client that referen
ce back to the linked serve
r. During these procedure calls we experience a high number of deadlocks, a
lot more than similar procedures running directly on the server. I'm looking
for documentation/ help on such scenarios.
Thanks in Advance
Paul G.Hi Paul,
Sounds like you have a hot spot in database and your will first need to iden
tify, which object(s) are causing problem. You may want to run sp_lock on s
erver to locate "Wait" in status column and note objectid. Overall, you will
need to understand your ap
plication and how it works. Things to consider:
1. Indexing
2. Network speed between clients and server
3. Long running queries - may need to modify Connections Timeout and Query T
imeout values
If you are really stuck, please email me and we can work together to pin-poi
nt issue
Cheers,
Phillip MacPherson
SQL Server Consultant DBA (MCP)

servers and distributed transactions question

In our production environment, we have three different SQL servers
that make up our application. One database sitting on each server.
The three servers are represented below with the corresponding
databases.
ServerPRD01: DB1
ServerPRD02: DB2
ServerPRD03: DB3
We created two network aliases through the SQL Server Client Network
Utility that we use to create linked server entries so that the
databases may talk to each other.
Alias-1: Alias to ServerPRD02
Aleas-2: Alias to ServerPRD03
Thus, DB1 can communicate to DB2 through a 3 part naming convention
after setting up linked server entries on ServerPRD01. A sample SQL
statement follows:
SELECT * FROM [Alias-1].DB2.dbo.ATable
Now, we don't have the resources to have three different SQL boxes in
our testing and development environments. Instead, we set up ONE SQL
server for each test and development environment. We then created the
network alias's that loop back to the same server.
ServerTST01: DB1, DB2, DB3
Alias-1: Alias to ServerTST01
Alias-2: Alias to ServerTST01
Thus, when we look at the SQL statement above, we are still using the
same alias, but we are going through the alias to communicate with a
database on the same server. We want to stick to using alias's so we
can maintain code integrity throughout our systems.
The problem is, the DTC doesn't seem to like running a distributed
transaction when the alias points back to the same server where the
transaction is started.
I'm not an expert in SQL server and I want to understand if this is
just the way it is, or is there a work-around. The error we get when
we try to run a transaction follows:
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. ]
I've seen posts where people were having trouble with DTC, but those
instances always consisted of linked servers on different physical
boxes.
Thanks for your help in advance. Please reply to this newsgroup, I
don't check the email attached to this posting.The behavior you are seeing is normal and expected.
From BOL (topic: Loopback Linked Servers)
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kevin B Ebert" <kevin_ebert@.hotmail.com> wrote in message
news:2c5e843.0402250800.51069a68@.posting.google.com...
> In our production environment, we have three different SQL servers
> that make up our application. One database sitting on each server.
> The three servers are represented below with the corresponding
> databases.
> ServerPRD01: DB1
> ServerPRD02: DB2
> ServerPRD03: DB3
> We created two network aliases through the SQL Server Client Network
> Utility that we use to create linked server entries so that the
> databases may talk to each other.
> Alias-1: Alias to ServerPRD02
> Aleas-2: Alias to ServerPRD03
> Thus, DB1 can communicate to DB2 through a 3 part naming convention
> after setting up linked server entries on ServerPRD01. A sample SQL
> statement follows:
> SELECT * FROM [Alias-1].DB2.dbo.ATable
> Now, we don't have the resources to have three different SQL boxes in
> our testing and development environments. Instead, we set up ONE SQL
> server for each test and development environment. We then created the
> network alias's that loop back to the same server.
> ServerTST01: DB1, DB2, DB3
> Alias-1: Alias to ServerTST01
> Alias-2: Alias to ServerTST01
> Thus, when we look at the SQL statement above, we are still using the
> same alias, but we are going through the alias to communicate with a
> database on the same server. We want to stick to using alias's so we
> can maintain code integrity throughout our systems.
> The problem is, the DTC doesn't seem to like running a distributed
> transaction when the alias points back to the same server where the
> transaction is started.
> I'm not an expert in SQL server and I want to understand if this is
> just the way it is, or is there a work-around. The error we get when
> we try to run a transaction follows:
> 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. ]
> I've seen posts where people were having trouble with DTC, but those
> instances always consisted of linked servers on different physical
> boxes.
> Thanks for your help in advance. Please reply to this newsgroup, I
> don't check the email attached to this posting.|||Geoff,
Thanks for the response. We are now looking to VMWare as a solution.
Kev
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OzBq457#DHA.3536@.tk2msftngp13.phx.gbl>...
> The behavior you are seeing is normal and expected.
> From BOL (topic: Loopback Linked Servers)
> Loopback linked servers cannot be used in a distributed transaction.
> Attempting a distributed query against a loopback linked server from within
> a distributed transaction causes an error:
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Kevin B Ebert" <kevin_ebert@.hotmail.com> wrote in message
> news:2c5e843.0402250800.51069a68@.posting.google.com...
> > In our production environment, we have three different SQL servers
> > that make up our application. One database sitting on each server.
> >
> > The three servers are represented below with the corresponding
> > databases.
> > ServerPRD01: DB1
> > ServerPRD02: DB2
> > ServerPRD03: DB3
> >
> > We created two network aliases through the SQL Server Client Network
> > Utility that we use to create linked server entries so that the
> > databases may talk to each other.
> >
> > Alias-1: Alias to ServerPRD02
> > Aleas-2: Alias to ServerPRD03
> >
> > Thus, DB1 can communicate to DB2 through a 3 part naming convention
> > after setting up linked server entries on ServerPRD01. A sample SQL
> > statement follows:
> >
> > SELECT * FROM [Alias-1].DB2.dbo.ATable
> >
> > Now, we don't have the resources to have three different SQL boxes in
> > our testing and development environments. Instead, we set up ONE SQL
> > server for each test and development environment. We then created the
> > network alias's that loop back to the same server.
> >
> > ServerTST01: DB1, DB2, DB3
> >
> > Alias-1: Alias to ServerTST01
> > Alias-2: Alias to ServerTST01
> >
> > Thus, when we look at the SQL statement above, we are still using the
> > same alias, but we are going through the alias to communicate with a
> > database on the same server. We want to stick to using alias's so we
> > can maintain code integrity throughout our systems.
> >
> > The problem is, the DTC doesn't seem to like running a distributed
> > transaction when the alias points back to the same server where the
> > transaction is started.
> >
> > I'm not an expert in SQL server and I want to understand if this is
> > just the way it is, or is there a work-around. The error we get when
> > we try to run a transaction follows:
> >
> > 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. ]
> >
> > I've seen posts where people were having trouble with DTC, but those
> > instances always consisted of linked servers on different physical
> > boxes.
> >
> > Thanks for your help in advance. Please reply to this newsgroup, I
> > don't check the email attached to this posting.

servers and distributed transactions question

In our production environment, we have three different SQL servers
that make up our application. One database sitting on each server.
The three servers are represented below with the corresponding
databases.
ServerPRD01: DB1
ServerPRD02: DB2
ServerPRD03: DB3
We created two network aliases through the SQL Server Client Network
Utility that we use to create linked server entries so that the
databases may talk to each other.
Alias-1: Alias to ServerPRD02
Aleas-2: Alias to ServerPRD03
Thus, DB1 can communicate to DB2 through a 3 part naming convention
after setting up linked server entries on ServerPRD01. A sample SQL
statement follows:
SELECT * FROM [Alias-1].DB2.dbo.ATable
Now, we don't have the resources to have three different SQL boxes in
our testing and development environments. Instead, we set up ONE SQL
server for each test and development environment. We then created the
network alias's that loop back to the same server.
ServerTST01: DB1, DB2, DB3
Alias-1: Alias to ServerTST01
Alias-2: Alias to ServerTST01
Thus, when we look at the SQL statement above, we are still using the
same alias, but we are going through the alias to communicate with a
database on the same server. We want to stick to using alias's so we
can maintain code integrity throughout our systems.
The problem is, the DTC doesn't seem to like running a distributed
transaction when the alias points back to the same server where the
transaction is started.
I'm not an expert in SQL server and I want to understand if this is
just the way it is, or is there a work-around. The error we get when
we try to run a transaction follows:
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. ]
I've seen posts where people were having trouble with DTC, but those
instances always consisted of linked servers on different physical
boxes.
Thanks for your help in advance. Please reply to this newsgroup, I
don't check the email attached to this posting.The behavior you are seeing is normal and expected.
From BOL (topic: Loopback Linked Servers)
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kevin B Ebert" <kevin_ebert@.hotmail.com> wrote in message
news:2c5e843.0402250800.51069a68@.posting.google.com...
> In our production environment, we have three different SQL servers
> that make up our application. One database sitting on each server.
> The three servers are represented below with the corresponding
> databases.
> ServerPRD01: DB1
> ServerPRD02: DB2
> ServerPRD03: DB3
> We created two network aliases through the SQL Server Client Network
> Utility that we use to create linked server entries so that the
> databases may talk to each other.
> Alias-1: Alias to ServerPRD02
> Aleas-2: Alias to ServerPRD03
> Thus, DB1 can communicate to DB2 through a 3 part naming convention
> after setting up linked server entries on ServerPRD01. A sample SQL
> statement follows:
> SELECT * FROM [Alias-1].DB2.dbo.ATable
> Now, we don't have the resources to have three different SQL boxes in
> our testing and development environments. Instead, we set up ONE SQL
> server for each test and development environment. We then created the
> network alias's that loop back to the same server.
> ServerTST01: DB1, DB2, DB3
> Alias-1: Alias to ServerTST01
> Alias-2: Alias to ServerTST01
> Thus, when we look at the SQL statement above, we are still using the
> same alias, but we are going through the alias to communicate with a
> database on the same server. We want to stick to using alias's so we
> can maintain code integrity throughout our systems.
> The problem is, the DTC doesn't seem to like running a distributed
> transaction when the alias points back to the same server where the
> transaction is started.
> I'm not an expert in SQL server and I want to understand if this is
> just the way it is, or is there a work-around. The error we get when
> we try to run a transaction follows:
> 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. ]
> I've seen posts where people were having trouble with DTC, but those
> instances always consisted of linked servers on different physical
> boxes.
> Thanks for your help in advance. Please reply to this newsgroup, I
> don't check the email attached to this posting.|||Geoff,
Thanks for the response. We are now looking to VMWare as a solution.
Kev
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OzBq457#DHA.3536@.tk2msftn
gp13.phx.gbl>...
> The behavior you are seeing is normal and expected.
> From BOL (topic: Loopback Linked Servers)
> Loopback linked servers cannot be used in a distributed transaction.
> Attempting a distributed query against a loopback linked server from withi
n
> a distributed transaction causes an error:
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Kevin B Ebert" <kevin_ebert@.hotmail.com> wrote in message
> news:2c5e843.0402250800.51069a68@.posting.google.com...

server?

I have multiple servers. From one production server (A), I can establish Li
nked Servers to other servers. However, I am not able to establish a Linked
Server to one specific server (B). The servers are on different domains. H
owever, I am able to link f
rom A to other servers on the same Domain as B.
-- From the production server A I can open Query Analyzer directly to server
B -- so I do believe that the Client Network Utility is correct
-- I have recreated the Linked Server/security panel for the login I"m using
, and believe the login/password is set up correctly.
-- Both server A and server B are clustered, running Win2003 and SQL2000 wit
h patches.
Here is the error message I receive when I try to do a select * from B.maste
r.dbo.sysobjects (or any other db/talble) when logged in to A in Query Analy
zer:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'MediaCatalogDB.1
'.
OLE DB error trace [OLE/DB Provider 'MediaCatalogDB.1' IDBInitialize::In
itialize returned 0x80030003].
BOL indicates this is due to two things: the server is set up with wrong dat
a in linked server or the login is incorrect.
Any other suggestions? Thanks much.
LindaAfter posting the message, I recreated the linked server in question again.
This time all is well.
So sorry for the post!|||Hi Linda:
How did you fix this? I am having the same problem. I am trying to link to
sql server in a different domain. I am getting the error message that sql
server does not exist. Thanks