BUG: An 8144 error occurs when you try to attach a database to an instance of SQL Server by using SQL Server Enterprise Manager (885712)

SYMPTOMS

When you try to attach a database to an instance of Microsoft SQL Server 2000 by using SQL Server Enterprise Manager, you receive the following error message:

Error 8144: Procedure or function sp_attach_db has too many arguments specified.

You receive this error message in the Microsoft SQL-DMO (ODBC SQLState: 42000) dialog box. This problem occurs if there are more than 16 files in the database that you try to attach, including the data files and the log files in the database.

CAUSE

When you try to attach a database to an instance of SQL Server 2000 by using SQL Server Enterprise Manager, the SQL Server internally runs the sp_attach_db system stored procedure. The data files and the log files in the database are passed as the file name arguments for the sp_attach_db system stored procedure. However, for the sp_attach_db system stored procedure, the maximum number of file name arguments that can be specified is 16. Therefore, when you try to attach a database that has more than 16 files, the number of arguments for the sp_attach_db system stored procedure is greater than this limit.

WORKAROUND

To work around this problem, use the CREATE DATABASE FOR ATTACH Transact-SQL statement to attach a database that has more than 16 files. For example, you can use the following Transact-SQL statement to attach the database.

CREATE DATABASE Database name
ON PRIMARY (FILENAME = 'Installation Drive:\Program Files\Microsoft SQL Server\Your Instance of SQL Server\Data\Primary file name.mdf')
FOR ATTACH

For more information about the CREATE DATABASE Transact-SQL statement, visit the following MSDN Web site:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_1up1.asp

MORE INFORMATION

Steps to reproduce the problem

Start SQL Server Enterprise Manager.
Create a database that is named test_database on your instance of SQL Server 2000.

For more information about how to create a database by using SQL Server Enterprise Manager, visit the following MSDN Web site:
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_4g8p.asp
Add 16 transaction log files to the test_database database.

For more information about how to add transaction log files to a database, visit the following MSDN Web site:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_03_9dbn.asp
Detach the test_database database from your instance of SQL Server 2000.

For more information about how to detach a database by using SQL Server Enterprise Manager, see the "To detach a database" section on the following MSDN Web site:
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_4wq1.asp
Try to attach the test_database database to your instance of SQL Server by using SQL Server Enterprise Manager.

For more information about how to attach a database by using SQL Server Enterprise Manager, see the "To attach a database" section on the following MSDN Web site:
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_4wq1.asp
Note By default, the .mdf file that corresponds to a database is located in the Installation Drive:\Program Files\Microsoft SQL Server\Your Instance of SQL Server\Data folder.