Download the SQL servere date to excel file using ASP.net

Hi EveryOne

I would like to download the SQL server databse to excel file using ASP.Net

I need to download the one table to excel file. If anyone knows please help me.

With Regards

Nibu Abraham

hi

i found this link

http://www.theserverside.net/discussions/thread.tss?thread_id=29385

give a try

|||

Hi Nibu

Create a stored procedure to display the required columns from the give table. Grant execute permission to this stored procedure to the role that your ASP.NET account is assigned to. In your datalayer, run the stored procedure to output a dataset. Add a gridview control (or datagrid if still at ASP.NET 1.1) to an ASPX form.

Set the datasource for the control to the dataset produced by the read function in your data layer, call databind and the data will be displayed.

There is a simple modifier to make the displayed page OLE load Excel - I will find out the how of this at the weekend.

HTH

|||

You need to add the following to your Page_Load

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

You will need to turn off the sort option on the grid and any other hyperlinks on the form.

HTH

sql

Down time from shrink?

Hello, everyone:

I have a production database in which data file takes 17GB and used 10GB. I want to shrink it. Once I take about one hour to shrink 1GB. So I am worry long shrinking time. Is there down time during the shrinking that doesn't allow user access?

Thanks

ZYThow big is(are) your log file(s)?

dbcc shrinkdatabase does not require the database to be in single user mode but you may want to schedule some down time as you are likely to see a performance hit on the server and you are going to want reindex after the shrink because of the frgamentation shrinking causes.|||Hi, Sean:

Thanks for the reply. The log file is not big, just be about 100MB. Only data files are too.

ZYT

how big is(are) your log file(s)?

dbcc shrinkdatabase does not require the database to be in single user mode but you may want to schedule some down time as you are likely to see a performance hit on the server and you are going to want reindex after the shrink because of the frgamentation shrinking causes.|||Are you shrinking the database on a regular interval? If you do: Don't!
If you are running out of disk space: add more disks.|||17GB is tiny for a database. After your shrink, I would review your automatic growth if you are worried about too much free space.

Doing Math on Tables

Hello everyone:

I have a database for one of my websites, a picture rating site. Anyways, right now there are quicte a few tables, and I was wondering how to give the server a break and was wondering if this was possible:

Basicly I have a members table, and a votes table. Members will rate other users pcitures on a scale of one to ten, then the votes will be inserted into the votes table. The only problem with this is that calcuating all the votes a user has can put a straing on the server. I was wondering if it would be possible to create a math column in the members table that would automaticly figure out the users average and having it stored in a field in the members table, so all I would have to do is query the members average located in the mebers table, rather than tallying all the votes in the votes table for each member.

Hope this makes sense, a tutorial or any suggestions would be great!

ThanksYou should be able to add 3 columns to your Members table to help with this: TotalVotes, TotalScore, and AverageScore. And then you can add a trigger to your Votes table which would update these columns in the Members table each time a Votes record is added/updated/deleted.

You can see what Books Online has to say aboutTriggers. And here's a little overview which gives a friendly overview of triggers:The Trouble with Triggers.

I might add that I am completely paranoid about this approach :-) I would want to periodically refresh these fields in the Members table (probably with a SQL job that runs nightly) to make sure the data stays in sync.

Terri