Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'list'.

• karthicsjk

  SSC Veteran

  Points: 217

  More actions

  August 5, 2012 at 11:53 pm

  #265316

  use master

  go

  EXEC sp_configure 'show advanced options',1

  go

  RECONFIGURE

  EXEC sp_configure 'xp_cmdshell',1

  go

  RECONFIGURE

  declare @FolderTo varchar(250)='c:\SQLDATA'CREATE TABLE #x(dbname sysname,fileName sysname,location varchar(200));

  get a 'list' of datafiles for ALL DB

  EXEC master.sys.sp_MSforeachdb 'INSERT #x(dbname,filename,location)SELECT ''[?]'',a.name as flname, a.filename as location FROM [?].sys.sysfiles a where a.filename like ''%.ldf'''

  'collect' information only about databases and log files

  select 'ALTER DATABASE ' +dbname+ ' Set offline with Rollback immediate'+ CHAR(13)+CHAR(10) + 'execute xp_cmdShell '+CHAR(39)+ 'move "'+ location + '" "'+ @FolderTO+right(location,CHARINDEX('\', reverse(location), 1))+'"' +CHAR(39)+ CHAR(13)+CHAR(10) + 'ALTER DATABASE ' +dbname+ ' MODIFY FILE (Name ="' +FileName+'", FILENAME = "'+@FolderTO+right(location,CHARINDEX('\', reverse(location), 1))+'")'+ CHAR(13)+CHAR(10)+'ALTER DATABASE ' +dbname+ ' Set online' from #X where dbname not in ('[master]','[tempdb]','[msdb]','[model]') DROP TABLE #x;

  Msg 102, Level 15, State 1, Line 3

  Incorrect syntax near 'list'.

  Msg 102, Level 15, State 1, Line 5

  Incorrect syntax near 'collect'.

• BrainDonor

  SSCoach

  Points: 19252

  More actions

  August 6, 2012 at 1:13 am

  #1521848

  When you place comments in your code you need to mark them as such:

  http://msdn.microsoft.com/en-us/library/ms181627.aspx

  Steve Hall
  Linkedin
  Blog Site