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
  • 4
    I wouldn't expect CREATE DATABASE to return any results. Perhaps a server-level trigger is buggy.
    – Dan Guzman
    Mar 6, 2019 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. Mar 6, 2019 at 11:44
  • I suspect a trigger, and the trigger behaves "correctly" when you execute from SSMS. Hunt down that trigger and debug it. Mar 6, 2019 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 ;-) Mar 6, 2019 at 17:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.