Can I execute sql drop database cmd w/a SqlParameter

30 Mar 2005 4:36 PM

John Aldrin <>

Hi,

I'm wondering if it is possible to execute a sql command to drop a
database with a SqlParameter containing the name of the db to drop.

I've tried the following and I get an error

SqlCommand dropDbSqlCmd = _masterDbConn.CreateCommand ();
dropDbSqlCmd.CommandType = CommandType.Text;
dropDbSqlCmd.CommandText = "DROP DATABASE @dbToDrop";
SqlParameter dbNameParam = dropDbSqlCmd.Parameters.Add ( @dbToDrop,
SqlDbType.NVarChar, 128 );
dbNameParam.Value = dbName;

Error is

System.Data.SqlClient.SqlException : Line 1: Incorrect syntax near
'@dbToDrop'.

Thanx

jra

30 Mar 2005 5:02 PM

Marina

Parameters are used to insert literal values. In effect, the resulting sql
statement is: DROP DATABASE 'MyDatabase'. Which isn't the same as: DROP
DATABASE MyDatabase

So the short story is, you can't use parameters here.

<John Aldrin> wrote in message

Show quote

news:utkl41hqlnhgo36q0lfaal8jh47d2apehl@4ax.com...
> Hi,
>
> I'm wondering if it is possible to execute a sql command to drop a
> database with a SqlParameter containing the name of the db to drop.
>
> I've tried the following and I get an error
>
> SqlCommand dropDbSqlCmd = _masterDbConn.CreateCommand ();
> dropDbSqlCmd.CommandType = CommandType.Text;
> dropDbSqlCmd.CommandText = "DROP DATABASE @dbToDrop";
> SqlParameter dbNameParam = dropDbSqlCmd.Parameters.Add ( @dbToDrop,
> SqlDbType.NVarChar, 128 );
> dbNameParam.Value = dbName;
>
>
> Error is
>
> System.Data.SqlClient.SqlException : Line 1: Incorrect syntax near
> '@dbToDrop'.
>
> Thanx
>
> jra

30 Mar 2005 6:16 PM

not

On Wed, 30 Mar 2005 12:02:10 -0500, "Marina" <someone@nospam.com>
wrote:

Thanx for the input.

I was able to get it to work using the exec syntax.

Show quote

>Parameters are used to insert literal values. In effect, the resulting sql
>statement is: DROP DATABASE 'MyDatabase'. Which isn't the same as: DROP
>DATABASE MyDatabase
>
>So the short story is, you can't use parameters here.
>
><John Aldrin> wrote in message
>news:utkl41hqlnhgo36q0lfaal8jh47d2apehl@4ax.com...
>> Hi,
>>
>> I'm wondering if it is possible to execute a sql command to drop a
>> database with a SqlParameter containing the name of the db to drop.
>>
>> I've tried the following and I get an error
>>
>> SqlCommand dropDbSqlCmd = _masterDbConn.CreateCommand ();
>> dropDbSqlCmd.CommandType = CommandType.Text;
>> dropDbSqlCmd.CommandText = "DROP DATABASE @dbToDrop";
>> SqlParameter dbNameParam = dropDbSqlCmd.Parameters.Add ( @dbToDrop,
>> SqlDbType.NVarChar, 128 );
>> dbNameParam.Value = dbName;
>>
>>
>> Error is
>>
>> System.Data.SqlClient.SqlException : Line 1: Incorrect syntax near
>> '@dbToDrop'.
>>
>> Thanx
>>
>> jra
>