Change SQL Object with ASP

[Esoteric]

Does anyone know if you can alter an SQL object with ASP? For instance: VIEW(s) I want to change the parameters in a VIEW statement within SQL and have it saved each time to run a report. Is this possible?

I need to update the VIEW(s) before the actual report executes. Currently I have to go in and manually update them.

 

[rac2]

You can submit DROP VIEW and CREATE VIEW statements with ADODB. The login for your ASP script will need dbo permission, or the minimum permission needed to add a view to the database.

Could you re-organize the queries so that the VIEW is always the same but query it with parameters in a stored procedure?

From ASP call the stored procedure with parameter values

CommandText = "{CALL monthly_report(vDateBegin, vDateEnd)}"

In the stored procedure use the parameters to query the view.

CREATE PROCEDURE [monthly_report](
@from DATETIME,
@thru  DATETIME)

AS

SELECT * FROM myViewForMonthlyReports
WHERE date_of_activity BETWEEN @from AND @thru

 

[AgentM]

Have you tried this in ASP:
----
Sql = "Begin Procname("& par1& ", " & par2& " end;"
conn.execute(sql)
--------
conn is your ative connection
Procname is the name of your procedure

hope this is of some use

 

[link9]

Check into "ALTER VIEW"

The benefit of using ALTER rather than DROP + CREATE is that with ALTER, you don't have to reset permissions, but rather leave them intact while changing the VIEW just the same:

"ALTER VIEW vMyView AS SELECT * FROM someTable"

"ALTER VIEW vMyView AS SELECT * FROM someOtherTable"