All Forums Database
rockett 3 posts Joined 06/10
29 Jun 2010
About call Stored procedure in ASP

I want to get a recordset in asp which is returned from teradata stored procedure.

from myTable;
OPEN cur1;
-----------------it running well and get a result list correctly by execute "call test()" in SQL Assistant.----------------------------

Then I tried several different ways in ASP to get the result list:

***DB connection using ODBC as:

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("DSN=myDSN;Database=myDB;Uid=myUsr;Pwd=myPwd;" )


Set objCmd = Server.CreateObject("ADODB.Command")
With objSPCmd
.ActiveConnection = DBconn
.CommandText = "test"
.CommandType = &H0004
End with
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = objSPCmd.execute

----------then it returns error says:
test is not a macro.


Set objCmd = Server.CreateObject("ADODB.Command")
With objSPCmd
.ActiveConnection = DBconn
.CommandText = "call test"
.CommandType = &H0004
End with
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = objSPCmd.execute
---------it returns error says:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[NCR][ODBC Teradata Driver][Teradata Database] '{' ('7B'X) is not a valid Teradata SQL token.

Set RS = Server.CreateObject("ADODB.RecordSet") "call test()" , DBConn
Response.Write RS.RecordCount

---------it reuturns error says:
Operation is not allowed when the object is closed.
*I think that means no record list returned, but I tested if "RS IS Nothing", it returns false.

Set RS = Server.CreateObject("ADODB.RecordSet") "SELECT col1,col2 from myTable" , DBConn
Response.Write RS.RecordCount
---------it running well, means at least the DB connection is OK and I also tried to execute Macro, it works, too. But actually, I have some more comlecated logic that must use SP to implement instead of Macro.

So, my question is:
1.How can I call a Teradata Strored Procedure in ASP correctly (not a Macro), and get the returned Recordset?
2.When I try to get the return value in ASP, what's the difference between running a select sql and returnning a cursor from SP?

Thanks very much, it really makes me mad.

Adeel Chaudhry 773 posts Joined 04/08
29 Jun 2010


Its been an age i worked on ASP .... but from what i can remember .... try looking into ADODB.Connection object and using that try to execute the command over that connection and use MOVENEXT to move to next row.

Following is the code what i can think of right now, but again its been ages with ASP so you better test it out thoroughly:

conn = server.createobject("adodb.connection")
c.Open Application("dbConn")
set r = c.Execute("CALL SP1()")
do while not r.bof and not r.eof
response.write r(0)




-- If you are stuck at something .... consider it an opportunity to think anew.

rockett 3 posts Joined 06/10
29 Jun 2010

Thanks very much for your reply.

Actually, your code is no error to run, but also no result output. In other words:
set r = c.Execute("CALL SP1()") executed successfully, but empty in RecordSet r.

but it will work fine to execute a Macro by
set r = c.Execute("EXEC myMacro()")

That's why I was puzzled: What's the difference between the result of select SQL and the cursor returned from SP, which related to the same source.

rockett 3 posts Joined 06/10
30 Jun 2010

What's more, I wrote a macro to make sure the DBcommand can execute directly, in which call the previous stored procedure:

create Macro ttt as (
call test();

And in ASP :
Set objCmd = Server.CreateObject("ADODB.Command")
With objSPCmd
.ActiveConnection = DBconn
.CommandText = "ttt"
.CommandType = &H0004
End with
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = objSPCmd.execute

--------it executed successfully, but still no data in RecordSet
what's wrong?.........

Saiira 1 post Joined 08/10
01 Aug 2010

Hi ,
Any success ? I am facing the same problem ...

pederfrohm 1 post Joined 04/10
20 Dec 2010


I´ve tried solving this issue all day, until i finally noticed that the option "Return Ouptut Parameters as Resultset" on my dsn was unticked, hence never returning anything through stored procedure calls. I had the same behaviour in Teradata SQL Assistant due to this feature. When i ticked it everything started to work as planned. Hope this is the solution in your case too, i see at least that you are using DSN.

The option is located under "Control panel"->"administrative tools"->ODBC Data Source Administrator-> [The DSN] -> configure -> options.


pdruley 3 posts Joined 01/10
21 Feb 2011


Did you ever get your stored procedure to return results to ASP? I am trying to do something very similar in an Excel Macro and getting the same errors.

I have tried changing my ODBC settings as pederfrohm suggested but I don't think that has anything to do with this paritucluar issues since it wasn't necessary to do so in SQL Assistant for me.


jerboa 10 posts Joined 04/12
22 May 2012


Hello everyone!

I can't create stored procedure which will execute (call) other existing stored procedures.

Can somebody help me?

ulrich 816 posts Joined 09/09
22 May 2012

why do you post this 6 times? Better open one new thread

And without sharing your code you are unlikely to get the right help.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

jerboa 10 posts Joined 04/12
22 May 2012

I am sorry for it :) 



replace procedure call_all_SP ()                  



call created_SP ('P9', 'P8');

call modified_SP ('P9', 'P8');

call removed_SP ('P9', 'P8);



This procedure created successfully, without any errors. But when I call it there are no results (it is not true):

call ABD.call_all_SP();

jerboa 10 posts Joined 04/12
22 May 2012

may be there are no results because of all these 3 procedures (created_SP, modified_SP, removed_SP)  has multiple result set... and I must  indicate something as  DYNAMIC RESULT SETS 2 or something else...     

You must sign in to leave a comment.