Linked server to Excel - Fails across network

I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
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 '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.
Make sure that your SQL Server services are running under a Domain User
account. Also, for that account, make sure that the Everyone group has FULL
CONTROL of the source share and underlying NTFS. Finally, make sure the
Everyone goup has FULL CONTROL of the temp directory for the SQL Server
service account. This is how Jet works.
Sincerely,
Anthony Thomas

"Dave Alvarado" <Dave Alvarado@.discussions.microsoft.com> wrote in message
news:95369ABE-EE31-4B69-A8BE-CA1222601A7F@.microsoft.com...
I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
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 '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on
devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.

Linked server to Excel - Fails across network

I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
--
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
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 '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.Make sure that your SQL Server services are running under a Domain User
account. Also, for that account, make sure that the Everyone group has FULL
CONTROL of the source share and underlying NTFS. Finally, make sure the
Everyone goup has FULL CONTROL of the temp directory for the SQL Server
service account. This is how Jet works.
Sincerely,
Anthony Thomas
"Dave Alvarado" <Dave Alvarado@.discussions.microsoft.com> wrote in message
news:95369ABE-EE31-4B69-A8BE-CA1222601A7F@.microsoft.com...
I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
--
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
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 '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on
devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.

Linked server to Excel - Fails across network

I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
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 '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on devbox03
.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.Make sure that your SQL Server services are running under a Domain User
account. Also, for that account, make sure that the Everyone group has FULL
CONTROL of the source share and underlying NTFS. Finally, make sure the
Everyone goup has FULL CONTROL of the temp directory for the SQL Server
service account. This is how Jet works.
Sincerely,
Anthony Thomas
"Dave Alvarado" <Dave Alvarado@.discussions.microsoft.com> wrote in message
news:95369ABE-EE31-4B69-A8BE-CA1222601A7F@.microsoft.com...
I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
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 '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on
devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.

Linked Server to DBC Data

Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset operati
on from SQL Server against once of the DBC files from one of our vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following e
rror...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************
Can someone advise?
Thanks!
TravisSorry, for clarification, a DBC file is a FoxPro Data file.
"REM7600" <rem7600@.hotmail.com> wrote in message news:%23mkG6JLiGHA.4044@.TK2
MSFTNGP03.phx.gbl...
Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset operati
on from SQL Server against once of the DBC files from one of our vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following e
rror...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************
Can someone advise?
Thanks!
Travis|||Hi Travis,
You haven't posted which version of SQL Server you are using or the code
you're using in the OpenRowset statement.
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I've successfully set up linked servers with code like this:
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_Northwind',
@.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
@.provider=N'VFPOLEDB',
@.datasrc=N'C:\Program Files\Microsoft Visual FoxPro
9\Samples\Northwind\Northwind.dbc',
@.provstr=N'VFPOLEDB.1'
After that you can access data with the four-part naming convention:
Select * From VFP_Northwind...Customers
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"REM7600" <rem7600@.hotmail.com> wrote in message
news:%23mkG6JLiGHA.4044@.TK2MSFTNGP03.phx.gbl...
Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset
operation from SQL Server against once of the DBC files from one of our
vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following
error...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o
Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************|||Cindy,
Thanks for the reply... I've definitely seen your name a time or two around
the web in my search. As it turns out, I believe it to be related to
security. And how our network (Linux/Samba based shares) handles
authentication. I could be wrong but I've given up... Locally I can make
it work fine, on a share NO GO...
Regardless, I've taken a different direction on the solution and it looks
like I'm going to be able to come out with my "goaled for" end result.
Thanks for the reply.
TR

> you're using in the OpenRowset statement.
> First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
> provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
> I've successfully set up linked servers with code like this:
> EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_Northwind',
> @.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
> @.provider=N'VFPOLEDB',
> @.datasrc=N'C:\Program Files\Microsoft Visual FoxPro
> 9\Samples\Northwind\Northwind.dbc',
> @.provstr=N'VFPOLEDB.1'
>