April 16, 2012 at 7:38 am
Hi,
I am using the below script to create multiple files for tempdb. But I need help correcting this code. Can anyone please correct this?
USE [master]
GO
DECLARE @file1 nvarchar(max)
DECLARE @file2 nvarchar(max)
DECLARE @file3 nvarchar(max)
select @file1=''''+REPLACE(filename,'tempdb.mdf','tempdb1.mdf')+'''' from sysdatabases where name='tempdb'
select @file2=''''+REPLACE(filename,'tempdb.mdf','tempdb2.mdf')+'''' from sysdatabases where name='tempdb'
select @file3=''''+REPLACE(filename,'tempdb.mdf','tempdb3.mdf')+'''' from sysdatabases where name='tempdb'
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = @file1 , SIZE = 8192KB , FILEGROWTH = 10%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = @file2 , SIZE = 8192KB , FILEGROWTH = 10%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = @file3 , SIZE = 8192KB , FILEGROWTH = 10%)
GO
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 16, 2012 at 8:07 am
And just what is wrong with the code? If you are getting an error message of some sort you really should provide that information. Personally, I'm not running someone elses code to affect my tempdb just to determine what is wrong with it.
April 16, 2012 at 8:10 am
Hi Lynn,
Here is the syntax error
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@file1'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@file2'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@file3'.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 16, 2012 at 8:20 am
You can't use a variable for the file name. Use dynamic SQL instead (or just write the code by hand)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply