Linked server problem

Hi, im trying to make a linked server with an oracle db. Here is the Tsql
code:
set nocount on
-- if exists must drop
IF EXISTS (select * from master.dbo.sysservers WHERE srvname = 'sena')
BEGIN
EXEC sp_droplinkedsrvlogin 'sena', NULL
EXEC sp_dropserver 'sena'
END
-- Create linked server
use master
go
EXEC sp_addlinkedserver @.server = 'sena', @.srvproduct = 'Oracle',
@.provider = 'MSDAORA', @.datasrc = 'bdsena'
go
EXEC sp_addlinkedsrvlogin 'sena', 'false', NULL, 'general', 'general'
go
SELECT * FROM OPENQUERY(sena, 'Select * From oferente')
set nocount off
When the analizer try to make the openquery i get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
The problem is that i have in the server the oracle client and i can connect
to the oracle database from sqlplus or activex (using msdaora)
Any idea?
Thanks,
Andres Chaves"Andres Chaves" <guest@.guest.com> wrote in message
news:#7OPittrDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, im trying to make a linked server with an oracle db. Here is the Tsql
> code:
> set nocount on
> -- if exists must drop
> IF EXISTS (select * from master.dbo.sysservers WHERE srvname = 'sena')
> BEGIN
> EXEC sp_droplinkedsrvlogin 'sena', NULL
> EXEC sp_dropserver 'sena'
> END
> -- Create linked server
> use master
> go
> EXEC sp_addlinkedserver @.server = 'sena', @.srvproduct = 'Oracle',
> @.provider = 'MSDAORA', @.datasrc = 'bdsena'
> go
> EXEC sp_addlinkedsrvlogin 'sena', 'false', NULL, 'general', 'general'
> go
> SELECT * FROM OPENQUERY(sena, 'Select * From oferente')
> set nocount off
> When the analizer try to make the openquery i get the following error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle client and networking components
> were not found. These components are supplied by Oracle Corporation and
are
> part of the Oracle Version 7.3.3 or later client software installation.
> Provider is unable to function until these components are installed.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> The problem is that i have in the server the oracle client and i can
connect
> to the oracle database from sqlplus or activex (using msdaora)
> Any idea?
> Thanks,
> Andres Chaves
>
Did you try to connect to the Oracle from sqlplus using SQL Server's Windows
NT account?
You might need to install Oracle client one more time.
Regards,
Igor Raytsin

linked server permissions

I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
Dan D.
If you are using linked server, check this out....
http://databasejournal.com/features/...le.php/3116011
Thanks
GYK
"Dan D." wrote:

> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.
|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features/...le.php/3116011
> Thanks
> GYK
> "Dan D." wrote:

linked server permissions

I have a query on a web site that I'm trying to run. I get this error: Acces
s
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features...cle.php/3116011
Thanks
GYK
"Dan D." wrote:

> I have a query on a web site that I'm trying to run. I get this error: Acc
ess
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
[vbcol=seagreen]
> If you are using linked server, check this out....
> http://databasejournal.com/features...cle.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
>

linked server permissions

I have a query on a web site that I'm trying to run. I get this error: Access
to the remote server is denied because no login-mapping exists.
I can run the query as myself (sysadmin). The user that has problems only
has db_datareader and db_datawriter permissions. Does this user need
different permissions?
Thanks,
--
Dan D.If you are using linked server, check this out....
http://databasejournal.com/features/mssql/article.php/3116011
Thanks
GYK
"Dan D." wrote:
> I have a query on a web site that I'm trying to run. I get this error: Access
> to the remote server is denied because no login-mapping exists.
> I can run the query as myself (sysadmin). The user that has problems only
> has db_datareader and db_datawriter permissions. Does this user need
> different permissions?
> Thanks,
> --
> Dan D.|||Thanks.
"GYK" wrote:
> If you are using linked server, check this out....
> http://databasejournal.com/features/mssql/article.php/3116011
> Thanks
> GYK
> "Dan D." wrote:
> > I have a query on a web site that I'm trying to run. I get this error: Access
> > to the remote server is denied because no login-mapping exists.
> >
> > I can run the query as myself (sysadmin). The user that has problems only
> > has db_datareader and db_datawriter permissions. Does this user need
> > different permissions?
> >
> > Thanks,
> > --
> > Dan D.

Linked server from SQL Server to Access db on network share?

I have an Access database that exists on a network share
that I'm trying to link to as a linked server from SQL
Server. Sitting at the SQL server machine, I can create
the linked server (both by EM and sql), connect to the
access db, query data, see tables, etc with no problems.
However, if I go to another machine and try to access the
linked database, either through EM or through sql, I get
this error message:
Server: Msg 7399, Level 16, State 1, Procedure
sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.
[OLE/DB provider returned message: The Microsoft Jet
database engine cannot open the
file '\\server\path\database.mdb'. It is already opened
exclusively by another user, or you need permission to
view its data.]
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The SQL server service is running as a domain user with
full access to the network share.
Any help?"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> I have an Access database that exists on a network share
> that I'm trying to link to as a linked server from SQL
> Server. Sitting at the SQL server machine, I can create
> the linked server (both by EM and sql), connect to the
> access db, query data, see tables, etc with no problems.
> However, if I go to another machine and try to access the
> linked database, either through EM or through sql, I get
> this error message:
> Server: Msg 7399, Level 16, State 1, Procedure
> sp_tables_ex, Line 20
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error.
> [OLE/DB provider returned message: The Microsoft Jet
> database engine cannot open the
> file '\\server\path\database.mdb'. It is already opened
> exclusively by another user, or you need permission to
> view its data.]
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> The SQL server service is running as a domain user with
> full access to the network share.
Does the domain user account have MODIFY permissions at the NTFS file layer?
The sql server service account must have the ability to create files, such
as the associated *.ldb file.
Steve|||Yes, the domain account has full access to the share (read, write, delete).
The linked database works fine from the SQL server machine, it just won't
work from any other machine.
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> > I have an Access database that exists on a network share
> > that I'm trying to link to as a linked server from SQL
> > Server. Sitting at the SQL server machine, I can create
> > the linked server (both by EM and sql), connect to the
> > access db, query data, see tables, etc with no problems.
> > However, if I go to another machine and try to access the
> > linked database, either through EM or through sql, I get
> > this error message:
> >
> > Server: Msg 7399, Level 16, State 1, Procedure
> > sp_tables_ex, Line 20
> > OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> > error.
> > [OLE/DB provider returned message: The Microsoft Jet
> > database engine cannot open the
> > file '\\server\path\database.mdb'. It is already opened
> > exclusively by another user, or you need permission to
> > view its data.]
> > OLE DB error trace [OLE/DB
> > Provider 'Microsoft.Jet.OLEDB.4.0'
> > IDBInitialize::Initialize returned 0x80004005: ].
> >
> > The SQL server service is running as a domain user with
> > full access to the network share.
> Does the domain user account have MODIFY permissions at the NTFS file
layer?
> The sql server service account must have the ability to create files, such
> as the associated *.ldb file.
> Steve
>|||Have you installed the latest Jet service pack and MDAC release on your SQL
Server?
Steve
"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Yes, the domain account has full access to the share (read, write,
delete).
> The linked database works fine from the SQL server machine, it just won't
> work from any other machine.
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> >
> > "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> > news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> > > I have an Access database that exists on a network share
> > > that I'm trying to link to as a linked server from SQL
> > > Server. Sitting at the SQL server machine, I can create
> > > the linked server (both by EM and sql), connect to the
> > > access db, query data, see tables, etc with no problems.
> > > However, if I go to another machine and try to access the
> > > linked database, either through EM or through sql, I get
> > > this error message:
> > >
> > > Server: Msg 7399, Level 16, State 1, Procedure
> > > sp_tables_ex, Line 20
> > > OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> > > error.
> > > [OLE/DB provider returned message: The Microsoft Jet
> > > database engine cannot open the
> > > file '\\server\path\database.mdb'. It is already opened
> > > exclusively by another user, or you need permission to
> > > view its data.]
> > > OLE DB error trace [OLE/DB
> > > Provider 'Microsoft.Jet.OLEDB.4.0'
> > > IDBInitialize::Initialize returned 0x80004005: ].
> > >
> > > The SQL server service is running as a domain user with
> > > full access to the network share.
> >
> > Does the domain user account have MODIFY permissions at the NTFS file
> layer?
> > The sql server service account must have the ability to create files,
such
> > as the associated *.ldb file.
> >
> > Steve
> >
> >
>|||Yup, everything is up to date on both machines...
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uNwFIN$9DHA.2932@.TK2MSFTNGP10.phx.gbl...
> Have you installed the latest Jet service pack and MDAC release on your
SQL
> Server?
> Steve
> "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> > Yes, the domain account has full access to the share (read, write,
> delete).
> > The linked database works fine from the SQL server machine, it just
won't
> > work from any other machine.
> >
> > "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> > news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > >
> > > "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> > > > I have an Access database that exists on a network share
> > > > that I'm trying to link to as a linked server from SQL
> > > > Server. Sitting at the SQL server machine, I can create
> > > > the linked server (both by EM and sql), connect to the
> > > > access db, query data, see tables, etc with no problems.
> > > > However, if I go to another machine and try to access the
> > > > linked database, either through EM or through sql, I get
> > > > this error message:
> > > >
> > > > Server: Msg 7399, Level 16, State 1, Procedure
> > > > sp_tables_ex, Line 20
> > > > OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> > > > error.
> > > > [OLE/DB provider returned message: The Microsoft Jet
> > > > database engine cannot open the
> > > > file '\\server\path\database.mdb'. It is already opened
> > > > exclusively by another user, or you need permission to
> > > > view its data.]
> > > > OLE DB error trace [OLE/DB
> > > > Provider 'Microsoft.Jet.OLEDB.4.0'
> > > > IDBInitialize::Initialize returned 0x80004005: ].
> > > >
> > > > The SQL server service is running as a domain user with
> > > > full access to the network share.
> > >
> > > Does the domain user account have MODIFY permissions at the NTFS file
> > layer?
> > > The sql server service account must have the ability to create files,
> such
> > > as the associated *.ldb file.
> > >
> > > Steve
> > >
> > >
> >
> >
>

Linked server from SQL Server to Access db on network share?

I have an Access database that exists on a network share
that I'm trying to link to as a linked server from SQL
Server. Sitting at the SQL server machine, I can create
the linked server (both by EM and sql), connect to the
access db, query data, see tables, etc with no problems.
However, if I go to another machine and try to access the
linked database, either through EM or through sql, I get
this error message:
Server: Msg 7399, Level 16, State 1, Procedure
sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.
[OLE/DB provider returned message: The Microsoft Jet
database engine cannot open the
file '\\server\path\database.mdb'. It is already opened
exclusively by another user, or you need permission to
view its data.]
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The SQL server service is running as a domain user with
full access to the network share.
Any help?Hi, I believe I answered this same question that was cross posted to a few
newsgroups...
Steve
"Hobbes" <anonymous@.discussions.microsoft.com> wrote in message
news:1372b01c3f726$aff2aac0$a501280a@.phx
.gbl...
> I have an Access database that exists on a network share
> that I'm trying to link to as a linked server from SQL
> Server. Sitting at the SQL server machine, I can create
> the linked server (both by EM and sql), connect to the
> access db, query data, see tables, etc with no problems.
> However, if I go to another machine and try to access the
> linked database, either through EM or through sql, I get
> this error message:
> Server: Msg 7399, Level 16, State 1, Procedure
> sp_tables_ex, Line 20
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error.
> [OLE/DB provider returned message: The Microsoft Jet
> database engine cannot open the
> file '\\server\path\database.mdb'. It is already opened
> exclusively by another user, or you need permission to
> view its data.]
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> The SQL server service is running as a domain user with
> full access to the network share.
> Any help?
>

Linked server from SQL Server to Access db on network share?

I have an Access database that exists on a network share
that I'm trying to link to as a linked server from SQL
Server. Sitting at the SQL server machine, I can create
the linked server (both by EM and sql), connect to the
access db, query data, see tables, etc with no problems.
However, if I go to another machine and try to access the
linked database, either through EM or through sql, I get
this error message:
Server: Msg 7399, Level 16, State 1, Procedure
sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.
[OLE/DB provider returned message: The Microsoft Jet
database engine cannot open the
file '\\server\path\database.mdb'. It is already opened
exclusively by another user, or you need permission to
view its data.]
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The SQL server service is running as a domain user with
full access to the network share.
Any help?"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> I have an Access database that exists on a network share
> that I'm trying to link to as a linked server from SQL
> Server. Sitting at the SQL server machine, I can create
> the linked server (both by EM and sql), connect to the
> access db, query data, see tables, etc with no problems.
> However, if I go to another machine and try to access the
> linked database, either through EM or through sql, I get
> this error message:
> Server: Msg 7399, Level 16, State 1, Procedure
> sp_tables_ex, Line 20
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error.
> [OLE/DB provider returned message: The Microsoft Jet
> database engine cannot open the
> file '\\server\path\database.mdb'. It is already opened
> exclusively by another user, or you need permission to
> view its data.]
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> The SQL server service is running as a domain user with
> full access to the network share.
Does the domain user account have MODIFY permissions at the NTFS file layer?
The sql server service account must have the ability to create files, such
as the associated *.ldb file.
Steve|||Yes, the domain account has full access to the share (read, write, delete).
The linked database works fine from the SQL server machine, it just won't
work from any other machine.
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> news:eQDAz289DHA.3436@.tk2msftngp13.phx.gbl...
> Does the domain user account have MODIFY permissions at the NTFS file
layer?
> The sql server service account must have the ability to create files, such
> as the associated *.ldb file.
> Steve
>|||Have you installed the latest Jet service pack and MDAC release on your SQL
Server?
Steve
"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Yes, the domain account has full access to the share (read, write,
delete).
> The linked database works fine from the SQL server machine, it just won't
> work from any other machine.
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> layer?
such
>|||Yup, everything is up to date on both machines...
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uNwFIN$9DHA.2932@.TK2MSFTNGP10.phx.gbl...
> Have you installed the latest Jet service pack and MDAC release on your
SQL
> Server?
> Steve
> "Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
> news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> delete).
won't
> such
>|||Under the Linked Server properties, security tab -- which security context
did you select?
Steve
"Hobbes159" <anonymous@.discussions.microsoft.com> wrote in message
news:e3$m6p#9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Yes, the domain account has full access to the share (read, write,
delete).
> The linked database works fine from the SQL server machine, it just won't
> work from any other machine.
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:OWnvfl99DHA.1428@.TK2MSFTNGP12.phx.gbl...
> layer?
such
>

Linked Server Configuration

When Im going to configure Linked server Its giving the error massege asying that

"Error 15028: The server 'JOY' already exists.

How can i solve this?

Thanks

It seams the server is already exsits..

Try the following query to check the server is alreay exist or not..

Select * from Sysservers

And check the column isremote, to test the server is remote or logical server.

If the name is already listed on this table, try to use the different....

|||

I had a similar situation. I ended up just wrapping a TRY-CATCH around procedure. Something along the lines of...

BEGIN TRY

EXEC sp_addlinkedserver {whatever}

END TRY

BEGIN CATCH

PRINT 'Server already linked.'

END CATCH

Linked Server Configuration

When Im going to configure Linked server Its giving the error massege asying that

"Error 15028: The server 'JOY' already exists.

How can i solve this?

Thanks

It seams the server is already exsits..

Try the following query to check the server is alreay exist or not..

Select * from Sysservers

And check the column isremote, to test the server is remote or logical server.

If the name is already listed on this table, try to use the different....

|||

I had a similar situation. I ended up just wrapping a TRY-CATCH around procedure. Something along the lines of...

BEGIN TRY

EXEC sp_addlinkedserver {whatever}

END TRY

BEGIN CATCH

PRINT 'Server already linked.'

END CATCH