About Sybase to Excel

Results 1 to 2 of 2

Thread: About Sybase to Excel

  1. #1
    Join Date
    Dec 1969

    Default About Sybase to Excel

    Hi,<BR>I&#039;d like to export a tables from Sybase to an Excel file when a user click on the "export excel file" button. The method I used is as follow:<BR><BR>&#060;%<BR> Option Explicit<BR><BR> Response.Buffer=True<BR> Response.ContentType = "application/vnd.ms-Excel"<BR> Response.AddHeader "content-disposition", "inline; filename=excel.xls"<BR><BR> Dim conn, rs, i<BR> Set conn = Server.CreateObject("ADODB.Connection")<BR> conn.Open "DSN=TradeSystem; PWD=ABC123;"<BR> Set rs = Server.CreateObject("ADODB.Recordset")<BR> rs.Open "select * from Transaction where customer_id = &#039;00001234&#039;", conn<BR> Response.Write "&#060;table border = 1&#062;"<BR> While Not rs.EOF<BR> Response.Write "&#060;tr&#062;"<BR> For i = 0 To rs.Fields.Count - 1<BR> Response.Write "&#060;td&#062;" & rs(i) & "&#060;/td&#062;"<BR> Next<BR> Response.Write "&#060;/tr&#062;"<BR> rs.MoveNext<BR> Wend<BR> rs.Close<BR> conn.Close<BR> Set rs = Nothing<BR> Set conn = Nothing<BR> Response.Flush<BR> Response.End<BR>%&#062;<BR>It works fine for 300 rows. When the rows returned increase, say 1000, it may hit an error:<BR>"Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e31&#039; [INTERSOLV][ODBC SQL Server driver][SQL Server]ct_results(): user api layer: internal Client Library error: Read from the server has timed out."<BR>Even if there&#039;s no errors, the time required is very long, about 90 secs to write a 300k excel file storing 200 rows. So I&#039;ve tried to write the records into a .csv file, which size is only 60k and completed in less than 10 secs. However, I cannot control the formatting when the file is being opened in Excel. Do you have any suggestion on how I can specify some formatting when a user open a csv file or speed up the process in writing the records to an Excel file directly.<BR><BR>Thanks<BR>Fung

  2. #2
    Join Date
    Dec 1969

    Default A couple suggestions...

    First, have you tried running your query directly against your database using query tool and checked your response time (i.e. - the one returning 1000 rows)?<BR><BR>Next, instead of looping through recordset and fields, in this case why not use the GetString method to write all the records? You are not doing any special formatting other than beginning and ending &#060;tr&#062;&#060;td&#062; tags. The GetString method has parameters to account for this...check it out. <BR><BR>Also, it is a good practice in your SQL to list all your columns out instead of doing <BR><BR>Select * from ....<BR><BR>Lastly, it looks like you are using an OLE DB provider so maybe try a DSN-less connection as well.<BR><BR>Provided your resultset is coming back pretty quickly, I think the GetString will make a big difference for you.<BR><BR>Good luck.<BR><BR>Pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts