Microsoft KB Archive/885712

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

Article ID: 885712

Article Last Modified on 5/18/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Bug #: 472560 (SQL Server 8.0)

Bug #: 471566 (SQL Server 8.0)

Bug #: 26988 (Content Maintenance)



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://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



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://msdn2.microsoft.com/en-us/library/aa197139(SQL.80).aspx

 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://msdn2.microsoft.com/en-us/library/aa933089(SQL.80).aspx

 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 &quot;To detach a database&quot; section on the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/aa197141(SQL.80).aspx

 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 &quot;To attach a database&quot; section on the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/aa197141(SQL.80).aspx

Note By default, the .mdf file that corresponds to a database is located in the :\Program Files\Microsoft SQL Server\ \Data folder.</li></ol>

<div class="references_section">