SQL Server 2005 ASP.net SQL server setup wizard (aspnet_regsql.exe)

[rmajors]

I am trying to use the ASP.net SQL server setup wizard


> (aspnet_regsql.exe) to set up my SQL Server 2005 database for use with


> Microsoft Visual Web Developer 2005.


>


> I can successfully connect to the database through the database


> explorer, but when I click Finish on the wizard mentioned above, I get


> the following error message:


>


>


>


> Setup failed.


>


> Exception:


> An error occurred during the execution of the SQL file


> 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException


> message is: String or binary data would be truncated.


>


> ----------------------------------------


> Details of failure


> ----------------------------------------


>


> SQL Server:


> Database: [SQL2005_150328_master]


> SQL file loaded:


> InstallCommon.sql


>


> Commands failed:


>


> CREATE TABLE #aspnet_Permissions


> (


> Owner sysname,


> Object sysname,


> Grantee sysname,


> Grantor sysname,


> ProtectType char(10),


> [Action] varchar(20),


> [Column] sysname


> )


>


> INSERT INTO #aspnet_Permissions


> EXEC sp_helprotect


>


> IF (EXISTS (SELECT name


> FROM sysobjects


> WHERE (name = N'aspnet_Setup_RestorePermissions')


> AND (type = 'P')))


> DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions


>


>


> SQL Exception:


> System.Data.SqlClient.SqlException: String or binary data would be truncated.


> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)


> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)


> at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)


> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)


> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)


> at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)


> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()


> at System.Web.Management.SqlServices.ExecuteFile(Stri ng file,


> String server, String database, String dbFileName, SqlConnection


> connection, Boolean sessionState, Boolean isInstall, SessionStateType


> sessionStatetype)


>

[SEAN432]

This is all for your development machine? Not trying to install SQL on the host I am assuming becuase that is a addon upgrade.

On your machine you most likely need MSDE or SQL Server Developer Edition ($50). MSDE is free single user database. Starting it up involves some command lines that I dont have handy and can be a bit of a pain actually. I got mine to work but still unclear how.

Never the less I havent run out and got SQL Server edition becuase I am contemplating moving everything to .Net 2.0

[rmajors]

I have tried the steps outlined by Penny on http://community.discountasp.net/default.aspx?f=24&m=8235to get Security working(as well as those steps in the link she provides), and I am stuck on a couple of issues. I am running Visual Studio 2005 Express (final release) and connecting to my discountasp.net SQL Server 2005 database. I do not have SQL Server Express installed (I uninstalled it and reinstalled VSE, because I thinkSSE was causing part of the problem.)


1. Did you have to run the aspnet_regsql utility to enable your discountasp.net SQL Server 2005 database to be ready to use with asp.net 2.0? I tried running this utility to setup my discountasp.net SQL Server 2005 database and received the error shown below.


2. In VSE, I choose Website, ASP.net Configuration. I go to the Provider Configuration tab, and Test the AspNetSqlProvider and it says it is able to successfully establish a connection to the database. However, when I go the the Security tab, I get the following error:


There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.


Any help much appreciated,


Randy


---------------------


ERROR associated with #1 above:


--------------------------------------------


Setup failed.


Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated.


----------------------------------------
Details of failure
----------------------------------------


SQL Server:
Database: [SQL2005_150328_master]
SQL file loaded:
InstallCommon.sql


Commands failed:


CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)


INSERT INTO #aspnet_Permissions
EXEC sp_helprotect


IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions



SQL Exception:
System.Data.SqlClient.SqlException: String or binary data would be truncated.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(Stri ng file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

[ashish]

Hi,
This is Dot Net bug...

Please try following steps

1.Go To WINDOWS\Microsoft.NET\Framework\v2.0.50215
2.Open InstallCommon.sql file.
3.Update all "Action" parameter length to 200 in file
4.save file
5.Run "aspnet_regsql.exe"

It works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[Amit]

Hi All,


Ashish's solution works fine. But there is one more solution to this problem.


Follow the following steps


1. Execute the following store procedure.


EXEC sp_helprotect


2. The output of this store procedure shows the number of records.


3. In these record, one of the record is having a length more that 20 character in Action column. and the definition for temparary table defines the length of Action column as varchar(20).


4. To resolve this error you might change the permissions of the specified user or you can change the InstallCommon.sql file.


Thanks.....

[tokrishna.n]

Hi Amit,


I tried the way told by Ashish, but i am getting the error message as
Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.


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: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

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): Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.]
System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) +857194
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) +734806
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.Util.SecUtility.CheckSchemaVersion(Prov iderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367
System.Web.Security.SqlMembershipProvider.CheckSch emaVersion(SqlConnection connection) +85
System.Web.Security.SqlMembershipProvider.CreateUs er(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612
System.Web.UI.WebControls.CreateUserWizard.Attempt CreateUser() +305
System.Web.UI.WebControls.CreateUserWizard.OnNextB uttonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Obj ect source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbl eEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbl eEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(Command EventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102






Version Information:Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210





Ichanged InstallCommon.sql, executed aspnet_regsql.exe. Can any one help me ..!!





































































Amit Patil said...



Hi All,


Ashish's solution works fine. But there is one more solution to this problem.


Follow the following steps


1. Execute the following store procedure.


EXEC sp_helprotect


2. The output of this store procedure shows the number of records.


3. In these record, one of the record is having a length more that 20 character in Action column. and the definition for temparary table defines the length of Action column as varchar(20).


4. To resolve this error you might change the permissions of the specified user or you can change the InstallCommon.sql file.


Thanks.....

[vvsharma]

The aspnet schema was not created properly.

Please do as follows:
1. Open the command prompt on your local computer, and navigate to: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
2. Execute the command: aspnet_regsql.exe -S [DB Server Name] -U [DB login] -P [Password] -A all -d [Database name]

(See:http://kb.discountasp.net/article.aspx?id=10413 to know how to configure ASP.NET 2.0 Membership/Roles Provider to use SQL 2000 or SQL 2005)

(Note:Replace all the placeholders by appropriate values.)

Vikram

DiscountASP.NET
www.DiscountASP.NET (http://www.discountasp.net/)