I am using a linked server to connect SQL Server to an Excel Spreadsheet on
the network. If I'm physically on the SQL Server box I can open the linked
server and see the named range on the desired spreadsheet. Also If I'm on
the SQL Server box I can query the spreadsheet using OPENQUERY and everythin
g
comes across beautifully.
However, if I connect to the SQL Server box from a different location (via
Enterprise Manager) and try to run a query using the OPENQUERY and linked
server in question. I keep getting the dreaded "error 7399: blah, blah,
blah".
Any thoughts?
Thanks in advance,
RaulYour proxy account does not have access to network resources.
See this article for more info:
http://msdn.microsoft.com/library/d.../>
sz_8sdm.asp
-oj
"Raul" <raul@.nothere.com> wrote in message
news:6AD242AB-BD43-49B2-AC3D-119EC788CF91@.microsoft.com...
>I am using a linked server to connect SQL Server to an Excel Spreadsheet on
> the network. If I'm physically on the SQL Server box I can open the
> linked
> server and see the named range on the desired spreadsheet. Also If I'm on
> the SQL Server box I can query the spreadsheet using OPENQUERY and
> everything
> comes across beautifully.
> However, if I connect to the SQL Server box from a different location (via
> Enterprise Manager) and try to run a query using the OPENQUERY and linked
> server in question. I keep getting the dreaded "error 7399: blah, blah,
> blah".
> Any thoughts?
> Thanks in advance,
> Raul|||Thanks,
Raul
"oj" wrote:
> Your proxy account does not have access to network resources.
> See this article for more info:
> http://msdn.microsoft.com/library/d...
a-sz_8sdm.asp
>
> --
> -oj
>
> "Raul" <raul@.nothere.com> wrote in message
> news:6AD242AB-BD43-49B2-AC3D-119EC788CF91@.microsoft.com...
>
>
Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts
Monday, February 20, 2012
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.
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.
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.
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 Excel
We've created a linked server to access an Excel spreadsheet that resides on
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides
on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provide
r
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...[vbcol=seagreen]
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
>
resides on[vbcol=seagreen]
2000)[vbcol=seagreen]
provider[vbcol=seagreen]
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides
on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provide
r
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...[vbcol=seagreen]
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
>
resides on[vbcol=seagreen]
2000)[vbcol=seagreen]
provider[vbcol=seagreen]
linked server to Excel
We've created a linked server to access an Excel spreadsheet that resides on
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>
|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...[vbcol=seagreen]
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
resides on[vbcol=seagreen]
2000)[vbcol=seagreen]
provider[vbcol=seagreen]
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>
|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...[vbcol=seagreen]
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
resides on[vbcol=seagreen]
2000)[vbcol=seagreen]
provider[vbcol=seagreen]
linked server to Excel
We've created a linked server to access an Excel spreadsheet that resides on
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
> > We've created a linked server to access an Excel spreadsheet that
resides on
> > the same server as SQL Server 2000. If we log on to the database server
> > (Windows 2000), then launch Enterprise Manager, the linked server works
> > perfectly.
> >
> > But, if we launch Enterprise Manager from a desktop machine (Windows
2000)
> > and attempt to use the linked server we get: "Error 7399 : OLE DB
provider
> > 'Microsoft.Jet.OLEDB.4.0' reported an error."
> >
> > Any ideas?
> >
> >
> >
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
> > We've created a linked server to access an Excel spreadsheet that
resides on
> > the same server as SQL Server 2000. If we log on to the database server
> > (Windows 2000), then launch Enterprise Manager, the linked server works
> > perfectly.
> >
> > But, if we launch Enterprise Manager from a desktop machine (Windows
2000)
> > and attempt to use the linked server we get: "Error 7399 : OLE DB
provider
> > 'Microsoft.Jet.OLEDB.4.0' reported an error."
> >
> > Any ideas?
> >
> >
> >
Subscribe to:
Posts (Atom)