Error Comparing Data

Brian Donahue

Hi Paul,

The action that Data Compare is talking about is the attempt to create a
linked server on one of your servers. This linked server is needed to
perform the distributed SQL queries needed to combine the two sets of data
in a UNION operation from both servers. In order to create a linked server,
you must be a SYSADMIN or SECURITYADMIN. Now, when you use the 'Advanced'
option, you choose which server the linked server gets built on, and you
need elevated priviliges on that server. By default, the first server in the
data comparison will be chosen to host the linked server if you don't use
the Advanced button.

Here's the pitch: SQL Data Compare version 3 does not have this issue
because it does not use distributed queries. ADO .NET has rendered that
requirement obsolete, which means that you can connect to both servers as
normal with SELECT rights only.

Regards,

Brian Donahue

Red Gate Technical Support

"Paul Durdin" <pdurdin@nospam_hotmail.com> wrote in message
news:kUqajojoDHA.2972@server53...
> I have been trying again to use SQL Data Compare v2 on a remote SQL Server
> 2k database and a local networked machine running SQL 2k. The attachment
> illustrates the problem that I have been encountering. I am using a SQL
> login on both machine (identical database name, log in name and password).
I
> get the error shown in the attached screen shot (User does not have
> permission to perform this action).
>
> There is no indication what action is being attempted.
>
> I can compare the remote machine with itself ok! I have tried setting the
> advanced option - but I am not clear what that is trying to do. I have
also
> tried using integrated (windows) logins on the local machine, but that
> doesn't bring any joy.
>
> Any ideas how to sort this?
>
> Paul Durdin
>
>
>

Brian Donahue

Hi Paul,

I'm glad that it works. Strange though, I can't find anything saying
that MSDE does not support distributed transactions. It may have just been
configured wrong, such as the DTC service not running or not configured for
network access. Anyway, it's going now, so I won't dwell on it for too long!

Regards,

Brian Donahue

Red Gate Technical Support

"Paul Durdin" <pdurdin@nospam_hotmail.com> wrote in message
news:rHDapG5pDHA.1500@server53...
> Hi Brian,
>
> Thanks for the reply. It turns out that two places that I have been trying
> this are actually running the desk of SQL Server - and cannot set up
> database links. When I did it on a proper server it worked ok!
>
> thanks again.
> --
> regards,
>
> Paul Durdin
>
>
> "Brian Donahue (Red Gate)" <brian.donahue@red-gate.com> wrote in message
> news:KBnB42UpDHA.1492@server53...
> > Hi Paul,
> >
> > The action that Data Compare is talking about is the attempt to
create
> a
> > linked server on one of your servers. This linked server is needed to
> > perform the distributed SQL queries needed to combine the two sets of
data
> > in a UNION operation from both servers. In order to create a linked
> server,
> > you must be a SYSADMIN or SECURITYADMIN. Now, when you use the
'Advanced'
> > option, you choose which server the linked server gets built on, and you
> > need elevated priviliges on that server. By default, the first server in
> the
> > data comparison will be chosen to host the linked server if you don't
use
> > the Advanced button.
> >
> > Here's the pitch: SQL Data Compare version 3 does not have this
issue
> > because it does not use distributed queries. ADO .NET has rendered that
> > requirement obsolete, which means that you can connect to both servers
as
> > normal with SELECT rights only.
> >
> > Regards,
> >
> > Brian Donahue
> >
> > Red Gate Technical Support
> >
> > "Paul Durdin" <pdurdin@nospam_hotmail.com> wrote in message
> > news:kUqajojoDHA.2972@server53...
> > > I have been trying again to use SQL Data Compare v2 on a remote SQL
> Server
> > > 2k database and a local networked machine running SQL 2k. The
attachment
> > > illustrates the problem that I have been encountering. I am using a
SQL
> > > login on both machine (identical database name, log in name and
> password).
> > I
> > > get the error shown in the attached screen shot (User does not have
> > > permission to perform this action).
> > >
> > > There is no indication what action is being attempted.
> > >
> > > I can compare the remote machine with itself ok! I have tried setting
> the
> > > advanced option - but I am not clear what that is trying to do. I have
> > also
> > > tried using integrated (windows) logins on the local machine, but that
> > > doesn't bring any joy.
> > >
> > > Any ideas how to sort this?
> > >
> > > Paul Durdin
> > >
> > >
> > >
> >
> >
>
>