Personal Site Starter Kit

Re: Set up Database using SQL Server 2005

plum — Thu, 12 Mar 2009 07:26:23 GMT

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!

Re: Set up Database using SQL Server 2005

danasegarane — Fri, 25 Jul 2008 10:13:39 GMT

Try this one

http://blog.nsight.dk/PermaLink,guid,78e73b3d-ce29-45bb-bccf-d727f794ceb2.aspx

Re: Set up Database using SQL Server 2005

Ronalda — Tue, 20 May 2008 20:18:34 GMT

Did you ever figure out this problem? B/C I'm expierencing the same thing.

Re: Set up Database using SQL Server 2005

ardmore — Thu, 31 Jan 2008 15:00:29 GMT

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

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)

Re: Set up Database using SQL Server 2005

toy — Tue, 07 Aug 2007 17:46:09 GMT

thanks so much this really helped and it was a great explanation

Re: Set up Database using SQL Server 2005

sarah56565 — Fri, 22 Jun 2007 19:36:30 GMT

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

Re: Set up Database using SQL Server 2005

bettyb — Thu, 01 Jun 2006 00:15:31 GMT

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.

Re: Set up Database using SQL Server 2005

y71chen — Thu, 01 Jun 2006 00:00:18 GMT

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

Re: Set up Database using SQL Server 2005

bettyb — Fri, 26 May 2006 23:26:38 GMT

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.

Re: Set up Database using SQL Server 2005

lxdff — Thu, 27 Apr 2006 08:25:31 GMT

thanks a lot

Re: Set up Database using SQL Server 2005

y71chen — Mon, 10 Apr 2006 22:54:12 GMT

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

Re: Set up Database using SQL Server 2005

neo302 — Mon, 10 Apr 2006 21:11:15 GMT

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

Re: Set up Database using SQL Server 2005

DesertRacer — Sun, 09 Apr 2006 18:39:55 GMT

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

Set up Database using SQL Server 2005

y71chen — Fri, 07 Apr 2006 22:02:48 GMT

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: Changes to Connection Settings will not take effect until you restart the Database Engine service.
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