transfer SQL data

S

shank

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
 
L

Larry Bud

shank said:
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.
 
S

shank

Larry Bud said:
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
 
B

Bob Barrows [MVP]

shank said:
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
 
S

shank

Bob Barrows said:
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!
 
B

Bob Barrows [MVP]

shank said:
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
 
S

shank

Bob Barrows said:
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
 
B

Bob Barrows [MVP]

shank said:
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.
 
S

shank

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!
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top