Help with TSQL statements.

Hello. I can't seem to get this to work. It tells me incorrect syntax near the variable containing the database name.
Here's the code -

DECLARE @TargetDataBase varchar(200)
DECLARE DataBase_cursor CURSOR FOR

SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master','model','msdb')
ORDER BY name
--
OPEN DataBase_cursor
FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase

 WHILE @@fetch_status <> -1
 BEGIN
  PRINT @TargetDataBase
  GO
  exec(Alter Database @TargetDataBase Set Recovery Simple)
  GO
  exec(Alter Database @TargetDataBase Set Recovery Full)
  GO
  exec(DBCC SHRINKFILE (@TargetDataBase + '_Log.LDF', 1))
  GO
  FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase
 END
CLOSE DataBase_cursor
DEALLOCATE DataBase_cursor



If anyone can get this working, please post the revised code.
Thank you!
Ken
yankeekAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NazermohideeenConnect With a Mentor Commented:
Two issues
1. No "GO" statements within the same batch of sql. So remove all GO statements
2. exec command takes a sql as a string. So you have to change all exec commands to string
  eg; exec('Alter Database ' + @TargetDataBase + ' Set Recovery Simple')
0
 
lcohanDatabase AnalystCommented:
--Please try this:

DECLARE @TargetDataBase varchar(200);
DECLARE @sqlstr varchar(max);

DECLARE DataBase_cursor CURSOR FOR
 SELECT name FROM master..sysdatabases
 WHERE name NOT IN ('master','model','msdb')
 ORDER BY name
 --
OPEN DataBase_cursor
FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase

  WHILE @@fetch_status <> -1
  BEGIN
   PRINT (@TargetDataBase);
   
   set @sqlstr = 'Alter Database ['+@TargetDataBase+'] Set Recovery Simple;';
   --print @sqlstr;
   exec @sqlstr;
   
   set @sqlstr = 'Alter Database ['+@TargetDataBase+ '] Set Recovery Full;';
   --print @sqlstr;
   exec @sqlstr;
   
   set @sqlstr = 'DBCC SHRINKFILE (''' +@TargetDataBase+ '_Log.LDF'', 1);';
   --print @sqlstr;
   exec @sqlstr;

   FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase
  END
 CLOSE DataBase_cursor
 DEALLOCATE DataBase_cursor
GO
0
 
Brian CroweDatabase AdministratorCommented:
I think you have your commands out of order.  Why would you switch recover from simple to full before shrinking the log file?

SELECT DB.name, MF.name
FROM sys.databases AS DB
INNER JOIN sys.master_files AS MF
	ON DB.database_id = MF.database_id
WHERE DB.name NOT IN ('master', 'model', 'msdb', 'tempdb')
	AND MF.type = 1;		--log file

OPEN crsDB;
FETCH NEXT FROM crsDB INTO @DBName, @LogicalFileName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	PRINT 'Database:    ' + @DBName;;
	PRINT 'Log File:    ' + @LogicalFileName;

	SET @SQL = 'ALTER [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT;';
	EXECUTE (@SQL);

	SET @SQL = 'DBCC SHRINKFILE ([' + @LogicalFileName + '], 1);';
	EXECUTE (@SQL);

	/*
	You should consider adding a clause to adjust the intial size and filegrowth settings so that you don't have to perform this action regularly

	ALTER DATABASE @DBName
	MODIFY FILE
	(
		NAME = @LogicalFileName
		SIZE = 1024MB,
		FILEGROWTH = 256MB
	);

	*/

	SET @SQL = 'ALTER [' + @DBName + '] SET RECOVERY FULL WITH NO_WAIT;';
	EXECUTE (@SQL);
END

CLOSE crsDB;
DEALLOCATE crsDB;

Open in new window

0
 
yankeekAuthor Commented:
Thanks. Although both answers are correct, Nazermohideeen got me on the right path.

Thanks folks
0
 
yankeekAuthor Commented:
Just a follow up. This is what I came up with. This is for you DBA's that need to keep your database size under control. I run this on all my SQL boxes. Enjoy

USE [master]
DECLARE @TargetDataBase varchar(200)
DECLARE @TargetLogFile varchar(200)
DECLARE @SQL varchar(1000)
DECLARE DataBase_cursor CURSOR FOR

SELECT a.name, b.name AS 'LogicalFilename' FROM master..sysdatabases  a INNER JOIN sysaltfiles b ON a.dbid = b.dbid
WHERE a.name NOT IN ('master','model','msdb','tempdb') AND (a.status <> 528) AND (b.fileid = 2)
ORDER BY a.name

--
OPEN DataBase_cursor
FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase, @TargetLogFile

 WHILE @@fetch_status <> -1
 BEGIN
  SET @SQL =
+ ' USE [' + @TargetDataBase +'];'
+ ' ALTER DATABASE [' + @TargetDataBase + '] Set Recovery Simple;'
+ ' DBCC SHRINKFILE ([' + LTRIM(RTRIM(@TargetLogFile)) + '],1);'
+ ' ALTER DATABASE [' + @TargetDataBase + '] Set Recovery Full;'

  exec(@SQL)
  PRINT @SQL
  FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase, @TargetLogFile
 END
CLOSE DataBase_cursor
DEALLOCATE DataBase_cursor
0
All Courses

From novice to tech pro — start learning today.