*sigh* This is getting frustrating. When I try to run the code using openrowset,
I get an error telling me that the .dbf file doesn't exists, but it does!

Here is my code:


SELECT a.*
FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Visual FoxPro Driver};
Exclusive=No;Collate=Machine;Deleted=Yes;Null=No;
SourceDB=C:\DTSTest\FPTables\02EMP_SKL.DBF; SourceType=DBF', 'SELECT * FROM
02EMP_SKL.DBF')
AS a




"Daniel Reber" <nospam@nospam.com> wrote:
>Yes you can connect to .dbf files instead of .dbc files. One of the links
>from the google search did just that. It even created dynamic sql with
an
>insert statement and then executed it.
>
>http://dbforums.com/showthread.php?threadid=648024
>
>Daniel Reber
>
>"Joe" <joe@stumped.com> wrote in message
>news:3e8dfd27$1@tnews.web.devx.com...
>>
>> Problem is, a .dbc file is an actual database for fox pro. I only have
>..dbf
>> files - which are fox pro tables. Can I use OpenRowSet and connect to
a
>..dbf
>> file? I should be able to because the dsn setup on my machine works fine.
>> The dsn points to a folder, not a file. THen if i create an ado connection
>> using the dsn, then a recordset for "select * from mytable", using that
>connection,
>> it works.
>>
>> Thanks again!
>>
>> "Daniel Reber" <nospam@nospam.com> wrote:
>> >It all depends on where your expertise lies. Some people never heard
of
>> >OPENROWSET. I would do what ever you are comfortable doing. I recommend
>> >researching it some more. Here is a google search that I did.
>> >
>>
>>http://www.google.com/search?hl=en&l...xpro+openrowse
>t
>> >+dbf+-dbc&btnG=Google+Search
>> >
>> >Daniel Reber
>> >
>> >
>> >"Joe" <joe@stupmed.com> wrote in message
>> >news:3e8df4d6$1@tnews.web.devx.com...
>> >>
>> >> So I can just spin through the table recordset and call one of three
>> >stored
>> >> procedures like the one below?? That's it!?!?
>> >>
>> >> Why would it be suggested to use DTS at all then??
>> >>
>> >> "Daniel Reber" <nospam@nospam.com> wrote:
>> >> >INSERT INTO sql_QC (field1, field2, field3)
>> >> >
>> >> >Use Visual FoxPro ODBC driver and OLE DB provider for ODBC. For
>example:
>> >> >SELECT field1, field2, field3
>> >> > FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Visual FoxPro Driver};
>> >> >Exclusive=No;Collate=Machine;Deleted=Yes;Null=No;
>> >> >SourceDB=d:\data\MyDatabase.dbc;SourceType=DBC;', 'SELECT * FROM
>> >foxpro_QC)
>> >> >AS foxpro_QC
>> >> >Or use Visual FoxPro OLE DB provider:
>> >> >SELECT field1, field2, field3FROM OPENROWSET('VFPOLEDB.1',
>> >> >'d:\data\MyDatabase.dbc';;, 'SELECT * FROM foxpro_QC') AS foxpro_QC
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >"Joe" <joe@stumped.com> wrote in message
>> >> >news:3e8dee88$1@tnews.web.devx.com...
>> >> >>
>> >> >> Well, that's not exactly true. The table names change, but so do
the
>> >> >sources
>> >> >> and destinations. Basically it's broken down by three parts. Skills,
>> >> >QualityControl,
>> >> >> and Usage.
>> >> >>
>> >> >> In these directories are .dbf files for skills, QC, and Usage. And
>> >there
>> >> >> are also sql tables already created for skills, QC, and Usage. These
>> >three
>> >> >> tables are different with different structures.
>> >> >>
>> >> >> But how can I connect to a .dbf file throug a stored procedure,
if
>> >that's
>> >> >> what u mean?
>> >> >>
>> >> >> "Daniel Reber" <nospam@nospam.com> wrote:
>> >> >> >If only the table name changes and the rest of the table definition
>> is
>> >> >the
>> >> >> >same the it would be relatively easy to create a sql server stored
>> >> >procedure
>> >> >> >that will iterate through your table of tables using either a
>cursor
>> >> or
>> >> >> a
>> >> >> >batch loop structure. Then create your INSERT & OPENROWSET syntax
>> to
>> >> >> >variables and execute the sql statement that is in the variables.
>> >> >> >
>> >> >> >Daniel Reber
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >"joe" <joe@stumped.com> wrote in message
>> >> >> >news:3e8de6e0$1@tnews.web.devx.com...
>> >> >> >>
>> >> >> >> I'd say there could be anywhere between a thousand or more in
>each
>> >> >table.
>> >> >> >> So kind of a lot.
>> >> >> >>
>> >> >> >> In regards to your second suggestion: why creat a vb app?
>Couldn't
>> >> u
>> >> >just
>> >> >> >> have a dts package that has an ActiveX Script task that will
>iterate
>> >> >> >through
>> >> >> >> the directories with the tables and create the packages and
>execute
>> >> >them?
>> >> >> >>
>> >> >> >>
>> >> >> >> I guess i'm still not sure what your second suggestion was. Why
>> >should
>> >> >> I
>> >> >> >> create a table with the table names and the sql statements? I
>> >already
>> >> >> have
>> >> >> >> a table that has the table names, this is the recordset I will
be
>> >> >looping
>> >> >> >> through to create the packages and execute them on each table.
>But
>> >> why
>> >> >> the
>> >> >> >> sql statements? they will be dynamic only in that the table name
>> >will
>> >> >> >change
>> >> >> >> in the from clause.
>> >> >> >>
>> >> >> >> Could u clarify?
>> >> >> >>
>> >> >> >> "Daniel Reber" <nospam@nospam.com> wrote:
>> >> >> >> >You may want to go with my 2nd suggestion. You will only have
>> to
>> >> >create
>> >> >> >> one
>> >> >> >> >stored procedure and the table that I described. By the way,
>what
>> >> is
>> >> >> the
>> >> >> >> >avg numbers of rows/columns for the tables?
>> >> >> >> >
>> >> >> >> >Daniel Reber
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >"Joe" <Joe@stupmed.com> wrote in message
>> >> >> >> >news:3e8de0c2$1@tnews.web.devx.com...
>> >> >> >> >>
>> >> >> >> >> So you think it's okay to create these 800 packages and
>execute
>> >> them
>> >> >> on
>> >> >> >> >the
>> >> >> >> >> fly??
>> >> >> >> >>
>> >> >> >> >> I just don't want to bring that to someone else and have them
>> say
>> >> >"why
>> >> >> >> in
>> >> >> >> >> the world would u do that?"
>> >> >> >> >>
>> >> >> >> >> Thanks for your advice
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "Daniel Reber" <nospam@nospam.com> wrote:
>> >> >> >> >> >By the way, dts probably uses OPENROWSET behind the scenes
to
>> >> >extract
>> >> >> >> the
>> >> >> >> >> >data from the foxpro tables anyway.
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >> >"Daniel Reber" <nospam@nospam.com> wrote in message
>> >> >> >> >> >news:3e8dd03d@tnews.web.devx.com...
>> >> >> >> >> >> It is really depending on the amount of rows that are in
>the
>> >> >tables.
>> >> >> >> >> If
>> >> >> >> >> >> there are not that many rows (a few hundred max) then
>creating
>> >> an
>> >> >> >> >insert
>> >> >> >> >> >> statement for each row may be faster. But if you have
>> >thousands
>> >> >> of
>> >> >> >> >rows
>> >> >> >> >> >per
>> >> >> >> >> >> table or more then the overhead for creating the dts
>packages
>> >> >would
>> >> >> >> be
>> >> >> >> >> >> negligible compared to the amount of time it will take
to
>> >create
>> >> >> the
>> >> >> >> >> >insert
>> >> >> >> >> >> statements. Consider this, you have 1000 rows on average
>> per
>> >> >table.
>> >> >> >> >> That
>> >> >> >> >> >> means that you will have to run ~800,000 insert statements
>> >> >through
>> >> >> >> ado.
>> >> >> >> >> >> Maybe my second solution would be easier to implement.
>> >> >> >> >> >>
>> >> >> >> >> >> Daniel Reber
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >> "Joe" <Joe@Stupmed.com> wrote in message
>> >> >> >> >> >> news:3e8dc7df$1@tnews.web.devx.com...
>> >> >> >> >> >> >
>> >> >> >> >> >> > Hi Daniel --
>> >> >> >> >> >> >
>> >> >> >> >> >> > What do you mean "dynamic dts packages" from vb?
>> >> >> >> >> >> >
>> >> >> >> >> >> > The way it's currently being done is to iterate through
>> >every
>> >> >> file
>> >> >> >> >> >(.dbf)
>> >> >> >> >> >> > in the directory and create packages, on the fly, to
>> >transform
>> >> >> the
>> >> >> >> >data.
>> >> >> >> >> >> > This is done through a vb app. But this will create 800
>> >> >packages
>> >> >> >> >through
>> >> >> >> >> >> > code during the course of the iteration. This would be
>> >faster
>> >> >> you
>> >> >> >> >think?
>> >> >> >> >> >> > There is a lot of code to create a package, lots of
>> >properties
>> >> >> to
>> >> >> >> set
>> >> >> >> >> >and
>> >> >> >> >> >> > method calls to make. I though it would be slower create
>> >> >packages
>> >> >> >> on
>> >> >> >> >> the
>> >> >> >> >> >> > fly. Is this what you meant?
>> >> >> >> >> >> >
>> >> >> >> >> >> > Thanks!
>> >> >> >> >> >> >
>> >> >> >> >> >> >
>> >> >> >> >> >> > "Daniel Reber" <nospam@nospam.com> wrote:
>> >> >> >> >> >> > >How many rows of data are there in the tables? If it
is
>> >> >> >thousands
>> >> >> >> >> of
>> >> >> >> >> >> rows
>> >> >> >> >> >> > >or more per table then be prepared to wait a while.
I
>> >would
>> >> >> >either:
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >1. Create a table in sql server that contains the full
>> path
>> >> >and
>> >> >> >> the
>> >> >> >> >> sql
>> >> >> >> >> >> > >statement to fetch the rows for each table and the
>> >destination
>> >> >> >> >table.
>> >> >> >> >> >> Then
>> >> >> >> >> >> > >iterate through the tables and use OPENROWSET with an
>> >insert
>> >> >> >> >statement
>> >> >> >> >> >to
>> >> >> >> >> >> > >the destination table in a stored procedure.
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >or
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >2. Create the same table as above and create dynamic
dts
>> >> >packages
>> >> >> >> >using
>> >> >> >> >> >> > VB.
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >This would be much faster.
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >Daniel Reber
>> >> >> >> >> >> > >Datamasters, Inc
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >"Joe" <Joe@Stumped.com> wrote in message
>> >> >> >> >> >> > >news:3e8db28f$1@tnews.web.devx.com...
>> >> >> >> >> >> > >>
>> >> >> >> >> >> > >> Thanks q-bert :-)
>> >> >> >> >> >> > >>
>> >> >> >> >> >> > >> I think the best idea would be to iterate through
all
>> the
>> >> >> >folders
>> >> >> >> >> and
>> >> >> >> >> >> > do
>> >> >> >> >> >> > >> selects on the fox pro tables and then inserts to
the
>> >> >> >destination
>> >> >> >> >> >> tables.
>> >> >> >> >> >> > >>
>> >> >> >> >> >> > >> There's no need to create tables in sql server that
>> >mirror
>> >> >> the
>> >> >> >> fox
>> >> >> >> >> >pro
>> >> >> >> >> >> > >tables
>> >> >> >> >> >> > >> - just moving the data to one table is all that I
need
>> to
>> >> >do.
>> >> >> >> >> >> > >>
>> >> >> >> >> >> > >> Actually, the destination tables are already created
>> so
>> >> I
>> >> >won't
>> >> >> >> >need
>> >> >> >> >> >to
>> >> >> >> >> >> > >>
>> >> >> >> >> >> > >> "Q*bert" <luke_davis_76@hotmail.com> wrote:
>> >> >> >> >> >> > >> >
>> >> >> >> >> >> > >> >I'd create a VB App that uses the File system object
>> to
>> >> >> >iterate
>> >> >> >> >> >> through
>> >> >> >> >> >> > >> all
>> >> >> >> >> >> > >> >the folders/files in your Parent directory. Then
use
>> >ADO
>> >> >> to
>> >> >> >> >Query
>> >> >> >> >> >> each
>> >> >> >> >> >> > >> file
>> >> >> >> >> >> > >> >for the table structure, including keys, use of
>Nulls,
>> >> >length,
>> >> >> >> >etc
>> >> >> >> >> >and
>> >> >> >> >> >> > >then
>> >> >> >> >> >> > >> >use a create table statement with the appropriate
>> >> >information.
>> >> >> >> >> Once
>> >> >> >> >> >> > the
>> >> >> >> >> >> > >> >structure had been created, I would then probably
>> >> >> >programatically
>> >> >> >> >> >> create
>> >> >> >> >> >> > >> >a dump of each file and then do a mass import into
>the
>> >> >created
>> >> >> >> >> >> structures
>> >> >> >> >> >> > >> >through automation.
>> >> >> >> >> >> > >> >
>> >> >> >> >> >> > >> >Now, If I wanted to do any filtering, or data checks,
>> I
>> >> >would
>> >> >> >> >most
>> >> >> >> >> >> likely
>> >> >> >> >> >> > >> >not do a data dump, instead, I would use ADO and
pull
>> >back
>> >> >> all
>> >> >> >> >> >records
>> >> >> >> >> >> > >individually
>> >> >> >> >> >> > >> >inserting them the new system. Before each insert,
>> I
>> >would
>> >> >> >check
>> >> >> >> >> to
>> >> >> >> >> >> > see
>> >> >> >> >> >> > >> >if it passed all data integrety checks I want to
>> >perform.
>> >> >> >> >> >> > >> >
>> >> >> >> >> >> > >> >Just the way I would do it.
>> >> >> >> >> >> > >> >
>> >> >> >> >> >> > >> >Cheers
>> >> >> >> >> >> > >> >Q*bert
>> >> >> >> >> >> > >> >"Joe" <Joe@Stumped.com> wrote:
>> >> >> >> >> >> > >> >>
>> >> >> >> >> >> > >> >>I have 800 visual fox pro tables that sit in a
>> >directory.
>> >> >> I
>> >> >> >> need
>> >> >> >> >> to
>> >> >> >> >> >> > >import
>> >> >> >> >> >> > >> >>all the data in those fox pro tables to sql server
>> >tables.
>> >> >> >> >Should
>> >> >> >> >> I
>> >> >> >> >> >> > use
>> >> >> >> >> >> > >> >DTS??
>> >> >> >> >> >> > >> >>If so, should I create 800 seperate transformation
>> >tasks
>> >> >> for
>> >> >> >> >each
>> >> >> >> >> >> table
>> >> >> >> >> >> > >> >to
>> >> >> >> >> >> > >> >>be imported (seems a little silly). Should I use
a
>> VB
>> >> App
>> >> >> >that
>> >> >> >> >> does
>> >> >> >> >> >> > >selects
>> >> >> >> >> >> > >> >>and inserts for each table??
>> >> >> >> >> >> > >> >>
>> >> >> >> >> >> > >> >>How would you guys do it??
>> >> >> >> >> >> > >> >>
>> >> >> >> >> >> > >> >>Thanks!
>> >> >> >> >> >> > >> >
>> >> >> >> >> >> > >>
>> >> >> >> >> >> > >
>> >> >> >> >> >> > >
>> >> >> >> >> >> >
>> >> >> >> >> >>
>> >> >> >> >> >>
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>