0

I have the following database command:

CREATE DATABASE [test1] ON 
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test1.mdf'), 
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test1_log.ldf') 
FOR ATTACH

When I do it via C# it returns me more than one value. Here is the C# code:

public void RunQuery(string query, bool longRunning = false)
{
    using (var cmd = new SqlCommand(query, Connection))
    {
        cmd.CommandTimeout = longRunning ? _longRunningCommandTimeout : _commandTimeout;
        cmd.ExecuteNonQuery();
    }
}

This results in the following error from SQL Server:

Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated

And here is the stack trace from the exception:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

The database attach succeeds, even though an error is raised.

When I execute the same query (under the same user) via Management Studio it gets me just the following:

Commands completed successfully.

Is it possible that the CREATE DATABASE statement returns more than one result?

  • 4
    I wouldn't expect CREATE DATABASE to return any results. Perhaps a server-level trigger is buggy. – Dan Guzman Mar 6 at 11:44
  • 3
    That last thing you showed is not a “result.” Show your C# code and also check if you have any server-level DDL triggers. The create database statement on its own can’t possibly generate this error. – Aaron Bertrand Mar 6 at 11:44
  • I suspect a trigger, and the trigger behaves "correctly" when you execute from SSMS. Hunt down that trigger and debug it. – Tibor Karaszi Mar 6 at 17:05
  • You should use Extended Events or SQL Profiler to watch what statements are actually being executed. Not only should CREATE DATABASE not return a result set, but there is no sub-query in the first place. There isn't even a query ;-) – Solomon Rutzky Mar 6 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.