SQL Server data transfer via ASP

Posted on 2013-08-28
Medium Priority
Last Modified: 2013-08-29
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?
Question by:koossa
  • 3
  • 2
LVL 29

Expert Comment

ID: 39447593
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.

Author Comment

ID: 39449990

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?
LVL 29

Assisted Solution

sammySeltzer earned 1500 total points
ID: 39450056
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.

Author Comment

ID: 39450079
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?
LVL 29

Accepted Solution

sammySeltzer earned 1500 total points
ID: 39450200
This link provides the simplest example for your need.


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.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question