The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. Solved...

Share |

While working with Asp.NET and Membership, using MS SQL 2008 R2 I came across following error. My web application was working perfect on my local machine but when I hosted it on remote server I got following error.

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

After R&D I had came across three possible solutions "WHICH I THINK ARE RIGHT, AS THEY WORKED FOR ME IN DIFFERENT SCENARIOS."

1. Check "aspnet_SchemaVersions" table in database and make sure CompatibleSchemaVersion is set to "1" for all the Features (e.g. common, membership, role manager etc) available in table. Also make sure IsCurrentVersion column values are set to 'True" for all Features.
2. In Web.Config, and aspnet_Applications table check if application Name is matching or not. Application name in web.config's Membership>Providers, Profile>Providers, RoleManager>Providers are matching with application name in database.
3. In Membership>Provider I had "AspNetSqlMembershipProvider" type as type="System.Web.Security.SqlMembershipProvider" which I modified (after referring to some articles online) to type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

Application worked for me when I checked all these things and made changes in my application.

Read more...

Cannot connect to local SQL Server Express 2008 R2

Share |

Some times while using SQL server R2 on local machines we get a connection error something looking like

TITLE: Connect to Server

Cannot connect to (local).

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

To deal with it, there are two things we need to check

1. Try this as your server: .\SQLEXPRESS
2. This error occurs if SQLEXPRESS instance is not started. To verify the service is started or not run services.msc and look for  "SQL Server (SQLEXPRESS)" service, status should be "Started", if it is not, try to start it manually.

Read more...

Drop All Functions in SQL SERVER

Share |

To DROP All function in SQL SERVER Database use following lines of codes, use it in Query window for particular database.

This code will save lots of time as DROPing All functions in SQL Server database is not a manual process, if we use this

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 

WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') 

AND category = 0 ORDER BY [name]) 

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND 
category = 0 AND [name] > @name ORDER BY [name])
END
GO

Read more...

Drop All Views in SQL SERVER

Share |

This is a very common scenario while updating or replacing database in SQL SERVER, we might need to delete all the views in SQL SERVER database. But to delete one view at a times is a time consuming process if database is huge with lots of Views.

To Drop all views in SQL SERVER database use following

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] = 'V' AND category = 0 AND [name] > @name 
ORDER BY [name])
END
GO

Read more...

Delete All Tables MSSQL

Share |

While working on ASP.Net application, I came across a situation where I had to delete all the Tables of the MSSQL 2008 database, and it was really painful process. 

In this case I had to delete all the SPs and all views as well. As I started searching like "Truncate database Sql 2008" or "Delete all tables in MSSQL" I came across two things and it worked for me.

(I am really not sure whether it works in all the cases but for Deleting all tables in SQL, this worked for me.) 

Firstly we need to remove all indexs from SQL table, I found following code when I Google the term 

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND
  indid > 0 AND indid < 255 AND
  INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC

OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
  SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
  EXEC sp_executesql @dropIndexSql

  FETCH NEXT FROM tableIndexes INTO @indexName
END

CLOSE tableIndexes
DEALLOCATE tableIndexes

After this we need to delete all the tables in SQL, for this I used following SP

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

It seems that this is a HIDDEN stored procedure in MSSQL.

Read more...