Problems with SS2K OPENROWSET accessing a dBase file on W2008
in [Programming SQL]
|
From: JimLad on 8 Jan 2010 12:08 Hi, I've got SQL Server 2000 sitting on W2003. I have a dBase file on a share on W2008. This file was recently moved here from a NAS box. I am trying to run the following command (which ran fine when the file was on the NAS box): SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM SALARIES') The error message is: [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] Invalid connection string attribute SourceType] [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. I have already tried giving Everyone full permissions on the share (share and NTFS permissions). I can't find any reference to 'Invalid connection string attribute SourceType' online. Can anyone help? I'm completely stuck on this one! James
From: Erland Sommarskog on 8 Jan 2010 18:21 JimLad (jamesdbirch(a)yahoo.co.uk) writes: > I've got SQL Server 2000 sitting on W2003. > > I have a dBase file on a share on W2008. This file was recently moved > here from a NAS box. > > I am trying to run the following command (which ran fine when the file > was on the NAS box): > > SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver > (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM > SALARIES') > > The error message is: > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > Invalid connection string attribute SourceType] > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > '(unknown)' is not a valid path. Make sure that the path name is > spelled correctly and that you are connected to the server on which > the file resides.] > OLE DB error trace [OLE/DB Provider 'MSDASQL' > IDBInitialize::Initialize returned 0x80004005: ]. > Msg 7399, Level 16, State 1, Line 1 > OLE DB provider 'MSDASQL' reported an error. What service account is SQL Server running under? If it's LocalSystem, try changing it to a domain account. LocalSystem cannot access network resources. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: JimLad on 11 Jan 2010 05:47 On 8 Jan, 23:21, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > JimLad (jamesdbi...(a)yahoo.co.uk) writes: > > I've got SQL Server 2000 sitting on W2003. > > > I have a dBase file on a share on W2008. This file was recently moved > > here from a NAS box. > > > I am trying to run the following command (which ran fine when the file > > was on the NAS box): > > > SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver > > (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM > > SALARIES') > > > The error message is: > > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > > Invalid connection string attribute SourceType] > > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > > '(unknown)' is not a valid path. Make sure that the path name is > > spelled correctly and that you are connected to the server on which > > the file resides.] > > OLE DB error trace [OLE/DB Provider 'MSDASQL' > > IDBInitialize::Initialize returned 0x80004005: ]. > > Msg 7399, Level 16, State 1, Line 1 > > OLE DB provider 'MSDASQL' reported an error. > > What service account is SQL Server running under? If it's LocalSystem, > try changing it to a domain account. LocalSystem cannot access > network resources. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text - > > - Show quoted text - No. It's not that - it's running under a Domain account. As I say, it worked with a UNC path to a NAS box, but now is not working with a UNC path to a Windows Server 2008 share. Permissions have been competely opened up. I'm assuming that no drivers are required on the file server? If they were then obviously it wouldn't work as WDAC 64bit doesn't support 32bit drivers. But I don't think it should be using any drivers on the Windows Server 2008 server holding the file. Am I mistaken? James
From: JimLad on 11 Jan 2010 07:06 On 11 Jan, 10:47, JimLad <jamesdbi...(a)yahoo.co.uk> wrote: > On 8 Jan, 23:21, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > > > > > JimLad (jamesdbi...(a)yahoo.co.uk) writes: > > > I've got SQL Server 2000 sitting on W2003. > > > > I have a dBase file on a share on W2008. This file was recently moved > > > here from a NAS box. > > > > I am trying to run the following command (which ran fine when the file > > > was on the NAS box): > > > > SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver > > > (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM > > > SALARIES') > > > > The error message is: > > > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > > > Invalid connection string attribute SourceType] > > > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > > > '(unknown)' is not a valid path. Make sure that the path name is > > > spelled correctly and that you are connected to the server on which > > > the file resides.] > > > OLE DB error trace [OLE/DB Provider 'MSDASQL' > > > IDBInitialize::Initialize returned 0x80004005: ]. > > > Msg 7399, Level 16, State 1, Line 1 > > > OLE DB provider 'MSDASQL' reported an error. > > > What service account is SQL Server running under? If it's LocalSystem, > > try changing it to a domain account. LocalSystem cannot access > > network resources. > > > -- > > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > > Links for SQL Server Books Online: > > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx-Hide quoted text - > > > - Show quoted text - > > No. It's not that - it's running under a Domain account. > > As I say, it worked with a UNC path to a NAS box, but now is not > working with a UNC path to a Windows Server 2008 share. Permissions > have been competely opened up. > > I'm assuming that no drivers are required on the file server? If they > were then obviously it wouldn't work as WDAC 64bit doesn't support > 32bit drivers. But I don't think it should be using any drivers on the > Windows Server 2008 server holding the file. Am I mistaken? > > James- Hide quoted text - > > - Show quoted text - More information: this all works fine when the command is run from the SQL Server server, not a client machine. So looks like some kind of double hop problem. SELECT * FROM OPENROWSET( 'MSDASQL','Driver={Microsoft dBase Driver (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM SALARIES') or SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;DATABASE=\\server\cpm\v\wok', 'SELECT * FROM SALARIES') When run from a workstation, the MSDASQL driver gives the original error message. The Jet version gives the following error message: [OLE/DB provider returned message: '\\itmserver-211\cpm\V\Wokingham' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. However, if I set up a linked server using Jet it works fine. I think I'm one step away from solving this... Is it possible to see the connection string that the linked server actually uses using Profiler? Can I add user name and password to the connection string as this kind of looks like a double hop issue. James
From: JimLad on 11 Jan 2010 07:54 On 11 Jan, 10:47, JimLad <jamesdbi...(a)yahoo.co.uk> wrote: > On 8 Jan, 23:21, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > > > > > JimLad (jamesdbi...(a)yahoo.co.uk) writes: > > > I've got SQL Server 2000 sitting on W2003. > > > > I have a dBase file on a share on W2008. This file was recently moved > > > here from a NAS box. > > > > I am trying to run the following command (which ran fine when the file > > > was on the NAS box): > > > > SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver > > > (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM > > > SALARIES') > > > > The error message is: > > > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > > > Invalid connection string attribute SourceType] > > > [OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] > > > '(unknown)' is not a valid path. Make sure that the path name is > > > spelled correctly and that you are connected to the server on which > > > the file resides.] > > > OLE DB error trace [OLE/DB Provider 'MSDASQL' > > > IDBInitialize::Initialize returned 0x80004005: ]. > > > Msg 7399, Level 16, State 1, Line 1 > > > OLE DB provider 'MSDASQL' reported an error. > > > What service account is SQL Server running under? If it's LocalSystem, > > try changing it to a domain account. LocalSystem cannot access > > network resources. > > > -- > > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > > Links for SQL Server Books Online: > > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx-Hide quoted text - > > > - Show quoted text - > > No. It's not that - it's running under a Domain account. > > As I say, it worked with a UNC path to a NAS box, but now is not > working with a UNC path to a Windows Server 2008 share. Permissions > have been competely opened up. > > I'm assuming that no drivers are required on the file server? If they > were then obviously it wouldn't work as WDAC 64bit doesn't support > 32bit drivers. But I don't think it should be using any drivers on the > Windows Server 2008 server holding the file. Am I mistaken? > > James- Hide quoted text - > > - Show quoted text - More information: this all works fine when the command is run from the SQL Server server, not a client machine. So looks like some kind of double hop problem. SELECT * FROM OPENROWSET( 'MSDASQL','Driver={Microsoft dBase Driver (*.dbf)}; ;DBQ=\\server\cpm\v\wok ;SourceType=DBF','SELECT * FROM SALARIES') or SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;DATABASE=\\server\cpm\v\wok', 'SELECT * FROM SALARIES') When run from a workstation, the MSDASQL driver gives the original error message. The Jet version gives the following error message: [OLE/DB provider returned message: '\\server\cpm\v\wok' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. However, if I set up a linked server using Jet, it works fine. It is set to 'Connection will be made without a security context.' Is it possible to make OPENROWSET behave in the same way? Is there any way to see the connection string that the linked server is actually using, for instance using Profiler? James
|
Next
|
Last
Pages: 1 2 Prev: Migrating/Promoting Schemata and Rows Next: sp_addlinkedserver -- sql server 2000 -- problem |