USE statement is not allowed - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

USE statement is not allowed

Author

Topic

10Dawg
Starting Member

46 Posts

Posted - 2012-03-13 : 11:31:41

SQL 2008

I'm trying to save this code as a stored procedure:
USE TVDB112;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE TVDB112

SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (TVDB112_log, 0);
GO

ALTER DATABASE TVDB112

SET RECOVERY FULL;
GO

and get the following error:

a USE database statement is not allowed in a procedure, function or trigger.

What would be the alternative?
PS- I am new to sql code. Be kind.

10Dawg

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-13 : 11:41:40

You don't need the USE statement. You will execute the CREATE PROC statement in the appropriate database. You also need to take out all of the GO statements. They are only used in SSMS.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-13 : 11:58:21

Also, please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

10Dawg
Starting Member

46 Posts

Posted - 2012-03-13 : 12:05:35

Which begs the question, which database? Let's back up (pun intended). I want to shrink all the database log files every night after a complete FULL backup. That is what I am trying to accomplish.
1- Is there a backup task that already does this that I can just add to my back up maintenance plan? (I want to shrink log files, not ths main dbs)
2- Is there a stored procedure that already does this?
3- If I want to shrink ALL the log files, do I have to address each db and repeat or is there code that will shrink all of them at one time?
4- If I want it to be a maintenance plan task, which db should I put the SP?

Thanks for the links. i will follow up.

10Dawg

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-13 : 16:41:44

quote:
Originally posted by 10Dawg

I want to shrink all the database log files every night after a complete FULL backup.

That's a very bad idea. Neither data nor log files should be regularly shrunk. Once-off shrinks after large data loads or large data purges is OK but scheduled shrinks are strongly not recommended.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources