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

    Question

  • All

     

    I’m automating a large Archiving\Purging script on 1TB multi-databases.

     

    I run all Sp’s from Archive database (Destination database).

     

    The log file gets full fast in both source and destination databases and the whole process stopped, I have to clear all log files, as well I have to shrink file after clearing it.

     

    After each archive or purge (delete or insert) statement I want to run a truncate log statement on both destination database and Source database (DB1, Db2, DB3…) like this:

     

    Use Archive

    BACKUP LOG Archive WITH TRUNCATE_ONLY

    DBCC SHRINKFILE (Archive_Log,2)

     

    Use DB1

    BACKUP LOG DB1 WITH TRUNCATE_ONLY

    DBCC SHRINKFILE (DB1_Log,2)

     

    I get this error message:

     

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

     

    Any solutions?

     

    Thanks

    Thursday, September 30, 2010 6:28 PM

Answers

  • Use separate scripts for each database. Think about the logical ordering of a session. Things happen inside a particular schema; there is no higher meta-level. 
    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Marked as answer by KJian_ Wednesday, October 06, 2010 8:21 AM
    Thursday, September 30, 2010 7:16 PM
  • Checkout the sprocObjectCountsInAllDBs stored procedure at the following link: http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

    It applies cursor to enumerate all databases and dynamic SQL to carry out the action.

    Let us know if helpful.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Wednesday, October 06, 2010 8:21 AM
    Wednesday, October 06, 2010 5:31 AM

All replies