Database on differnet servers ...

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 2008 Forums

SQL Server Administration (2008)

Database on differnet servers ...

Author

Topic

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-20 : 01:42:42

Hi,
i need to write one trigger which will insert in to data in to a table which is in differnt database on differnt machine..

is this possible ..?

this trigger will take data from table A which is in server ABC ..and insert this data in to table A whcih is in server XYZ..users is also different on both databases ...

and one database is in sql server 2005 and second is on sql server 2008 ..

any idea about it ....

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 03:13:42

Should be OK

Make a linked server ABC to the server XYZ (lets call the linked server name Server_XYZ). You can make this login using a different user to the one on Server ABC.

The in your trigger, on Table_A on Server_ABC just do:


CREATE TRIGGER dbo.MyTrigger
ON dbo.Table_A
AFTER INSERT
AS
SET NOCOUNT ON

INSERT INTO Server_XYZ.MyDatabase.dbo.TableA(Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM inserted

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-20 : 03:21:47

Thanks a lot ..

can you share some url/artical with me ..for steps how to create link etc..

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 04:02:33

SQL Server documentation explains how to add a linked server

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-20 : 08:44:26

Hi,

i created linked server...by using management studio..
i gave remote server ip in the product name and data source ..
and in catalog i gave remote database name ..
then in security...be made using this security context..
and gave remote login name and password..

then in server option i select rpc and rpc out to true..

now it is showing linked server name in the server object..

but when i am trying to insert database in to remote server table by
using trigger . "insert into linkedserver.remotedatabae.dbo.tablename''

it is giving error .......

Theoperation could not perform because OLED DB Provider "sqlnc110"
fro linked server was unlable to begin a distributed transaction.

return message 'the partener transaction manager has disabled its
support for remote/network transaction.'

how to check this ..any idea..?

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 12:53:09

"now it is showing linked server name in the server object.."

So far so good, well done

"Theoperation could not perform because OLED DB Provider "sqlnc110"
fro linked server was unlable to begin a distributed transaction."

Yeah, that's a pain, for sure

I don't know the way around that. Some thoughts based on how I ahve worked around it in the past:

1) Make sure you are NOT using BEGIN TRANSACTION - i.e. so that the transaction is running without a transaction block. (that may not be what you want, but see if that fixes the problem, for now).

2) Try using OPENQUERY(LinkedServerName, "SELECT ...") instead of LinkedServer.RemoteDatabase.dbo.RemoteTable. The syntax for an INSERT, using OPENQUERY, is utterly daft IMHO, but it does work.

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-27 : 06:35:11

I resolved this ...

i set inbound out bound in the componet manager .security configuratin
...MSDTS ...

Thanks ...

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 07:21:03

Ah, MSDTS. I remember that now ... thanks for coming back and sharing that info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources