Table-Valued User-Defined Functions with Temp table

Create FUNCTION GetEmployee()
RETURNS  @rtnTable TABLE
(
    — columns returned by the function
    ID UNIQUEIDENTIFIER NOT NULL,
    Name nvarchar(255) NOT NULL
)
AS
BEGIN
DECLARE @myTable table (id uniqueidentifier, name nvarchar(255))

insert into @myTable
select from your stuff

–This select returns data
insert into @rtnTable
SELECT ID, name FROM @myTable
return
END

you can’t access Temporary Tables from within a SQL Function. You will need to use table variables so essentially. After select the value to return it as table formate you have to insert the selected records  to ‘@rtnTable’ which returns in table structure.

The DELETE statement conflicted with the REFERENCE constraint in membership table

To delete a user from the database i used ‘Membership.DeleteUser(user)‘ while doing smoke test it is working in development environment but not in testing environment.

The DELETE statement conflicted with the REFERENCE constraint “FK__aspnet_Us__UserI__47489C52”. The conflict occurred in database “TestDB”, table “dbo.aspnet_UsersInRoles”, column ‘UserId’.

After analysis the ‘aspnet_Users_DeleteUser‘ procedure i came to know all the views for aspnet membership are missing

using ‘SELECT * FROM sysobjects where type = ‘V’‘

Refrence: Roles and Views in the Application Services Database for SQL Server

The maximum message size quota for incoming messages (65536) has been exceeded.

The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.

Make sure that the readerQuotas settings are the same both on client and server side.Also if you are transferring some file or large object consider to increase the httpRunTime elements maxRequestLength value in the system.web section.

Configure the corresponding binding section in Web.Config  as below

<binding name="Binding1" closeTimeout="00:10:00" openTimeout="00:10:00"
      sendTimeout="00:10:00" maxBufferPoolSize="2147483647" maxBufferSize="2147483647"
      maxReceivedMessageSize="2147483647" portSharingEnabled="true">
      <readerQuotas maxArrayLength="2147483647" maxNameTableCharCount="2147483647"
          maxStringContentLength="2147483647" maxDepth="2147483647"
          maxBytesPerRead="2147483647" />
      <security mode="None" />
    </binding>