quote:

---

Originally posted by yadhu_cse

hi,
can help me in this regard


ALTER PROCEDURE [dbo].[databaseSnapshot]

AS
BEGIN
declare @MyDay varchar(20)
declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)
DECLARE @Mytime VARCHAR(50)
DECLARE @Myminute VARCHaR(50)
set @Myday = (SELECT DATENAME(WEEKDAY,getdate()))
print 'It is ' + @MyDay
set @Mytime=(SELECT DATEPART(hour,GETDATE()))
set @Myminute=(SELECT DATEPART(minute,GETDATE()))
Set @DatabaseName ='MyDB'
Set @SnapDataName='MyDB'
Set @SnapshotName ='MyDB_Snapshot'+'_'+@MyDay
Set @SnapFilename ='c:\data\MyDB_Data'+'_'+@MyDay+'.ss'
Print 'Snapshot name is ' +@SnapshotName
select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName
if @@rowcount <>0
begin
set @query = 'Drop database '+ @SnapshotName
print @query
exec(@query)
end
SET @query='create Database ' + @SnapshotName + ' on (Name=''' +@snapDataName +''','
+N'Filename=' +'''' +@SnapFilename +''')' +'AS SNAPSHOT of ' +@databasename
print @query
exec(@query)
END

GO

exec databaseSnapshot

---

quote:

---

Originally posted by yadhu_cse

Thanks bohra

can u tel me what mistake i have done in this code its recurssivly executing


ALTER PROCEDURE [dbo].[EmailHandlerWithUpdate]
@useprofile VARCHAR(50)
WITH EXECUTE AS 'dbmailuser'

AS
SET NOCOUNT ON

BEGIN
DECLARE @tableHTML VARCHAR(100)
DECLARE @maxSeqno INT
DECLARE @minSeqno INT
DECLARE @emailTo VARCHAR(100)
DECLARE @emailSubject VARCHAR(100)
DECLARE @emailCC VARCHAR(100)
DECLARE @emailBody VARCHAR(1000)
DECLARE @emailSenton DATETIME
DECLARE @emailAttach VARCHAR(50)
DECLARE @file_exists INT
SET @emailSenton=NULL
SET @maxSeqno=-1

SET @minSeqno=0
SELECT @minSeqno= ISNULL(MIN(Seqno),0),@maxSeqno=ISNULL(MAX(seqno),-1)
FROM EmailsToSend
WHERE emailSenton IS NULL


BEGIN TRY
WHILE (@minSeqno<=@maxSeqno)
BEGIN
SET @emailSenton=NULL
SELECT @emailTo=emailto, @emailSubject=emailSubject,@emailBody=emailBody,@emailCC=emailCC,@emailSenton=emailSenton, @emailAttach=emailAttach
FROM EmailsToSend
WHERE Seqno=@minSeqno


EXEC master.dbo.xp_fileexist @emailAttach, @file_exists OUTPUT
BEGIN
IF @emailSenton IS NULL
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test',
@recipients = @emailTo ,
@body =@emailBody ,
@body_format='HTML',
@copy_recipients=@emailCC,
@subject =@emailSubject ,
-- @query='SET NOCOUNT ON select *from databasemail.dbo.emailstosend FOR XML PATH(''ROW''), root(''ROOT'') type',
@query='select *from databasemail.dbo.emailstosend',
@attach_query_result_as_file=1,
@query_result_separator=',',
@query_result_width=100,
@query_result_no_padding=1,
@query_result_header=1,
@file_attachments=@emailAttach;
UPDATE EmailsToSend SET EmailSentOn=GETDATE() WHERE Seqno=@minSeqno

END
SET @minSeqno=@minSeqno+1

END
END
print @minSeqno
END TRY


BEGIN CATCH
SELECT 'Problem With Update'+ ERROR_MESSAGE()
END CATCH
END

GO

exec OroviaDatabaseEmailHandlerWithUpdate 'Test'



I am gettting following error



Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

---

quote:

---

Originally posted by yadhu_cse

Thanks a lot bohra.
i was fed up with thanks a lot

---

quote:

---

Originally posted by yadhu_cse

hi ,
can u tel me what other things can be done using snapshot. i did snap shot creation and restore from snapshot.

---