Set up Database using SQL Server 2005 LockedRSS

• • Reply
  y71chen

  None

  0 Points

  35 Posts

  Set up Database using SQL Server 2005

  Apr 07, 2006 06:02 PM|y71chen|LINK

  Hi
  
  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 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
  
  
  
• • Reply
  DesertRacer

  None

  0 Points

  43 Posts

  Re: Set up Database using SQL Server 2005

  Apr 09, 2006 02:39 PM|DesertRacer|LINK

  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
  
• • Reply
  neo302

  Member

  6 Points

  80 Posts

  Re: Set up Database using SQL Server 2005

  Apr 10, 2006 05:11 PM|neo302|LINK

  did you open sql server config manager and set the protocols to accept tcp/ip or whatever else needed?
• • Reply
  y71chen

  None

  0 Points

  35 Posts

  Re: Set up Database using SQL Server 2005

  Apr 10, 2006 06:54 PM|y71chen|LINK

  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.....
  
• • Reply
  lxdff

  None

  0 Points

  1 Post

  Re: Set up Database using SQL Server 2005

  Apr 27, 2006 04:25 AM|lxdff|LINK

  thanks a lot

• • Reply
  bettyb

  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.

   

• • Reply
  y71chen

  None

  0 Points

  35 Posts

  Re: Set up Database using SQL Server 2005

  May 31, 2006 08:00 PM|y71chen|LINK

  Hi Betty:
  
  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
  
  
• • Reply
  bettyb

  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.

   

• • Reply
  sarah56565

  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!!!!!!!!!!!

• • Reply
  toy

  Member

  20 Points

  169 Posts

  Re: Set up Database using SQL Server 2005

  Aug 07, 2007 01:46 PM|toy|LINK

  thanks so much this really helped and it was a great explanation
  just a girl coder
• • Reply
  ardmore

  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)

   

  
  

• • Reply
  Ronalda

  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.

• • Reply
  danasegarane

  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
• • Reply
  plum

  None

  0 Points

  7 Posts

  Re: Set up Database using SQL Server 2005

  Mar 12, 2009 03:26 AM|plum|LINK

  y71chen

  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.....
  

   

   

  This helped me greatly, Thanks!
  

• • Reply
  Anupama96

  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
  

• • Reply
  abilgic

  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

• • Reply
  mkamoski2

  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

• • Reply
  jacobambat

  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