June 24, 2004 at 3:20 pm
Hi everyone,
Can anyone plz help me to solve this issue.
In my SQL Server Enterprise Manager, when I right click and select the restore command, I get the following error message:
Error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.
I'm totally baffled what this means...
Thanx...and I appreciate whatever help I get.
Muneeb.
June 24, 2004 at 10:23 pm
If the you are equating one value from the outer query but the subquery returns more than one value in that case this type of error occurs.
June 24, 2004 at 11:51 pm
I too have seen this error, with queries like
select * from table1 t1 where t1.id = (select t2.id from table2 t2 ... where t2.id = t1.id)
The error occurs when the subquery returns more than result for any single occurrence of t1.id.
But ... it sounds as though you are getting the error within EM - after right-clicking on a database, selecting 'All Tasks' and then 'Restore Database'. Is that correct?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 25, 2004 at 8:34 am
2 things to try:
1 - You can run a profiler trace while you perform this command. This should allow you to see the SQL or stored procedure that is causing the error. Then, determine what the subquery is accessing and clean up whatever tables are involved. This would require you to modify tables in msdb, but it should work.
2 - Use the "restore database" command from T-SQL instead of relying on the GUI in EM. I learned a long time ago to "never trust your GUI". You should find plenty of examples in SQL BOL to perform the restore in this way (using T-SQL). You will need to know where the backup exists at on disk. By default, it will be under "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP" (replace C: with whatever drive SQL Server was installed in).
Hope one of these helps!
June 25, 2004 at 10:53 am
Run the profiler as geh1 suggests to see the query that is causing the problem.
It sounds like one of your 'Backup...' system tables on database MSDB might have a problem record on it or the indexes are corrupt. Have you had any probems with backups failing half way through? Perhaps rebuild the indexes on the tables called 'backup...' in MSDB.
It would help if we knew what version of SQLServer you are running?
Regards
Peter
January 3, 2012 at 7:21 am
Did you solve this error ? I am getting this error too and i am working on it. please let me know if you found any solution please.
February 6, 2012 at 10:02 am
This error is generated by enterprise manager.
When you click in restore, EM run this query
declare @b-2 int;
set @b-2 = (select backup_set_id
from msdb..restorehistory o
where o.restore_date = (select max(i.restore_date)
from msdb..restorehistory i
where i.destination_database_name = N'database_Name_here'));
select min(backup_set_id)
from msdb..backupset
where type = 'L'
and backup_set_id > @b-2
and database_name = (select database_name
from msdb..backupset
where backup_set_id = @b-2)
And when EM set @b-2, the second subquery obtain de maximun restore date of "database_Name_here" database. if you have several restores at the same date, hour (second and millisecond), the result of value it's a set of "backup_set_id" and not an unique value, therefore, EM launch the error 512.
EM Run this query for all database in the server, for this, in several oportunities, EM launch this error.
Sorry for my english.
Greetings,
Fernando
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply