Linked to Oracle

In my SQL Server I had a link to Oracle server and everything was working fine. Recently I moved all the SQL Server databases to a different SQL Server box and also installed Oracle client on this new server and restarted the server after installing Oracl
e client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?
Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Linked to Oracle

In my SQL Server I had a link to Oracle server and everything was working fine. Recently I moved all the SQL Server databases to a different SQL Server box and also installed Oracle client on this new server and restarted the server after installing Oracle client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Linked to Oracle

In my SQL Server I had a link to Oracle server and everything was working fi
ne. Recently I moved all the SQL Server databases to a different SQL Server
box and also installed Oracle client on this new server and restarted the se
rver after installing Oracl
e client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
Thanks,
CGW
The permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:

> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specified
as a login and database user with select priviledges on all tables and views.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin in
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:
> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run from
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an execution
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed are
> domain or SQL admins. Those who fail are members of a domain group, specified
> as a login and database user with select priviledges on all tables and views.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW|||Good guess, but actually, that is how they are set up. What else do you think
might be the problem?
--
Thanks,
CGW
"John Scragg" wrote:
> The permissions across the link are based on the logins associated with the
> link. You may have set them up with user accounts on the remote server, but
> they credentials passed to that remote server are those configured for the
> link. My guess is that you need to set up for users to make connections
> "using the login's current security context". In EM you can check under the
> Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> QA to see what you get.
> HTH,
> John Scragg
> "CGW" wrote:
> > We recently moved a database from one SQL server to another and replicated
> > logins, users, permissions, etc. The problem occurs when joins are run from
> > the original server, referencing tables on the new server, using the
> > four-part name. Using the same query, some users time out with an execution
> > plan that looks like it ignores the indexes on the remote server. Other's
> > execute in seconds.
> >
> > I've been over permissions with a fine-tooth comb. The users who succeed are
> > domain or SQL admins. Those who fail are members of a domain group, specified
> > as a login and database user with select priviledges on all tables and views.
> >
> > Ownership is sa, just as it is for databases on the original server. Can
> > anyone suggest what else I might look for? Thanks!
> > --
> > Thanks,
> >
> > CGW|||Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
be the case that the chain is broken for regular users.
Using Ownership Chaining (http://tinyurl.com/8q5dq)
Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
Best of luck,
John
"CGW" wrote:
> Good guess, but actually, that is how they are set up. What else do you think
> might be the problem?
> --
> Thanks,
> CGW
>
> "John Scragg" wrote:
> > The permissions across the link are based on the logins associated with the
> > link. You may have set them up with user accounts on the remote server, but
> > they credentials passed to that remote server are those configured for the
> > link. My guess is that you need to set up for users to make connections
> > "using the login's current security context". In EM you can check under the
> > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > QA to see what you get.
> >
> > HTH,
> >
> > John Scragg
> >
> > "CGW" wrote:
> >
> > > We recently moved a database from one SQL server to another and replicated
> > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > the original server, referencing tables on the new server, using the
> > > four-part name. Using the same query, some users time out with an execution
> > > plan that looks like it ignores the indexes on the remote server. Other's
> > > execute in seconds.
> > >
> > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > as a login and database user with select priviledges on all tables and views.
> > >
> > > Ownership is sa, just as it is for databases on the original server. Can
> > > anyone suggest what else I might look for? Thanks!
> > > --
> > > Thanks,
> > >
> > > CGW|||Thanks, we're still researching. When we link with a specific security
context, we can make the problem go away, which gave us some clues.
--
Thanks,
CGW
"John Scragg" wrote:
> Dunno. Maybe the ownership chain is broken. If the admin roles work, it may
> be the case that the chain is broken for regular users.
> Using Ownership Chaining (http://tinyurl.com/8q5dq)
> Cross DB Ownership Chaining (http://tinyurl.com/7n9jf)
> Best of luck,
> John
> "CGW" wrote:
> > Good guess, but actually, that is how they are set up. What else do you think
> > might be the problem?
> > --
> > Thanks,
> >
> > CGW
> >
> >
> > "John Scragg" wrote:
> >
> > > The permissions across the link are based on the logins associated with the
> > > link. You may have set them up with user accounts on the remote server, but
> > > they credentials passed to that remote server are those configured for the
> > > link. My guess is that you need to set up for users to make connections
> > > "using the login's current security context". In EM you can check under the
> > > Security tab on your linked server properties or run sp_helplinkedsrvlogin in
> > > QA to see what you get.
> > >
> > > HTH,
> > >
> > > John Scragg
> > >
> > > "CGW" wrote:
> > >
> > > > We recently moved a database from one SQL server to another and replicated
> > > > logins, users, permissions, etc. The problem occurs when joins are run from
> > > > the original server, referencing tables on the new server, using the
> > > > four-part name. Using the same query, some users time out with an execution
> > > > plan that looks like it ignores the indexes on the remote server. Other's
> > > > execute in seconds.
> > > >
> > > > I've been over permissions with a fine-tooth comb. The users who succeed are
> > > > domain or SQL admins. Those who fail are members of a domain group, specified
> > > > as a login and database user with select priviledges on all tables and views.
> > > >
> > > > Ownership is sa, just as it is for databases on the original server. Can
> > > > anyone suggest what else I might look for? Thanks!
> > > > --
> > > > Thanks,
> > > >
> > > > CGW

server, join, same query, different plans for different use

We recently moved a database from one SQL server to another and replicated
logins, users, permissions, etc. The problem occurs when joins are run from
the original server, referencing tables on the new server, using the
four-part name. Using the same query, some users time out with an execution
plan that looks like it ignores the indexes on the remote server. Other's
execute in seconds.
I've been over permissions with a fine-tooth comb. The users who succeed are
domain or SQL admins. Those who fail are members of a domain group, specifie
d
as a login and database user with select priviledges on all tables and views
.
Ownership is sa, just as it is for databases on the original server. Can
anyone suggest what else I might look for? Thanks!
--
Thanks,
CGWThe permissions across the link are based on the logins associated with the
link. You may have set them up with user accounts on the remote server, but
they credentials passed to that remote server are those configured for the
link. My guess is that you need to set up for users to make connections
"using the login's current security context". In EM you can check under the
Security tab on your linked server properties or run sp_helplinkedsrvlogin i
n
QA to see what you get.
HTH,
John Scragg
"CGW" wrote:

> We recently moved a database from one SQL server to another and replicated
> logins, users, permissions, etc. The problem occurs when joins are run fro
m
> the original server, referencing tables on the new server, using the
> four-part name. Using the same query, some users time out with an executio
n
> plan that looks like it ignores the indexes on the remote server. Other's
> execute in seconds.
> I've been over permissions with a fine-tooth comb. The users who succeed a
re
> domain or SQL admins. Those who fail are members of a domain group, specif
ied
> as a login and database user with select priviledges on all tables and vie
ws.
> Ownership is sa, just as it is for databases on the original server. Can
> anyone suggest what else I might look for? Thanks!
> --
> Thanks,
> CGW