transfer SQL data

Discussion in 'ASP General' started by shank, Jan 2, 2006.

  1. shank

    shank Guest

    I need to make a table's data available to users. The table is about 150,000
    rows x 20 columns. So Excel is out of the question. I think the next common
    file types would be MDB or DBF. Using ASP, what are the steps to converting
    this data to one of those file types, then ZIPPING the files for download?

    thanks
     
    shank, Jan 2, 2006
    #1
    1. Advertising

  2. shank

    Larry Bud Guest

    shank wrote:
    > I need to make a table's data available to users. The table is about 150,000
    > rows x 20 columns. So Excel is out of the question. I think the next common
    > file types would be MDB or DBF. Using ASP, what are the steps to converting
    > this data to one of those file types, then ZIPPING the files for download?


    What do they need to do with it? Do they need to access it through an
    ASP page?

    You could export it as XML just by adding "for xml auto" to the end of
    the SQL statement.
     
    Larry Bud, Jan 3, 2006
    #2
    1. Advertising

  3. shank

    shank Guest

    "Larry Bud" <> wrote in message
    news:...
    >
    > shank wrote:
    >> I need to make a table's data available to users. The table is about
    >> 150,000
    >> rows x 20 columns. So Excel is out of the question. I think the next
    >> common
    >> file types would be MDB or DBF. Using ASP, what are the steps to
    >> converting
    >> this data to one of those file types, then ZIPPING the files for
    >> download?

    >
    > What do they need to do with it? Do they need to access it through an
    > ASP page?
    >
    > You could export it as XML just by adding "for xml auto" to the end of
    > the SQL statement.



    I don't think the XML solution is practical for the size of the file. I
    think I prefer writing to a DBF or MDB. I don't want to write it to the
    screen, just to a file for zipping or download.
    thanks
     
    shank, Jan 3, 2006
    #3
  4. shank wrote:
    > "Larry Bud" <> wrote in message
    > news:...
    >>
    >> shank wrote:
    >>> I need to make a table's data available to users. The table is about
    >>> 150,000
    >>> rows x 20 columns. So Excel is out of the question. I think the next
    >>> common
    >>> file types would be MDB or DBF. Using ASP, what are the steps to
    >>> converting
    >>> this data to one of those file types, then ZIPPING the files for
    >>> download?

    >>
    >> What do they need to do with it? Do they need to access it through
    >> an ASP page?
    >>
    >> You could export it as XML just by adding "for xml auto" to the end
    >> of the SQL statement.

    >
    >
    > I don't think the XML solution is practical for the size of the file.
    > I think I prefer writing to a DBF or MDB. I don't want to write it to
    > the screen, just to a file for zipping or download.
    > thanks


    Fine, but the other question Larry asked is equally important. In order for
    us to provide a good recommendation, you need to provide as much information
    as possible.

    Are you simply creating a report to be printed? Is somebody actually going
    to read the data contained in 3,000,000 fields? Would it make more sense to
    provide a summary of this data?

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 3, 2006
    #4
  5. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > shank wrote:
    >> "Larry Bud" <> wrote in message
    >> news:...
    >>>
    >>> shank wrote:
    >>>> I need to make a table's data available to users. The table is about
    >>>> 150,000
    >>>> rows x 20 columns. So Excel is out of the question. I think the next
    >>>> common
    >>>> file types would be MDB or DBF. Using ASP, what are the steps to
    >>>> converting
    >>>> this data to one of those file types, then ZIPPING the files for
    >>>> download?
    >>>
    >>> What do they need to do with it? Do they need to access it through
    >>> an ASP page?
    >>>
    >>> You could export it as XML just by adding "for xml auto" to the end
    >>> of the SQL statement.

    >>
    >>
    >> I don't think the XML solution is practical for the size of the file.
    >> I think I prefer writing to a DBF or MDB. I don't want to write it to
    >> the screen, just to a file for zipping or download.
    >> thanks

    >
    > Fine, but the other question Larry asked is equally important. In order
    > for us to provide a good recommendation, you need to provide as much
    > information as possible.
    >
    > Are you simply creating a report to be printed? Is somebody actually going
    > to read the data contained in 3,000,000 fields? Would it make more sense
    > to provide a summary of this data?
    >
    > Bob Barrows


    Users will be downloading the contents of a table so that they can populate
    their own databases with product. This will happen many times throughout a
    week. They will be comparing what we have discontinued and of course what is
    new product. With about 150,000 rows and 20+ columns of data, I don't think
    XML is practical. The ideal method would be to automate a process every
    morning that would write a new MDB or DBF or maybe both, zip them and make
    them available for download. I'm only guessing at this point, but I would
    imagine 5MB-6MB of data. I don't want any writing to screen. It would be of
    no use.

    thanks!
     
    shank, Jan 3, 2006
    #5
  6. shank wrote:
    > Users will be downloading the contents of a table so that they can
    > populate their own databases with product. This will happen many
    > times throughout a week. They will be comparing what we have
    > discontinued and of course what is new product. With about 150,000
    > rows and 20+ columns of data, I don't think XML is practical. The
    > ideal method would be to automate a process every morning that would
    > write a new MDB or DBF or maybe both, zip them and make them
    > available for download. I'm only guessing at this point, but I would
    > imagine 5MB-6MB of data. I don't want any writing to screen. It would
    > be of no use.
    > thanks!


    How many users are we talking about? Is this in a LAN/WAN? Or over the
    internet?

    My first thought is to provide them with an Access database containing a
    linked table. This database file would not need to be provided via ASP. If
    you are on a LAN/WAN, it could be put on a file server. The users would need
    instructions in creating an ODBC DSN (or a startup macor could be written to
    automate the creation of the DSN - see an Access group for details)

    You can create a read-only view of the table in you SQL Server database, and
    point the linked table at that view.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 3, 2006
    #6
  7. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > shank wrote:
    >> Users will be downloading the contents of a table so that they can
    >> populate their own databases with product. This will happen many
    >> times throughout a week. They will be comparing what we have
    >> discontinued and of course what is new product. With about 150,000
    >> rows and 20+ columns of data, I don't think XML is practical. The
    >> ideal method would be to automate a process every morning that would
    >> write a new MDB or DBF or maybe both, zip them and make them
    >> available for download. I'm only guessing at this point, but I would
    >> imagine 5MB-6MB of data. I don't want any writing to screen. It would
    >> be of no use.
    >> thanks!

    >
    > How many users are we talking about? Is this in a LAN/WAN? Or over the
    > internet?
    >
    > My first thought is to provide them with an Access database containing a
    > linked table. This database file would not need to be provided via ASP. If
    > you are on a LAN/WAN, it could be put on a file server. The users would
    > need instructions in creating an ODBC DSN (or a startup macor could be
    > written to automate the creation of the DSN - see an Access group for
    > details)
    >
    > You can create a read-only view of the table in you SQL Server database,
    > and point the linked table at that view.
    >
    > Bob Barrows
    >


    I've gone the route of trying to get users to setup an ODBC connection with
    Access. It works great for the techie users, unfortunately, the bulk of
    users are not going the route of ODBC. I can only assume they do not
    understand. That leaves me with providing a file to download over the
    internet. Up to 400 users will have access.
    thanks
     
    shank, Jan 3, 2006
    #7
  8. shank wrote:
    >>

    >
    > I've gone the route of trying to get users to setup an ODBC
    > connection with Access. It works great for the techie users,
    > unfortunately, the bulk of users are not going the route of ODBC. I
    > can only assume they do not understand. That leaves me with providing
    > a file to download over the internet. Up to 400 users will have
    > access.


    Again, it is possible to create a VBA sub in an Access module that will
    automate the creation of the DSN. That should not be considered an obstacle.
    The obstacle is "the internet". A linked table will not work outside a LAN
    or WAN.

    What I would do is create a DTS package to export the SQL Server data to an
    mdb file and schedule it to run nightly. Use a third-party tool to zip it in
    an ActiveX script in the DTS package. Then provide a link to the zip file to
    the users.


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 3, 2006
    #8
  9. shank

    shank Guest

    >
    > I've gone the route of trying to get users to setup an ODBC connection
    > with Access. It works great for the techie users, unfortunately, the bulk
    > of users are not going the route of ODBC. I can only assume they do not
    > understand. That leaves me with providing a file to download over the
    > internet. Up to 400 users will have access.
    > thanks


    Is anyone familiar with: http://www.motobit.com/help/RSConv/database.asp
    Recordset Converter

    It appears to be what I want,but I don't know if it will work on a server
    environment.
    Is there another alternative?

    thanks!
     
    shank, Jan 3, 2006
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TheVooDooChild
    Replies:
    2
    Views:
    7,690
    gaurav_magic
    Apr 20, 2007
  2. Harry Zoroc
    Replies:
    1
    Views:
    948
    Gregory Vaughan
    Jul 12, 2004
  3. ecoolone
    Replies:
    0
    Views:
    767
    ecoolone
    Jan 3, 2008
  4. Mark B
    Replies:
    3
    Views:
    2,044
    Philipp Post
    Sep 29, 2008
  5. Jim
    Replies:
    1
    Views:
    909
    Aaron Bertrand - MVP
    Jan 28, 2004
Loading...

Share This Page