SQL2000 Trigger or Stored Proc, export to .csv? 2

[metahari]

Hi there, I have a question.

I'm trying to figure out how to make a trigger or stored procedure or a combination of those that will export a table to a .csv file when you click a button on a webpage.

Has anyone done something like this?

I'm not really sure a trigger is even what I want or need. I use SQL 2000, but haven't needed to make a stored procedure or trigger.

thanks for your help,
Stacie

 

[NoCoolHandle]

You will need the trigger to either fire a dts package or call bcp, or Call xp_cmdshell and osql specifying an output file..

Just a few options..

I would tend to go towards xp_Cmdshell and the osql tool for simplicity..
BCP and or DTS for Speed...


HTH

Rob

PS YOu would need to write your query in a way to format the output as xml.. Either by string concatination or the "for XML" clause in a query.

 

[mrdenny]

A trigger isn't going to work for this, unless you want this export to happen every time a record is inserted, updated and/or deleted. A trigger is only fired when one of these three events happens.

What you want is to create a stored procedure. I would recommend having the stored procedure use xp_cmdshell run bcp to dump the table to a csv file.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[metahari]

Whoah, I'll have to dig into xp_cmdshell and osql, I've never even heard of those things. (looks through the msdn)

Any tips?

thanks,
Stacie

--Metahari

 

[mrdenny]

osql is a command line tool for running sql queries. From a command prompt run osql /? and it will give you a list of commands. BCP is a better tool for exporting data from the database. If you use osql to export the data you can have formatting problems within your text file.

xp_cmdshell is an extended stored procedure that allows you to run dos commands from a SQL command. Check out books online (the SQL help files) for syntax. xp_cmdshell will allow you to run any dos command that doesn't require user input.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[metahari]

How would I call this from the asp.

I've never used a stored procedure from asp, I would need some sort of object?

--Metahari

 

[NoCoolHandle]

If you stored proc was called proc1
in asp

<%
set cn = server.createobject("adodb.connection")
cn.open ("provider=sqloledb;server=yourServer;database=yourdb;Trusted_connection=yes")

cn.execute proc1
cn.close
set cn = nothing

%>

Have care of line wrap

 

[jbenson001]

You could crystal reports in your app and use it to export to excel.

 

[NoCoolHandle]

You know if you are doing this in asp..

why don't you open a recordset and then save it to an array then write the array to disk from the asp page..

the recordsets.getrows method returns all rows into a comma seperated array..

Simple matter to open a filesystemobject, create a textstream for write and then loop through your array to build the lines..

At that point save the text stream..

no nead to have sql do something your asp page can.

My 1c

Rob

 

[metahari]

I'm not certain that the asp page would be on the machine that needs the table exported to csv. That is a good point/option to keep in mind.


So this would look like

Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
>loop through the file here? Is this where I'd add commas?<
Set MyFile = fso.CreateTextFile("c:\testfile.csv", True)
~~~

Ok, just connection.execute storedproc , that seems simple enough. That also answers the next question I was going to ask , which was, does a stored proc have to have extra parameters, and it looks like no is the answer to that.


--Metahari

 

[NoCoolHandle]

In descending order....

yes, no, maybe, yes

Actually less cryptic..

You don't need to have either input or output parameters with storedprocs..

And the idea of working with the fiesystem object is basicly correct.. I will hack something together tomorrow and post back. I am off home for a beer@!

If you want to read the results of a storedproc the connection execute has a final parameter of recordset which allows you to execute a storedproc and return the result set, but I tend to go with the recordset.open and just pass it the proc and parametrs like a sql statement..
eg,

rs.open "someproc 'param1','param2'" ,cn

however this has strengths and weeknesses.. using the command object allows you to return outparameters, and you can't do that via the connection or recordset without building a command object.

Another wierd option with Connections and storedprocs.. is that you can execute the proc as a method of the connection.

ie if you had a proc with 2 integer parameters and it was called SomeProc (not the case)
you can execute it

cn.SomeProc 2,4,Recordset

where cn = your connection and recordset is your recordset.

1 big word of caution.. If your app is going to be around for a while, THe last option tends to cause BIG memory leaks..

We have a suite of apps that run 24/7/365 and some haven't been restarted in over a year. It isn't noticable at first, but over a period of weeks I managed to consume every piece of memory on our clients and drag them to a halt...

I would also tend to stay away from this approach with asp as asp tends to leave objects open far longer than you would anticipate..


Rob

 

[NoCoolHandle]

I played with this a bit, and BCP is the easiest way
Using the recordset on the server required a little work. GetRows seems better for what you want than GetString (it has been a while since I worked in non dotnot.)

So.. 3 Choices
1. Bcp from a proc... (execute proc as above)
OK first BCP..

exec master.dbo.xp_cmdshell 'bcp "select firstname,lastname from northwind..employees" queryout "c:\employeelist.txt" -c -T -t,'

Where -c = Character Mode (ascii results)
-T = Trusted_Connection = yes
-t, = comma sepearated fields...

Produces a text file that contains the following

Nancy,Davolio
Andrew,Fuller
Janet,Leverling
Margaret,Peacock
Steven,Buchanan
Michael,Suyama
Robert,King
Laura,Callahan
Anne,Dodsworth
rob,lynch
Micky,mouse

2 and 3
filesystemobject and asp
Save it as some asp file in your webserver (in an asp dir) and execute it in your browser.. (have some care of linewrap.)

<% @Language=vbscript %>
<html>
<body>
<%
Set rs = Server.CreateObject("Adodb.Recordset")
rs.Open "Select firstname,lastname from Northwind.dbo.employees","Provider=sqloledb;server=localhost;trusted_connection=yes"

results = rs.GetString
Response.write "<b>Results from GetString Call (Tab Seperated Ascii String)</b><br>"
Response.write "<pre>" & results & "<br></pre>"

Set fso =  Server.CreateObject("scripting.filesystemobject")
Set ts = fso.CreateTextFile("C:\Export1.txt", True)
ts.Write results
ts.Close

response.write "<b><i>File c:\Export1.txt created....</i></b><br><br>"

rs.MoveFirst
results = rs.GetRows()

Dim x
Dim y
Dim s

s = ""
For y = LBound(results, 2) To UBound(results, 2)
    For x = LBound(results, 1) To UBound(results, 1)
        s = s & results(x, y) & ","
    Next
    s = Left(s, Len(s) - 1) & vbCrLf
Next

Response.write "<b>Results from GetRows Call - returns a 2 dimensional array that represents the rows and fields</b><br>"
Response.write "<pre>" & s & "</pre>"
Set ts = fso.CreateTextFile("C:\export2.txt", True)
ts.Write s
ts.Close
response.write "<b><i>File c:\export2.txt created....</i></b>"

%>
</body>
</html>

 

[metahari]

Ok, so, I need to make a stored procedure that does this?


So I should:
Right-click Stored Procedures; then click New Stored Procedure called 'procExportTable'
Enter the text of the stored procedure.
(Paste this?)
exec master.dbo.xp_cmdshell 'bcp "select firstname,lastname from northwind..employees" queryout "c:\exportedtable.csv" -c -T -t,'

Press CTRL-TAB to indent the text of a stored procedure.

and then call it by this asp page?

Page called 'exporttablepage.asp'

<%@LANGUAGE="VBSCRIPT"%>
<%@ Language=VBScript %>
<%
set cn = server.createobject("adodb.connection")
cn.open ("provider=sqloledb;server=yourServer;database=yourdb;Trusted_connection=yes")
dim ExportYes
ExportYes=Request.Form("submit")
If ExportYes <> " " then
     cn.execute procExportTable
End If
cn.close
set cn = nothing
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html>
<head>
	<title>Export Table</title>
</head>
<body>
<div align="center">
<h1>Export Table</h1>
<p>Pressing this button will export a comma separated file to : c:\exportedtable.csv
<form method="post" action="exporttablepage.asp">
<input type="submit" name="submit" value="Export Table">
</form>
</div>
</body>
</html>

Will this work?

--Metahari

 

[NoCoolHandle]

Yes that should work..

For the text to put into QA (i tend to stay away from the wizards.. but they do the same thing

Create Proc procExportTable
as
Set NoCount ON
exec master.dbo.xp_cmdshell 'bcp "select firstname,lastname from northwind..employees" queryout "c:\exportedtable.csv" -c -T -t,'

OF course if you are using the wizard just cut the stuff after the as..

I think it is important to not the use of
[blue]set nocount on[/blue]

This can actually cut back on app overhead. Normally any command sends back the results + as an extra return the number of rows being affected.
(recordsaffected argument of the command and connection objects)
If you aren't interested in them (and in this case I see no need) specifying that can speed up your app and reduce network traffic (ok very slightly - but every little bit.....)

In short . Looks good to me. (except the 2 instances of setting the @language attribute

and
<snip>
<p>Pressing this button will export a comma separated file to : c:\exportedtable.csv
</snip>
as you pointed out earlier the path of the file is being sent relative to your SQL server and not the web app..


Rob

 

[metahari]

Oh wow, thanks!
Oops, must have been scrolled down when I put the second language line in. And I forgot a </p>!

Just another quick clarification.

northwind..employees == database..table ??

Good call on the export location, again, I'll make that clearer.

--Metahari

 

[NoCoolHandle]

Yes..

[blue]Northwind[/blue] = database

[blue]..[/blue] (no gap between dots) = shorthand for DBO in some documentation (but better to
be explicit - I suspect the .. = active user or dbo, but that requires a quick check to sysojbects to see if there is a rob.employees first and then looks for the dbo.employees table)

[blue]Employees[/blue] = Table

Most correctly, it should be
Northwind.dbo.Employees

If you wanted to output to the webserver, the other script worked very well (I was so surprised I started to send it adhoc querys, batchs and it did very well

THe biggest issues I bumped into were that if you were returning multiple recordsets, using getstring tended to block the second recordset, but Getrows worked great.

Worth playing with
Latest modified script

<% @Language=vbscript %>
<html>
<body>
<%
On Error Resume NExt
Dim x
Dim y
Dim s

If len(request("Query") )= 0 then
Response.write "<form method=get><textarea Style=""width:600px;height:400px;"" name=Query>Select firstname,lastname,title from Northwind.dbo.employees</textarea><br><input type=submit></form>"
else
Response.write "You submitted the following command:" &  request("Query") & "<br><br>"
set cn = server.createobject("adodb.Connection")
Set rs = Server.CreateObject("Adodb.Recordset")
cn.open "DRIVER={sql server};server=127.0.0.1;trusted_connection=yes"
Query = request("Query")
rs.Open Query,cn
do until err.number <> 0
results = rs.GetRows()
x=0
y=0
s = ""
if ubound(results) > 0 then
For y = LBound(results, 2) To UBound(results, 2)
    For x = LBound(results, 1) To UBound(results, 1)
        s = s & results(x, y) & ","
    Next
    s = Left(s, Len(s) - 1) & vbCrLf
Next
Response.write "<b>Results from GetRows Call - returns a 2 dimensional array that represents the rows and fields</b><br>"
Response.write "<pre>" & s & "</pre>"

'Set ts = fso.CreateTextFile("C:\export2.txt", True)
'ts.Write s
'ts.Close
set ts = nothing

response.write "<b><i>File c:\export2.txt created....</i></b>"
end if
set rs = rs.nextrecordset
Loop
response.write "error : "  & err.description
rs.close
cn.close
set rs = nothing
set cn = nothing
End if
%>
</body>
</html>

 

[metahari]

Thanks so much for your help.

It's crazy, I'm in SQL everyday for something, mostly some queries and testing, and there's still so much to learn.

Thanks for letting me ask the little questions too.

--Metahari