Login failed for user ‘IIS APPPOOL\AppPoolName’

Applicationb Pool configuration
2014-05-28_11h52_18

Error message from applicaiton

Cannot open database "My_database" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\AppPoolName'.

Solution

To fix it you have to add new login to your SQL Server

  • In SQL Server Management Studio find the Security folder
  • Right click logins and select “New Login”
  • In the Login name field, type IIS APPPOOL\AppPoolName – do not click search
  • Click OK

Retrieving last changes from log table.

Table definition

ScreenShot131

SELECT LogId, CreationDate, UserId, ApprovalForSendingSMS
FROM dbo.Log
WHERE UserId = 102
ORDER BY CreationDate DESC

ScreenShot133

SELECT TOP 1 x.CreationDate, x.CurrentApprovalForSendingSMS, x.PreviousApprovalForSendingSMS
FROM
(
    SELECT l.CreationDate, l.ApprovalForSendingSMS AS CurrentApprovalForSendingSMS, (SELECT TOP 1 e.ApprovalForSendingSMS FROM dbo.Log e WHERE l.UserId = e.UserId AND e.CreationDate < l.CreationDate ORDER BY e.CreationDate DESC) AS PreviousApprovalForSendingSMS
    FROM dbo.Log l
    WHERE l.UserId = 102    
) x 
WHERE x.CurrentApprovalForSendingSMS  x.PreviousApprovalForSendingSMS
ORDER BY x.CreationDate DESC 

Final result…

ScreenShot134

Conditional sorting and paging in T-SQL

DECLARE
@Take INT,
@Skip INT,
@SortField VARCHAR(100)

;WITH workers AS (
SELECT 
	   w.WorkerId,
	   w.FirstName,
	   w.LastName,
	   w.Specialization,    
	   ROW_NUMBER() OVER 
	   (
		  ORDER BY 			 
			 CASE WHEN @SortField = 'WorkerId#desc' THEN w.WorkerId
			 END DESC,
			 CASE WHEN @SortField = 'WorkerId#asc' THEN w.WorkerId
			 END ASC,
			 CASE WHEN @SortField = 'LastName#desc' THEN w.LastName
			 END DESC,
			 CASE WHEN @SortField = 'LastName#asc' THEN w.LastName
			 END ASC,
			 CASE WHEN @SortField = 'Specialization#desc' THEN w.Specialization
			 END DESC,
			 CASE WHEN @SortField = 'Specialization#asc' THEN w.Specialization
			 END ASC,				 
			 CASE WHEN 1 = 1 THEN w.LastName
			 END ASC
	   ) AS rowNumber
    FROM Workers w
)

    SELECT 
	   w.WorkerId,
	   w.FirstName,
	   w.LastName
	   w.Specialization,
	   (SELECT COUNT(*) FROM workers) AS TotalRows
    FROM workers w
    WHERE rowNumber BETWEEN @Skip + 1 AND @Skip + @Take
    ORDER BY rowNumber ASC

Retrieving the conditional intersection of the tables

First we create three tables and fill them with sample data.

CREATE TABLE [dbo].[DataSet1](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Col1] [CHAR](1) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DataSet2](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Col1] [CHAR](1) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DataSet3](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Col1] [CHAR](1) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO dbo.DataSet1(Col1)
SELECT 'A'
UNION ALL
SELECT 'Z'
UNION ALL
SELECT 'Y'

INSERT INTO dbo.DataSet2(Col1)
SELECT 'Z'
UNION ALL
SELECT 'X'
UNION ALL
SELECT 'Y'

INSERT INTO dbo.DataSet3(Col1)
SELECT 'A'
UNION ALL
SELECT 'Z'
UNION ALL
SELECT 'X'
UNION ALL
SELECT 'B'

We have to retrieve the intersection of these tables based on conditions.

DECLARE @takeDataSet1Dimension BIT = 1;
DECLARE @takeDateSet2Dimension BIT = 0;
DECLARE @takeDateSet3Dimension BIT = 1;

DECLARE @dimensionCounter INT = 0;

IF (@takeDataSet1Dimension = 1)
    SET @dimensionCounter = @dimensionCounter + 1;

IF (@takeDateSet2Dimension = 1)
    SET @dimensionCounter = @dimensionCounter + 1;
    
IF (@takeDateSet3Dimension = 1)
    SET @dimensionCounter = @dimensionCounter + 1;
    
WITH d AS (
SELECT Col1
FROM dbo.DataSet1
WHERE @takeDataSet1Dimension = 1
UNION ALL
SELECT Col1
FROM dbo.DataSet2
WHERE @takeDateSet2Dimension = 1
UNION ALL
SELECT Col1
FROM dbo.DataSet3
WHERE @takeDateSet3Dimension = 1
), dd AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS dimensionAmount, Col1
   FROM d
)
select Col1, dimensionAmount
from dd
where dimensionAmount = @dimensionCounter

In this example, we take the intersection of the first and third table. We will get two rows: A and Z

Using table-valued parameters in SQL Server

private static void BuildCollection(IEnumerable inputCollection, IList outputCollection, SqlMetaData[] metaData)
        {
            foreach (var i in inputCollection)
            {
                var rec = new SqlDataRecord(metaData);
                rec.SetValue(0, i);
                outputCollection.Add(rec);
            }
        }

private static void PrepareDataRecordParameter(SqlCommand command, string paramName, IEnumerable records, string typeName)
        {
            if (records.Any())
            {
                var listParam = new SqlParameter(paramName, SqlDbType.Structured);
                listParam.Direction = ParameterDirection.Input;
                listParam.TypeName = typeName;
                listParam.Value = records;

                command.Parameters.Add(listParam);
            }
        }

Use these methods as follow

IEnumerable orders = new List {1, 200, 234, 23}

IList drOrders = new List();

SqlMetaData[] IntegerTable =
{
    new SqlMetaData("Id", SqlDbType.Int)
};

BuildCollection(orders, drOrders , intIdTable);

var command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.CheckOrders";
 
PrepareDataRecordParameter(command, "@orders", drOrders, "dbo.IntIdTable");


Stored procedure looks like this:

CREATE PROCEDURE dbo.CheckOrders(
@orders [dbo].[IntegerTable] readonly
)
AS
...

Paging in stored procedures

Pass two additional parameters to you stored procedure and perform where condition like this

WHERE rowNumber BETWEEN @skip + 1 AND @skip + @take

Example

@take int = 20
@skip int = 0

;WITH orders AS (
SELECT 
   OrderId,
   InvoiceNumber
    ROW_NUMBER() OVER (ORDER BY OrderId) AS rowNumber
FROM dbo.Orders
)

SELECT 
OrderId,
InvoiceNumber,
(SELECT COUNT(*) FROM orders) as TotalRows
FROM orders
WHERE rowNumber BETWEEN @skip + 1 AND @skip + @take

Warunkowy INSERT/UPDATE z wykorzystaniem MERGE

Jeśli chce warunkowo wykonać operację INSERT lub UPDATE na tabeli, wówczas zamiast pisać logikę sprawdzającą, czy dany rekord istnieje i wykonać odpowiednią akcję, mogę wykorzystać polecenie MERGE. Poniżej przykład na tabeli Query. Jeśli w tabeli istnieje rekord o QueryId = 1 wówczas kolumna QueryNumber zostanie zaktualizowana, w przeciwnym wypadku, nowy wiersz zostanie dodany.

declare @QueryId tinyint
set @QueryId = 1

declare @QueryNumber nvarchar(20)
set @QueryNumber = 'Query Number'

MERGE dbo.Query as T
USING (SELECT @QueryId as QueryId, @QueryNumber as QueryNumber) as S
ON T.QueryId = S.QueryId
WHEN MATCHED THEN
    UPDATE SET T.QueryNumber = S.QueryNumber
WHEN NOT MATCHED BY TARGET THEN
    INSERT(QueryNumber) VALUES (S.QueryNumber);