ASP Alliance Member: MTG

The Scenario:

Let's say your building a web site for a book pusblishing company and you want to provide a downloadable list of authors. We'll base the rest of our tutorial on that scenario accordinlgy. Make sure your SQL Server has the pubs database and all of it's objects present before continuing.

The Code:

<center>
<% If Not Request.Form("action") = "Prepare Download Now" Then %>
<form action="test.asp" method="post">
<input type="submit" value="Prepare Download Now" name="action">
</form>
<% End If %>

<%
If Request.Form("action") = "Prepare Download Now" Then
 shortfile = "authors" & Session.SessionID & ".xls"
 file = Server.MapPath("/virtualpath") & Session.SessionID & ".xls"
 strSQL = "EXEC xp_cmdshell '" & _
	  "bcp pubs..authors out " & file & " -c -Sservernameorip -Ppassword'"
 strConn = "Provider=SQLOLEDB;Data Source=sqlnameorip;User ID=userid;Password=password"
 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open strConn
 objConn.Execute(strSQL)
 objConn.Close
 Set objConn = Nothing
 Set objFS = Server.CreateObject("Scripting.FileSystemObject")
 If objFS.FileExists(file) Then
  %><a href="/<%=shortfile%>"%>It's Ready - Click HERE!<%
 End If
 Set objFS = Nothing
End If
%>
</center>

The Explanation:

test.asp is the name of the ASP script/page that's used for this process.
"authors" & Session.SessionID & ".xls" is the actual name of the exported file that is too be used in the HREF statement
"/<%=shortfile%>".
Server.MapPath("/virtualpath") & Session.SessionID & ".xls" constructs the entire path to the file. NOTICE that I use the Session.SessionID variable here. Why? Because, of concurrency - to make sure that the file name is unique and does not cause problems due to the same file being accessed by other users, this method was implemented.
Server.Mappath maps the web site's virtual directory to a real/physical directory.
pubs..authors is the database/table that is to be exported to the destination file.
servernameorip is the name of the server (or IP address) where the destination file is to be written.
password is the password belongin the account accessing the directory in which this export file is to be written. I WILL BE DISCUSSING SECURITY REQUIREMENT BELOW:
I'll write the rest later... I'm tired.... uhhhmmmmm.... ohhhh..stretching......yawning....

Comments & Questions Form Comment(s):