SQL Server data transfer via ASP

I've got a Visual basic database application that flags data changed on a local SQL Server and update these changes to a communal online SQL Server Database using form post methods.
Everything is works fine, but if the user lose his local data I want to give him the option to restore his data by retrieving only the portion of his data from the online database.

What would be the best option to minimize bandwidth usage?
koossaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
I can't think of a better option than select only data that exists in communal online sql sever but doesn't exist in local data.

INSERT INTO Localdata(columns)
SELECT columns
FROM communal Online
WHERE communal Online
.key NOT IN (SELECT key FROM Localdata)

Open in new window


This is your pseudo-code but the concept is sound.

Insert into table in localdata (columnNames) Select columnNames from table in communal server where table.pk in communalServer NOT IN (select key from table in localdata).

If this data belongs to a particular user, then

WHERE user ='some user' and  communal Online
.key NOT IN (SELECT key FROM Localdata)

Open in new window


Another option, which is not nearly as efficient, in my view, is to backup local data and restore from communal online data.
0
koossaAuthor Commented:
Hi

I know how to do the query part, what I want to know is what is the way to transfer the data that will use the least amount of bandwidth, eg XML, CSV, MDB, Zipped, compressed as string?
0
sammySeltzerCommented:
Between xml, csv and mdb, I would use csv; although some would argue in favor of xml.

Either way, xml and csv are better than mdb.

As far as zipped is concerned, you still need to export to any of the formats you listed before you can zip them.

Obviously, compressing files is the best method of saving some bandwidth.
0
koossaAuthor Commented:
Ons the server side I can generate CSV file from the SQL data without a problem, but is there a way to compress them using ASP?
0
sammySeltzerCommented:
This link provides the simplest example for your need.

http://www.xstandard.com/en/documentation/xzip/

First download, unzip and register xstandard.

Then use this:

<%
Dim objZip
Set objZip = Server.CreateObject("XStandard.Zip")
objZip.Pack "C:\FolderName\yourfile.csv", "C:\FolderName\yourfile.zip"
Set objZip = Nothing
%>

Open in new window

It unzips your file called yourfile.csv or xml or whatever the extension.

Then renames it to yourfile.zip

If you decide to zip it and move to a different folder, then something like:

objZip.Pack "C:\FolderName\yourfile.csv", "C:\differentFolder\yourfile.zip"

Open in new window


The component is free.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.