I was following the steps for setting up SQL server 2005 for PWSK. When I came to this step:
1.Double click
aspnet_regsql.exe in E:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
2.The asp.net sql server wizard will start
click next
3.Choose configure SQL Serverand click next
4.Select Server and database;
Give database a name, default name is aspnetdb.mdf and choose type of authentication
5.Confirm setting and click next
The database pull down box said: <default> when i click on the downward arrow i get the following error message:
Failed to query a list of database names from the SQL server.
An error has occurred while establishing a connection to the server.
When connecting to SQL server 2005, this failure may be caused by the
fact that under the default settings SQL server does not allow remote
connections. [provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server]
So I followed the following steps to change the setting from local connection to local and remote connection:
1.
Click Start, point to
Programs, point to Microsoft SQL Server 2005, point to
Configuration Tools, and then click
SQL Server Surface Area Configuration.
2.
On the SQL Server 2005 Surface Area Configuration page, click
Surface Area Configuration for Services and Connections.
3.
On the Surface Area Configuration for Services and Connections page, expand
Database Engine, click Remote Connections, click
Local and remote connections, click the appropriate protocol to enable for your environment, and then click
Apply.
Note Click OK when you receive the following message:
<div class="message">Changes to Connection Settings will not take effect until you restart the Database Engine service.</div>
4.
On the Surface Area Configuration for Services and Connections page, expand
Database Engine, click Service, click
Stop, wait until the MSSQLSERVER service stops, and then click
Start to restart the MSSQLSERVER service.
And I selected both TCP/IP and name pipes, but when i go bak and changed the <default> to some name i get the following error:
Setup failed.
Exception:
Unable to connect to SQL Server database.
----------------------------------------
Details of failure
----------------------------------------
System.Web.HttpException: Unable to connect to SQL Server database. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert,
Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
--- End of inner exception stack trace ---
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()
I really donno where I did wrong or what steps i'm missing. And I really am running out of ideas. Please help!!!! Thanks a lot
-yue
I would fire up SQL Server Management Studio and make sure you can look at the istalled databases.
You can use aspnet_regsql.exe to make a script for the nessary tables for aspnet. Then make your new database and excute the script on your new database
Problem solved...it turn out that in ASP.NET SQL server setup wizard when it asks for server name, I have to specifiy the
instance name as well as the server name when MS refer to "Server Name" they really mean "Server Instance Name") eg if your server was named 'bigturnip' then you need to specify 'bigturnip\sqlexpress' (where sqlexpress is the instance name
- this one just happens to be the default used by SQL Server 2005 SQLExpress). By default the server name was entered as merely "bigturnip" but after i added "\sqlexpress", then everything worked. If anyone else is having this remote connection problem and
when they did select remote connection and still does not work this is the way to solve the problem.....
When I enter machinename\servername instead of servername, I no longer get the Name Pies Provider, error 40. However I do get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'sysdatabases'.
I do have an instance of SQL Server 2000 and a named instance of SQL Server 2005 on the same machine.
I think I read in one of the previous posts, which says that you can't have both Server 2000 and Server 2005 together. There can only be one installed.
hope that helps!
-yue
Fortunately, that is not true. You can have only one default instance but multiple named instances of any combination of 2000 and 2005. My 2000 is my default and my 2005 is named. See the following link:
I have been trying to run this aspnet_regsql all day for 7 hours and this post just solved my problem. I added the SQL server name after the server name and it worked like a charm.
Hi, I followed the wizard and input the server name as
YOUR-787F71A704\SQLEXPRESS
Then I chose a database named as "Players".
After two Next, finally I got an error
Setup failed.
Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 262 and the SqlException message is: CREATE PROCEDURE permission denied in database 'Players'.
Problem solved...it turn out that in ASP.NET SQL server setup wizard when it asks for server name, I have to specifiy the
instance name as well as the server name when MS refer to "Server Name" they really mean "Server Instance Name") eg if your server was named 'bigturnip' then you need to specify 'bigturnip\sqlexpress' (where sqlexpress is the instance name
- this one just happens to be the default used by SQL Server 2005 SQLExpress). By default the server name was entered as merely "bigturnip" but after i added "\sqlexpress", then everything worked. If anyone else is having this remote connection problem and
when they did select remote connection and still does not work this is the way to solve the problem.....
None
0 Points
35 Posts
Set up Database using SQL Server 2005
Apr 07, 2006 06:02 PM|y71chen|LINK
I was following the steps for setting up SQL server 2005 for PWSK. When I came to this step:
1. Double click aspnet_regsql.exe in E:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
2. The asp.net sql server wizard will start click next
3. Choose configure SQL Server and click next
4. Select Server and database; Give database a name, default name is aspnetdb.mdf and choose type of authentication
5. Confirm setting and click nextThe database pull down box said: <default> when i click on the downward arrow i get the following error message:
Failed to query a list of database names from the SQL server.
An error has occurred while establishing a connection to the server.
When connecting to SQL server 2005, this failure may be caused by the
fact that under the default settings SQL server does not allow remote
connections. [provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server]
So I followed the following steps to change the setting from local connection to local and remote connection:
Note Click OK when you receive the following message: <div class="message">Changes to Connection Settings will not take effect until you restart the Database Engine service.</div>
And I selected both TCP/IP and name pipes, but when i go bak and changed the <default> to some name i get the following error:
Setup failed.
Exception:
Unable to connect to SQL Server database.
----------------------------------------
Details of failure
----------------------------------------
System.Web.HttpException: Unable to connect to SQL Server database. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
--- End of inner exception stack trace ---
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()
I really donno where I did wrong or what steps i'm missing. And I really am running out of ideas. Please help!!!! Thanks a lot
-yue
None
0 Points
43 Posts
Re: Set up Database using SQL Server 2005
Apr 09, 2006 02:39 PM|DesertRacer|LINK
You can use aspnet_regsql.exe to make a script for the nessary tables for aspnet. Then make your new database and excute the script on your new database
Member
6 Points
80 Posts
Re: Set up Database using SQL Server 2005
Apr 10, 2006 05:11 PM|neo302|LINK
None
0 Points
35 Posts
Re: Set up Database using SQL Server 2005
Apr 10, 2006 06:54 PM|y71chen|LINK
None
0 Points
1 Post
Re: Set up Database using SQL Server 2005
Apr 27, 2006 04:25 AM|lxdff|LINK
thanks a lot
None
0 Points
2 Posts
Re: Set up Database using SQL Server 2005
May 26, 2006 07:26 PM|bettyb|LINK
When I enter machinename\servername instead of servername, I no longer get the Name Pies Provider, error 40. However I do get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'sysdatabases'.
I do have an instance of SQL Server 2000 and a named instance of SQL Server 2005 on the same machine.
BettyB.
None
0 Points
35 Posts
Re: Set up Database using SQL Server 2005
May 31, 2006 08:00 PM|y71chen|LINK
I think I read in one of the previous posts, which says that you can't have both Server 2000 and Server 2005 together. There can only be one installed.
hope that helps!
-yue
None
0 Points
2 Posts
Re: Set up Database using SQL Server 2005
May 31, 2006 08:15 PM|bettyb|LINK
Fortunately, that is not true. You can have only one default instance but multiple named instances of any combination of 2000 and 2005. My 2000 is my default and my 2005 is named. See the following link:
http://geekswithblogs.net/tmoore/archive/2005/11/13/59971.aspx
BettyB.
None
0 Points
3 Posts
Re: Set up Database using SQL Server 2005
Jun 22, 2007 03:36 PM|sarah56565|LINK
I have been trying to run this aspnet_regsql all day for 7 hours and this post just solved my problem. I added the SQL server name after the server name and it worked like a charm.
THANK YOU SO MUCH!!!!!!!!!!!
Member
20 Points
169 Posts
Re: Set up Database using SQL Server 2005
Aug 07, 2007 01:46 PM|toy|LINK
Member
43 Points
145 Posts
Re: Set up Database using SQL Server 2005
Jan 31, 2008 10:00 AM|ardmore|LINK
Hi, I followed the wizard and input the server name as
YOUR-787F71A704\SQLEXPRESS
Then I chose a database named as "Players".
After two Next, finally I got an error
Setup failed.
Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 262 and the SqlException message is: CREATE PROCEDURE permission denied in database 'Players'.
----------------------------------------
Details of failure
----------------------------------------
SQL Server:
Database: [Players]
SQL file loaded:
InstallCommon.sql
Commands failed:
CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
@name sysname
AS
BEGIN
DECLARE @object sysname
DECLARE @protectType char(10)
DECLARE @action varchar(60)
DECLARE @grantee sysname
DECLARE @cmd nvarchar(500)
DECLARE c1 cursor FORWARD_ONLY FOR
SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
OPEN c1
FETCH c1 INTO @object, @protectType, @action, @grantee
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
EXEC (@cmd)
FETCH c1 INTO @object, @protectType, @action, @grantee
END
CLOSE c1
DEALLOCATE c1
END
SQL Exception:
System.Data.SqlClient.SqlException: CREATE PROCEDURE permission denied in database 'Players'.
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)
None
0 Points
1 Post
Re: Set up Database using SQL Server 2005
May 20, 2008 04:18 PM|Ronalda|LINK
Did you ever figure out this problem? B/C I'm expierencing the same thing.
Member
145 Points
213 Posts
Re: Set up Database using SQL Server 2005
Jul 25, 2008 06:13 AM|danasegarane|LINK
Try this one
http://blog.nsight.dk/PermaLink,guid,78e73b3d-ce29-45bb-bccf-d727f794ceb2.aspx
Remember to click "Mark as Answer" on the post that helps U
None
0 Points
7 Posts
Re: Set up Database using SQL Server 2005
Mar 12, 2009 03:26 AM|plum|LINK
This helped me greatly, Thanks!
None
0 Points
6 Posts
Re: Set up Database using SQL Server 2005
Jul 23, 2010 02:45 AM|Anupama96|LINK
thank you very much
it is really useful
Member
6 Points
22 Posts
Re: Set up Database using SQL Server 2005
Nov 26, 2010 08:22 PM|abilgic|LINK
thanks a lot it solved my problem
Member
82 Points
82 Posts
Re: Set up Database using SQL Server 2005
May 31, 2011 04:30 PM|mkamoski2|LINK
All -
This is a follow-up to the thread above with (yet another) workaround.
I had tried fully qualifing the server name as...
MyMachineName\SqlExpress
...but I was still getting the dreaded error...
Failed to query a list of database names from the SQL server. Invalid object name 'sysdatabases'.
...so then I found a hint that one can try to manually type in the database name (rather than use the dropdownlist) and that worked for me.
HTH.
Thank you.
- Mark Kamoski
Member
252 Points
67 Posts
Re: Set up Database using SQL Server 2005
Jul 26, 2011 10:58 PM|jacobambat|LINK
Hi chen,
Thanks a lot .Even I had the same issue