How would u do it (design question) ? - Page 2


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18

Thread: How would u do it (design question) ?

  1. #16
    Daniel Reber Guest

    Re: How would u do it (design question) ?

    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...pro+openrowset
    +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!
    > >> >> >> >> > >> >
    > >> >> >> >> > >>
    > >> >> >> >> > >
    > >> >> >> >> > >
    > >> >> >> >> >
    > >> >> >> >>
    > >> >> >> >>
    > >> >> >> >
    > >> >> >> >
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  2. #17
    Joe Guest

    Re: How would u do it (design question) ?


    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...pro+openrowset
    >+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!
    >> >> >> >> >> > >> >
    >> >> >> >> >> > >>
    >> >> >> >> >> > >
    >> >> >> >> >> > >
    >> >> >> >> >> >
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >
    >> >> >> >> >
    >> >> >> >>
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  3. #18
    Daniel Reber Guest

    Re: How would u do it (design question) ?

    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!
    > >> >> >> >> >> > >> >
    > >> >> >> >> >> > >>
    > >> >> >> >> >> > >
    > >> >> >> >> >> > >
    > >> >> >> >> >> >
    > >> >> >> >> >>
    > >> >> >> >> >>
    > >> >> >> >> >
    > >> >> >> >> >
    > >> >> >> >>
    > >> >> >> >
    > >> >> >> >
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center