Calling Stored Procedure in an .asp

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

Development Tools

ASP.NET

Calling Stored Procedure in an .asp

Author

Topic

sbhegel
Starting Member

19 Posts

Posted - 2005-12-15 : 10:09:59

Hello,

I don't know if this is the right place to post this and if not please let me know.

I am trying to call stored procedure from a .asp page. This isn't asp.net. I honestly don't know the difference since this is my second asp page.

The stored procedure takes about 2 - 3 minutes to run. When I submit the page to run, it will spin for about 30 seconds and then come up with a page cannot be display error. For all that I can tell the stored procedure does not run.

I posted the code below. What I am wondering is if I can call the SP and just have the page display the status of the SP every 10 seconds or so.

Here is the code that I have:

<%@ LANGUAGE="VBSCRIPT" %>


<html header stuff and body tag>

Set cn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
cn.Open "SQLDB", "adminuser", "adminpassword"
Set cmd.ActiveConnection = cn
cmd.CommandText = "custom.dbo.LoadQADParts"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute

cn.close

Any help would be appreciated.

Scott

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-12-15 : 12:57:39

asp is a scripting language, it was microsoft's first answer to dynamic web pages, while the .net is a more advance dynamic language, its a framework, a totally new innovation.

i guess this is the name of your SP ? custom.dbo.LoadQADParts

paste your full code here, i dont see you passing your parameters to the SP , also did you grant exec permissions to your SP ?

afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-12-15 : 13:07:49

a few other pointers worthty of note

always enclose your script in


<%
Set cn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
cn.Open "SQLDB", "adminuser", "adminpassword"
Set cmd.ActiveConnection = cn
cmd.CommandText = "custom.dbo.LoadQADParts"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute

cn.close
%>

then in debugging, always look for the error message

if it doesnt say in your browser, am assuming its internet explorer

go to tools >> internet options >> click the advanced tab, and scroll down to browsing which is the second option and uncheck, show friendly user error messages

IE would tell you the exact error, so you could easily debug

hope this helps

post any other problems you might be expieriencing

Afrika

sbhegel
Starting Member

19 Posts

Posted - 2005-12-15 : 13:45:33

That was the full code. Did you mean the full code for the stored procedure?

I have executed the stored procedure from withing query analyzer many times and I am logging in with the ca account so it should have the ability to run it.

I have the <% in the code on my asp, I just did not post them. Sorry about that. Here is exactly what my html page looks like.

CODE :

<%@ LANGUAGE="VBSCRIPT" %>

<html>
<head>
<title>Generate SQL Parts List</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<div align="center">
<p>
<%
strSubmit = request.form("submit")

if strSubmit = "Update List Now" then

Set cn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
cn.Open "db", "sa", "password"
Set cmd.ActiveConnection = cn
cmd.CommandText = "custom.dbo.LoadQADParts"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute

cn.close
end if

%>
</p>
</p>
<p> </p>
<form name="form1" method="post" action="">
<table width="300" border="1" cellspacing="0" cellpadding="2">
<tr>
<td><p><font size="4" face="Geneva, Arial, Helvetica, sans-serif">BLAHBLAHBLAHBLAH</font></p>
<p><font size="4" face="Geneva, Arial, Helvetica, sans-serif">This process will take about 2-3 minutes to complete.</font></p>
<p> </p></td>
</tr>
<tr>
<td><div align="center">
<input type="submit" name="Submit" value="Update List Now">
</div></td>
</tr>
</table>
</form>
<p> </p>
</div>
</body>
</html>

Thanks,
Scott

sbhegel
Starting Member

19 Posts

Posted - 2005-12-15 : 14:07:27

After changing my browser settings I get the following error displayed.

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

/manufacturing/GenMarkemDB.asp, line 25

Line 25 of the page is the cmd.execute statement. Is this timing out because it takes so long to run this procedure? Is there a way to submit this and not wait until it is done running and check on the status of it every 10 seconds or so?

Scott

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-12-15 : 15:32:20

two questions
what is line 25?
could you paste your SP here?

sbhegel
Starting Member

19 Posts

Posted - 2005-12-15 : 16:34:46

I figured out what was going on. I had to increase the command timeout setting to a higher value so the .asp page wouldn't time out at the server. After I set this the SP ran just fine.

Line 25 was the cmd.execute statement.

Thanks

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-12-16 : 03:17:32

the only thing that suprises me, is why it would take so long to run ?

what timeout value did you set it to ? and what is your SP set out to achieve ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-16 : 05:18:08

Refer this on how to call sp from ASP page
http://www.mindsdoor.net/aSP/DBAccess.inc.html

For timeout errors
http://vyaskn.tripod.com/watch_your_timeouts.htm

Madhivanan

Failing to plan is Planning to fail

sbhegel
Starting Member

19 Posts

Posted - 2005-12-20 : 09:42:42

The reason it takes so long to run is that it is connecting to a remote db that is a Progress DB. For whatever reason this connection and retrieval of information takes an extremely long time to connect and run.

I don't know why this is. When I connect to the DB using something like winsql that connection is very fast. I believe it has something to do with the fact that the remote server isn't using the ODBC driver that the database normally uses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources