Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Linked server

Author  Topic 

vanisa
Starting Member

10 Posts
Posted - 2009-08-19 : 03:12:27
Hi All,

I want to update the table from one database(let say 172.16.3.35) to table in other database(172.16.3.225).
1.For this i created a Linked server in Enterprise Manager.
2.Enabled MSDTC to allow the network transaction.
3.Configured Windows Firewall to include the MSDTC program and to include port 135 as an exception

Now using the trigger i want to update my table in 172.16.3.225.
But Am getting the Follwing error

Server: Msg 7391, Level 16, State 1, Procedure Updateusername, Line 6
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. ]


Please Help Me out.This is very Urgent

Ali.M.Habib
Yak Posting Veteran

54 Posts
Posted - 2009-08-19 : 03:21:05
check your connection string
Go to Top of Page

vanisa
Starting Member

10 Posts
Posted - 2009-08-19 : 03:26:26
quote:
Originally posted by Ali.M.Habib

check your connection string



You mean to say the Linked Server name???

Using that connection name Am able to execute the select queries..
while trying to update i got the posted error..
Go to Top of Page

Ali.M.Habib
Yak Posting Veteran

54 Posts
Posted - 2009-08-19 : 03:36:04
quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

check your connection string



You mean to say the Linked Server name???

Using that connection name Am able to execute the select queries..
while trying to update i got the posted error..



No , when you connect you use

EXEC sp_addlinkedserver
@server = linkedserver_name,
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=1.1.1.1;User ID=xxx;Password=xxxx'

then u should start the trigger by

sp_addlinkedsrvlogin 'linkedserver_name', 'false', NULL, 'xxx', 'xxxx'

this is so important to start the server
Go to Top of Page

vanisa
Starting Member

10 Posts
Posted - 2009-08-19 : 04:07:43
quote:
Originally posted by Ali.M.Habib

quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

check your connection string



You mean to say the Linked Server name???

Using that connection name Am able to execute the select queries..
while trying to update i got the posted error..



No , when you connect you use

EXEC sp_addlinkedserver
@server = linkedserver_name,
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=1.1.1.1;User ID=xxx;Password=xxxx'

then u should start the trigger by

sp_addlinkedsrvlogin 'linkedserver_name', 'false', NULL, 'xxx', 'xxxx'

this is so important to start the server








Thanks,

But i could not trace out pls help me


I created a new linked server as you said,coding am placing for ur verification.

EXEC sp_addlinkedserver
@server = van1,
@srvproduct = 'SQL 2000',
@provider = 'Microsoft OLE DB provider for SQL SERVER',
@provstr = 'DRIVER={SQL Server};SERVER=172.16.3.225;User ID=sa;Password=laser'

then, i executed this
sp_addlinkedsrvlogin 'van1', 'false', NULL, 'sa', 'laser'


The following proc i want to execute
1.linktab1 is the table in 172.16.0.35 in which i executed the
add linkecd server query.
2.linktab2 is the table in 172.16.3.225

using the below trigger i want to update the table in 172.16.3.225
server.

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update KIRTHIKA.Scdevelopment.dbo.linktab2 set empname='kirthika' where userid=1

After this update statement in 35 server the above trigger should be executed.

update linktab1 set empname='jo' where userid=1
Go to Top of Page

Ali.M.Habib
Yak Posting Veteran

54 Posts
Posted - 2009-08-19 : 04:19:34
quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

check your connection string



You mean to say the Linked Server name???

Using that connection name Am able to execute the select queries..
while trying to update i got the posted error..



No , when you connect you use

EXEC sp_addlinkedserver
@server = linkedserver_name,
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=1.1.1.1;User ID=xxx;Password=xxxx'

then u should start the trigger by

sp_addlinkedsrvlogin 'linkedserver_name', 'false', NULL, 'xxx', 'xxxx'

this is so important to start the server








Thanks,

But i could not trace out pls help me


I created a new linked server as you said,coding am placing for ur verification.

EXEC sp_addlinkedserver
@server = van1,
@srvproduct = 'SQL 2000',
@provider = 'Microsoft OLE DB provider for SQL SERVER',
@provstr = 'DRIVER={SQL Server};SERVER=172.16.3.225;User ID=sa;Password=laser'

then, i executed this
sp_addlinkedsrvlogin 'van1', 'false', NULL, 'sa', 'laser'


The following proc i want to execute
1.linktab1 is the table in 172.16.0.35 in which i executed the
add linkecd server query.
2.linktab2 is the table in 172.16.3.225

using the below trigger i want to update the table in 172.16.3.225
server.

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update KIRTHIKA.Scdevelopment.dbo.linktab2 set empname='kirthika' where userid=1

After this update statement in 35 server the above trigger should be executed.

update linktab1 set empname='jo' where userid=1


try this :

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update
linkedservername.dbname.schema(owner in sql server 2000).linktab2 set empname='kirthika' where userid=1

use the sequence
Go to Top of Page

vanisa
Starting Member

10 Posts
Posted - 2009-08-19 : 04:53:02
quote:
Originally posted by Ali.M.Habib

quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

check your connection string



You mean to say the Linked Server name???

Using that connection name Am able to execute the select queries..
while trying to update i got the posted error..



No , when you connect you use

EXEC sp_addlinkedserver
@server = linkedserver_name,
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=1.1.1.1;User ID=xxx;Password=xxxx'

then u should start the trigger by

sp_addlinkedsrvlogin 'linkedserver_name', 'false', NULL, 'xxx', 'xxxx'

this is so important to start the server








Thanks,

But i could not trace out pls help me


I created a new linked server as you said,coding am placing for ur verification.

EXEC sp_addlinkedserver
@server = van1,
@srvproduct = 'SQL 2000',
@provider = 'Microsoft OLE DB provider for SQL SERVER',
@provstr = 'DRIVER={SQL Server};SERVER=172.16.3.225;User ID=sa;Password=laser'

then, i executed this
sp_addlinkedsrvlogin 'van1', 'false', NULL, 'sa', 'laser'


The following proc i want to execute
1.linktab1 is the table in 172.16.0.35 in which i executed the
add linkecd server query.
2.linktab2 is the table in 172.16.3.225

using the below trigger i want to update the table in 172.16.3.225
server.

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update KIRTHIKA.Scdevelopment.dbo.linktab2 set empname='kirthika' where userid=1

After this update statement in 35 server the above trigger should be executed.

update linktab1 set empname='jo' where userid=1


try this :

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update
linkedservername.dbname.schema(owner in sql server 2000).linktab2 set empname='kirthika' where userid=1

use the sequence



I used as u said but i got the error as below

Server: Msg 7403, Level 16, State 1, Procedure Updateusername, Line 6
Could not locate registry entry for OLE DB provider 'Microsoft OLE DB provider for SQL SERVER'.
Go to Top of Page

Ali.M.Habib
Yak Posting Veteran

54 Posts
Posted - 2009-08-19 : 05:19:29
quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

quote:
Originally posted by vanisa

quote:
Originally posted by Ali.M.Habib

check your connection string



You mean to say the Linked Server name???

Using that connection name Am able to execute the select queries..
while trying to update i got the posted error..



No , when you connect you use

EXEC sp_addlinkedserver
@server = linkedserver_name,
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=1.1.1.1;User ID=xxx;Password=xxxx'

then u should start the trigger by

sp_addlinkedsrvlogin 'linkedserver_name', 'false', NULL, 'xxx', 'xxxx'

this is so important to start the server








Thanks,

But i could not trace out pls help me


I created a new linked server as you said,coding am placing for ur verification.

EXEC sp_addlinkedserver
@server = van1,
@srvproduct = 'SQL 2000',
@provider = 'Microsoft OLE DB provider for SQL SERVER',
@provstr = 'DRIVER={SQL Server};SERVER=172.16.3.225;User ID=sa;Password=laser'

then, i executed this
sp_addlinkedsrvlogin 'van1', 'false', NULL, 'sa', 'laser'


The following proc i want to execute
1.linktab1 is the table in 172.16.0.35 in which i executed the
add linkecd server query.
2.linktab2 is the table in 172.16.3.225

using the below trigger i want to update the table in 172.16.3.225
server.

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update KIRTHIKA.Scdevelopment.dbo.linktab2 set empname='kirthika' where userid=1

After this update statement in 35 server the above trigger should be executed.

update linktab1 set empname='jo' where userid=1


try this :

create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update
linkedservername.dbname.schema(owner in sql server 2000).linktab2 set empname='kirthika' where userid=1

use the sequence



I used as u said but i got the error as below

Server: Msg 7403, Level 16, State 1, Procedure Updateusername, Line 6
Could not locate registry entry for OLE DB provider 'Microsoft OLE DB provider for SQL SERVER'.



see this link http://forums.asp.net/t/1149643.aspx may help u
Go to Top of Page

vanisa
Starting Member

10 Posts
Posted - 2009-08-19 : 05:31:45
Thanks,

EXEC sp_addlinkedserver
@server = kirthics,
@srvproduct = '',
@provider = 'SQLOLEDB',
@provstr = 'DRIVER={SQL Server};SERVER=172.16.3.225;User ID=sa;Password=laser'


sp_addlinkedsrvlogin 'kirthics', 'false', NULL, 'sa', 'laser'


create trigger Updateusername
on linktab1
after update
as
print 'trigger UpdateUserName - executed'
update kirthics.Scdevelopment.dbo.linktab2 set empname='kirthika' where userid=1


Now i executed as u said...All the queries executed successfully
and the trigger is also created successfully.

But when i update below statement,Am getting back the same error.

update linktab1 set empname='kir' where userid=1

Error:

Server: Msg 7391, Level 16, State 1, Procedure Updateusername, Line 6
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. ]


But i can execute insert queries and select queriees and all.
Only Update i can't execute.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site