servers without MSDTC?

Simply put is it possible?

Will all transactions that involve a linked server connection by promoted to a Distributed transaction or can you set something somewhere that will keep the transaction 'normal'

Thanks in advance.

DaveTo ensure consistency across the server MSDTC will be needed and automatically involved.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

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...

servers

I have two servers and they are linked servers. When I query a table on ON Box2 from Box1
The distributed query works fine but when I tried the oppisite it says 'SQL Server does not exist or access denied.'
What could be the reason.
Thanks.You've registered Box1 as a linked server on Box2?
And registered Box2 as a linked server on Box1?
You've specified the correct username/password (or security context)?
You're using the correct four-part naming convention (or correct OPENQUERY syntax)?
Are both servers SQL Server (service pack)?
Are they both configured for network connectivity (ie, listening on TCP or named pipes)?
Are you using the OLE DB for SQL Server connection or have you configured them as linked native SQL Servers (I prefer the former)?

If you've done all these, then I'm at a bit of a loss. Linked servers aren't too hard, but they do require some attention to basic details.

Regards,

hmscott|||I can say 'yes' for all the questions and the distributed queries were working fine for last so many months and got the pbm for last one day.

Thanks.

server/OLE DB Performance problem with parameters?

Hey,
I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
Cheers,
James
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.http://www.sql-server-performance.com/linked_server.asp for reference on performance of distributed queries.
HTH
"SqlJunkies User" wrote:
> Hey,
> I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

server/OLE DB Performance problem with parameters?

Hey,
I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
Cheers,
James
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
http://www.sql-server-performance.com/linked_server.asp for reference on performance of distributed queries.
HTH
"SqlJunkies User" wrote:

> Hey,
> I have been getting some odd results when performance testing my distributed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as dynamic sql they always go fast. Is anyone aware of performance problems with parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>

server/OLE DB Performance problem with parameters?

Hey,
I have been getting some odd results when performance testing my distributed
queries. Sometimes they go fast, sometimes slow.
When I translate the variables into constants, or execute the queries as dyn
amic sql they always go fast. Is anyone aware of performance problems with p
arameters in the SQL Server OLE DB Provider?
Cheers,
James
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.http://www.sql-server-performance.com/linked_server.asp for reference on per
formance of distributed queries.
HTH
"SqlJunkies User" wrote:

> Hey,
> I have been getting some odd results when performance testing my distribut
ed queries. Sometimes they go fast, sometimes slow.
> When I translate the variables into constants, or execute the queries as d
ynamic sql they always go fast. Is anyone aware of performance problems with
parameters in the SQL Server OLE DB Provider?
> Cheers,
> James
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>

server to Oracle

Hello Group:

I use a Linked Server to connect to a Oracle 8i base. Now I want to implement a distributed transaction using this Linked Server: Two-Phase Commit, but it doesn't work. I can send a "begin tran", followed by an "update" at the SQL 2000, but when I send a query to Linked Server, a simple "select", I get this error:
Server: Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider 'MSDAORA'. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

I try the Oracle OLE DB Provider without sucess.

Can anyone help me ?

Thank you,

Aldair.Try sending simple queries to the oracle server & see what you get. Then gradually work up to nested transactions - keep it simple through each step of troubleshooting.

Also, do some research on the version of driver you are using - some are known to have bugs similar to what you describe.

Post back if problems,,

Cheers,

SG|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Hi

When you send a command to oracle, check the type of default locking that oracle uses - in SQL its read uncommitted for a transaction but oracle may be different. You can specify locking hints in your transactions BUT oracle may NOT recognise them, so you may need to customise the distributed transactions using oracle commands accordingly.

This type of problem requires slow, careful & methodical testing - dont rush it.

Post back if problems

Cheers

SG|||Hi.

Thank you for yours helps. I will try with care.

SYL.

Aldair.