Linked Tables in Microsoft Access

If SQL Server tables contained on a Server computer are used as linked table
s
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client
?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?> If SQL Server tables contained on a Server computer are used as linked
tables
> in an Access database on a client computer via ODBC, and action queries
> written in Access are run with Access specific functions and references to
> controls on Access forms, is the processing done on the Server or the Client?[/vbc
ol]
The processing works on the client. If your filter is written without
functions, the records to change will be gathered based on your filter. But
the routine actually executes one line at a time from the client.
[vbcol=seagreen]
> Do you still run the risk of corrupting the "back-end" database if a writ
e
> operation is interrupted if you use tables in SQL Server database as linke
d
> tables instead of using tables in an Access database as linked tables?
The transaction won't commit until the action query completes. If the
machine goes down then the transaction will rollback.

Linked Tables in Microsoft Access

If SQL Server tables contained on a Server computer are used as linked tables
in an Access database on a client computer via ODBC, and action queries
written in Access are run with Access specific functions and references to
controls on Access forms, is the processing done on the Server or the Client?
Do you still run the risk of corrupting the "back-end" database if a write
operation is interrupted if you use tables in SQL Server database as linked
tables instead of using tables in an Access database as linked tables?
> If SQL Server tables contained on a Server computer are used as linked
tables
> in an Access database on a client computer via ODBC, and action queries
> written in Access are run with Access specific functions and references to
> controls on Access forms, is the processing done on the Server or the Client?
The processing works on the client. If your filter is written without
functions, the records to change will be gathered based on your filter. But
the routine actually executes one line at a time from the client.

> Do you still run the risk of corrupting the "back-end" database if a write
> operation is interrupted if you use tables in SQL Server database as linked
> tables instead of using tables in an Access database as linked tables?
The transaction won't commit until the action query completes. If the
machine goes down then the transaction will rollback.

Linked Tables

I currently have a SQL Database with 1 Table. I have connected to the SQL table via MS Access 2003 via Linked Tables. I can import records from either an XLS or CSV file to the Link Tables all day. However, I cannot update nor delete records. I have given my account full DBO access to the SQL Database and Table but still cannot make any modification from within Access. Is this even possible for what I am attempting to do within Access? If so, please tell me what I need to modify within SQL. Thanks in advance!I created linked table using ODBC datasource with SQL Native Client provider, it works fine in updating table in SQL2005.|||Access has problems updating linked tables that dont have a primary key defined, as youve imported from an external source and probably dont have a PK defined this may be your problem|||

colinrobinson wrote:

Access has problems updating linked tables that dont have a primary key defined, as youve imported from an external source and probably dont have a PK defined this may be your problem

You took the words right out of my mouth. I had a time trying to figure that out when I was importing some data to my SQL server and trying to edit/delete records. BTW, I was using another SQL server, so this is not just limited to Access.

sql

Linked Tables

I currently have a SQL Database with 1 Table. I have connected to the SQL table via MS Access 2003 via Linked Tables. I can import records from either an XLS or CSV file to the Link Tables all day. However, I cannot update nor delete records. I have given my account full DBO access to the SQL Database and Table but still cannot make any modification from within Access. Is this even possible for what I am attempting to do within Access? If so, please tell me what I need to modify within SQL. Thanks in advance!I created linked table using ODBC datasource with SQL Native Client provider, it works fine in updating table in SQL2005.|||Access has problems updating linked tables that dont have a primary key defined, as youve imported from an external source and probably dont have a PK defined this may be your problem|||

colinrobinson wrote:

Access has problems updating linked tables that dont have a primary key defined, as youve imported from an external source and probably dont have a PK defined this may be your problem

You took the words right out of my mouth. I had a time trying to figure that out when I was importing some data to my SQL server and trying to edit/delete records. BTW, I was using another SQL server, so this is not just limited to Access.

Linked Sql Servers

Trying to link 2 servers via:
SELECT *
from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=pwd;
Trusted_Connection=yes;',
'SELECT * FROM Web_Configurator.dbo.MC_WS_Countries')
-or-
select * from krusty.Web_Configurator.dbo.MC_WS_Countries
Keep getting the dreaded:
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I ran the following scripts and verified that the database server I'm
attempting to reach is in mixed mode:
exec sp_addlinkedserver @.server='krusty'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'krusty',@.rmtuser = 'uid',@.rmtpassword = 'pwd'
How I can check each server for correct configuration and get this simple
query working?Make sure that the remote server is using SQL Server Authentication,
the error message indicates that it is expecting Windows
authentication only.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Trusted_Connection =yes means use Windows Authentication even if you suply a
UID and a Password. The system is doing exactly what you told it to do.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"dbach" <dbach@.discussions.microsoft.com> wrote in message
news:C5EAB394-091D-4886-A518-55099CC2342B@.microsoft.com...
> Trying to link 2 servers via:
> SELECT *
> from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=pwd;
> Trusted_Connection=yes;',
> 'SELECT * FROM Web_Configurator.dbo.MC_WS_Countries')
> -or-
> select * from krusty.Web_Configurator.dbo.MC_WS_Countries
> Keep getting the dreaded:
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I ran the following scripts and verified that the database server I'm
> attempting to reach is in mixed mode:
> exec sp_addlinkedserver @.server='krusty'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'krusty',@.rmtuser => 'uid',@.rmtpassword = 'pwd'
> How I can check each server for correct configuration and get this simple
> query working?|||Thanks Geoff. After removing Trusted_Connection=yes... I now see a new error:
Note... SSA_ERPDB.results exists. tried SSA_ERPDB.dbo.results as well
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'ssa_erpdb.results'.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
"Geoff N. Hiten" wrote:
> Trusted_Connection =yes means use Windows Authentication even if you suply a
> UID and a Password. The system is doing exactly what you told it to do.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "dbach" <dbach@.discussions.microsoft.com> wrote in message
> news:C5EAB394-091D-4886-A518-55099CC2342B@.microsoft.com...
> > Trying to link 2 servers via:
> >
> > SELECT *
> > from OPENROWSET('SQLOLEDB','Server=SERVER;uid=uid;pwd=pwd;
> > Trusted_Connection=yes;',
> > 'SELECT * FROM Web_Configurator.dbo.MC_WS_Countries')
> >
> > -or-
> >
> > select * from krusty.Web_Configurator.dbo.MC_WS_Countries
> >
> > Keep getting the dreaded:
> >
> > Msg 18452, Level 14, State 1, Line 1
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I ran the following scripts and verified that the database server I'm
> > attempting to reach is in mixed mode:
> >
> > exec sp_addlinkedserver @.server='krusty'
> > exec sp_addlinkedsrvlogin @.rmtsrvname = 'krusty',@.rmtuser => > 'uid',@.rmtpassword = 'pwd'
> >
> > How I can check each server for correct configuration and get this simple
> > query working?
>

Linked SQL Server to Access

Hi, I now this question has been posted a few times, but I did not find an
answer.
I have a linked server on my SQL server to an access database via a mapped
drive on a domain. (2 computers, one with SQL server and the other has the
access database)
When I log in to query analyzer using windows authentication I run a stored
procedure to query data from the access database via a linked server,
everything works fine.
When I log in as SA, I get this errorâ'It is already opened exclusively by
another user, or you need permission to view its dataâ'
The same is with the DTS package, if I right click the package and execute
the DTS, it works. But if I create a job to call the DTS it gives me the same
error, even if I say that my domain username is the password.
I even ran DTS via command prompt and that too works.
I know itâ's some kind of permission level, but I donâ't know what to do.
I changed my c drive (the one with the access database) security to
everyone. I changed the access database security to everyone too.
Any one knows how to fix this?Your sqlagent proxy is probably not set with proper access.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
Also, be aware that SP3 has added a new flag. You might have to set it to
your environment.
e.g.
--restrict to admin only. 1=yes, 0=no
EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
go
-oj
"André" <Andr@.discussions.microsoft.com> wrote in message
news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> Hi, I now this question has been posted a few times, but I did not find an
> answer.
> I have a linked server on my SQL server to an access database via a mapped
> drive on a domain. (2 computers, one with SQL server and the other has the
> access database)
> When I log in to query analyzer using windows authentication I run a
> stored
> procedure to query data from the access database via a linked server,
> everything works fine.
> When I log in as SA, I get this error"It is already opened exclusively by
> another user, or you need permission to view its data"
> The same is with the DTS package, if I right click the package and execute
> the DTS, it works. But if I create a job to call the DTS it gives me the
> same
> error, even if I say that my domain username is the password.
> I even ran DTS via command prompt and that too works.
> I know it's some kind of permission level, but I don't know what to do.
> I changed my c drive (the one with the access database) security to
> everyone. I changed the access database security to everyone too.
> Any one knows how to fix this?
>|||HI,
You are correct, I did not have my proxy set. After I changed both my proxy
and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still get
the same error.
What else should I try?
"oj" wrote:
> Your sqlagent proxy is probably not set with proper access.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
> Also, be aware that SP3 has added a new flag. You might have to set it to
> your environment.
> e.g.
> --restrict to admin only. 1=yes, 0=no
> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> > Hi, I now this question has been posted a few times, but I did not find an
> > answer.
> >
> > I have a linked server on my SQL server to an access database via a mapped
> > drive on a domain. (2 computers, one with SQL server and the other has the
> > access database)
> >
> > When I log in to query analyzer using windows authentication I run a
> > stored
> > procedure to query data from the access database via a linked server,
> > everything works fine.
> >
> > When I log in as SA, I get this error"It is already opened exclusively by
> > another user, or you need permission to view its data"
> >
> > The same is with the DTS package, if I right click the package and execute
> > the DTS, it works. But if I create a job to call the DTS it gives me the
> > same
> > error, even if I say that my domain username is the password.
> >
> > I even ran DTS via command prompt and that too works.
> >
> > I know it's some kind of permission level, but I don't know what to do.
> > I changed my c drive (the one with the access database) security to
> > everyone. I changed the access database security to everyone too.
> >
> > Any one knows how to fix this?
> >
>
>|||Sorry for the late reply...Anyway, you might need to restart the service for
this to take effect.
--
-oj
"André" <Andr@.discussions.microsoft.com> wrote in message
news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
> HI,
> You are correct, I did not have my proxy set. After I changed both my
> proxy
> and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still
> get
> the same error.
> What else should I try?
>
> "oj" wrote:
>> Your sqlagent proxy is probably not set with proper access.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
>> Also, be aware that SP3 has added a new flag. You might have to set it to
>> your environment.
>> e.g.
>> --restrict to admin only. 1=yes, 0=no
>> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
>> go
>>
>> --
>> -oj
>>
>> "André" <Andr@.discussions.microsoft.com> wrote in message
>> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
>> > Hi, I now this question has been posted a few times, but I did not find
>> > an
>> > answer.
>> >
>> > I have a linked server on my SQL server to an access database via a
>> > mapped
>> > drive on a domain. (2 computers, one with SQL server and the other has
>> > the
>> > access database)
>> >
>> > When I log in to query analyzer using windows authentication I run a
>> > stored
>> > procedure to query data from the access database via a linked server,
>> > everything works fine.
>> >
>> > When I log in as SA, I get this error"It is already opened exclusively
>> > by
>> > another user, or you need permission to view its data"
>> >
>> > The same is with the DTS package, if I right click the package and
>> > execute
>> > the DTS, it works. But if I create a job to call the DTS it gives me
>> > the
>> > same
>> > error, even if I say that my domain username is the password.
>> >
>> > I even ran DTS via command prompt and that too works.
>> >
>> > I know it's some kind of permission level, but I don't know what to do.
>> > I changed my c drive (the one with the access database) security to
>> > everyone. I changed the access database security to everyone too.
>> >
>> > Any one knows how to fix this?
>> >
>>|||Hi,
I rebooted my server and I still get the same results. I did check to make
sure that my proxy is still set. and it is.
Thank you,
Andre
"oj" wrote:
> Sorry for the late reply...Anyway, you might need to restart the service for
> this to take effect.
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
> > HI,
> >
> > You are correct, I did not have my proxy set. After I changed both my
> > proxy
> > and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still
> > get
> > the same error.
> >
> > What else should I try?
> >
> >
> >
> > "oj" wrote:
> >
> >> Your sqlagent proxy is probably not set with proper access.
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
> >>
> >> Also, be aware that SP3 has added a new flag. You might have to set it to
> >> your environment.
> >>
> >> e.g.
> >> --restrict to admin only. 1=yes, 0=no
> >> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> >> go
> >>
> >>
> >>
> >> --
> >> -oj
> >>
> >>
> >> "André" <Andr@.discussions.microsoft.com> wrote in message
> >> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> >> > Hi, I now this question has been posted a few times, but I did not find
> >> > an
> >> > answer.
> >> >
> >> > I have a linked server on my SQL server to an access database via a
> >> > mapped
> >> > drive on a domain. (2 computers, one with SQL server and the other has
> >> > the
> >> > access database)
> >> >
> >> > When I log in to query analyzer using windows authentication I run a
> >> > stored
> >> > procedure to query data from the access database via a linked server,
> >> > everything works fine.
> >> >
> >> > When I log in as SA, I get this error"It is already opened exclusively
> >> > by
> >> > another user, or you need permission to view its data"
> >> >
> >> > The same is with the DTS package, if I right click the package and
> >> > execute
> >> > the DTS, it works. But if I create a job to call the DTS it gives me
> >> > the
> >> > same
> >> > error, even if I say that my domain username is the password.
> >> >
> >> > I even ran DTS via command prompt and that too works.
> >> >
> >> > I know it's some kind of permission level, but I don't know what to do.
> >> > I changed my c drive (the one with the access database) security to
> >> > everyone. I changed the access database security to everyone too.
> >> >
> >> > Any one knows how to fix this?
> >> >
> >>
> >>
> >>
>
>|||Andre,
How's about dropping and recreating the link like so:
--Linked server to Access on Shared drive
EXEC sp_addlinkedserver
@.server = 'myAccess',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = '\\ghost\ghost\oj\Access.mdb'
GO
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
go
-oj
"André" <Andr@.discussions.microsoft.com> wrote in message
news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
> Hi,
> I rebooted my server and I still get the same results. I did check to
> make
> sure that my proxy is still set. and it is.
> Thank you,
> Andre
> "oj" wrote:
>> Sorry for the late reply...Anyway, you might need to restart the service
>> for
>> this to take effect.
>> --
>> -oj
>>
>> "André" <Andr@.discussions.microsoft.com> wrote in message
>> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
>> > HI,
>> >
>> > You are correct, I did not have my proxy set. After I changed both my
>> > proxy
>> > and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I
>> > still
>> > get
>> > the same error.
>> >
>> > What else should I try?
>> >
>> >
>> >
>> > "oj" wrote:
>> >
>> >> Your sqlagent proxy is probably not set with proper access.
>> >>
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
>> >>
>> >> Also, be aware that SP3 has added a new flag. You might have to set it
>> >> to
>> >> your environment.
>> >>
>> >> e.g.
>> >> --restrict to admin only. 1=yes, 0=no
>> >> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
>> >> go
>> >>
>> >>
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >> "André" <Andr@.discussions.microsoft.com> wrote in message
>> >> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
>> >> > Hi, I now this question has been posted a few times, but I did not
>> >> > find
>> >> > an
>> >> > answer.
>> >> >
>> >> > I have a linked server on my SQL server to an access database via a
>> >> > mapped
>> >> > drive on a domain. (2 computers, one with SQL server and the other
>> >> > has
>> >> > the
>> >> > access database)
>> >> >
>> >> > When I log in to query analyzer using windows authentication I run a
>> >> > stored
>> >> > procedure to query data from the access database via a linked
>> >> > server,
>> >> > everything works fine.
>> >> >
>> >> > When I log in as SA, I get this error"It is already opened
>> >> > exclusively
>> >> > by
>> >> > another user, or you need permission to view its data"
>> >> >
>> >> > The same is with the DTS package, if I right click the package and
>> >> > execute
>> >> > the DTS, it works. But if I create a job to call the DTS it gives me
>> >> > the
>> >> > same
>> >> > error, even if I say that my domain username is the password.
>> >> >
>> >> > I even ran DTS via command prompt and that too works.
>> >> >
>> >> > I know it's some kind of permission level, but I don't know what to
>> >> > do.
>> >> > I changed my c drive (the one with the access database) security to
>> >> > everyone. I changed the access database security to everyone too.
>> >> >
>> >> > Any one knows how to fix this?
>> >> >
>> >>
>> >>
>> >>
>>|||I still get an error :(
"oj" wrote:
> Andre,
> How's about dropping and recreating the link like so:
> --Linked server to Access on Shared drive
> EXEC sp_addlinkedserver
> @.server = 'myAccess',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = '\\ghost\ghost\oj\Access.mdb'
> GO
> --Everyone connects via Admin Access db user
> EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
> > Hi,
> >
> > I rebooted my server and I still get the same results. I did check to
> > make
> > sure that my proxy is still set. and it is.
> >
> > Thank you,
> >
> > Andre
> >
> > "oj" wrote:
> >
> >> Sorry for the late reply...Anyway, you might need to restart the service
> >> for
> >> this to take effect.
> >>
> >> --
> >> -oj
> >>
> >>
> >> "André" <Andr@.discussions.microsoft.com> wrote in message
> >> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
> >> > HI,
> >> >
> >> > You are correct, I did not have my proxy set. After I changed both my
> >> > proxy
> >> > and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I
> >> > still
> >> > get
> >> > the same error.
> >> >
> >> > What else should I try?
> >> >
> >> >
> >> >
> >> > "oj" wrote:
> >> >
> >> >> Your sqlagent proxy is probably not set with proper access.
> >> >>
> >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
> >> >>
> >> >> Also, be aware that SP3 has added a new flag. You might have to set it
> >> >> to
> >> >> your environment.
> >> >>
> >> >> e.g.
> >> >> --restrict to admin only. 1=yes, 0=no
> >> >> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> >> >> go
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> -oj
> >> >>
> >> >>
> >> >> "André" <Andr@.discussions.microsoft.com> wrote in message
> >> >> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> >> >> > Hi, I now this question has been posted a few times, but I did not
> >> >> > find
> >> >> > an
> >> >> > answer.
> >> >> >
> >> >> > I have a linked server on my SQL server to an access database via a
> >> >> > mapped
> >> >> > drive on a domain. (2 computers, one with SQL server and the other
> >> >> > has
> >> >> > the
> >> >> > access database)
> >> >> >
> >> >> > When I log in to query analyzer using windows authentication I run a
> >> >> > stored
> >> >> > procedure to query data from the access database via a linked
> >> >> > server,
> >> >> > everything works fine.
> >> >> >
> >> >> > When I log in as SA, I get this error"It is already opened
> >> >> > exclusively
> >> >> > by
> >> >> > another user, or you need permission to view its data"
> >> >> >
> >> >> > The same is with the DTS package, if I right click the package and
> >> >> > execute
> >> >> > the DTS, it works. But if I create a job to call the DTS it gives me
> >> >> > the
> >> >> > same
> >> >> > error, even if I say that my domain username is the password.
> >> >> >
> >> >> > I even ran DTS via command prompt and that too works.
> >> >> >
> >> >> > I know it's some kind of permission level, but I don't know what to
> >> >> > do.
> >> >> > I changed my c drive (the one with the access database) security to
> >> >> > everyone. I changed the access database security to everyone too.
> >> >> >
> >> >> > Any one knows how to fix this?
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||I tried this on another Sql server and I get the same results. Do I have to
be an Admin on the network for this to work?
Andre
"André" wrote:
> I still get an error :(
> "oj" wrote:
> > Andre,
> >
> > How's about dropping and recreating the link like so:
> >
> > --Linked server to Access on Shared drive
> > EXEC sp_addlinkedserver
> > @.server = 'myAccess',
> > @.provider = 'Microsoft.Jet.OLEDB.4.0',
> > @.srvproduct = 'OLE DB Provider for Jet',
> > @.datasrc = '\\ghost\ghost\oj\Access.mdb'
> > GO
> > --Everyone connects via Admin Access db user
> > EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
> > go
> >
> >
> > --
> > -oj
> >
> >
> > "André" <Andr@.discussions.microsoft.com> wrote in message
> > news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
> > > Hi,
> > >
> > > I rebooted my server and I still get the same results. I did check to
> > > make
> > > sure that my proxy is still set. and it is.
> > >
> > > Thank you,
> > >
> > > Andre
> > >
> > > "oj" wrote:
> > >
> > >> Sorry for the late reply...Anyway, you might need to restart the service
> > >> for
> > >> this to take effect.
> > >>
> > >> --
> > >> -oj
> > >>
> > >>
> > >> "André" <Andr@.discussions.microsoft.com> wrote in message
> > >> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
> > >> > HI,
> > >> >
> > >> > You are correct, I did not have my proxy set. After I changed both my
> > >> > proxy
> > >> > and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I
> > >> > still
> > >> > get
> > >> > the same error.
> > >> >
> > >> > What else should I try?
> > >> >
> > >> >
> > >> >
> > >> > "oj" wrote:
> > >> >
> > >> >> Your sqlagent proxy is probably not set with proper access.
> > >> >>
> > >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
> > >> >>
> > >> >> Also, be aware that SP3 has added a new flag. You might have to set it
> > >> >> to
> > >> >> your environment.
> > >> >>
> > >> >> e.g.
> > >> >> --restrict to admin only. 1=yes, 0=no
> > >> >> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> > >> >> go
> > >> >>
> > >> >>
> > >> >>
> > >> >> --
> > >> >> -oj
> > >> >>
> > >> >>
> > >> >> "André" <Andr@.discussions.microsoft.com> wrote in message
> > >> >> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> > >> >> > Hi, I now this question has been posted a few times, but I did not
> > >> >> > find
> > >> >> > an
> > >> >> > answer.
> > >> >> >
> > >> >> > I have a linked server on my SQL server to an access database via a
> > >> >> > mapped
> > >> >> > drive on a domain. (2 computers, one with SQL server and the other
> > >> >> > has
> > >> >> > the
> > >> >> > access database)
> > >> >> >
> > >> >> > When I log in to query analyzer using windows authentication I run a
> > >> >> > stored
> > >> >> > procedure to query data from the access database via a linked
> > >> >> > server,
> > >> >> > everything works fine.
> > >> >> >
> > >> >> > When I log in as SA, I get this error"It is already opened
> > >> >> > exclusively
> > >> >> > by
> > >> >> > another user, or you need permission to view its data"
> > >> >> >
> > >> >> > The same is with the DTS package, if I right click the package and
> > >> >> > execute
> > >> >> > the DTS, it works. But if I create a job to call the DTS it gives me
> > >> >> > the
> > >> >> > same
> > >> >> > error, even if I say that my domain username is the password.
> > >> >> >
> > >> >> > I even ran DTS via command prompt and that too works.
> > >> >> >
> > >> >> > I know it's some kind of permission level, but I don't know what to
> > >> >> > do.
> > >> >> > I changed my c drive (the one with the access database) security to
> > >> >> > everyone. I changed the access database security to everyone too.
> > >> >> >
> > >> >> > Any one knows how to fix this?
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||This is the error i get in query analyzer
Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
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 '\\mypath'. 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: ].
"André" wrote:
> I tried this on another Sql server and I get the same results. Do I have to
> be an Admin on the network for this to work?
> Andre
> "André" wrote:
> > I still get an error :(
> >
> > "oj" wrote:
> >
> > > Andre,
> > >
> > > How's about dropping and recreating the link like so:
> > >
> > > --Linked server to Access on Shared drive
> > > EXEC sp_addlinkedserver
> > > @.server = 'myAccess',
> > > @.provider = 'Microsoft.Jet.OLEDB.4.0',
> > > @.srvproduct = 'OLE DB Provider for Jet',
> > > @.datasrc = '\\ghost\ghost\oj\Access.mdb'
> > > GO
> > > --Everyone connects via Admin Access db user
> > > EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
> > > go
> > >
> > >
> > > --
> > > -oj
> > >
> > >
> > > "André" <Andr@.discussions.microsoft.com> wrote in message
> > > news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
> > > > Hi,
> > > >
> > > > I rebooted my server and I still get the same results. I did check to
> > > > make
> > > > sure that my proxy is still set. and it is.
> > > >
> > > > Thank you,
> > > >
> > > > Andre
> > > >
> > > > "oj" wrote:
> > > >
> > > >> Sorry for the late reply...Anyway, you might need to restart the service
> > > >> for
> > > >> this to take effect.
> > > >>
> > > >> --
> > > >> -oj
> > > >>
> > > >>
> > > >> "André" <Andr@.discussions.microsoft.com> wrote in message
> > > >> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
> > > >> > HI,
> > > >> >
> > > >> > You are correct, I did not have my proxy set. After I changed both my
> > > >> > proxy
> > > >> > and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I
> > > >> > still
> > > >> > get
> > > >> > the same error.
> > > >> >
> > > >> > What else should I try?
> > > >> >
> > > >> >
> > > >> >
> > > >> > "oj" wrote:
> > > >> >
> > > >> >> Your sqlagent proxy is probably not set with proper access.
> > > >> >>
> > > >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
> > > >> >>
> > > >> >> Also, be aware that SP3 has added a new flag. You might have to set it
> > > >> >> to
> > > >> >> your environment.
> > > >> >>
> > > >> >> e.g.
> > > >> >> --restrict to admin only. 1=yes, 0=no
> > > >> >> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> > > >> >> go
> > > >> >>
> > > >> >>
> > > >> >>
> > > >> >> --
> > > >> >> -oj
> > > >> >>
> > > >> >>
> > > >> >> "André" <Andr@.discussions.microsoft.com> wrote in message
> > > >> >> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> > > >> >> > Hi, I now this question has been posted a few times, but I did not
> > > >> >> > find
> > > >> >> > an
> > > >> >> > answer.
> > > >> >> >
> > > >> >> > I have a linked server on my SQL server to an access database via a
> > > >> >> > mapped
> > > >> >> > drive on a domain. (2 computers, one with SQL server and the other
> > > >> >> > has
> > > >> >> > the
> > > >> >> > access database)
> > > >> >> >
> > > >> >> > When I log in to query analyzer using windows authentication I run a
> > > >> >> > stored
> > > >> >> > procedure to query data from the access database via a linked
> > > >> >> > server,
> > > >> >> > everything works fine.
> > > >> >> >
> > > >> >> > When I log in as SA, I get this error"It is already opened
> > > >> >> > exclusively
> > > >> >> > by
> > > >> >> > another user, or you need permission to view its data"
> > > >> >> >
> > > >> >> > The same is with the DTS package, if I right click the package and
> > > >> >> > execute
> > > >> >> > the DTS, it works. But if I create a job to call the DTS it gives me
> > > >> >> > the
> > > >> >> > same
> > > >> >> > error, even if I say that my domain username is the password.
> > > >> >> >
> > > >> >> > I even ran DTS via command prompt and that too works.
> > > >> >> >
> > > >> >> > I know it's some kind of permission level, but I don't know what to
> > > >> >> > do.
> > > >> >> > I changed my c drive (the one with the access database) security to
> > > >> >> > everyone. I changed the access database security to everyone too.
> > > >> >> >
> > > >> >> > Any one knows how to fix this?
> > > >> >> >
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||Sorry for the late reply...I've been out of town.
Anyhow, if you use this code:
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
you essentially allow all users to connect via sqlserver service account.
You will have to check to make sure sqlserver is not started by LocalSystem.
This special NT account does not have access to network resources. In this
case, it's your Access file on a network shared.
Btw, you might want to check MS KB. I remember seeing an article which
details steps to resolve error 7399.
--
-oj
"André" <Andr@.discussions.microsoft.com> wrote in message
news:4301AD8D-A3DC-42BC-80EC-21B3AFEFFB32@.microsoft.com...
> This is the error i get in query analyzer
> Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
> 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 '\\mypath'. 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: ].
>
> "André" wrote:
>> I tried this on another Sql server and I get the same results. Do I have
>> to
>> be an Admin on the network for this to work?
>> Andre
>> "André" wrote:
>> > I still get an error :(
>> >
>> > "oj" wrote:
>> >
>> > > Andre,
>> > >
>> > > How's about dropping and recreating the link like so:
>> > >
>> > > --Linked server to Access on Shared drive
>> > > EXEC sp_addlinkedserver
>> > > @.server = 'myAccess',
>> > > @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> > > @.srvproduct = 'OLE DB Provider for Jet',
>> > > @.datasrc = '\\ghost\ghost\oj\Access.mdb'
>> > > GO
>> > > --Everyone connects via Admin Access db user
>> > > EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
>> > > go
>> > >
>> > >
>> > > --
>> > > -oj
>> > >
>> > >
>> > > "André" <Andr@.discussions.microsoft.com> wrote in message
>> > > news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
>> > > > Hi,
>> > > >
>> > > > I rebooted my server and I still get the same results. I did check
>> > > > to
>> > > > make
>> > > > sure that my proxy is still set. and it is.
>> > > >
>> > > > Thank you,
>> > > >
>> > > > Andre
>> > > >
>> > > > "oj" wrote:
>> > > >
>> > > >> Sorry for the late reply...Anyway, you might need to restart the
>> > > >> service
>> > > >> for
>> > > >> this to take effect.
>> > > >>
>> > > >> --
>> > > >> -oj
>> > > >>
>> > > >>
>> > > >> "André" <Andr@.discussions.microsoft.com> wrote in message
>> > > >> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
>> > > >> > HI,
>> > > >> >
>> > > >> > You are correct, I did not have my proxy set. After I changed
>> > > >> > both my
>> > > >> > proxy
>> > > >> > and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
>> > > >> > I
>> > > >> > still
>> > > >> > get
>> > > >> > the same error.
>> > > >> >
>> > > >> > What else should I try?
>> > > >> >
>> > > >> >
>> > > >> >
>> > > >> > "oj" wrote:
>> > > >> >
>> > > >> >> Your sqlagent proxy is probably not set with proper access.
>> > > >> >>
>> > > >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
>> > > >> >>
>> > > >> >> Also, be aware that SP3 has added a new flag. You might have to
>> > > >> >> set it
>> > > >> >> to
>> > > >> >> your environment.
>> > > >> >>
>> > > >> >> e.g.
>> > > >> >> --restrict to admin only. 1=yes, 0=no
>> > > >> >> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
>> > > >> >> go
>> > > >> >>
>> > > >> >>
>> > > >> >>
>> > > >> >> --
>> > > >> >> -oj
>> > > >> >>
>> > > >> >>
>> > > >> >> "André" <Andr@.discussions.microsoft.com> wrote in message
>> > > >> >> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
>> > > >> >> > Hi, I now this question has been posted a few times, but I
>> > > >> >> > did not
>> > > >> >> > find
>> > > >> >> > an
>> > > >> >> > answer.
>> > > >> >> >
>> > > >> >> > I have a linked server on my SQL server to an access database
>> > > >> >> > via a
>> > > >> >> > mapped
>> > > >> >> > drive on a domain. (2 computers, one with SQL server and the
>> > > >> >> > other
>> > > >> >> > has
>> > > >> >> > the
>> > > >> >> > access database)
>> > > >> >> >
>> > > >> >> > When I log in to query analyzer using windows authentication
>> > > >> >> > I run a
>> > > >> >> > stored
>> > > >> >> > procedure to query data from the access database via a linked
>> > > >> >> > server,
>> > > >> >> > everything works fine.
>> > > >> >> >
>> > > >> >> > When I log in as SA, I get this error"It is already opened
>> > > >> >> > exclusively
>> > > >> >> > by
>> > > >> >> > another user, or you need permission to view its data"
>> > > >> >> >
>> > > >> >> > The same is with the DTS package, if I right click the
>> > > >> >> > package and
>> > > >> >> > execute
>> > > >> >> > the DTS, it works. But if I create a job to call the DTS it
>> > > >> >> > gives me
>> > > >> >> > the
>> > > >> >> > same
>> > > >> >> > error, even if I say that my domain username is the password.
>> > > >> >> >
>> > > >> >> > I even ran DTS via command prompt and that too works.
>> > > >> >> >
>> > > >> >> > I know it's some kind of permission level, but I don't know
>> > > >> >> > what to
>> > > >> >> > do.
>> > > >> >> > I changed my c drive (the one with the access database)
>> > > >> >> > security to
>> > > >> >> > everyone. I changed the access database security to everyone
>> > > >> >> > too.
>> > > >> >> >
>> > > >> >> > Any one knows how to fix this?
>> > > >> >> >
>> > > >> >>
>> > > >> >>
>> > > >> >>
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >

Linked SQL Server to Access

Hi, I now this question has been posted a few times, but I did not find an
answer.
I have a linked server on my SQL server to an access database via a mapped
drive on a domain. (2 computers, one with SQL server and the other has the
access database)
When I log in to query analyzer using windows authentication I run a stored
procedure to query data from the access database via a linked server,
everything works fine.
When I log in as SA, I get this error”It is already opened exclusively by
another user, or you need permission to view its data”
The same is with the DTS package, if I right click the package and execute
the DTS, it works. But if I create a job to call the DTS it gives me the same
error, even if I say that my domain username is the password.
I even ran DTS via command prompt and that too works.
I know it’s some kind of permission level, but I don’t know what to do.
I changed my c drive (the one with the access database) security to
everyone. I changed the access database security to everyone too.
Any one knows how to fix this?
Your sqlagent proxy is probably not set with proper access.
http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp
Also, be aware that SP3 has added a new flag. You might have to set it to
your environment.
e.g.
--restrict to admin only. 1=yes, 0=no
EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
go
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> Hi, I now this question has been posted a few times, but I did not find an
> answer.
> I have a linked server on my SQL server to an access database via a mapped
> drive on a domain. (2 computers, one with SQL server and the other has the
> access database)
> When I log in to query analyzer using windows authentication I run a
> stored
> procedure to query data from the access database via a linked server,
> everything works fine.
> When I log in as SA, I get this error"It is already opened exclusively by
> another user, or you need permission to view its data"
> The same is with the DTS package, if I right click the package and execute
> the DTS, it works. But if I create a job to call the DTS it gives me the
> same
> error, even if I say that my domain username is the password.
> I even ran DTS via command prompt and that too works.
> I know it's some kind of permission level, but I don't know what to do.
> I changed my c drive (the one with the access database) security to
> everyone. I changed the access database security to everyone too.
> Any one knows how to fix this?
>
|||HI,
You are correct, I did not have my proxy set. After I changed both my proxy
and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still get
the same error.
What else should I try?
"oj" wrote:

> Your sqlagent proxy is probably not set with proper access.
> http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp
> Also, be aware that SP3 has added a new flag. You might have to set it to
> your environment.
> e.g.
> --restrict to admin only. 1=yes, 0=no
> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
>
>
|||Sorry for the late reply...Anyway, you might need to restart the service for
this to take effect.
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...[vbcol=seagreen]
> HI,
> You are correct, I did not have my proxy set. After I changed both my
> proxy
> and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still
> get
> the same error.
> What else should I try?
>
> "oj" wrote:
|||Hi,
I rebooted my server and I still get the same results. I did check to make
sure that my proxy is still set. and it is.
Thank you,
Andre
"oj" wrote:

> Sorry for the late reply...Anyway, you might need to restart the service for
> this to take effect.
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
>
>
|||Andre,
How's about dropping and recreating the link like so:
--Linked server to Access on Shared drive
EXEC sp_addlinkedserver
@.server = 'myAccess',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = '\\ghost\ghost\oj\Access.mdb'
GO
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
go
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I rebooted my server and I still get the same results. I did check to
> make
> sure that my proxy is still set. and it is.
> Thank you,
> Andre
> "oj" wrote:
|||I still get an error
"oj" wrote:

> Andre,
> How's about dropping and recreating the link like so:
> --Linked server to Access on Shared drive
> EXEC sp_addlinkedserver
> @.server = 'myAccess',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = '\\ghost\ghost\oj\Access.mdb'
> GO
> --Everyone connects via Admin Access db user
> EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
>
>
|||I tried this on another Sql server and I get the same results. Do I have to
be an Admin on the network for this to work?
Andre
"André" wrote:
[vbcol=seagreen]
> I still get an error
> "oj" wrote:
|||This is the error i get in query analyzer
Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
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 '\\mypath'. 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: ].
"André" wrote:
[vbcol=seagreen]
> I tried this on another Sql server and I get the same results. Do I have to
> be an Admin on the network for this to work?
> Andre
> "André" wrote:
|||Sorry for the late reply...I've been out of town.
Anyhow, if you use this code:
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
you essentially allow all users to connect via sqlserver service account.
You will have to check to make sure sqlserver is not started by LocalSystem.
This special NT account does not have access to network resources. In this
case, it's your Access file on a network shared.
Btw, you might want to check MS KB. I remember seeing an article which
details steps to resolve error 7399.
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:4301AD8D-A3DC-42BC-80EC-21B3AFEFFB32@.microsoft.com...[vbcol=seagreen]
> This is the error i get in query analyzer
> Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
> 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 '\\mypath'. 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: ].
>
> "Andr" wrote:

Linked SQL Server to Access

Hi, I now this question has been posted a few times, but I did not find an
answer.
I have a linked server on my SQL server to an access database via a mapped
drive on a domain. (2 computers, one with SQL server and the other has the
access database)
When I log in to query analyzer using windows authentication I run a stored
procedure to query data from the access database via a linked server,
everything works fine.
When I log in as SA, I get this error”It is already opened exclusively by
another user, or you need permission to view its data”
The same is with the DTS package, if I right click the package and execute
the DTS, it works. But if I create a job to call the DTS it gives me the sam
e
error, even if I say that my domain username is the password.
I even ran DTS via command prompt and that too works.
I know it’s some kind of permission level, but I don’t know what to do.
I changed my c drive (the one with the access database) security to
everyone. I changed the access database security to everyone too.
Any one knows how to fix this?Your sqlagent proxy is probably not set with proper access.
http://msdn.microsoft.com/library/d.../>
sz_8sdm.asp
Also, be aware that SP3 has added a new flag. You might have to set it to
your environment.
e.g.
--restrict to admin only. 1=yes, 0=no
EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
go
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> Hi, I now this question has been posted a few times, but I did not find an
> answer.
> I have a linked server on my SQL server to an access database via a mapped
> drive on a domain. (2 computers, one with SQL server and the other has the
> access database)
> When I log in to query analyzer using windows authentication I run a
> stored
> procedure to query data from the access database via a linked server,
> everything works fine.
> When I log in as SA, I get this error"It is already opened exclusively by
> another user, or you need permission to view its data"
> The same is with the DTS package, if I right click the package and execute
> the DTS, it works. But if I create a job to call the DTS it gives me the
> same
> error, even if I say that my domain username is the password.
> I even ran DTS via command prompt and that too works.
> I know it's some kind of permission level, but I don't know what to do.
> I changed my c drive (the one with the access database) security to
> everyone. I changed the access database security to everyone too.
> Any one knows how to fix this?
>|||HI,
You are correct, I did not have my proxy set. After I changed both my proxy
and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still get
the same error.
What else should I try?
"oj" wrote:

> Your sqlagent proxy is probably not set with proper access.
> http://msdn.microsoft.com/library/d...
a-sz_8sdm.asp
> Also, be aware that SP3 has added a new flag. You might have to set it to
> your environment.
> e.g.
> --restrict to admin only. 1=yes, 0=no
> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
>
>|||Sorry for the late reply...Anyway, you might need to restart the service for
this to take effect.
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...[vbcol=seagreen]
> HI,
> You are correct, I did not have my proxy set. After I changed both my
> proxy
> and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still
> get
> the same error.
> What else should I try?
>
> "oj" wrote:
>|||Hi,
I rebooted my server and I still get the same results. I did check to make
sure that my proxy is still set. and it is.
Thank you,
Andre
"oj" wrote:

> Sorry for the late reply...Anyway, you might need to restart the service f
or
> this to take effect.
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
>
>|||Andre,
How's about dropping and recreating the link like so:
--Linked server to Access on Shared drive
EXEC sp_addlinkedserver
@.server = 'myAccess',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = '\\ghost\ghost\oj\Access.mdb'
GO
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
go
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I rebooted my server and I still get the same results. I did check to
> make
> sure that my proxy is still set. and it is.
> Thank you,
> Andre
> "oj" wrote:
>|||I still get an error
"oj" wrote:

> Andre,
> How's about dropping and recreating the link like so:
> --Linked server to Access on Shared drive
> EXEC sp_addlinkedserver
> @.server = 'myAccess',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = '\\ghost\ghost\oj\Access.mdb'
> GO
> --Everyone connects via Admin Access db user
> EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
>
>|||I tried this on another Sql server and I get the same results. Do I have to
be an Admin on the network for this to work?
Andre
"André" wrote:
[vbcol=seagreen]
> I still get an error
> "oj" wrote:
>|||This is the error i get in query analyzer
Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\mypath'. 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: ].
"André" wrote:
[vbcol=seagreen]
> I tried this on another Sql server and I get the same results. Do I have
to
> be an Admin on the network for this to work?
> Andre
> "André" wrote:
>|||Sorry for the late reply...I've been out of town.
Anyhow, if you use this code:
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
you essentially allow all users to connect via sqlserver service account.
You will have to check to make sure sqlserver is not started by LocalSystem.
This special NT account does not have access to network resources. In this
case, it's your Access file on a network shared.
Btw, you might want to check MS KB. I remember seeing an article which
details steps to resolve error 7399.
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:4301AD8D-A3DC-42BC-80EC-21B3AFEFFB32@.microsoft.com...[vbcol=seagreen]
> This is the error i get in query analyzer
> Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
> 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 '\\mypath'. 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: ].
>
> "Andr" wrote:
>

servers to UDB via IBMDADB2, access denied

A linked server definition in MSSQL 2000, can succcessfully authenticates
with a UDB database v8.1.0.61 on a SUN box, but then gets access denied,
eventhough the account being used has DBADM authority. However, I can
successfully authenticate and query the same UDB databases from a DOS DB2
command prompt on the same windows 2003 server.
See example below and results:
On the windoes 2003 server...
set nocount on
EXEC sp_addlinkedserver
@.server = 'srvr1833',
@.srvproduct = 'aeplm148',
@.provider = 'IBMDADB2',
@.datasrc = 'aeplm148',
@.location = 'srvr1833.dbms.gdomain.com:18330',
@.catalog = 'aeplm148'
go
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'srvr1833',
@.useself = false,
@.rmtuser = 'User1',
@.rmtpassword = 'Password1'
go
EXEC sp_serveroption
@.server = 'srvr1833',
@.optname = 'data access',
@.optvalue = 'true'
go
SELECT *
FROM srvr1833.aepcclm148.epl.mline
Results:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDADB2' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'IBMDADB2' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
Yet on a DOS command line using db2...
db2 => connect to aeplm148 user User1 using Password1
Database Connection Information
Database server = DB2/SUN 8.1.6
SQL authorization ID = DGIEPL
Local database alias = AEPLM148
db2 => list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = AEPLM148
Database name = AEPLM148
Node name = XX
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
db2 => list node directory show detail
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = XX
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = srvr1833.dbms.gdomain.com
Service name = 18330
Remote instance name =
System =
Operating system type = None
db2 => select * from aeplm148.epl.mline fetch first 2 rows only
I_PLANT_PL I_STDY I_PRES_LINE I_PRES_TYP N_LINE_NAME
L_PLAN I_LOAD_TYP I_AUTOM_TYP I_PRES_LINE_SIZE I_PRES_MECHNM_TYP
L_
TCH_PL_LINE_TO_PLT_LINE Q_SHUT_HGT Q_NMBR_OF_STATNS I_UPD_TID D_UPD_LAST
X_COMN
More results followed but not pasted...
Can anybody offer some suggestions?
I've run out of ideas.
I know the linked server is authenticating properly because when I put in a
bad password, the error message returned says, bad password or userid.
One would assume that the problem is on the UDB server only. Yet I can
access the data from the Windows 2003 server using the UDB client in a DOS
prompt.
Both techniques authenticate properly. Only the linked server gets access
denied.
The driver version on the Windows 2003 Server is DB2 v8.1.12.99A clue was found.
This will work when you remote desktop to the server and log in as either
the administrators account to the account in which the database engine is
running under. But it will not work with any other account, even if that
account is part of the administrators group.
What is different about the "Administrator" account from an a account that
is simply a member of the Administor"s" group?
The account used by the sqlserver engine is also part of the administrators
group.
"DBA449" wrote:

> A linked server definition in MSSQL 2000, can succcessfully authenticates
> with a UDB database v8.1.0.61 on a SUN box, but then gets access denied,
> eventhough the account being used has DBADM authority. However, I can
> successfully authenticate and query the same UDB databases from a DOS DB2
> command prompt on the same windows 2003 server.
> See example below and results:
> On the windoes 2003 server...
> set nocount on
> EXEC sp_addlinkedserver
> @.server = 'srvr1833',
> @.srvproduct = 'aeplm148',
> @.provider = 'IBMDADB2',
> @.datasrc = 'aeplm148',
> @.location = 'srvr1833.dbms.gdomain.com:18330',
> @.catalog = 'aeplm148'
> go
> EXEC sp_addlinkedsrvlogin
> @.rmtsrvname = 'srvr1833',
> @.useself = false,
> @.rmtuser = 'User1',
> @.rmtpassword = 'Password1'
> go
> EXEC sp_serveroption
> @.server = 'srvr1833',
> @.optname = 'data access',
> @.optvalue = 'true'
> go
> SELECT *
> FROM srvr1833.aepcclm148.epl.mline
> Results:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'IBMDADB2' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'IBMDADB2' IUnknown::QueryInterfac
e
> returned 0x80070005: Access denied.].
>
> Yet on a DOS command line using db2...
>
> DB2 => connect to aeplm148 user User1 using Password1
> Database Connection Information
> Database server = DB2/SUN 8.1.6
> SQL authorization ID = DGIEPL
> Local database alias = AEPLM148
> DB2 => list database directory
> System Database Directory
> Number of entries in the directory = 1
> Database 1 entry:
> Database alias = AEPLM148
> Database name = AEPLM148
> Node name = XX
> Database release level = a.00
> Comment =
> Directory entry type = Remote
> Catalog database partition number = -1
> Alternate server hostname =
> Alternate server port number =
> DB2 => list node directory show detail
> Node Directory
> Number of entries in the directory = 1
> Node 1 entry:
> Node name = XX
> Comment =
> Directory entry type = LOCAL
> Protocol = TCPIP
> Hostname = srvr1833.dbms.gdomain.com
> Service name = 18330
> Remote instance name =
> System =
> Operating system type = None
> DB2 => select * from aeplm148.epl.mline fetch first 2 rows only
> I_PLANT_PL I_STDY I_PRES_LINE I_PRES_TYP N_LINE_NAME
> L_PLAN I_LOAD_TYP I_AUTOM_TYP I_PRES_LINE_SIZE I_PRES_MECHNM_T
YP
> L_
> TCH_PL_LINE_TO_PLT_LINE Q_SHUT_HGT Q_NMBR_OF_STATNS I_UPD_TID D_UPD_LAST
> X_COMN
> More results followed but not pasted...
> Can anybody offer some suggestions?
> I've run out of ideas.
> I know the linked server is authenticating properly because when I put in
a
> bad password, the error message returned says, bad password or userid.
> One would assume that the problem is on the UDB server only. Yet I can
> access the data from the Windows 2003 server using the UDB client in a DOS
> prompt.
> Both techniques authenticate properly. Only the linked server gets access
> denied.
> The driver version on the Windows 2003 Server is DB2 v8.1.12.99
>sql

servers to DB2 and AS400

Hi!
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.

I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to

configure DB2OLEDB drivers.

Could any one suggest how do go about doing this.

Do I need to buy Host integration server or is there any other way to do it.

your help is much appreciated

NalinaThere are more ways to skin a cat than there are cats. I can think of several ways to do this, from several different vendors.

My suggestion would be to approach your vendor of choice (probably IBM in this case), and ask them to propose a solution. If that solution works for you, and is affordable, I'd run with it. If not, open up the bidding to at least two more vendors (and inform your vendor of choice, of course), then see what happens.

The biggest problem that I see is that when you are doing this kind of integration, there are many factors that come into play. Without considerable knowledge of your circumstances it would be easy to lead you astray and cost you an enormous amount of extra money or create a solution that was only part of what you need.

-PatP

servers SQL2000 to SQL2005

Hi. I am struggling to link a SQL2005 box to a SQL2000 box via linked server.
I have done this before and am replicating what I did then but I can`t get it
to connect. However when I connect via query analyser using the same
credentials it connects with no issues. Has anyone seen this before ?
Si
Si,
Is there any difference in the Security tab of the linked server properties
from your model linked server?
Is Kerberos properly activated on both the SQL2005 and SQL200 boxes? (What
OS?)
Do you get any useful error messages when you try to use the linked server?
RLF
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:E71D6BEE-B7AC-4D6A-B48F-611AFAE89FE3@.microsoft.com...
> Hi. I am struggling to link a SQL2005 box to a SQL2000 box via linked
> server.
> I have done this before and am replicating what I did then but I can`t get
> it
> to connect. However when I connect via query analyser using the same
> credentials it connects with no issues. Has anyone seen this before ?
> Si

servers SQL2000 to SQL2005

Hi. I am struggling to link a SQL2005 box to a SQL2000 box via linked server
.
I have done this before and am replicating what I did then but I can`t get i
t
to connect. However when I connect via query analyser using the same
credentials it connects with no issues. Has anyone seen this before ?
SiSi,
Is there any difference in the Security tab of the linked server properties
from your model linked server?
Is Kerberos properly activated on both the SQL2005 and SQL200 boxes? (What
OS?)
Do you get any useful error messages when you try to use the linked server?
RLF
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:E71D6BEE-B7AC-4D6A-B48F-611AFAE89FE3@.microsoft.com...
> Hi. I am struggling to link a SQL2005 box to a SQL2000 box via linked
> server.
> I have done this before and am replicating what I did then but I can`t get
> it
> to connect. However when I connect via query analyser using the same
> credentials it connects with no issues. Has anyone seen this before ?
> Si

servers SQL2000 to SQL2005

Hi. I am struggling to link a SQL2005 box to a SQL2000 box via linked server.
I have done this before and am replicating what I did then but I can`t get it
to connect. However when I connect via query analyser using the same
credentials it connects with no issues. Has anyone seen this before ?
SiSi,
Is there any difference in the Security tab of the linked server properties
from your model linked server?
Is Kerberos properly activated on both the SQL2005 and SQL200 boxes? (What
OS?)
Do you get any useful error messages when you try to use the linked server?
RLF
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:E71D6BEE-B7AC-4D6A-B48F-611AFAE89FE3@.microsoft.com...
> Hi. I am struggling to link a SQL2005 box to a SQL2000 box via linked
> server.
> I have done this before and am replicating what I did then but I can`t get
> it
> to connect. However when I connect via query analyser using the same
> credentials it connects with no issues. Has anyone seen this before ?
> Sisql

servers

I have a linked server connection on a SQL 2k server connected to a Sybase ASE 12.5 server via Sybase OLE DB connection. To this point everything has been working great for months. Now we're encountering a problem where new tables created on the Sybase db won't show up in the linked server connection. I've checked permissions and the object owners and everything is exactly the same as the pre-existing tables except the table's new.

Anyone have any ideas wht's up?Describe "won't show up" in a bit more detail. What exactly happens when you try to issue a SELECT statement against a new table?

-PatP|||When I say "won't show up" I mean you can't see the table in the table list in enterprise manager when I open the linked server. Here's the SQL and error message:

select i_con_contract from openquery([32tlsql2-dreamdb],
'Select i_con_contract From dbo.temp_client_3' )

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [Native Error code: 208]
[DataDirect ADO Sybase Provider] dbo.temp_client_3 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||What happens in Query Analyzer?

I suspect that this is another example of EM (SQL Enterprise Mangler) caching information, but never refreshing the cache.

-PatP|||The SQL and error message posted are from Query Analyzer.

I've even created a new linked server connection and the tables still won't show up. So I don't think it's a caching issue in EM.

Any other ideas? I appreciate the help.|||I was a bit corn-fused when you were talking about EM and posting SQL in the same message. I figured that somehow I must have "missed a meeting" in there somewhere.

Is there any chance that the Sybase objects are owned by a non-dbo user? Does the user being used by OPENQUERY have access to those objects when you use Sybase tools (like ISQL) to try to access them?

-PatP|||Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.|||Well, at least for now I'm stumped. I'm sure that come 03:30 I'll have a bright idea, but right now I'm fresh out. Sorry.

-PatP|||Originally posted by peterlemonjello
Nope, they're owned by dbo and every user and group in the Sybase db have select, insert, update, and delete permissions on the tables.

What's the linked server login?

Did you grant right to it on the new tables?|||Yep, the login the linked server is using has explicit permissions set on every table in the db. I've even changed to login to sa and still can't see the tables.|||I'm stuck...did you stop and restart EM?

Can you query them in QA?

Hey Pat, it's past 3:30...EST

Is there a way to see the linked server catalogs?|||Yep, I've rebooted the SQL Server. I can see the catalog using the sp_tables_ex proc and by looking in the sysremote_tables table in the master db. They're missing the new tables too. I have no freakin idea what's going on. I've turned the trace on in the OLEDB properties to try and isolate how SQL Server gets the table list from Sybase. Unfortuantely, when I refresh the tables in EM nothing appears in the OLEDB trace output.|||OK, let's get stupid (since I'm already there)

Can you create a new linked server with the same code?

Did you do it with code or through EM?|||I did it through EM. I have two SQL Server instances on seperate servers that are having the same problem. The only common denominator is the target Sybase server. I can't duplicate the problem against any other Sybase servers. I'm going to reboot the Sybase server tonight to see if that clears anything up. I'll let you know how it goes.|||Good Luck...

Time for a 'rita...

Later...|||Originally posted by Brett Kaiser
Hey Pat, it's past 3:30...EST

Is there a way to see the linked server catalogs? Not hardly, it was just past 15:30 EST when you posted! I really meant 03:30!

-PatP|||all righty then...

Hey Peter, did bouncing the box help?|||OK, I found the problem. There's a bug in Sybase's OLEDB provider. Luckily there's a patch out for the bug.

If you've setup more than one OLEDB profile in the Configuration Manager the first profile that's setup will apply to all additional profiles. No matter what properties are specified in the additional profiles settings.

For example create Profile1 that connects to 10.5.1.4 port 7682.
Cretae Profile2 and specify server 10.5.1.5 port 7680. Eventhough the properties are set correctly on Profile2 it will always connect to 10.5.1.4 port 7682. Also, any addiotional profiles will connect the profile that was created first in the Configuration Manager.

I couldn't see the new tables (in my test db) because my OLEDB connection was logging into a different server (production). Which happen to have the same schema except for the new tables.|||Wow! That's a pretty good one.

-PatP

servers

Hello,
Is there any limit to the number of linked servers i can add via EM?. where can i find more info on adding/configuring linked servers in MSSQL2k and the limitations/drawbacks if any?
Thanks in advance..one limitation of linked servers I discovered recently is that loopback scenarios are not supported by MS, even though they often work. what I mean by loopback is a LS that points to itself.

One case I know that is broken in a loopback scenario is this:

INSERT INTO localtable EXEC LoopbackLinkedServer.dbo.RemoteSproc

as is detailed here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=124137&siteid=1

Linked servers are very useful. why couldn't MS get the obvious use case of loopback to work? :(

I should note that the case above is broken on 2005. it may work on 2000, I haven't tried it.

server, stored procedure and Views (Heterogeneous Errors)

I have linked to another SQL Instance, created a view and a stored procedure
to access it. This works great through MS Query, but not via SQL Triggers or
through calling through code in another application.
The views and sprocs create without errors, it's only when running them.
Any Ideas?Ray
Can you show us how you call the statemnet?
"Ray" <rayc@.rsc.com> wrote in message
news:FEEE24DA-571B-4C46-887B-33D4F772553E@.microsoft.com...
>I have linked to another SQL Instance, created a view and a stored
>procedure
> to access it. This works great through MS Query, but not via SQL Triggers
> or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?|||you will have to use the four part naming convention
linkedservername.database.owner.object
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ray" wrote:

> I have linked to another SQL Instance, created a view and a stored procedu
re
> to access it. This works great through MS Query, but not via SQL Triggers
or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?

server via secure connection

I am trying to setup a linked server to a MySQL database over at my hosting company.
I would like the communication between the SQL server and the MySQL database to be via a secure connection. anyone know how to do this?
my DB is SQL 2000Do you just want to manage the remote MySQL from your local console, or you want your SQL Server to perform actions over the remote MySQL as part of its work?|||basically, i would like to do selects and updates to the MySQL server with data from the SQL server.|||First you need a crtificate server (could be internal) and have both machines obtain certificates from it. I know there is a Certificate Server included in the MSDN subscription but I don't know how exactly to set it up. Shouldn't be a big deal.

Then in the Client Network Utility on the SQL Server machine check the Force Protocol Encryption box.|||thanks, i will try that

server via ODBC and stored procedure

Hi

We're trying to use call a stored procedure to update information in a
remote Ingres database. We've linked the server, and can read
information using SELECT * FROM OPENQUERY (........), but we can't
find a suitable syntax for executing a procedure.

Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
string gives a message about not returning any columns - not surprising
as there aren't any, and trying to execute the procedure more directly
using:-

EXECUTE abrs..vipdba.ats_reader_pi0 ......

Gives the error

Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
[OLE/DB provider returned message: Parameter type cannot be determined
for at least one variant parameter.]

Any bright ideas?

Chloe(chloe.crowder@.bl.uk) writes:
> We're trying to use call a stored procedure to update information in a
> remote Ingres database. We've linked the server, and can read
> information using SELECT * FROM OPENQUERY (........), but we can't
> find a suitable syntax for executing a procedure.
> Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
> string gives a message about not returning any columns - not surprising
> as there aren't any, and trying to execute the procedure more directly
> using:-
> EXECUTE abrs..vipdba.ats_reader_pi0 ......
> Gives the error
> Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
> [OLE/DB provider returned message: Parameter type cannot be determined
> for at least one variant parameter.]

Assuming that you are on SQL 2000:

Does the procedure have any "difficult" parameters?

First of all, I would examine whether there is an OLE DB provider
for Ingres, rather than using the MSDASQL provider.

If there is no OLE DB provider available, I would first try a parameterless
stored procedure. If this fails, then it seems that the ODBC driver
have problems to retrieve parameter information at all.

If there is a tool similar to Profiler on the Ingres side, you could
use that to see what calls the ODBC driver makes.

One thing that looks suspicious to me is that the third component is
empty, but I don't know Ingres, so this may be alright.

Unfortuantely, linked servers to other products can be a bit of trial
and error. There is a generic OLE DB layer which you have little control
over.

If you are on SQL 2005, there may be an easy way out. To wit you
can say:

EXEC('ingres-SQL here') AT abrs

to send a pass-through query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

server via ODBC - help!

Hi All
I have set up several linked SQL servers which work ok - but I'm having
trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
The ODBC points to an 'Alchemy' database and uses an Alchemy driver
ironically i can set up linked tables in an Access DB, via ODBC - and I can
pull the same data into Excel via ODBC - so there is no problem connecting t
o
Alchemy using other MS apps.
When I add the linked server in SQL, it appears to be added but no tables
are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
returns an error "Cannot initialize the data source object of OLE DB provide
r
'MSDASQL' "
Its driving me crazy...Anyone have any ideas'
Thanks in advanceMake sure the driver is compatible with the MDAC version on
the SQL Server box. Make sure you are using the latest
driver. Make sure the driver is correctly installed on the
SQL Server box.
Sometimes those issues can be related to issues specific to
the driver and interactions with service accounts. Try
logging onto the server using the service account for SQL
Server and install the driver while logged in under the
service account SQL Server is running under.
-Sue
On Thu, 28 Sep 2006 15:29:02 -0700, grasshopper
<nospam@.nospam.com> wrote:

>Hi All
>I have set up several linked SQL servers which work ok - but I'm having
>trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
>The ODBC points to an 'Alchemy' database and uses an Alchemy driver
>ironically i can set up linked tables in an Access DB, via ODBC - and I can
>pull the same data into Excel via ODBC - so there is no problem connecting
to
>Alchemy using other MS apps.
>When I add the linked server in SQL, it appears to be added but no tables
>are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
>returns an error "Cannot initialize the data source object of OLE DB provid
er
>'MSDASQL' "
>Its driving me crazy...Anyone have any ideas'
>Thanks in advance