aspnet_regsql.exe and generated SQL

Where is the sql generated byaspnet_regsql.exe saved?

It can be found in App_Code folder as mdf and ldf files. If you are using VS 2005 then refresh the solution explorer to show the App_Code folder and the database.|||Oops its not App_Code but App_Data folder!!!!

aspnet_regsql.exe - cannot insert null values

Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errors

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.

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

SQL Server:
Database: [AddressVerification]
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: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
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)

Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??

** I am logged in with full admin privaliges

tia

Mark.

This is because the preinstalled user defined data type sysname by default does not allow nulls--that means column of sysname data type won't accept nulls unless you explicitly set the column to allow null in column definition. So if the sp_helprotect stored procedure returns some rows with nulls in the [column] field (this is an rare issue but it does exist), the insert command will fail. So here I have to say it is a flaw of the aspnet_regsql utility.|||

I wrote a simple script to update the system table systypes to make sysname data type to accept NULLs. However the script is not fully tested and since it modifies system table directly, I don't recommend to use it and you need to consider the potential risk when using it. If some issue is caused by the script, please pass 'NoNulls' as parameter to change the systypes back to default:

CREATE PROCEDURE usp_KillUsers
@.p_DBName SYSNAME = NULL
AS

/* Check Paramaters */
/* Check for a DB name */
IF (@.p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@.p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@.p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied

SET NOCOUNT ON

/* Declare Variables */
DECLARE @.v_spid INT,
@.v_SQL NVARCHAR(255)

/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.p_DBName

OPEN c_Users

FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.v_SQL = 'KILL ' + CONVERT(NVARCHAR, @.v_spid)
-- PRINT @.v_SQL
EXEC (@.v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @.v_spid
END -- While

CLOSE c_Users
DEALLOCATE c_Users

CREATE PROCEDURE sp_SetSysnameAllowNulls @.Operation varchar(10)='AllowNulls'
AS
BEGIN
DECLARE @.cmd nvarchar(4000)
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
EXEC sp_msforeachdb N'use[?];
IF (DATABASEPROPERTY(db_name(),''IsReadOnly'')=1)
BEGIN
declare @.dbname sysname;
set @.dbname=db_name();
use master
EXEC usp_KillUsers @.dbname;
EXEC sp_dboption @.dbname,''read only'',''false'';
END'

IF (@.Operation = 'AllowNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status-1
WHERE name=''sysname'' AND (status&1)=1'
ELSE
IF (@.Operation = 'NoNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status+1
WHERE name=''sysname'' AND (status&1)=0'
ELSE RAISERROR('Expected @.Opertaion=''AllowNulls'' or ''NoNulls''',16,1)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
END

aspnet_regsql.exe

hi...

I want to Create an Application Service Database for SQL Server,SqlWebEventProvider, and i am trying to write in command line:

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S sqlserver\sqltest -A w

i get the following error:

The argument 'sqlserver\sqltest' is invalid.

why is that?

do i have to use the following flags:

-ssadd -sstype t ?

and, how is the right way to do this...?

thank you...

Try to use one of this options:

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S sqlserver -A w

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S sqltest -A w

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S localhost -A w

ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)

I just ran the utility without any commands ant the wizard has come up. This is all independent of my web application.

I am guessing this is going to install the database tables to my existing database.UPDATE: They have been created

Will I then connect my web app to this database schema that is created?

How do I change the membership/roles users in my web app to point to the database tables?

To connect you app to the membership database you define the membership provider in web.config
main setting are connectionstring and application name.

Here a link with more info about the settings for you

http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

To manage the membership role and user you can use ASP.NET Configuration tool you will
find it on the website menu in visual web developer.

Hope that helps you.

|||

Weird, there is only 1 thing in my applications web config that references the roles and memberships..

<roleManager enabled="true" />

What should I be doing to connect the current roles and membership to the new SQL Server 2000 tables that the utility created?
In the ASP.NET Configuration tool, there is a provider tab, in this area there is a AspNetSqlProvider and a radio button, there is also a test link.

When I click the test link it eventually fails, even though I have created all the necessary tables in the 2000 database.

|||

Ok you will need to setup a connection string to you database and define the membership provider.

Here is example for you

<connectionStrings><remove name="LocalSqlServer"/><!-- Connection for SQL 2000 --><add name="LocalSqlServer" connectionString="Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;" providerName="System.Data.SqlClient"/></connectionStrings> <membership> <providers> <clear/> <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true"requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" /> </providers></membership>

You then can setup the users and roles using the asp.net confrigation tool you will find it in the website menu in visual web developer express

I hope this helps

|||

Ok, I will try to get farther. I am suprised none of this was added to the Web.config file as I have a working membership login ability... anyone know why?

Also, for the record, I am using Visual Studio 2005Standard Edition, will that change anything?

What is the situation with <remove name="...">

Are you just clearing the existing pair/key value? Is that needed?

|||

I think the logic around why it is not added is to allow you to define the setting ie because you are using SQL 2000 or 2005.

No problem with a higher version just lots of people here are using express.

Yes you are right about the remove name, it just to make sure you are overriding the system defaults.

Hope it helps