?
Solved

SQL running dbcc shrinkfile from a stored procedure

Posted on 2008-02-06
2
Medium Priority
?
291 Views
Last Modified: 2008-03-06
Hi,
I have a stored proc on a bespoke utils database that takes the parameters @database, @size. When the proc is called it finds the tran log file id for the given database and truncates it. However I also want to run the DBCC shrinkfile (<file_id>, option, size) against this database.

Essentially I need to know how to run this command against a different database to the one currently OR how to get some form of "use database" command in a stored proc.

Any help greatly appreciated
0
Comment
Question by:Netstore
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20831059
you would need dynamic sql:


CREATE PROCEDURE yourproc 
( @database sysname
, @fileid int
, @size int
)
AS
DECLARE @sql VARCHAR(2000)
SET @sql = '
   USE [' + @database + ']
   DBCC SHRINKFILE( ' + CAST( @fileid as VARCHAR(100)) + ' , option, ' + CAST( @size as VARCHAR(100)) + ' ) 
  '
  
EXEC(@sql)

Open in new window

0
 
LVL 5

Author Comment

by:Netstore
ID: 20831106
I've just thought of that - dynamic SQL - I'll give it a go and I think your right 8)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question