Can't change sql server file database size

Question

I can't change the database file size with a C# query. For some reason I get an exception: "Incorrect syntax near '@databaseName'.

This is the code that executed the query:

command = connection.CreateCommand();
command.CommandText = @"
                        ALTER DATABASE @databaseName
                        MODIFY FILE
                        (NAME = @databaseFile, SIZE = @newSize)
                        ";
dbParam = command.CreateParameter();
dbParam.ParameterName = "databaseFile";
dbParam.Value = dbFileName;
command.Parameters.Add(dbParam);
dbParam = command.CreateParameter();
dbParam.ParameterName = "newSize";
dbParam.Value = newSize;
command.Parameters.Add(dbParam);
dbParam = command.CreateParameter();
dbParam.ParameterName = "databaseName";
dbParam.Value = databaseName;
command.Parameters.Add(dbParam);
command.ExecuteNonQuery();

Now there might be several problems. Firstly the database is on a different machine so wouldn't the db file path be different?

score 3 · Accepted Answer · answered Feb 06 '13 at 12:39

3

Some things cannot be parameterized. That includes things like table and column names in DML, but includes most of DDL. It is not expecting, and cannot process, parameters in this scenario.

To check this; just run it in SSMS, declaring the variables ahead of time and giving them values. You will find the error message is the same. If it doesn't work in SSMS it is very unlikely to work from ADO.NET.

answered Feb 06 '13 at 12:39

Marc Gravell

1,026,079
266
2,566
2,900

So how can I create the query? Just build it from strings? – Alecu Feb 06 '13 at 12:44
@Alecu well the first thing I'd ask is *why* would you want application code doing that? that seems highly irregular. But basically, yes. You can use square brackets to avoid some escaping issues (for example `[Foo.Database]`), but ultimately you will need to do some sanity checking on these values too: don't allow arbitrary user input. Also: this suggests your application code is running as dbo... that is risky. – Marc Gravell Feb 06 '13 at 12:45
I need this for provisioning. I have webservices that must handle database provisioning. – Alecu Feb 06 '13 at 12:48
@Alecu then yes, you'll have to do it via concatenation. Re elevating to a particular user - you could maybe have a proc with `EXECUTE AS` which takes the parameters, builds a final TSQL string, and calls `EXEC` - that would perhaps avoid having your app code run evevated - but up to you. – Marc Gravell Feb 06 '13 at 13:01
And another question. Now I get an error that it could not find the file. I believe it expects the file to be on the machine that is running the query. – Alecu Feb 06 '13 at 13:04
1

@Alecu - if this is for provisioning, might it not be better to use [SMO](http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.datafile.aspx), which offers a rich object model for managing SQL Server? – Damien_The_Unbeliever Feb 06 '13 at 13:57

Can't change sql server file database size

1 Answers1