Archive

Archive for January, 2013

Table-Valued User-Defined Functions with Temp table

January 31, 2013 Leave a comment

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.

Advertisements

The DELETE statement conflicted with the REFERENCE constraint in membership table

January 30, 2013 Leave a comment

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.

January 15, 2013 Leave a comment

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>
%d bloggers like this: