Can not delete Agent Jobs

Seems that someone deleted the maintenace jobs and now I can not delete any
of the jobs associated with them. I get error:
TITLE: Microsoft SQL Server Management Studio
--
Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP WSS
Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
--
BUTTONS:
OK
--
Any Idea's?
ThanksClick on the Jobs node in SSMS and click Refresh.
You know, sometimes items are not refreshed automatically in SSMS (and EM of
course) and you see that Maintenance tasks are deleted but jobs are still
there but they are not actually. So you are trying to delete the deleted
stuff. I've seen this situation lots of times and I thought this could be
your situation.
--
Ekrem Ã?nsoy
"RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in message
news:8A0F2AF9-A5EC-4052-A162-99C94ABD5C96@.microsoft.com...
> Seems that someone deleted the maintenace jobs and now I can not delete
> any
> of the jobs associated with them. I get error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP WSS
> Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
> --
> BUTTONS:
> OK
> --
> Any Idea's?
> Thanks|||Geez whiz, Now I feel dumb. Thanks However I still have one out there I
can not get rid of. I get error:
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (.Net SqlClient Data Provider)
"Ekrem Ã?nsoy" wrote:
> Click on the Jobs node in SSMS and click Refresh.
> You know, sometimes items are not refreshed automatically in SSMS (and EM of
> course) and you see that Maintenance tasks are deleted but jobs are still
> there but they are not actually. So you are trying to delete the deleted
> stuff. I've seen this situation lots of times and I thought this could be
> your situation.
> --
> Ekrem Ã?nsoy
>
> "RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in message
> news:8A0F2AF9-A5EC-4052-A162-99C94ABD5C96@.microsoft.com...
> > Seems that someone deleted the maintenace jobs and now I can not delete
> > any
> > of the jobs associated with them. I get error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
> > (Microsoft.SqlServer.Smo)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP WSS
> > Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
> > Any Idea's?
> > Thanks
>|||Hi Rick!
There is a Foreign Key constraint there.
Try this:
USE MSDB
SELECT * FROM dbo.sysmaintplan_subplans
Find your plan_id and then
DELETE FROM dbo.sysmaintplan_log WHERE plan_id = 'plan_id '
EXECUTE sp_delete_maintenance_plan 'plan_id '
Ekrem Ã?nsoy
"RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in message
news:F7029524-ACA4-4F62-85F7-18D5EB924F46@.microsoft.com...
> Geez whiz, Now I feel dumb. Thanks However I still have one out there I
> can not get rid of. I get error:
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column 'job_id'.
> The statement has been terminated. (.Net SqlClient Data Provider)
> "Ekrem Ã?nsoy" wrote:
>> Click on the Jobs node in SSMS and click Refresh.
>> You know, sometimes items are not refreshed automatically in SSMS (and EM
>> of
>> course) and you see that Maintenance tasks are deleted but jobs are still
>> there but they are not actually. So you are trying to delete the deleted
>> stuff. I've seen this situation lots of times and I thought this could be
>> your situation.
>> --
>> Ekrem Ã?nsoy
>>
>> "RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in
>> message
>> news:8A0F2AF9-A5EC-4052-A162-99C94ABD5C96@.microsoft.com...
>> > Seems that someone deleted the maintenace jobs and now I can not delete
>> > any
>> > of the jobs associated with them. I get error:
>> >
>> > TITLE: Microsoft SQL Server Management Studio
>> > --
>> >
>> > Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
>> > (Microsoft.SqlServer.Smo)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP
>> > WSS
>> > Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
>> >
>> > --
>> > BUTTONS:
>> >
>> > OK
>> > --
>> >
>> > Any Idea's?
>> > Thanks

Can not attach database,

Can not attach database,
Error message;
The file you've specified is not a valid SQL Server database file.

My costumer HDD some files Deleted mybe elektric prblem -Elektric
go,com,go,come- then windows not opened.
insert corrupt hdd Another computer, then recovering (recover my files
software)
Recover mdf and ldf files. but I can not not attach mdf. file,

I am recovery .mdf file http://www.officerecovery.com/mssql/ some table ok.
but my importand table is not ok.

if I can recover another recovery software , my database correct recover or
same.?
I am using RecoverMyFiles.

Pleace help me.

Thanks.

It appears that you have a corrupted database file (.MDF), and have been attempting to use a 3rd-party data recovery tool to extract as much data as possible from the damaged database. Is this correct?

It also appears that your disk is likely damaged. Can I assume that you've replaced it?

The primary response in this situation would always be to restore the database from backup. When is your most recent database backup from? Without a valid backup, there's unfortunately not much you can do.

sql

Can not access the Deleted System Table

I have a problem where I can not perform "Select * from Deleted" within
a trigger, but the problem occurs when I insert/update data from
Enterprise Manager ONLY. The trigger operates fine if I modify data in
Query Analyzer or through VBCode.
I finally found a workaround, modifying the select statement to "Select
* Into #deleted from Deleted", but am worried that will impact
performance on larger databases. I'm 99% certain it is just an access
issue somewhere, I just can't figure out where.
Any help y'all can suggest is appreciated.
Here is a snippet of the trigger:
CREATE trigger InsertUpdate_Table1
On Table1
For Update, Insert As
Declare @.Temp Int
Select * from Deleted
Select @.Temp = @.@.Rowcount
If @.Temp > 0
{rest of the code has been confirmed to work just fine}> Select * from Deleted
> Select @.Temp = @.@.Rowcount
So your trigger returns 2 resultsets? I can see how this would confuse EM
because special application code is needed to handle this situation.
This technique to get the number of updated rows is a bad practice for a
number of reasons. The proper way to get the number of updated rows is with
@.@.ROWCOUNT after the UPDATE statement rather in the trigger itself:
UPDATE dbo.Table1
SET MyColumn = 1
SELECT @.@.ROWCOUNT
Hope this helps.
Dan Guzman
SQL Server MVP
"Fayven" <FayvenWren@.gmail.com> wrote in message
news:1154833842.671883.224220@.b28g2000cwb.googlegroups.com...
>I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>|||FYI - SQL EM is using RPC to run the SQL Statements
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Fayven" wrote:

> I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>sql

Can LOG PI or Log Explorer recover deleted records BEFORE

installation of their softwares? If there has NEVER been a backup of
the Database (.mdf), Recovery Model Set to Full, and there has never
been a backup of the Log (.ldf) files?

Using the trial software for both, this was unclear.

Thank you for your time,

bd"bd420" <bd420@.hotmail.com> wrote in message
news:1136940831.527840.99440@.o13g2000cwo.googlegro ups.com...
> installation of their softwares? If there has NEVER been a backup of
> the Database (.mdf), Recovery Model Set to Full, and there has never
> been a backup of the Log (.ldf) files?

My understanding is that as long as you have NOT truncated your log file the
answer should be yes.

> Using the trial software for both, this was unclear.
> Thank you for your time,
> bd|||bd420 (bd420@.hotmail.com) writes:
> installation of their softwares? If there has NEVER been a backup of
> the Database (.mdf), Recovery Model Set to Full, and there has never
> been a backup of the Log (.ldf) files?
> Using the trial software for both, this was unclear.

I believe the answer is no. The key here is that you have never taken a
backup of the database. As long as you have done that, the log works as
if you were in simple recovery. After, a log is supposed to be applied
to a database back, so if there isn't any...

But hadn't it been for this fine detail, the answer would be yes. That
is, there is a full backup taken some time, and you are running in
full recovery.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

can I reset tbl ID to 1

I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate.

Is there any way to reset the indentity column without removing the constraints?

Hi Jack,

You can reset the seed of the identity using DBCC CHECKIDENT.

See this link for more info:

Resetting IDENTITY