asp - stored procedure exectuion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

asp - stored procedure exectuion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-14 : 08:34:28

sjanarthan writes "In Asp program, I want to execute a stored procedure (i.e. the stored procedure results like report format). How to execute the stored procedure from ASP Program. I am sure that the results are stored in Recordset. But I want to store the results (stored procedure exectuion results) in a textfile. What is the syntax and connection with database and execute the sp.

please give me full details.

I try the following but not produce the results

set con=Server.CreateObject ("ADODB.Connection")
con.open "DSN=jrly; uid=rsm; pwd=rsm987; connectstring=rdbm;"
set MyCommand=Server.CreateObject("ADODB.Command")
set MyCommand.ActiveConnection=con
MyCommand.CommandType = adCmdStoredProc
MyCommand.CommandText = "jana"
MyCommand.Execute

Here "jana" is a stored procedure (without any argument or parameters)

thank you for your kindness"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-14 : 09:02:12

Thanks for giving us your username and password...

But its an interesting question, can you save the contents of a recordset into a text file? I know you can serialize it as an XML file ...and then perhaps, apply an XSLT to transform into a CSV?

Owais

Make it idiot proof and someone will make a better idiot

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-14 : 22:50:45

quote:
I know you can serialize it as an XML file ...and then perhaps, apply an XSLT to transform into a CSV?

Ugh, you've been brainwashed too Owais! Make it stop!!!!

Very simple way to get a comma-delimited file in ASP:

set con=Server.CreateObject ("ADODB.Connection")
set rs=Server.CreateObject("ADODB.Recordset")
con.open "DSN=jrly; uid=rsm; pwd=rsm987; connectstring=rdbm;"
set MyCommand=Server.CreateObject("ADODB.Command")
set MyCommand.ActiveConnection=con
MyCommand.CommandType = adCmdStoredProc
MyCommand.CommandText = "jana"
set rs=MyCommand.Execute
data=rs.GetString(,,"," , vbCrLf, "") 'create comma-delimited version of recordset
rs.Close
con.Close
set fs=Server.CreateObject("Scripting.FileSystem") 'set up, open, and write file
set fil = fs.OpenTextFile("c:\testfile.txt", ForWriting, false)
fil.Write data
fil.Close

Anytime you need to convert data into a string, and you're using ADO, the GetString method is the best way to go. You can search SQL Team for "GetString" and you'll find some more examples, but the best resource for it is:

http://www.learnasp.com/

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-15 : 00:52:49

Or you could write it directly from the SP.
http://www.nigelrivett.net/WriteTextFile.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-15 : 01:15:29

But Rob, XML is the best thing to happen to the database industry since Ted Codd...

NOT

DavidM

"SQL-3 is an abomination.."

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-15 : 01:18:23

C'mon Rob, I was just pulling your leg, I read your blogs ya know!