Nimble Coder

When aspnet_regsql.exe won't connect

I was building a quick test web site and I was using the aspnet_regsql tool to add membership to a SQLEXPRESS database. At first, I tried:

aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\code\Test\APP_DATA\aspnetdb.mdf"

For some reason, the SqlConnection insisted that it try to create the database and disregarded the full path to the database (note the path in the exception).

SQL Exception:
System.Data.SqlClient.SqlException: Directory lookup for the file "C:\Documents and Settings\user\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\C:\code\Asp.net\ServerControlTest\App_Data\Database.mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Creating the C:\code\Asp.net\ServerControlTest\App_Data\Database database...
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Next I tried the simple commands (credit Scott Mitchell) that worked great:

sqlcmd -S localhost\SQLExpress -Q "EXEC sp_attach_db 'Foobar', N'pathToDBfile'"
aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all

References

1. Working with SQL Server 2005 Express Database
2. Using ASPNET_RegSQL.exe with SQL Express databases in APP_DATA

Print | posted on Thursday, April 24, 2008 3:50 PM | Filed Under [ ASP.NET SQL ]