CODEWITHSUNDEEP
sql-server-2008
Stunner
Stunner

Reputation: 63

How to set value of parameter to be in MB or in KB (in MS SQL 2008)?

It works well if I write the query like this:

ALTER DATABASE [test] MODIFY FILE ( 
  NAME = N'test_log', SIZE = 3456KB, 
  MAXSIZE = 6789999KB, FILEGROWTH = 2345KB)

But it is a problem, if I try to use parameters:

ALTER DATABASE [@DbName] MODIFY FILE ( 
  NAME = N'@LogFileName', SIZE = @InitialSize,
  MAXSIZE = @MaxSize , FILEGROWTH = @FileGrowth)

I know that MS SQL 2008 has auto-convert, but when i use parameters occurs an error. So, my question is: From which type must be the declared parameters, which I use to set the size in MB(@InitialSize, @MaxSize, @FileGrowth)?

Upvotes: 1

Views: 157

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

N'@LogFileName' is incorrect, it represents a string with the value @LogFileName, not a variable.

The default for sizes is MB, but I don't think you can pass in variables. Few DDL statements accept variables. When they do the documentation specifies variable_literal, like in the case of CREATE/ALTER ASSEMBLY, which accepts variables for the assembly bits argument.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562731

See ALTER DATABASE File and Filegroup Options:

The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB.

Upvotes: 0

Related Questions