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 cluster fails (sometimes)

Howdy.
Here's the question: why does my linked server function properly only part
of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a
database that is on the same server as the linked server, but I want to redi
rect the link to another copy of the database on a *cluster* elsewhere on th
e network. I am trying to t
est this by setting up a link to the production database on a test SQL Serve
r.
The linked server is used by another database-I will call this the "primary"
database. Generally the primary database is accessed via stored procedure c
alls and in some cases these stored procedures invoke stored procedures that
exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a re
ad-only SQL user that has appropriate permissions in the pertinent database
on the cluster.
The SQL Server on which I have created the linked server is *not* my develop
ment box. When I use Enterprise Manager running on my development box and cl
ick on "Tables" under the linked server, I get this error: "Error 17: SQL Se
rver does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via
Terminal Services) and do the same thing I am able to view the list of table
s in the linked database with no error.
I also have two .NET applications that touch the linked server. These applic
ations invoke the same stored procedures within the primary database which i
n turn remotely invoke the same stored procedures via the linked server. One
of these applications is a
"rich client" application that uses Windows forms controls deployed onto a w
eb page via Fusion technology and the other is a standard web application (A
SP.NET). The rich client application gets its data remotely via calls to a w
eb service while the data
access tier for the web application runs in the same process space as the ap
plication's server side code. The rich client application accesses data via
the linked server correctly while the web application reports the same error
as the one reported when I
try to view the linked tables on my development box via Enterprise Manager (
"SQL Server does not exist or access denied"). I need to get both of these
applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
MattHey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Allan:
Thank you for your help. I will forward your comments to the DBA who is hel
ping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:

> Some things to think about :
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the
virtual server you mention. I apologize for being unfamiliar with clusters
, but this is my first exposure to them.

> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage conn
ections made via the Linked Server? I'm assuming that, if a failover occurs
either in the middle of a call across the linked server or just before the
call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failov
er occurs functionality will be restored to the linked server since it was s
et up pointing to the virtual server. Is this correct?

> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.

> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my kno
wledge, no failovers have occurred during my testing. There seems to be som
ething different (under the seams) about the way my two applications use the
linked server, and this is
what I'm having trouble identifying.

>
>|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the
DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover.
I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:

> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Linked Server to cluster fails (sometimes)

Howdy.
Here's the question: why does my linked server function properly only part of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a database that is on the same server as the linked server, but I want to redirect the link to another copy of the database on a *cluster* elsewhere on the network. I am trying to t
est this by setting up a link to the production database on a test SQL Server.
The linked server is used by another database-I will call this the "primary" database. Generally the primary database is accessed via stored procedure calls and in some cases these stored procedures invoke stored procedures that exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a read-only SQL user that has appropriate permissions in the pertinent database on the cluster.
The SQL Server on which I have created the linked server is *not* my development box. When I use Enterprise Manager running on my development box and click on "Tables" under the linked server, I get this error: "Error 17: SQL Server does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via Terminal Services) and do the same thing I am able to view the list of tables in the linked database with no error.
I also have two .NET applications that touch the linked server. These applications invoke the same stored procedures within the primary database which in turn remotely invoke the same stored procedures via the linked server. One of these applications is a
"rich client" application that uses Windows forms controls deployed onto a web page via Fusion technology and the other is a standard web application (ASP.NET). The rich client application gets its data remotely via calls to a web service while the data
access tier for the web application runs in the same process space as the application's server side code. The rich client application accesses data via the linked server correctly while the web application reports the same error as the one reported when I
try to view the linked tables on my development box via Enterprise Manager ("SQL Server does not exist or access denied"). I need to get both of these applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
Matt
Hey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Allan:
Thank you for your help. I will forward your comments to the DBA who is helping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:

> Some things to think about:
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the virtual server you mention. I apologize for being unfamiliar with clusters, but this is my first exposure to them.

> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage connections made via the Linked Server? I'm assuming that, if a failover occurs either in the middle of a call across the linked server or just before the call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failover occurs functionality will be restored to the linked server since it was set up pointing to the virtual server. Is this correct?

> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.

> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my knowledge, no failovers have occurred during my testing. There seems to be something different (under the seams) about the way my two applications use the linked server, and this is
what I'm having trouble identifying.

>
>
|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover. I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:

> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.