Please help me set up SQL Express

I am extremely new to ASP, SQL, everything. I'm trying to set up a simple username/password system for our website (built with Expression Web) that would only be used by about 15 employees. I am following directions in Zak Ruvalcaba's book on Expression Web, and I'm stuck. I have .NET Framework and SQL Express downloaded and installed. I'm running the ASP.NET SQL Server Setup Wizard, but Setup fails because it is unable to connect to SQL Server database. I'm completely lost. Any help would be greatly appreciated. Thanks.

How to configure services for Asp.net is covered in the link below, post again if you still need help.

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Please Help me

I have to learn VS2005 with SQL Express What should I do? Where are beginner's guides and tutorials?

try here:

http://msdn.microsoft.com/vstudio/express/vb/learning/

http://www.learnvisualstudio.net/

|||

Thank you very much Lepaca

I have error message

SQL Exception error

An error has occurred while establishing a connection to server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the defualt setting SQL Server does not allow remote connections (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

|||

Maybe, this thread can help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=147346&SiteID=1&PageID=1

sql

Please help ??

I have installed SQL Server 2005 Express on Windows XP Pro with SP2.

I have also installed ASP.NET Web Matrix on same machine. Upon selecting the Data tab (next to the Workspace tab), I click on the Database Connection button. I am asked if I want to connect to an Access db or a SQL Server db. I select the SQL Server db and I then receive a dialog box that asks me the Server Name: the default is (local) and I have used that as well as localhost. I use SQL Server authentication and enter a username of sa and a password that I set up during the installation process. I finally select the Create a New Database link and I then get the message SQL Server does not exist or access denied.

I have checked that my services are running. I also have uninstalled and reinstalled both SQL Server Express 2005 and ASP.NET Web Matrix but still the same glitch.

Any help would be greatly appreciated...

Glenn

Hello All,

I have solved my problem. The server name was my issue. I had list (local), localhost and my machine name, none of which worked. I then added my machine name\sqlexpress and it worked.

GO FIGURE ...

sql

Please help > Problem restoring database

We have 2 database on a production server running sql express 2005, the first one is 200mb and the other one is 1.3gig.

We make backup under sql management studio using option full.

When we try to recover database on another server the 200mb database restore perfectly, the 1.3 gig
always fail with the following message :

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The file "database_name" failed to initialize correctly. Examine the error logs for more details. (Microsoft.SqlServer.Express.Smo)

We tried dbcc checkdb and everything seems alright.
I updated the statistics, no change.
I switched the recovery model from full to simple, no change.
I tried using "restore database from disk='' with norecovery, same error message

We tried stopping the production server then copying the file to the other server and attaching and it still fail with the following message :

An error occured when attaching the database(s). >

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_Data.MDF". Operating system error 8: "8(error not found)". (Microsoft SQL Server, Error: 5120)

We dont know what to try anymore, the goal is to be sure our backup are alright....

Please helppppp ! thank you.
sorry for my bad english...

Youhou !!!!!!! Finaly.

Ok, I uninstalled avg antivirus, then free my disk drive to have 10gig free.

restored my backup and it completed successfully !!!!

thank you for your help rmiaosql

Please Help - Web application breaks when SQL .ldf file is relocat

Hi,
Here is the scenario:
My web developer is building a new site in asp.net, in turn he is using SQL
express in building his application. In my research on deploying SQL express
I have found it is in best practice to separate the .mdf and .ldf files to
different drives with adequate space. When he copies the database files he
has created from his local desktop up to the app_data folder on the web
server his database driven app works perfectly. Though when I go to move the
.ldf file to another location the application breaks, throwing the error
below. I have tried attaching the database to the management studio express
and configuring the location of the files but that did not work.
I have looked to into impersonation, metadata, and asp.net coding but am not
quite sure which may be causing this issue.
Please Help!!
Server Error in '/' Application.
---
---
Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user
default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject) +172
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection) +357
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject) +494
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
System.Web.UI.Control.PreRenderRecursiveInternal() +86
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041It's nothing to do with the app codes. It's about the database.
After detaching your database and changing your log file's path, to attach
your database again run a script like the following according to your
situation.
CREATE DATABASE [Test] ON
( FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\Data\Test.mdf' ),
( FILENAME = N'D:\Test_log.ldf' )
FOR ATTACH
--
Ekrem Ã?nsoy
"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:D4F0B3BA-D066-4771-937A-0FF6579C69AB@.microsoft.com...
> Hi,
> Here is the scenario:
> My web developer is building a new site in asp.net, in turn he is using
> SQL
> express in building his application. In my research on deploying SQL
> express
> I have found it is in best practice to separate the .mdf and .ldf files to
> different drives with adequate space. When he copies the database files he
> has created from his local desktop up to the app_data folder on the web
> server his database driven app works perfectly. Though when I go to move
> the
> .ldf file to another location the application breaks, throwing the error
> below. I have tried attaching the database to the management studio
> express
> and configuring the location of the files but that did not work.
> I have looked to into impersonation, metadata, and asp.net coding but am
> not
> quite sure which may be causing this issue.
> Please Help!!
> Server Error in '/' Application.
> ---
> ---
> Cannot open user default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Cannot open user
> default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> An unhandled exception was generated during the execution of the current
> web
> request. Information regarding the origin and location of the exception
> can
> be identified using the exception stack trace below.
> Stack Trace:
>
> [SqlException (0x80131904): Cannot open user default database. Login
> failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection) +800131
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj) +186
> System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj) +1932
> System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> enlistOK) +33
> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject) +172
> System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> +381
> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance) +181
> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> identity, SqlConnectionString connectionOptions, Object providerInfo,
> String
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> +173
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection) +357
> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
> owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
> System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
> owningObject) +424
> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
> owningObject) +66
> System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject) +494
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection) +82
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory) +105
> System.Data.SqlClient.SqlConnection.Open() +111
> System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable,
> IDbCommand command, CommandBehavior behavior) +121
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord,
> Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
> behavior) +137
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
> +83
> System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments
> arguments) +1770
> System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
> DataSourceViewSelectCallback callback) +17
> System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
> System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
> System.Web.UI.WebControls.GridView.DataBind() +4
> System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
> System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
> System.Web.UI.Control.PreRenderRecursiveInternal() +86
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Page.ProcessRequestMain(Boolean
> includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
>
>|||Thank You for your imput, I will give it a try.
"Ekrem Ã?nsoy" wrote:
> It's nothing to do with the app codes. It's about the database.
> After detaching your database and changing your log file's path, to attach
> your database again run a script like the following according to your
> situation.
> CREATE DATABASE [Test] ON
> ( FILENAME = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\Data\Test.mdf' ),
> ( FILENAME = N'D:\Test_log.ldf' )
> FOR ATTACH
> --
> Ekrem Ã?nsoy
>
> "lca1630" <lca1630@.discussions.microsoft.com> wrote in message
> news:D4F0B3BA-D066-4771-937A-0FF6579C69AB@.microsoft.com...
> > Hi,
> >
> > Here is the scenario:
> >
> > My web developer is building a new site in asp.net, in turn he is using
> > SQL
> > express in building his application. In my research on deploying SQL
> > express
> > I have found it is in best practice to separate the .mdf and .ldf files to
> > different drives with adequate space. When he copies the database files he
> > has created from his local desktop up to the app_data folder on the web
> > server his database driven app works perfectly. Though when I go to move
> > the
> > .ldf file to another location the application breaks, throwing the error
> > below. I have tried attaching the database to the management studio
> > express
> > and configuring the location of the files but that did not work.
> >
> > I have looked to into impersonation, metadata, and asp.net coding but am
> > not
> > quite sure which may be causing this issue.
> >
> > Please Help!!
> >
> > Server Error in '/' Application.
> > ---
> >
> > ---
> >
> > Cannot open user default database. Login failed.
> > Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> > Description: An unhandled exception occurred during the execution of the
> > current web request. Please review the stack trace for more information
> > about
> > the error and where it originated in the code.
> >
> > Exception Details: System.Data.SqlClient.SqlException: Cannot open user
> > default database. Login failed.
> > Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> >
> > Source Error:
> >
> > An unhandled exception was generated during the execution of the current
> > web
> > request. Information regarding the origin and location of the exception
> > can
> > be identified using the exception stack trace below.
> >
> > Stack Trace:
> >
> >
> > [SqlException (0x80131904): Cannot open user default database. Login
> > failed.
> > Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
> > System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> > exception, Boolean breakConnection) +800131
> >
> > System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> > stateObj) +186
> > System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> > cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> > bulkCopyHandler, TdsParserStateObject stateObj) +1932
> > System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> > enlistOK) +33
> >
> > System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> > serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> > timerExpire, SqlConnection owningObject) +172
> > System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> > host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> > owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> > +381
> >
> > System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> > owningObject, SqlConnectionString connectionOptions, String newPassword,
> > Boolean redirectedUserInstance) +181
> >
> > System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> > identity, SqlConnectionString connectionOptions, Object providerInfo,
> > String
> > newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> > +173
> >
> > System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> > options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> > owningConnection) +357
> >
> > System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
> > owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
> > System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
> > owningObject) +424
> > System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
> > owningObject) +66
> > System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> > owningObject) +494
> > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> > owningConnection) +82
> > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> > outerConnection, DbConnectionFactory connectionFactory) +105
> > System.Data.SqlClient.SqlConnection.Open() +111
> > System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> > DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> > srcTable,
> > IDbCommand command, CommandBehavior behavior) +121
> > System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> > startRecord,
> > Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
> > behavior) +137
> > System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
> > +83
> >
> > System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments
> > arguments) +1770
> > System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
> > DataSourceViewSelectCallback callback) +17
> > System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
> > System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
> > System.Web.UI.WebControls.GridView.DataBind() +4
> > System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
> > System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
> > System.Web.UI.Control.PreRenderRecursiveInternal() +86
> > System.Web.UI.Control.PreRenderRecursiveInternal() +170
> > System.Web.UI.Control.PreRenderRecursiveInternal() +170
> > System.Web.UI.Control.PreRenderRecursiveInternal() +170
> > System.Web.UI.Control.PreRenderRecursiveInternal() +170
> > System.Web.UI.Page.ProcessRequestMain(Boolean
> > includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
> >
> >
> >
> >
>|||I am wondering if anyone can look at the stack trace and give me an idea of
what if going on. Thanks
Server Error in '/' Application
----
Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user
default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Source Error:
An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject) +172
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection) +133
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject) +494
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
System.Web.UI.Control.PreRenderRecursiveInternal() +86
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
----
Version Information: Microsoft .NET Framework Version:2.0.50727.1433;
ASP.NET Version:2.0.50727.1433
"lca1630" wrote:
> Hi,
> Here is the scenario:
> My web developer is building a new site in asp.net, in turn he is using SQL
> express in building his application. In my research on deploying SQL express
> I have found it is in best practice to separate the .mdf and .ldf files to
> different drives with adequate space. When he copies the database files he
> has created from his local desktop up to the app_data folder on the web
> server his database driven app works perfectly. Though when I go to move the
> .ldf file to another location the application breaks, throwing the error
> below. I have tried attaching the database to the management studio express
> and configuring the location of the files but that did not work.
> I have looked to into impersonation, metadata, and asp.net coding but am not
> quite sure which may be causing this issue.
> Please Help!!
> Server Error in '/' Application.
> ---
> ---
> Cannot open user default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information about
> the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Cannot open user
> default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> An unhandled exception was generated during the execution of the current web
> request. Information regarding the origin and location of the exception can
> be identified using the exception stack trace below.
> Stack Trace:
>
> [SqlException (0x80131904): Cannot open user default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection) +800131
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj) +186
> System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj) +1932
> System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> enlistOK) +33
> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject) +172
> System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381
> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance) +181
> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> identity, SqlConnectionString connectionOptions, Object providerInfo, String
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection) +357
> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
> System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
> owningObject) +424
> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
> owningObject) +66
> System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject) +494
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection) +82
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory) +105
> System.Data.SqlClient.SqlConnection.Open() +111
> System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
> IDbCommand command, CommandBehavior behavior) +121
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
> Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
> behavior) +137
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
> System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
> System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
> DataSourceViewSelectCallback callback) +17
> System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
> System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
> System.Web.UI.WebControls.GridView.DataBind() +4
> System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
> System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
> System.Web.UI.Control.PreRenderRecursiveInternal() +86
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Page.ProcessRequestMain(Boolean
> includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
>
>|||Every Login has a default database which is set as "master" by default.
In cases such as yours, people change default database of a Login and then
they delete that database or something and then when they try to connect to
SQL Server using this Login they get this error because this Login' s
default database is not exist, it's not there anymore so it raises this
error.
Ensure that Network Service Login' s default database is set correctly. You
can correct this from your Login' s properties.
--
Ekrem Ã?nsoy
"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:0FF290EE-3C57-4974-B9F5-90D6F3012288@.microsoft.com...
>I am wondering if anyone can look at the stack trace and give me an idea of
> what if going on. Thanks
> Server Error in '/' Application.
> ----
> Cannot open user default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.Data.SqlClient.SqlException: Cannot open user
> default database. Login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Source Error:
> An unhandled exception was generated during the execution of the current
> web
> request. Information regarding the origin and location of the exception
> can
> be identified using the exception stack trace below.
> Stack Trace:
>
> [SqlException (0x80131904): Cannot open user default database. Login
> failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection) +800131
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj) +186
> System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj) +1932
> System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> enlistOK) +33
> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject) +172
> System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> +381
> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance) +181
> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> identity, SqlConnectionString connectionOptions, Object providerInfo,
> String
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> +173
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection) +133
> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
> owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
> System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
> owningObject) +424
> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
> owningObject) +66
> System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject) +494
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection) +82
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory) +105
> System.Data.SqlClient.SqlConnection.Open() +111
> System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable,
> IDbCommand command, CommandBehavior behavior) +121
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord,
> Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
> behavior) +137
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
> +83
> System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments
> arguments) +1770
> System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
> DataSourceViewSelectCallback callback) +17
> System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
> System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
> System.Web.UI.WebControls.GridView.DataBind() +4
> System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
> System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
> System.Web.UI.Control.PreRenderRecursiveInternal() +86
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Control.PreRenderRecursiveInternal() +170
> System.Web.UI.Page.ProcessRequestMain(Boolean
> includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
>
>
> ----
> Version Information: Microsoft .NET Framework Version:2.0.50727.1433;
> ASP.NET Version:2.0.50727.1433
> "lca1630" wrote:
>> Hi,
>> Here is the scenario:
>> My web developer is building a new site in asp.net, in turn he is using
>> SQL
>> express in building his application. In my research on deploying SQL
>> express
>> I have found it is in best practice to separate the .mdf and .ldf files
>> to
>> different drives with adequate space. When he copies the database files
>> he
>> has created from his local desktop up to the app_data folder on the web
>> server his database driven app works perfectly. Though when I go to move
>> the
>> .ldf file to another location the application breaks, throwing the error
>> below. I have tried attaching the database to the management studio
>> express
>> and configuring the location of the files but that did not work.
>> I have looked to into impersonation, metadata, and asp.net coding but am
>> not
>> quite sure which may be causing this issue.
>> Please Help!!
>> Server Error in '/' Application.
>> ---
>> ---
>> Cannot open user default database. Login failed.
>> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
>> Description: An unhandled exception occurred during the execution of the
>> current web request. Please review the stack trace for more information
>> about
>> the error and where it originated in the code.
>> Exception Details: System.Data.SqlClient.SqlException: Cannot open user
>> default database. Login failed.
>> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
>> Source Error:
>> An unhandled exception was generated during the execution of the current
>> web
>> request. Information regarding the origin and location of the exception
>> can
>> be identified using the exception stack trace below.
>> Stack Trace:
>>
>> [SqlException (0x80131904): Cannot open user default database. Login
>> failed.
>> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.]
>> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
>> exception, Boolean breakConnection) +800131
>> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
>> stateObj) +186
>> System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
>> SqlCommand
>> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
>> bulkCopyHandler, TdsParserStateObject stateObj) +1932
>> System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
>> enlistOK) +33
>> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
>> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
>> timerExpire, SqlConnection owningObject) +172
>> System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
>> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
>> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
>> +381
>> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
>> owningObject, SqlConnectionString connectionOptions, String newPassword,
>> Boolean redirectedUserInstance) +181
>> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
>> identity, SqlConnectionString connectionOptions, Object providerInfo,
>> String
>> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
>> +173
>> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
>> options, Object poolGroupProviderInfo, DbConnectionPool pool,
>> DbConnection
>> owningConnection) +357
>> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
>> owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
>> System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
>> owningObject) +424
>> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
>> owningObject) +66
>> System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
>> owningObject) +494
>> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
>> owningConnection) +82
>> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
>> outerConnection, DbConnectionFactory connectionFactory) +105
>> System.Data.SqlClient.SqlConnection.Open() +111
>> System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
>> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
>> srcTable,
>> IDbCommand command, CommandBehavior behavior) +121
>> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
>> startRecord,
>> Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
>> behavior) +137
>> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
>> srcTable) +83
>> System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments
>> arguments) +1770
>> System.Web.UI.DataSourceView.Select(DataSourceSelectArguments
>> arguments,
>> DataSourceViewSelectCallback callback) +17
>> System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
>> System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
>> System.Web.UI.WebControls.GridView.DataBind() +4
>> System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
>> System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
>> System.Web.UI.Control.PreRenderRecursiveInternal() +86
>> System.Web.UI.Control.PreRenderRecursiveInternal() +170
>> System.Web.UI.Control.PreRenderRecursiveInternal() +170
>> System.Web.UI.Control.PreRenderRecursiveInternal() +170
>> System.Web.UI.Control.PreRenderRecursiveInternal() +170
>> System.Web.UI.Page.ProcessRequestMain(Boolean
>> includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
>> +2041
>>
>>

PIVOT statement whitout knowing values

Just a small issue...

I'm trying the new SQL 2005 (Express) because the PIVOT function was finally added.

I've a table with three columns ID, Height and Width

Now I'd like to have a table with for each height the number of ID for each Width

The easiest way is to use the PIVOT statement.....but..... to use it in SQL2005 I should use:

SELECT Height, [100] AS Width01, [200] AS Width02
FROM (

SELECT ID, Height, Width FROM TestTable) p

PIVOT ( COUNT (ID) FOR Width IN([100], [200]) ) AS pvt

This kind of querry works perfectly in a static situation, but if I add new record in the table referencing the "300" Width to obtain the correct result I have to modify the query.

Is there an options or a technique for having the list of the Width dinamically filled according the table contents.

Thank you very much to anyone how can help me

H

You have to use dynamic SQL to execute the SELECT statement after generating the values for the IN list. There is no other way using static SQL code.|||

To be clear, there are good reasons for this restriction.

SQL Server's PIVOT can exist anywhere in the query tree (unlike in Access), supports UNPIVOT (unlike Access), and does not require recompilation for each execution (unlike Access). These are good things for complex queries, as compilation time would be significantly worse if these did not exist.

SQL Server's query optimizer has a requirement that the column list be known before compilation begins. This allows faster compiles because we can identify duplicate alternatives more easily and avoid doing extra work during compilation. This also helps us to determine if we can avoid searching portions of the possible plan space that obviously will not help find a faster plan than what has been found so far during optimization.

I understand the desire to not have to bother specifying a column list, and perhaps that is something we can add in a future release. The reasons above are reasons it was not added in SQL 2005. Even if such a feature were added, it would be likely better if you could specify a column list to speed system throughput.

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

Thank you all for the clear answer, now I understood that the restriction is due to performances.

Of course this type of restriction have very few impact over small databases like the ones I working on (~100 MB). So I will keep my application over access where the power of the TRANSFORM-PIVOT scheme will help me reducing the programming effort.

Thanks again

H