asp front-end scripting problem

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 front-end scripting problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-23 : 08:33:56

Richard writes "Guys, Hey, please help with a problem thats driving me crazy.

First, heres the (daily8.asp) code...
* * * * * * * * * *
<FORM METHOD="get" ACTION="daily8.asp">


<INPUT TYPE="Submit" VALUE="Press Twice">

</FORM>


sConn = "Provider=SQLOLEDB;Data Source=UNITYSQL1\SQLSERVER;Database=Misun;Integrated Security=SSPI"
set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open sConn


sSQL = "ALTER PROCEDURE [sp_daily08] AS execute sp_daily " & Request.QueryString("date")
Set sResults = oConn.Execute(sSQL)


sSQL = "EXEC sp_runwebtask @procname = N'sp_daily08'"
Set sResult = oConn.Execute(sSQL)

* * * * * * * * * *
... I know there's probably 101 examples of bad practice here (not declaring things, not using functions, not turning buffering to false, not setting expires to zero, including the output, not tidying up afterwards) but I've experimented, and they don't help.

This works! (aslong as you click the button twice). I need a way to either, automatically 'click the button twice', stop browser caching, or insert a 'go' between the two querys - I think.

The more fundimental problem is not being able to import a value into a webtask EG: 'EXEC sp_runwebtask @procname = N'sp_daily 2' (2 being an offset from today IE: two days ago, EG: '@st_date = getdate()-@offset')

sorry starting to blabber
any help appreciated

Richard
oh, BTW the environment is Windows/SQL 2000"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-23 : 13:17:58

Why don't you just do this?

sConn = "Provider=SQLOLEDB;Data Source=UNITYSQL1\SQLSERVER;Database=Misun;Integrated Security=SSPI"
set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open sConn

sSQL = "EXEC sp_runwebtask @procname = N'sp_daily08 " & Request.QueryString("date") & "' "
Set sResult = oConn.Execute(sSQL)

Now you've got only one call to the SQL Server.

Edited by - robvolk on 01/23/2002 13:18:29

Richard101
Starting Member

30 Posts

Posted - 2002-01-23 : 17:38:42

Thanks for that but "EXEC sp_runwebtask @procname = N'sp_daily08 2'" or similar would give an error (saying that it couldn't find [sp_daily08 2], I think).

quote:

Why don't you just do this?

sConn = "Provider=SQLOLEDB;Data Source=UNITYSQL1\SQLSERVER;Database=Misun;Integrated Security=SSPI"
set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open sConn

sSQL = "EXEC sp_runwebtask @procname = N'sp_daily08 " & Request.QueryString("date") & "' "
Set sResult = oConn.Execute(sSQL)

Now you've got only one call to the SQL Server.

Edited by - robvolk on 01/23/2002 13:18:29

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-23 : 17:41:34

Sorry, I think I goofed, how about this:

sSQL = "EXEC sp_runwebtask @procname = N'sp_daily " & Request.QueryString("date") & "' "
Set sResult = oConn.Execute(sSQL)

Richard101
Starting Member

30 Posts

Posted - 2002-02-02 : 17:08:10

Thanks I'll try it on Monday, but I think the last paragraph in my original message covers it. That is, you can't input a value into a webtask. If you try to, -and use the wizard- it will ask what the value is (thus predefining it).

I suppose I could call sp_daily<n>, and have a bunch of sp's waiting IE: sp_daily1, sp_daly2, sp_daily3... This would work but, as the integer represents the number of days to count back from today, there would soon need to be hugh number of sp's !! (this MIS started collecting 5_Oct_01 - that's over one hundred already !!)

I've been all through this, and feel a workround will involve (the stuff I don't know about) getting the button to 'double-click' each time its pressed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources