sql server dts: openrowset to dbase files, specifically what drivers -- sql server dts

openrowset to dbase files, specifically what drivers

Steve Mull
9/27/2006 8:55:30 AM

sql server dts:

Hi -

I have a 32 bit SQL Server 2000 installation that is current on service
packs that is installed on a 64 bit version of Windows 2003 (this actually
works ok). It's not mine, but I have to deal with it.

There are a few DTS packages I need to transpose into T-SQL that read from
dbase files. I know DTS has drivers to read dbase files (because one can
"connect" to them), but in looking in the DSN area on the machine, I see
that it indicates no drivers are installed except for SQL Server. I need to
write T-SQL, using something like openrowset or openquery, that opens and
reads from these dbase files.

1) Can I utilize the dbase drivers built-in to DTS, without having to
install some old version of MDAC ? How ?

2) If I have to use MDAC, apparently I have to go back to the 2.5 version
because the later versions don't support file formats like dbase ? Is this
correct ?

3) Can someone please please point me to specific correct connection string
examples for the above cases ? The connection strings seem to be more of a
pain than the drivers, half of the time ..

Thank-you very much for any assistance.

Steve

RE: openrowset to dbase files, specifically what drivers

Charles Kangai
9/28/2006 2:00:02 AM

Hi Steve,

Something like this should work, with SourceDB pointing to the folder with
the dBase tables:
select * from OPENROWSET('MSDASQL',
'Driver={Microsoft dBase Driver};SourceDB=c:\MyApp\;SourceType=dbf', 'select
* from MyTable')

Or you could create a linked server with Provider = Microsoft.Jet.OLEDB.4.0,
a datasource that points to the folder with the dBase tables, and
ProviderString = 'dBase IV'.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email alias: charles
email domain: kangai.demon.co.uk

[quoted text, click to view]

Re: openrowset to dbase files, specifically what drivers

Steve Mull
9/28/2006 2:24:39 AM

Charles -

Thanks you SO much -

I got the second hint of yours to work, and I'm off to the races. This is
helping me a great deal :)

fyi, here is what I got when I tried the first approach (I checked my paths,
etc ..) :

select * from OPENROWSET('MSDASQL',
'Driver={Microsoft dBase
Driver};SourceDB=F:\trs_imports_exports\ImportToTonnage\LongBeach\;SourceType=dbf',
'select
* from allton')

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].

Steve

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.