can anybody please tell me how to query a table from a linked server?
thanks
Sure, thats quite easy, use the four part notation:
SELECT <columnlist> FROM LinkedServerName.DatabaseName.Owner.Objectname
HTH, Jens Suessmeyer.
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
linked server problem with error message 7391
I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
linked server problem with error message 7391
I have two computers both running Windows Server 2003 and SQL Server 2000.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
Wednesday, March 28, 2012
Linked Server Problem
Folks i've two SQL SERVER, SERVER1 and SERVER2. I am updating a table at SERVER1 by joining a table with SERVER2. The query works fine, but update fails.
UPDATE a
set col1=b.col2
FROM mytable a JOIN SERVER2.mydb.dbo.mytable b ON a.id=b.id
WHERE a.date=getdate()
I get the following error:
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].
Plz advise!You need to start the "Distributed Transaction Coordinator" service on both servers.
Roby2222|||It's already running on both of the servers.|||Check whether any firewall rule is obstructing the DTC access.
To work around this behavior, install network DTC access on both servers:
Click Start, and then click Control Panel.
Click Add or Remove Programs, and then click Add/Remove Windows Components.
In the Components box, click Application Server, and then click Details.
Click to select the Enable network DTC access check box, and then click OK.
Click Next, and then follow the instructions that appear on the screen to complete the installation process.
Stop and then restart the Distributed Transaction Coordinator service.
Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).|||Thanx for the guidance, Satya.
I would let ya know after i restart the machines(on production).
Howdy!
UPDATE a
set col1=b.col2
FROM mytable a JOIN SERVER2.mydb.dbo.mytable b ON a.id=b.id
WHERE a.date=getdate()
I get the following error:
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].
Plz advise!You need to start the "Distributed Transaction Coordinator" service on both servers.
Roby2222|||It's already running on both of the servers.|||Check whether any firewall rule is obstructing the DTC access.
To work around this behavior, install network DTC access on both servers:
Click Start, and then click Control Panel.
Click Add or Remove Programs, and then click Add/Remove Windows Components.
In the Components box, click Application Server, and then click Details.
Click to select the Enable network DTC access check box, and then click OK.
Click Next, and then follow the instructions that appear on the screen to complete the installation process.
Stop and then restart the Distributed Transaction Coordinator service.
Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).|||Thanx for the guidance, Satya.
I would let ya know after i restart the machines(on production).
Howdy!
linked server problem
i linked to a remote server (sql 2000) using sp_addlinkedserver
when i tried to insert data in a table in the remote server it is throwing the following error.
i used transaction in the query.
The operation could not be performed because OLE DB
provider "SQLNCLI" for linked server "server1" was unable to begin a
distributed transaction. - Export Operation Failed
please find me way
thx in advance..
This is not a SQL Server Integration Services issue, so I have moved it to a more appropriate forum. Linked servers are in the are data access/engine not part of the SSIS toolset.
The error seems a bit sparse, I would have expected a bit more about why it cannot create a distributed transaction. Since the distributed transaction appears to be the issue, have you tested this outside of a linked server? There are plenty of KB articles on http://support.microsoft.com that discuss distributed transaction issues, try searching there. I have found the DTCPing, WINRM and DTCTester tools useful for diagnosing such issues, again search the KB or them.
sqlLinked server Problem
Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues
like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider[vbcol=seagreen]
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>sql
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues
like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider[vbcol=seagreen]
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>sql
linked server problem
linked to a remote server (sql 2000) using sp_addlinkedserver
when i tried to insert data in a table in the remote server it is throwing the following error.
i used transaction in the query.
The operation could not be performed because OLE DB
provider "SQLNCLI" for linked server "server1" was unable to begin a
distributed transaction. - Export Operation Failed
please find me way
thx in advance..
Let's try the data access forum.sql
Linked server Problem
Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.
Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.
|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I
|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in[vbcol=seagreen]
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider
>
>.
>
|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.
Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.
|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I
|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in[vbcol=seagreen]
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider
>
>.
>
|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
Monday, March 26, 2012
Linked server Problem
Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB'
Jef
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
>> Hi,
>> We linked 2 sql servers and executing stored procedures
>> between the two servers.
>> We are using the 4 part key to reference a table.
>> We can able to execute sql statements with four part
key
>> from sqlserver1 to sqlserver2 for certain databases and
>> cannot able to do on certain databases. All settings
and
>> users are same in all databases.
>> For eg: My query is
>> 'select * from sqlserver1.db1.dbo.table1'.
>> For some database, I getting following error
>> Server: Msg 7314, Level 16, State 1, Line 1
>> OLE DB provider 'SQLSERVER1' does not contain
>> table '"db1"."dbo"."table1"'. The table either does
not
>> exist or the current user does not have permissions on
>> that table.
>> OLE DB error trace [Non-interface error: OLE DB
provider
>> does not contain the table: ProviderName='SQLSERVER1',
>> TableName='"db1"."dbo"."table1"'].
>> Any help would very much appreciated.
>> Thanks in advance.
>> Rajah V.
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB'
Jef
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
>> Hi,
>> We linked 2 sql servers and executing stored procedures
>> between the two servers.
>> We are using the 4 part key to reference a table.
>> We can able to execute sql statements with four part
key
>> from sqlserver1 to sqlserver2 for certain databases and
>> cannot able to do on certain databases. All settings
and
>> users are same in all databases.
>> For eg: My query is
>> 'select * from sqlserver1.db1.dbo.table1'.
>> For some database, I getting following error
>> Server: Msg 7314, Level 16, State 1, Line 1
>> OLE DB provider 'SQLSERVER1' does not contain
>> table '"db1"."dbo"."table1"'. The table either does
not
>> exist or the current user does not have permissions on
>> that table.
>> OLE DB error trace [Non-interface error: OLE DB
provider
>> does not contain the table: ProviderName='SQLSERVER1',
>> TableName='"db1"."dbo"."table1"'].
>> Any help would very much appreciated.
>> Thanks in advance.
>> Rajah V.
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
Linked server performance
SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
Regards,
Jamie
Forgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission to
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to access
table3 on db2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
--
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
> us to have both the ability to login under a given security context as well
> as specify individual logins. The linked server also contains the provision
> for impersonation in 2005. If possible, I would like to allow permission to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on db2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on db2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do not
> want. Instead, can I make the access work such that if UserA tries to access
> table3 on db2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list under
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server OLE DB provider does not contain the table: Provide
Getting a linked server error. The way SQLServer 7.0 linked to 2000 allowed
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
us to have both the ability to login under a given security context as well
as specify individual logins. The linked server also contains the provision
for impersonation in 2005. If possible, I would like to allow permission t
o
be propagated from one server to the other. Example: SQL 2005 UserA has
login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
logged into the production server (SQL 2000 and executes a query joining
table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
(Server2005.db5.dbo.table2).
As it is understood from the reading, impersonation would use a login (sa
for example) which would always give this permission which is what I do not
want. Instead, can I make the access work such that if UserA tries to acces
s
table3 on DB2 which is not a table that usera has permission to that UserA
would be denied from SQL 2000 as well as from SQL 2005 server?
For some reason, I cannot get this part to work. UserA is in the list under
security for the linked server 2005 on the server 2000 but still cannot
access the information on Server 2005.
--
Regards,
JamieForgot to list the error:
SCHEMA LOCK permission denied on object
Regards,
Jamie
"thejamie" wrote:
> Getting a linked server error. The way SQLServer 7.0 linked to 2000 allow
ed
> us to have both the ability to login under a given security context as wel
l
> as specify individual logins. The linked server also contains the provisi
on
> for impersonation in 2005. If possible, I would like to allow permission
to
> be propagated from one server to the other. Example: SQL 2005 UserA has
> login for table1 on DB2 and table3 on db5 on the 2005 server. UserA is
> logged into the production server (SQL 2000 and executes a query joining
> table1 on DB2 (Server2005.db2.dbo.table1) and table2 from db5
> (Server2005.db5.dbo.table2).
> As it is understood from the reading, impersonation would use a login (sa
> for example) which would always give this permission which is what I do no
t
> want. Instead, can I make the access work such that if UserA tries to acc
ess
> table3 on DB2 which is not a table that usera has permission to that UserA
> would be denied from SQL 2000 as well as from SQL 2005 server?
> For some reason, I cannot get this part to work. UserA is in the list und
er
> security for the linked server 2005 on the server 2000 but still cannot
> access the information on Server 2005.
> --
> Regards,
> Jamie
Linked Server Not Displaying Table Details
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
Hi,
you need SQL Server 2k5 SP2 to browse through databases, tables etc from UI.
Code Snippet
EXEC sp_addlinkedserver
@.server = N'LONDON2',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.provstr = N'SERVER=192.168.100.150;Integrated Security=True'
EXEC sp_tables_ex @.table_server = 'LONDON2',
@.table_catalog = 'AdventureWorks',
@.table_schema = 'HumanResources',
@.table_type = 'TABLE'
Wednesday, March 21, 2012
Linked Server gives Internal SQL Server error
Hi All,
I am accessing a linked server (also a SQL Server) from a stored
procedure. There is an insert statement that I run on a table in the
linked server. This statement causes the Internal SQL Server error. But
if I run the insert statement separately from Query Analyzer, it works
fine! Also, I put the stored procedure directly into the database on
the linked SQL Server and it ran fine on it. Has anybody come across
something of this sort?
Thanks,
Sanjeevmahajan.sanjeev@.gmail.com napisa(a):
> Hi All,
> I am accessing a linked server (also a SQL Server) from a stored
> procedure. There is an insert statement that I run on a table in the
> linked server. This statement causes the Internal SQL Server error. But
> if I run the insert statement separately from Query Analyzer, it works
> fine! Also, I put the stored procedure directly into the database on
> the linked SQL Server and it ran fine on it. Has anybody come across
> something of this sort?
> Thanks,
> Sanjeev
When you start procedure from query analyzer you do it as some user
but when this procedure is started by sql agent as job it is another
user which doesn't have an access to the database linked by linked
server, and this is the problem.
You can try to run this procedure from vbs for example with ado
connection the same as in query analyzer
Monday, March 19, 2012
Linked server error in SQL 2005 RTM
I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ========================================
=============
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get th
e
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linke
d
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ========================================
=============
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.g
bl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:41073
5
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the tem
p
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1
,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" return
ed
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ========================================
=============
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get th
e
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linke
d
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ========================================
=============
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.g
bl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:41073
5
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the tem
p
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1
,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" return
ed
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>
Linked server error in SQL 2005 RTM
I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>
|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
|
|||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>
|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>
|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ================================================== ===
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
|
|||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get the
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linked
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ================================================== ===
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the temp
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.
BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>
|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
|
|||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>
|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>
|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> ================================================== ===
| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
|
|||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get the
same error.
This was working with MSDE.
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linked
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> ================================================== ===
> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the temp
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>
Linked server error in SQL 2005 RTM
I have a stored procedure which inserts data into a temp table by calling
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>> Hi,
>> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
>> with with 2005 instance. If not, could you download and run DTCPing, as
>> described in
>> in 306843 How To Troubleshoot MS DTC Firewall Issues
>> http://support.microsoft.com/?id=306843?
>> Also, use this in your script so that you may get more information about
>> the error:
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET XACT_ABORT ON
>> BEGIN TRAN
>> insert into #rs
>> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
>> @.param_string
>>
>> COMMIT TRAN
>>
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> --
>> | From: "Quinn" <dellsql@.newsgroups.nospam>
>> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
>> | Subject: Re: Linked server error in SQL 2005 RTM
>> | Date: Fri, 11 Nov 2005 02:14:29 -0600
>> | Lines: 28
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
>> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
>> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | BTW I am running on Windows 2003 SP1.
>> |
>> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
>> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>> | >I have a stored procedure which inserts data into a temp table by
>> calling
>> | >another stored procedure via a linked server. It works fine under SQL
>> 2000.
>> | >However I'm getting the below error after upgrading to SQL 2005. Below
>> is
>> | >also an example of the code. If I take out the insert into the temp
>> table
>> | >then the procedure executes without error.
>> | >
>> | > insert into #rs
>> | >
>> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
>> | > @.param_string
>> | >
>> | >
>> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
>> | > message "No transaction is active.".
>> | >
>> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
>> | >
>> | > The operation could not be performed because OLE DB provider
>> "SQLNCLI"
>> for
>> | > linked server "linkedserver" was unable to begin a distributed
>> | > transaction.
>> | >
>> | >
>> |
>> |
>> |
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> =====================================================| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get the
same error.
This was working with MSDE.
--
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linked
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> =====================================================> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the temp
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>
another stored procedure via a linked server. It works fine under SQL 2000.
However I'm getting the below error after upgrading to SQL 2005. Below is
also an example of the code. If I take out the insert into the temp table
then the procedure executes without error.
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message
"No transaction is active.".
Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "linkedserver" was unable to begin a distributed transaction.BTW I am running on Windows 2003 SP1.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which inserts data into a temp table by calling
>another stored procedure via a linked server. It works fine under SQL 2000.
>However I'm getting the below error after upgrading to SQL 2005. Below is
>also an example of the code. If I take out the insert into the temp table
>then the procedure executes without error.
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> message "No transaction is active.".
> Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "linkedserver" was unable to begin a distributed
> transaction.
>|||Hi,
It seems to be a DTC issue. Is SQL server 2000 that works on the same box
with with 2005 instance. If not, could you download and run DTCPing, as
described in
in 306843 How To Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/?id=306843?
Also, use this in your script so that you may get more information about
the error:
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN TRAN
insert into #rs
exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
@.param_string
COMMIT TRAN
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Fri, 11 Nov 2005 02:14:29 -0600
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| BTW I am running on Windows 2003 SP1.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >I have a stored procedure which inserts data into a temp table by
calling
| >another stored procedure via a linked server. It works fine under SQL
2000.
| >However I'm getting the below error after upgrading to SQL 2005. Below
is
| >also an example of the code. If I take out the insert into the temp
table
| >then the procedure executes without error.
| >
| > insert into #rs
| >
| > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| > @.param_string
| >
| >
| > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| > message "No transaction is active.".
| >
| > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
| >
| > The operation could not be performed because OLE DB provider "SQLNCLI"
for
| > linked server "linkedserver" was unable to begin a distributed
| > transaction.
| >
| >
|
|
||||Yes. It worked fine on SQL 2000. DTCPING was also successful.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
> with with 2005 instance. If not, could you download and run DTCPing, as
> described in
> in 306843 How To Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/?id=306843?
> Also, use this in your script so that you may get more information about
> the error:
> SET IMPLICIT_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN TRAN
> insert into #rs
> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> @.param_string
>
> COMMIT TRAN
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | Lines: 28
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | BTW I am running on Windows 2003 SP1.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >I have a stored procedure which inserts data into a temp table by
> calling
> | >another stored procedure via a linked server. It works fine under SQL
> 2000.
> | >However I'm getting the below error after upgrading to SQL 2005. Below
> is
> | >also an example of the code. If I take out the insert into the temp
> table
> | >then the procedure executes without error.
> | >
> | > insert into #rs
> | >
> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | > @.param_string
> | >
> | >
> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | > message "No transaction is active.".
> | >
> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
> | >
> | > The operation could not be performed because OLE DB provider "SQLNCLI"
> for
> | > linked server "linkedserver" was unable to begin a distributed
> | > transaction.
> | >
> | >
> |
> |
> |
>|||Looks like there is a problem with the server because pointing the linked
server to a different server works fine.
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Yes. It worked fine on SQL 2000. DTCPING was also successful.
> "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
>> Hi,
>> It seems to be a DTC issue. Is SQL server 2000 that works on the same box
>> with with 2005 instance. If not, could you download and run DTCPing, as
>> described in
>> in 306843 How To Troubleshoot MS DTC Firewall Issues
>> http://support.microsoft.com/?id=306843?
>> Also, use this in your script so that you may get more information about
>> the error:
>> SET IMPLICIT_TRANSACTIONS OFF
>> SET XACT_ABORT ON
>> BEGIN TRAN
>> insert into #rs
>> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
>> @.param_string
>>
>> COMMIT TRAN
>>
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> --
>> | From: "Quinn" <dellsql@.newsgroups.nospam>
>> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
>> | Subject: Re: Linked server error in SQL 2005 RTM
>> | Date: Fri, 11 Nov 2005 02:14:29 -0600
>> | Lines: 28
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
>> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
>> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | BTW I am running on Windows 2003 SP1.
>> |
>> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
>> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
>> | >I have a stored procedure which inserts data into a temp table by
>> calling
>> | >another stored procedure via a linked server. It works fine under SQL
>> 2000.
>> | >However I'm getting the below error after upgrading to SQL 2005. Below
>> is
>> | >also an example of the code. If I take out the insert into the temp
>> table
>> | >then the procedure executes without error.
>> | >
>> | > insert into #rs
>> | >
>> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
>> | > @.param_string
>> | >
>> | >
>> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
>> | > message "No transaction is active.".
>> | >
>> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line 420
>> | >
>> | > The operation could not be performed because OLE DB provider
>> "SQLNCLI"
>> for
>> | > linked server "linkedserver" was unable to begin a distributed
>> | > transaction.
>> | >
>> | >
>> |
>> |
>> |
>|||Hello,
Before we go further, I'd like to confirm the sitatution is that:
The linked server on SQL 2000 instance to this same server works fine.
The linked server on SQL 2005 instance to this server does not work
The linked server on SQL 2005 instance to another server does work.
DTCping works fine to that remote server from the problemtic server.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Quinn" <dellsql@.newsgroups.nospam>
| References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
<#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
<NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
<uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: Linked server error in SQL 2005 RTM
| Date: Sat, 12 Nov 2005 21:37:54 -0600
| Lines: 108
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Response
| Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Looks like there is a problem with the server because pointing the linked
| server to a different server works fine.
|
| "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
| > Yes. It worked fine on SQL 2000. DTCPING was also successful.
| >
| > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
| >> Hi,
| >>
| >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
box
| >> with with 2005 instance. If not, could you download and run DTCPing,
as
| >> described in
| >>
| >> in 306843 How To Troubleshoot MS DTC Firewall Issues
| >> http://support.microsoft.com/?id=306843?
| >>
| >> Also, use this in your script so that you may get more information
about
| >> the error:
| >>
| >> SET IMPLICIT_TRANSACTIONS OFF
| >> SET XACT_ABORT ON
| >> BEGIN TRAN
| >>
| >> insert into #rs
| >>
| >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> @.param_string
| >>
| >>
| >> COMMIT TRAN
| >>
| >>
| >> Best Regards,
| >>
| >> Peter Yang
| >> MCSE2000/2003, MCSA, MCDBA
| >> Microsoft Online Partner Support
| >>
| >> When responding to posts, please "Reply to Group" via your newsreader
so
| >> that others may learn and benefit from your issue.
| >>
| >> =====================================================| >>
| >>
| >>
| >> This posting is provided "AS IS" with no warranties, and confers no
| >> rights.
| >>
| >> --
| >> | From: "Quinn" <dellsql@.newsgroups.nospam>
| >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
| >> | Subject: Re: Linked server error in SQL 2005 RTM
| >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
| >> | Lines: 28
| >> | X-Priority: 3
| >> | X-MSMail-Priority: Normal
| >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| >> | X-RFC2646: Format=Flowed; Response
| >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
| >> | Newsgroups: microsoft.public.sqlserver.server
| >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
70.112.155.207
| >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
| >> | X-Tomcat-NG: microsoft.public.sqlserver.server
| >> |
| >> | BTW I am running on Windows 2003 SP1.
| >> |
| >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
| >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
| >> | >I have a stored procedure which inserts data into a temp table by
| >> calling
| >> | >another stored procedure via a linked server. It works fine under
SQL
| >> 2000.
| >> | >However I'm getting the below error after upgrading to SQL 2005.
Below
| >> is
| >> | >also an example of the code. If I take out the insert into the temp
| >> table
| >> | >then the procedure executes without error.
| >> | >
| >> | > insert into #rs
| >> | >
| >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
| >> | > @.param_string
| >> | >
| >> | >
| >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
| >> | > message "No transaction is active.".
| >> | >
| >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
420
| >> | >
| >> | > The operation could not be performed because OLE DB provider
| >> "SQLNCLI"
| >> for
| >> | > linked server "linkedserver" was unable to begin a distributed
| >> | > transaction.
| >> | >
| >> | >
| >> |
| >> |
| >> |
| >>
| >
| >
|
|
||||I also have this problem.
I have SQL Server 2005 Express on a Windows XP SP 2 machine and on a Windows
2003 Server machine. I have configured DTC for both machines per the
documentation that is out there. I have tried many different combinations of
settings and so forth, with no success.
I can successfully execute queries from the Windows XP SP 2 machine once the
servers are linked, but when I try to do a distributed transaction, I get the
same error.
This was working with MSDE.
--
-Corey
"Peter Yang [MSFT]" wrote:
> Hello,
> Before we go further, I'd like to confirm the sitatution is that:
> The linked server on SQL 2000 instance to this same server works fine.
> The linked server on SQL 2005 instance to this server does not work
> The linked server on SQL 2005 instance to another server does work.
> DTCping works fine to that remote server from the problemtic server.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> <NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl>
> <uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl>
> | Subject: Re: Linked server error in SQL 2005 RTM
> | Date: Sat, 12 Nov 2005 21:37:54 -0600
> | Lines: 108
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <ePYujOA6FHA.3296@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com 70.112.155.207
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410905
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Looks like there is a problem with the server because pointing the linked
> | server to a different server works fine.
> |
> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | news:uSwdsSy5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> | > Yes. It worked fine on SQL 2000. DTCPING was also successful.
> | >
> | > "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | > news:NWCIknp5FHA.3356@.TK2MSFTNGXA02.phx.gbl...
> | >> Hi,
> | >>
> | >> It seems to be a DTC issue. Is SQL server 2000 that works on the same
> box
> | >> with with 2005 instance. If not, could you download and run DTCPing,
> as
> | >> described in
> | >>
> | >> in 306843 How To Troubleshoot MS DTC Firewall Issues
> | >> http://support.microsoft.com/?id=306843?
> | >>
> | >> Also, use this in your script so that you may get more information
> about
> | >> the error:
> | >>
> | >> SET IMPLICIT_TRANSACTIONS OFF
> | >> SET XACT_ABORT ON
> | >> BEGIN TRAN
> | >>
> | >> insert into #rs
> | >>
> | >> exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> @.param_string
> | >>
> | >>
> | >> COMMIT TRAN
> | >>
> | >>
> | >> Best Regards,
> | >>
> | >> Peter Yang
> | >> MCSE2000/2003, MCSA, MCDBA
> | >> Microsoft Online Partner Support
> | >>
> | >> When responding to posts, please "Reply to Group" via your newsreader
> so
> | >> that others may learn and benefit from your issue.
> | >>
> | >> =====================================================> | >>
> | >>
> | >>
> | >> This posting is provided "AS IS" with no warranties, and confers no
> | >> rights.
> | >>
> | >> --
> | >> | From: "Quinn" <dellsql@.newsgroups.nospam>
> | >> | References: <#Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl>
> | >> | Subject: Re: Linked server error in SQL 2005 RTM
> | >> | Date: Fri, 11 Nov 2005 02:14:29 -0600
> | >> | Lines: 28
> | >> | X-Priority: 3
> | >> | X-MSMail-Priority: Normal
> | >> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | >> | X-RFC2646: Format=Flowed; Response
> | >> | Message-ID: <#Jz#ufp5FHA.2484@.TK2MSFTNGP09.phx.gbl>
> | >> | Newsgroups: microsoft.public.sqlserver.server
> | >> | NNTP-Posting-Host: cpe-70-112-155-207.austin.res.rr.com
> 70.112.155.207
> | >> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | >> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:410735
> | >> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | >> |
> | >> | BTW I am running on Windows 2003 SP1.
> | >> |
> | >> | "Quinn" <dellsql@.newsgroups.nospam> wrote in message
> | >> | news:%23Fa5JCo5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> | >> | >I have a stored procedure which inserts data into a temp table by
> | >> calling
> | >> | >another stored procedure via a linked server. It works fine under
> SQL
> | >> 2000.
> | >> | >However I'm getting the below error after upgrading to SQL 2005.
> Below
> | >> is
> | >> | >also an example of the code. If I take out the insert into the temp
> | >> table
> | >> | >then the procedure executes without error.
> | >> | >
> | >> | > insert into #rs
> | >> | >
> | >> | > exec linkedserver.dbname.dbo.proc_procedure_name @.c1, @.c2, @.c3, 1,
> | >> | > @.param_string
> | >> | >
> | >> | >
> | >> | > OLE DB provider "SQLNCLI" for linked server "linkedserver" returned
> | >> | > message "No transaction is active.".
> | >> | >
> | >> | > Msg 7391, Level 16, State 2, Procedure proc_procedure_name, Line
> 420
> | >> | >
> | >> | > The operation could not be performed because OLE DB provider
> | >> "SQLNCLI"
> | >> for
> | >> | > linked server "linkedserver" was unable to begin a distributed
> | >> | > transaction.
> | >> | >
> | >> | >
> | >> |
> | >> |
> | >> |
> | >>
> | >
> | >
> |
> |
> |
>
Subscribe to:
Posts (Atom)