Linked Server in trigger

Question

Linked Server in trigger

lixin-68090

Old Hand

Points: 378
More actions
May 24, 2006 at 12:39 pm

#168219

Hi,

I have a merge replication system. When data are updated to the publisher db I want to query data from a linked server using trigger.
The code like:
Declare @fnmane VARCHAR(30)
Declare @lname VARCHAR(30)
Declare @email VARCHAR(50)
Select @fnmae = fname FROM INSERTED
Select @lname = lname FROM INSERTED
Select @email=Email from linkedServer.dbName.tbName where fname=@fname and lname=@lname

I tested this using Insert query and Query Analyzer. Both worked fine, but when I start the merge agent to merge data from the subscribes the trigger fails.
I guess it is a security issue but can not figure out how to fix it.

Any help is great appreciated.
May 29, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 1

When you use replication, you logon to SQL Server as a specific user. This is most likely a different user to the one you use when you connect to Query Analyser.

What you need to do is look at who is your user for replicaiton and try to run QA as that user, then you will most likely see some other error - security related I expect.

Then you need to look at how to apply permissions to to the linked server connection - start with sp_add_linked_server in BOL and then hunt around from there.

HTH

Steve

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

lixin-68090 Old Hand Points: 378 More actions · Answer 2

The replication agent connection to Publisher uses SQL Server Authentication called dbUserAccount. I test this account in QL and worked fine.

The linked server login is also SQL user who can access both servers.

Because the trigger failed I get error when start the merger agent:

The process could not query row metadata at the 'Publisher'.

(Source: Merge Replication Provider (Agent); Error number: -2147200996)

Any idea?

Thanks

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 3

Perhaps it is not the Linked Server that is the problem.

The error indicates that there is a problem at the publisher.

Perhaps what you need to look at is something to prove that the linked server is the error.

For instance, take the linked server reference out of the trigger temporarily and see if you still ge the same issue.

Unfortunately I think you are at the point where you need to divide and conquer, eliminate possibilities as you go.

Can't offer you any more than that I'm afraid.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

lixin-68090 Old Hand Points: 378 More actions · Answer 4

The reason I think it is linked server problem is because when I remove the select ... from linked server line in the trigger the merge agent works just fine.

I will look at other settings of the linked server since it worked before.

Anyway, Thank you Steve,

steven.kik SSC Rookie Points: 25 More actions · Answer 5

Hihi, it's been awhile for this issue, and I am encountering the exact same problem, not sure if there is already a solution on this matter? Please help.

Thanks in advance.

🙂