SQL 2008 64Bit and dBASE III Application
All, I am trying to link my SQL 2008 64bit server to a legacy dBase III program that we have on site using the linked servers in SQL. After much reading on the internet and trial and error I have downloaded the ACE 64bit driver and I am using the following query:
This completes however when I go to test the connection I get an error that says the @datasrc is not a valid path. I know the path is correct so it must be something different.
Currently I am using a SSIS job to upload information from this database hourly but I want to be able to update it in real-time so any other suggestions on how to do this are also welcome.
EXEC sp_addlinkedserver @server = N'WINTAM',
@srvproduct=N'DBF', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'\\servername\foldername\foldername\',
@provstr=N'DBASE IV' ;This completes however when I go to test the connection I get an error that says the @datasrc is not a valid path. I know the path is correct so it must be something different.
Currently I am using a SSIS job to upload information from this database hourly but I want to be able to update it in real-time so any other suggestions on how to do this are also welcome.
Chris Luttrell
Check the service account you have Integration Services running as and make sure it has access to the source path. It sounds like a possible permission problem to me.
Even the 64 bit ACE.OLEDB driver does not recognize long path names...
You have to define the datasrc by 8.3 notation only: d:\folder1\subfldr\
To obtain 8.3 names of your folders you may issue DIR /x in CMD window.
You have to define the datasrc by 8.3 notation only: d:\folder1\subfldr\
To obtain 8.3 names of your folders you may issue DIR /x in CMD window.
ASKER
So the syntax is correct? I tried both AND get the same this same error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0"
OLE DB provider "Microsoft.ACE.OLEDB.12.0"
In your example above, you stated a drive connection. I tried that as well and got the same message. and where does the drive need to be connected on the sql server itself?
OLE DB provider "Microsoft.ACE.OLEDB.12.0"
In your example above, you stated a drive connection. I tried that as well and got the same message. and where does the drive need to be connected on the sql server itself?
8.3 path name does not include UNC pathes.
You should really test the format d:\folder1\subfldr\ where "d:" represents one single disk drive letter.
To access DBF files on another computer is a little bit problematic task...
It means you have to place DBF files on the same computer where is the SQL Server Service running. The drive letter is C: or D: (etc.) then. The user under which is the SQL Server Service running must have full access to the folder where you placed DBF files and the folder name must be in 8.3 format in connection string.
You should really test the format d:\folder1\subfldr\ where "d:" represents one single disk drive letter.
To access DBF files on another computer is a little bit problematic task...
It means you have to place DBF files on the same computer where is the SQL Server Service running. The drive letter is C: or D: (etc.) then. The user under which is the SQL Server Service running must have full access to the folder where you placed DBF files and the folder name must be in 8.3 format in connection string.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kalpesh, did you already use the Sybase 64 bit driver to read DBF tables from SQL Server?
ASKER
I couldn't get this to work. I ended up creating a 32bit server and working from there.