How 2 EXPORT data from SQL server ?

[Yovav]

Hi all!

How can I export data from a remote SQL server ?
I have access through Enterprise Manager,

but like BACKUP - is there a "command" that export data / tables ?

I like 2 execute it from an ASP page

Any ideas ?

[antares686]

Depends on what you hope to accomplish. Most commonly is to use DTS and call the process thru ASP. But there is also BCP, and writing a file from ASP and structuring yourself.

There is a good example somewhere at www.asp101.com that I just cannot find right now.

[Yovav]

I did find there some specific stuff:

http://www.asp101.com/articles/carvin/dts/default.asp


I have to say that asp101 has the biggest and the most active ASP forum I've ever seen

[antares686]

That was the article I was looking for. Another good place for SQL stuff is www.sqlservercentral.com , forums are very active and the site is dedicated to use of SQL Server.

[Yovav]

I see you know some about ASP-SQL,

can U also help with this:

I just finished a ASP web that is using a BIG SQL DB
about 8GB...

and It goes reel slow,
I'm trying to optimize queries...

Anyway - is there a way to open one "global" connection
to ADO instead of just connect and release it on every page ?

[antares686]

There are just so many things you can do to optimize. One thing is to allow Connection Pooling to do it's job, don't try to put into a Session or Application object as it will hold to the one session. But instead of closing the connection object and setting it = nothing just close the object. The connection will remain open for several minutes anyway and if you do that the connection object althought coded to rebuild does not actually. It or another session may freely use the already open connection in the pool reducing the reconnect time. Lots of places including MSDN to read about this.

Other things you can do is use Stored Procedures, they have the advantage of stored execution plans that can increase performance.

Make sure you have good indexes in place for your queries but don't go overboard.

Make sure you perform regular DB maintainence such as index Rebuild or Defrag (depends on SQL version), update stats, update usage. A lot of stuff on this at www.sql-server-performance.com and www.sqlservercentral.com (best forums for SQL).

If you have mutliple queries for a single page, build all into a single Stored Procedure to return as seperate recordsets. Then you use movenextrecordset to move thru them. This helps with round trips to the server decreasing network traffic.

When building SPs use SET NOCOUNT ON to reduce some network chatter. Also, if you don't need data back then use the ADODB.Command object with SP and no return message to prevent the extra talking. And if you only need a single value from a query use an SP with OUTPUT parameter and following the same thing as about no data back, just pass the value back thru the OUTPUT paramter instead.

Again there is so much you could discuss on this you could write a nice thick book. Go checkout those sites I mentioned and search www.sqlservercentral.com forums (I have been posting there for a long time and know there is an enormous amount of data on this very question).

[Yovav]

Very useful info,

10X a lot.