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.
Showing posts with label file. Show all posts
Showing posts with label file. 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 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 dBASE
I'm trying to create a "Linked Server" in QA to a dBASE file. Below are some
examples I've tried. They seem to create the "Linked Server" in Ent.
Manager, but give errors when i run a SELECT .. OPENQUERY statement. They
also give errors with Ent. Manager when trying to expand the linked server's
node.
The errors say the path is wrong, which it isn't. Other errors when clicking
"tables" under the linked server are "OLE DB" errors.
Does anyone have the correct syntax for creating a linked server to a dBASE
file? Which provider should I use?
-- Example 1:
Exec sp_addlinkedserver @.server ='alarm_dBASE',
@.srvproduct ='DBASE',
@.Provider ='Microsoft.Jet.OLEDB.4.0',
@.datasrc='E:\data\databases\myDBASEfile.dbf'
-- example 2
exec sp_addlinkedserver 'alarm_dBASE2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\data\databases\myDBASEfile.dbf',
NULL,
'dBase IV'
go
exec sp_addlinkedsrvlogin @.rmtsrvname='alarm_dBASE2',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = NULL,
@.rmtpassword= NULL
goscott (sbailey@.mileslumber.com) writes:
> I'm trying to create a "Linked Server" in QA to a dBASE file. Below are
> some examples I've tried. They seem to create the "Linked Server" in
> Ent. Manager, but give errors when i run a SELECT .. OPENQUERY
> statement. They also give errors with Ent. Manager when trying to expand
> the linked server's
> node.
> The errors say the path is wrong, which it isn't. Other errors when
> clicking "tables" under the linked server are "OLE DB" errors.
Note that the path is from the SQL Server machine, not on your local
machine. (Unless SQL Server runs on your local machine.)
Since I don't know anything about dBase, this is all I can contribute
with.
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|||i'm doing all of this from the server.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BCD2B8BF520Yazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Note that the path is from the SQL Server machine, not on your local
> machine. (Unless SQL Server runs on your local machine.)
> Since I don't know anything about dBase, this is all I can contribute
> with.
>
> --
> 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|||You're using the wrong driver so it cannot work. You're trying to use the
Jet engine (Microsoft Access .mdb engine).
You have an ODBC driver for .dbf files. I'm not shure if it works with Dbase
IV (most probably it'll do the trick).
Go to Control Panel - Data Sources (ODBC) and make a DSN.
change @.Provider to OLEDB for ODBC and supply your DSN (search the web on
exact syntax)
Milan|||Use "Microsoft OLE DB Provider for ODBC Drivers" instead of MS.Jet
NO "@.datasrc" but use provider string as of "Driver={Microsoft dBASE Driver
(*.dbf)};DriverID=21;Dbq=(you file, no filename);Mode=Read;
i.e. @.provstr = 'c:\' if you file is under c drive
examples I've tried. They seem to create the "Linked Server" in Ent.
Manager, but give errors when i run a SELECT .. OPENQUERY statement. They
also give errors with Ent. Manager when trying to expand the linked server's
node.
The errors say the path is wrong, which it isn't. Other errors when clicking
"tables" under the linked server are "OLE DB" errors.
Does anyone have the correct syntax for creating a linked server to a dBASE
file? Which provider should I use?
-- Example 1:
Exec sp_addlinkedserver @.server ='alarm_dBASE',
@.srvproduct ='DBASE',
@.Provider ='Microsoft.Jet.OLEDB.4.0',
@.datasrc='E:\data\databases\myDBASEfile.dbf'
-- example 2
exec sp_addlinkedserver 'alarm_dBASE2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\data\databases\myDBASEfile.dbf',
NULL,
'dBase IV'
go
exec sp_addlinkedsrvlogin @.rmtsrvname='alarm_dBASE2',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = NULL,
@.rmtpassword= NULL
goscott (sbailey@.mileslumber.com) writes:
> I'm trying to create a "Linked Server" in QA to a dBASE file. Below are
> some examples I've tried. They seem to create the "Linked Server" in
> Ent. Manager, but give errors when i run a SELECT .. OPENQUERY
> statement. They also give errors with Ent. Manager when trying to expand
> the linked server's
> node.
> The errors say the path is wrong, which it isn't. Other errors when
> clicking "tables" under the linked server are "OLE DB" errors.
Note that the path is from the SQL Server machine, not on your local
machine. (Unless SQL Server runs on your local machine.)
Since I don't know anything about dBase, this is all I can contribute
with.
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|||i'm doing all of this from the server.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BCD2B8BF520Yazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Note that the path is from the SQL Server machine, not on your local
> machine. (Unless SQL Server runs on your local machine.)
> Since I don't know anything about dBase, this is all I can contribute
> with.
>
> --
> 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|||You're using the wrong driver so it cannot work. You're trying to use the
Jet engine (Microsoft Access .mdb engine).
You have an ODBC driver for .dbf files. I'm not shure if it works with Dbase
IV (most probably it'll do the trick).
Go to Control Panel - Data Sources (ODBC) and make a DSN.
change @.Provider to OLEDB for ODBC and supply your DSN (search the web on
exact syntax)
Milan|||Use "Microsoft OLE DB Provider for ODBC Drivers" instead of MS.Jet
NO "@.datasrc" but use provider string as of "Driver={Microsoft dBASE Driver
(*.dbf)};DriverID=21;Dbq=(you file, no filename);Mode=Read;
i.e. @.provstr = 'c:\' if you file is under c drive
Subscribe to:
Posts (Atom)