you need to modify your code like below.

IF EXISTS(SELECT 1 FROM master.dbo.sysobjects Where name='sc_CRDB' and Type='P')

DROP PROCEDURE sc_CRDB

GO

CREATE PROCEDURE sc_CRDB

AS

IF EXISTS(SELECT 1 FROM master.dbo.sysdatabases where name='MNCPCORP')

DROP DATABASE MNCPCORP

--GO

CREATE DATABASE MNCPCORP

ON...

Another way:

;WITH cte AS

(

SELECT ID

,dense_rank() over ( partition by ID order by ID,) AS R

FROM MyTable

)

SELECT ID

FROM cte

WHERE R > 1

GROUP BY ID

Go with Lynn. Mine is wrong in your scenario.

It converts Second in integer value to HH:MM:SS format

Try this:

SELECT DISTINCT j.name

,AVG(h.run_duration)

,substring(convert(varchar(25),(dateadd(ss

,AVG(h.run_duration)

,convert(datetime,convert(varchar(10),getdate(),121))

)

),121)

,11,9) As AvgDuration

FROM sysjobs j

INNER JOIN sysjobhistory h ON h.job_id=j.job_id

INNER JOIN

(SELECT job_id, MAX(STR(run_date,8)+ STR(run_time,8)) as LastRunDate

FROM sysjobhistory GROUP BY job_id) x

ON j.job_id=x.job_id

GROUP BY j.name

ORDER BY j.name

Yes I did bring the original names back and it is working since then...

Steve,

I would also say that...Please don't stop.

I visit the site everyday...to read articles...to participate in forums...to read the discussions going on in different post....to participate in QotD. In all,...

Oops...my bad...I didn't notice that...I also now think it is 2000..

You can write a blog on configuring DBMail 🙂 Again a small but helpful article.

Yes..He has not mentioned...

My reply stands for 2005 and 2008;-)

Hey Bru, I checked you site...you have small...

sp_send_dbmail must be present in MSDB under system Stored procedures.

To use this procedure to send mails first you need to configure Database Mail under the Management tab in SSMS.

You can use a for each loop in SSIS to load all the files in the folder.

Search on BOL for details on Foreach loop.

Lets wait for his reply.:Whistling:

Bru Medishetty (11/25/2009)

---

vikas bindra (11/25/2009)

---

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'ProfName',

@recipients = '',

@query = 'SELECT 1,1,1,1,1',---specify your select...

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'ProfName',

@recipients = '',

@query = 'SELECT 1,1,1,1,1',---specify your select query here

...

There is no END IF in t-SQL.

use

IF

BEGIN

END

ELSE

BEGIN

END

Nabha (11/23/2009)

---

Jeff Moden (11/20/2009)

---

Heh... no it's not...that function has a While Loop in it and it's going to...