sql query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sql query

Author

Topic

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-01-18 : 22:04:50

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 22:18:48

it would be more easy if you could explain the actual requirement complemented by structure of tables and some sample data

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-19 : 05:05:49

I assume this vbscript is in an asp page . If there is still a requirement to use th asp page , then you might want to look at a stored procedure using the ADO.COMMAND object to execute

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-01-19 : 13:30:30

The job should run automatically each night
An email confirmation should be sent indicating the job completed successfully

how this code performs?

thanks a lot!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-19 : 13:54:21

This will provide you with stub to work on. If you want precise code post your tale structure with some sample data.

CREATE PROC WebOrderEntry
AS

INSERT INTO CUSTOMER (CreditCardNo,other fields...)
SELECT CreditCardNo,...
FROM Sales Transactions Work table
WHERE OrderType='WEB'
AND OrderDate = DATEADD(d,-1,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))


DELETE FROM Sales Payment Work and History table t1
INNER JOIN Sales Transactions Work table t2
ON t2.orderID=t1.OrderID
WHERE t2.OrderType='WEB'
AND t2.OrderDate = DATEADD(d,-1,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))

GO

make a job to schedule the execution of SP and configue database mail to send confirmation mail if job is success.

The steps for configuring sql server agent job can be found here:-

[url]http://msdn2.microsoft.com/en-us/library/ms191439.aspx[/url]

Also refer link for configuring dbmail:-
[url]http://www.databasejournal.com/features/mssql/article.php/3626056[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-20 : 08:33:40

quote:
Originally posted by sqlhelp14

thanks

please dont overwrite your original post. add as a new reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources