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.

sql

Linked server problem

Hi,

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

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

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

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

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

change begin tran to BEGIN DISTRIBUTED TRANSACTION and see

Madhu

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

what is the service pack on these box

Madhu

|||

Hi Shah,

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

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

linked server problem

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,

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

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

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

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

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

change begin tran to BEGIN DISTRIBUTED TRANSACTION and see

Madhu

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

what is the service pack on these box

Madhu

|||

Hi Shah,

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

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

Linked server insert problem

Hi,

I've just upgraded our main server to Windows 2003 Server / SQL 2000
from NT4/SQL2000.

We have a secondary server, running NT4/SQL7, which does a remote update to the primary server using this kind of query:

INSERT [MainServer].Database.dbo.UpdateTable (Product_ID, Branch_ID, Qty) VALUES (@.a, @.b, @.c)

This query works fine when you run it from Query Analyer, but as soon as i place this query in a begin/commit transaction block, as soon as i run it, the query hangs, and the only way i can get it to stop is by resetting SQL server.

I have tried doing this update from/to a different combination of OS's and SQL server versions, but it only seems to hang when the query is done to a W2003S/SQL2000 machine.

Does anyone have any suggestions? (Besides rolling back to W2K server)

Cheers,

JoshHowdy

I have found issues where if you run from QA all runs fine, but from a job etc it dies.

Are you using linked servers & if so how is the link configured?

How do you communicate to the remote server?

Is the Distributed Transaction Service running on the remote server?

Cheers,

SG|||I've found the problem.

The only way i could find it was installing another W2003S/SQL2000 machine, and running the query from there to the primary server.

It actually gave me an error message, which after investigating, found that DTC needs to be enabled for network access under W2003S. Why this doesn't have this enabled by default i don't know, but it's workin now! :)

Cheers,

Josh

Linked server insert problem

I have two sql servers, I have defined each one as a linked server to
the other. I can mostly access the servers from one another, but I get
the following error on a sql insert.

Insert statement...

INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files
(database_name, tl_file_name, tl_applied, lsplanid, lssecid,
compression_type) VALUES ('javaweb', 'c:', 'N', 1, 1, 0)

i get an error message

Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 10. Database may not be activated yet or may
be in transition.

I can query the table with select using the following
select * from [u1stsv02].[custom log shipping].dbo.ls_secondary_files

and I can delete rows from the table using
delete from [u1stsv02].[custom log shipping].dbo.ls_secondary_files

I have searched Microsoft's site and googled for a while and cannot seem
to find a solution.

Both servers are running SQL Server 2000 with service pack 4

Thanks in advance for any replies.

Steve Kuekes
Physicians Pharmacy Alliance
just remove the "1", "2", "3" from my email to reach me."Steve Kuekes" <skuekes123@.mail.com> wrote in message
news:7xWre.10099$Kw.364234@.twister.southeast.rr.co m...
>I have two sql servers, I have defined each one as a linked server to the
>other. I can mostly access the servers from one another, but I get the
>following error on a sql insert.
> Insert statement...
> INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files
> (database_name, tl_file_name, tl_applied, lsplanid, lssecid,
> compression_type) VALUES ('javaweb', 'c:', 'N', 1, 1, 0)
> i get an error message
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 10. Database may not be activated yet or may be
> in transition.
> I can query the table with select using the following
> select * from [u1stsv02].[custom log shipping].dbo.ls_secondary_files
> and I can delete rows from the table using
> delete from [u1stsv02].[custom log shipping].dbo.ls_secondary_files
> I have searched Microsoft's site and googled for a while and cannot seem
> to find a solution.
> Both servers are running SQL Server 2000 with service pack 4
> Thanks in advance for any replies.
> Steve Kuekes
> Physicians Pharmacy Alliance
> just remove the "1", "2", "3" from my email to reach me.

No idea - if the SELECT and DELETE work, then I can't see any obvious reason
why the INSERT would fail, unless perhaps you have a trigger on the table?
Or, since your queries are written in different cases, if one server is
case-sensitive then you might have problems.

You might want to check the MSSQL error log for anything unusual, and you
could also try using Profiler to trace what's happening on the server. If
that doesn't help, then it would probably be best to contact Microsoft,
especially if you can reproduce the problem consistently on another server.

Simon|||Steve Kuekes (skuekes123@.mail.com) writes:
> I have two sql servers, I have defined each one as a linked server to
> the other. I can mostly access the servers from one another, but I get
> the following error on a sql insert.
> Insert statement...
> INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files
> (database_name, tl_file_name, tl_applied, lsplanid, lssecid,
> compression_type) VALUES ('javaweb', 'c:', 'N', 1, 1, 0)
> i get an error message
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 10. Database may not be activated yet or may
> be in transition.

Hm, what happens if you rename database to CustomLogShipping? I seem
to recall that there have been issues with names that needs quoting
because of special characters.

If you do a SELECT * FROM master..sysdatabases on the target server,
do you see any database with db_id = 10? Which id does [Custom Log
Shipping] have?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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

Linked server behavior not funny at all

Hi All
My procedure uses remote database and remote server name as parameters. Using these parameter the proc builds a dynamic sql which should insert records into a table on local server. This should be so simple but it isn't.
The process freezes when I am running it from query analyzer.
When I kill it, it remains in the killed/ rollback state indefinitely. You cannot stop sql server service now as spid 3 blocks on this process. So the only recourse is to kill sqlserver process from taskmanager or restart the server. horrid!!!
I can run the dynamic sql (being executed in the proc) in query analyzer with the login used for linking the servers.
What is happening here?
Thanks for reading and your help
Arun
PS
From my experience over the last 2-3 days stress testing linked servers, it appears that MS has spent minimal time testing linked server functionality. It works when I do select * from srv2.db2.dbo.tbl but anything more, it falls apart.
is it also your experience?
Hi,
Could you please paste the code of the stored proc? As I understand it, you
are trying to pass a remote server and remote db name to a stored proc, and
that will take the data located on that remote data source and insert it
into a local table on the local server?
How long have you waited for the unresponsive behaviour?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Hi Vikram
Thanks for your response. Since posting, I have changed the process to avoid the linked server by putting data locally (delivery pressure). So I don't have the code snippets ready at hand. I will create new ones and post them.
But this is basically what I was trying.
The insert takes less than no time when done locally. When I do it across linked servers, it wasn't done in 7 minutes and I killed it. There were no records in the destination table.
-- dynamic sql in proc
select * from srv2.db.dbo.tbl a where fld3 = (select max(fld3) from srv2.db.dbo.tbl where fld1 = a.fld1 and fld2 = a.fld2)
proc is fired from local server
insert tbl2 exec proc @.srv, @.db
Arun
"Vikram Jayaram [MS]" wrote:

> Hi,
> Could you please paste the code of the stored proc? As I understand it, you
> are trying to pass a remote server and remote db name to a stored proc, and
> that will take the data located on that remote data source and insert it
> into a local table on the local server?
> How long have you waited for the unresponsive behaviour?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>

Linked server behavior not funny at all

Hi All
My procedure uses remote database and remote server name as parameters. Usin
g these parameter the proc builds a dynamic sql which should insert records
into a table on local server. This should be so simple but it isn't.
The process freezes when I am running it from query analyzer.
When I kill it, it remains in the killed/ rollback state indefinitely. You c
annot stop sql server service now as spid 3 blocks on this process. So the o
nly recourse is to kill sqlserver process from taskmanager or restart the se
rver. horrid!!!
I can run the dynamic sql (being executed in the proc) in query analyzer wit
h the login used for linking the servers.
What is happening here?
Thanks for reading and your help
Arun
PS
From my experience over the last 2-3 days stress testing linked servers, it
appears that MS has spent minimal time testing linked server functionality.
It works when I do select * from srv2.db2.dbo.tbl but anything more, it fall
s apart.
is it also your experience?Hi,
Could you please paste the code of the stored proc? As I understand it, you
are trying to pass a remote server and remote db name to a stored proc, and
that will take the data located on that remote data source and insert it
into a local table on the local server?
How long have you waited for the unresponsive behaviour?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Hi Vikram
Thanks for your response. Since posting, I have changed the process to avoid
the linked server by putting data locally (delivery pressure). So I don't h
ave the code snippets ready at hand. I will create new ones and post them.
But this is basically what I was trying.
The insert takes less than no time when done locally. When I do it across li
nked servers, it wasn't done in 7 minutes and I killed it. There were no rec
ords in the destination table.
-- dynamic sql in proc
select * from srv2.db.dbo.tbl a where fld3 = (select max(fld3) from srv2.db.
dbo.tbl where fld1 = a.fld1 and fld2 = a.fld2)
proc is fired from local server
insert tbl2 exec proc @.srv, @.db
Arun
"Vikram Jayaram [MS]" wrote:

> Hi,
> Could you please paste the code of the stored proc? As I understand it, yo
u
> are trying to pass a remote server and remote db name to a stored proc, an
d
> that will take the data located on that remote data source and insert it
> into a local table on the local server?
> How long have you waited for the unresponsive behaviour?
> Thanks,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>

linked server

hello
we have a job which will update the data in one server and insert data in another server.for this we created a linked server and wrote a procedure to update and insert the data in both the servers. when we are executing the procedure manually it is working fine. but when we tried to execute the procedure through a local job. job execution is getting failed and encountered the following error

Msg 18456, Sev 14: Login failed for user '\'. [SQLSTATE 28000]

can u please suggest a proper solution for this. we already gave all permissions and roles to both the users.

thank you.Hi Karraaruna!

You probably get problem with user-right. Your sql-servers and specially sql agents should not be running on local account. You must give the sql-server (with agents) a nt-account that has user-rights on the other machine.

Becuse when the sql-agent start the job, then the job is personated with the account of sql-agent. With other word, the next machine see the other machine as a user.
/Mada|||Mada, almost. NT accounts/authentication will only work if both servers are using Active Directory. If you are not using Active Directory then on your linked server proerties window select the Security tab, and set up a login mapping from the NT account your SQL Agent uses to a SQL Server authenticated id on the remote server.|||Hi Paul Young!

Active Directory or not, if you give a nt-account "user-rights" on a another sql-server it will be able to execute an stored procedure. Its no different then any other user.

It works...

/Mada|||Agreed, however the original question was about executing a stored procedure on a linked server. If you are using an NT Authenticated ID when you login and are not using Active Directory the linked server will not be able to authenticate the user unless you mapp the NT Authenticated ID to a server authenticated id.

linked server

I want to be able to access/modify/insert to a table which is in another server.

Can someone just list out the steps briefly for me? Or just tell me if i am right..

1. create linked server(eg.server2) (I have done that)

2. create distributed views on server1

-creating the distributed views i understand that i can use OPENDATASOURCE or OPENROWSET or just a four-partname right?

After creating the distributed views can i use a four-part name to make references to the remote databases?You don't need to create views. If the mapped user has permission then

select * from lnksrvr.dbname.owner.tablename

You can test on a single server by creating a linked server pointing to itself.