SP

HI,
I have a stored procedure which was working untill day before yesterday but all of sudden it stopped working today. It doesn't give any error. Can somebody please, help me with this problem? What can be the reason?
Thanks,
Jackson

Can you use sp:Stmt Starting and sp:Stmt Completed events on Trace and filter for the said stored procedure?Also, check on SQL Server's error log for any possible errors.
Is the stored procedure called from an applciation? Did you try running from Management Studio?

I have seen this behaviour in early versions of SQL Server When there is a high resource utilization, it happend... Not heard of the same thing in SQL Server 2005.

Hi preethi,
Thanks for quick reply. I will try that way.
Jackson

Hi Madhivanan,
Here is the procedure code.
create procedure DB_DatabaseBackup
@Path varchar(500) ,
@DBName varchar(128),
@BackupType Varchar(10)
as
declare @FileName varchar(4000)

If @BackupType = 'FULL'
BEGIN
select @FileName = @Path + @DBName + '_Full_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'
backup database @DBName
to disk = @FileName WITH init
END

If @BackupType = 'Diff'
BEGIN
select @FileName = @Path + @DBName + '_Diff_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'

backup database @DBName
to disk = @FileName
with differential, INIT
END

If @BackupType = 'Log'
BEGIN
select @FileName = @Path + @DBName + '_Log_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
+ '.bak'

backup log @DBName
to disk = @FileName WITH INIT
END
Thanks,
JACKSON