Dynamic SQL in Restore Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL in Restore Statement

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-06-29 : 11:04:00

Folks

I am running the following query a server server1:

declare @db_backup_file_name varchar(256)
Set @db_backup_file_name=

select replace((
select physical_device_name from backupmediafamily where media_set_id in (

select top 1 media_set_id from backupset where type='d' and database_name='db1'
order by backup_start_date desc)),'g:','\\server1');

It errors our at select replace.
But, if I just run the select replace downwards, it returns correct string. Is there a way to assign this value to a variable?

Regards
Paresh Motiwala
Boston, USA

mobius
Starting Member

13 Posts

Posted - 2007-06-29 : 11:18:39

what was the error message?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-06-29 : 11:22:42

quote:
Originally posted by mobius

what was the error message?

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.

Regards
Paresh Motiwala
Boston, USA

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-29 : 12:06:19

oops
select @db_backup_file_name=
replace((
select physical_device_name from backupmediafamily where media_set_id in (

select top 1 media_set_id from backupset where type='d' and database_name='db1'
order by backup_start_date desc)),'g:','\\server1');

I would do

select @db_backup_file_name=
physical_device_name from backupmediafamily where media_set_id in (

select top 1 media_set_id from backupset where type='d' and database_name='db1'
order by backup_start_date desc))

select @db_backup_file_name = replace(@db_backup_file_name, 'g:','\\server1')

or
select @db_backup_file_name = replace(physical_device_name, ,'g:','\\server1')
from
(select physical_device_name from backupmediafamily where media_set_id in (
select top 1 media_set_id from backupset where type='d' and database_name='db1'
order by backup_start_date desc)) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-06-29 : 12:35:47

Thank you nr. That worked.

Regards
Paresh Motiwala
Boston, USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources