asked on Triggering a stored procedure to copy from one tble to another using ASP.
INSERT INTO [dbo].[messagelog_archive]
SELECT * FROM [dbo].[messagelog]
WHERE [DateSent] < DateAdd(m,-3,getdate())
DELETE [dbo].[messagelog]
WHERE SN in
(Select SN from [dbo].[messagelog_archive]
I would like to implement this as a stored procedure that I can call from an ASP page, how would I get that done
SELECT * FROM [dbo].[messagelog]
WHERE [DateSent] < DateAdd(m,-3,getdate())
DELETE [dbo].[messagelog]
WHERE SN in
(Select SN from [dbo].[messagelog_archive]
I would like to implement this as a stored procedure that I can call from an ASP page, how would I get that done
Microsoft SQL Server 2005ASP
Last Comment
golfDoctor
What database are you using?
ASKER
MSSQL 2005
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oh yes... the asp code:
<%
Set cn = Server.CreateObject("ADODB
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB
Set cmd.ActiveConnection = cn
cmd.CommandText = "YourSprocName"
cmd.CommandType = adCmdStoredProc
cmd.Execute
%>
<%
Set cn = Server.CreateObject("ADODB
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB
Set cmd.ActiveConnection = cn
cmd.CommandText = "YourSprocName"
cmd.CommandType = adCmdStoredProc
cmd.Execute
%>
So you know, you could just run that like this:
<%
sql = "INSERT INTO [dbo].[messagelog_archive]
" SELECT * FROM [dbo].[messagelog] " & vbcrlf & _
"WHERE [DateSent] < DateAdd(m,-3,getdate()) " & vbcrlf & _
"DELETE [dbo].[messagelog] " & vbcrlf & _
"WHERE SN in " & vbcrlf & _
"(Select SN from [dbo].[messagelog_archive]
myconnection.execute(sql)
%>
<%
sql = "INSERT INTO [dbo].[messagelog_archive]
" SELECT * FROM [dbo].[messagelog] " & vbcrlf & _
"WHERE [DateSent] < DateAdd(m,-3,getdate()) " & vbcrlf & _
"DELETE [dbo].[messagelog] " & vbcrlf & _
"WHERE SN in " & vbcrlf & _
"(Select SN from [dbo].[messagelog_archive]
myconnection.execute(sql)
%>