Export from SQL Server to CSV

[A.S.]

I have an app that gets data from SQL Server and writes it to a csv
file. My problem is that my query returns 25K + records. IE seems to
'hang' and the csv file never gets created. Any Ideas how I can get this
to work? Below is my code:

Response.Expires = -1
Response.Buffer = True
'Response.ContentType="application/vnd.ms-excel"
Server.ScriptTimeout = 10000

fileName2 = createGUID()
filesavepath2 = rootDir
&"\assets\docs\"&Request.Cookies("supplypoint")("mepSite")&"_personnel.csv"

headerRow = ""
headerRow = headerRow & "SupplyPoint ID, Org Name, Title, Title2, Job
Function, Prefix, First Name, Middle, Last Name,"
headerRow = headerRow & "Suffix, Extended Info, Email, Phone"

dataRows = ""

'FOR STORED PROCEDURE BEGIN
strsql = "sp_exportKeyPersonnel '" & mepId & "', '" & showType & "'"
Set rs = DB.Execute (strsql)
'FOR STORED PROCEDURE END
While (NOT rs.EOF)
currentOrgId = rs("id")

dataRows = dataRows & """" & rs("msccId") & """,""" & rs("name")
&""",""" & rs("title") &""",""" & rs("title2") &""",""" &
rs("jFunction") &""",""" & rs("prefix") &""",""" & rs("firstName") &""","
dataRows = dataRows & """" & rs("MI") &""",""" & rs("lastName") &""","""
& rs("suffix") &""",""" & rs("extendedInfo") &""","
dataRows = dataRows & """" & rs("email") &""",""" & rs("phone") &""""
dataRows = dataRows & ""&vbcrlf&""
rs.MoveNext()
Wend
rs.close
Set rs = Nothing
Set DB = Nothing

set FSO = Server.CreateObject("scripting.FileSystemObject")
set csvfile = fso.CreateTextFile(filesavepath2, true)
csvfile.WriteLine(headerRow & vbcrlf)
csvfile.WriteLine(dataRows)
csvfile.Close

Ariel

 

[Adrian Forbes [ASP MVP]]

Your page will be timing out as you move through your recordset. As it
times out and all you are doing is concatinating lots of text nothing
appears to have been done.

The way you are adding to the same variable is incredibly slow and
inneficient. Rather than creating one big var then writing it to FSO in one
go, open your file then write to it line by line and it'll be quicker.
Hopefully enough to complete before it times out.

 

[A.S.]

I will try that out.

Remember, it is not timing out, just hanging. Very odd behavior.

Ariel

 

[Adrian Forbes [ASP MVP]]

Maybe this then

http://www.aspfaq.com/show.asp?id=2180

 

[A.S.]

Definitely not antivirus.

Maybe this then

http://www.aspfaq.com/show.asp?id=2180