Fixing Error validating element: The element has been orphaned from it’s login and cannot be deployed.

When trying to export .bacpac from the sql server database, you can encounter this error.

error_orphaned

The reason why you might get it is that some of the users in your database do not have a corresponding login. So, you can manually create logins by using “CREATE LOGIN” command.

Alternatively, you can use the following script to create logins automatically where they are missing. (Courtesy of stackoverflow)

SET NOCOUNT ON
USE {your-db-name}
GO
DECLARE @loop INT
DECLARE @USER sysname
DECLARE @sqlcmd NVARCHAR(500) = ''

IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL 
 BEGIN
  DROP TABLE #orphaned
 END

CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1))

INSERT INTO #Orphaned (UserName)
SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')

IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS'
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password'''
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
     END

    SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']'
    Exec(@sqlcmd)
    PRINT @USER + ' link to DB user reset';
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF
Advertisements

Xamarin forms error (iOS) – App is not compatible with this device (Solved)

If this is your first time building apps for iOS using Xamarin you can be confused by different options and settings available from Visual Studio interface. One important setting you need to know about is called “Supported Architectures”.

To find this setting, right-click on the project and click Properties.

step_1

Then go to iOS build setting and change the setting to the desired option.

 

step_2

You should select the option that will support all of your devices. For device/processor list you can visit http://iossupportmatrix.com/ and this list: https://en.wikipedia.org/wiki/List_of_iOS_devices

arm7: Used in the oldest iOS 7-supporting devices
arm7s: As used in iPhone 5 and 5C
arm64: For the 64-bit ARM processor in iPhone 5S
i386: For the 32-bit simulator
x86_64: Used in 64-bit simulator

Courtesy of the info: https://stackoverflow.com/a/43525187/194076

For example, if you need to support iPhone 5 and iphone X then you will need to set the option to ARM64 + ARMv7.

How to copy files in Azure Storage (Using AzCopy)

If you ever need to copy files from one “folder” in Azure Storage you can do this by running the following powershell command.

Example:

If you need to copy all files from mystorage.blob.core.windows.net/old-location to mystorage.blob.core.windows.net/new-location including all sub-paths (ex. old-location/folder1, old-location/folder2, etc.) you can do this by running the following command.

 AzCopy  /Source:https://mystorage.blob.core.windows.net/old-folder
/Dest:https://mystorage.blob.core.windows.net/new-folder

/SourceKey: your_key /DestKey:your_key /S 

/S command will perform recursive copy. If you do not need to copy recursively just remove this command.

If you are copying within the same storage your SourceKey and DestKey will be the same. You can find your storage key using powershell, azure portal, or with Microsoft Azure Storage Explorer

With Azure Storage Explorer you can right-click on the storage and click “Copy primary key”

How to export all data from the SQL server database to Excel (using C#)

Sometimes non-technical users prefer to work with the raw data in Excel instead of querying the database. It is easy to export one db table to Excel using simple C# script. But how to do this for the whole database?

Let’s say you have a button in the interface called “Export everything”. After clicking on it an excel file will be downloaded with one table per tab including column names as headers.

My version of the script uses Epplus library:

Install-Package EPPlus -Version 4.1.1
And below is a full script. It is pretty straightforward :

public byte[] ExportAllTables()
{
var cs = ConfigurationManager.ConnectionStrings[“DrexelContext”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
var listTables = new List<string>();
con.Open();
DataTable t = con.GetSchema(“Tables”);

using (ExcelPackage p = new ExcelPackage())
{
for (var i = 0; i < t.Rows.Count; i++)
{
var x = Convert.ToString(t.Rows[i][“TABLE_NAME”]);
using (SqlCommand sqlCmd = new SqlCommand(“select * from ” + x , con))
{
using (SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd))
{
DataSet ds = new DataSet();
sqlDa.Fill(ds);
if (ds.Tables.Count == 1)
{
foreach (DataTable table in ds.Tables)
{
table.TableName = x;
ExcelWorksheet worksheet = p.Workbook.Worksheets.Add(x);
worksheet.Cells[“A1”].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Medium2);
}
}
}
}
}
con.Close();
return p.GetAsByteArray();
}
}
}

How to automatically truncate/trim strings on insert in entity framework

Recently we had a task when we had a database with limited varchar length. Example:


TaskId int not null

TaskName nvarchar(5) null

Description nvarchar(50) null

We have data coming from this table from multiple sources and sometimes data overflows the fields. In entity framework if you try to assign value to taskName that is larger than 5 characters it will fail with “Entity Validation Failed”

for example:

task.TaskName ="MyLongTaskName";
ctx.SaveChanges();

Now, in our case we wanted to substring all values to fit in the field without throwing an exception.

so, in our example we wanted to have TaskName=”MyLon”

Since we are using EF6.1.3 it was possible to utilize EF interceptors to do this.

Below is the full code for Entity Framework interceptor that will truncate/trim all of the strings to the max size of a column. Just create a separate file and it should work.

You will also need to modify configuration to use this interceptor.

public class MyConfiguration : DbConfiguration
{
public MyConfiguration()
{

AddInterceptor(new StringTrimmerInterceptor());

}

}

 


public class StringTrimmerInterceptor : IDbCommandTreeInterceptor
{
private static readonly string[] _typesToTrim = {"nvarchar", "varchar", "char", "nchar"};

public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
{
if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
{
var insertCommand = interceptionContext.Result as DbInsertCommandTree;
if (insertCommand != null)
{

List<DbModificationClause> finalSetClauses =
new List<DbModificationClause>(
(IEnumerable<DbModificationClause>) insertCommand.SetClauses.Select(
a =>
{
var dbSetClause = a as DbSetClause;
if (dbSetClause != null)
{
var dbPropertyExpression = dbSetClause.Property as DbPropertyExpression;

if (dbPropertyExpression != null)
{
var edmProperty = dbPropertyExpression.Property as EdmProperty;
if (edmProperty != null && edmProperty.MaxLength != null &&
_typesToTrim.Contains(edmProperty.TypeName))
{
var dbConstantExpression = dbSetClause.Value as DbConstantExpression;
if (dbConstantExpression != null && dbConstantExpression.Value != null)
{
var value = dbConstantExpression.Value.ToString();
if (!string.IsNullOrEmpty(value) &&
value.Length > (int) edmProperty.MaxLength)
{
return DbExpressionBuilder.SetClause(
DbExpressionBuilder.Property(
DbExpressionBuilder.Variable(
insertCommand.Target.VariableType,
insertCommand.Target.VariableName),
dbPropertyExpression.Property.Name),
EdmFunctions.Trim(
dbConstantExpression.Value.ToString()
.Substring(0, (int) edmProperty.MaxLength)));
}

}
}
}
}

return a;
}));

var newInsertCommand = new DbInsertCommandTree(
insertCommand.MetadataWorkspace,
insertCommand.DataSpace,
insertCommand.Target,
new ReadOnlyCollection<DbModificationClause>(finalSetClauses),
insertCommand.Returning);

interceptionContext.Result = newInsertCommand;
}
}

&nbsp;

var updateCommand = interceptionContext.Result as DbUpdateCommandTree;
if (updateCommand != null)
{

List<DbModificationClause> finalSetClauses =
new List<DbModificationClause>(
(IEnumerable<DbModificationClause>) updateCommand.SetClauses.Select(
a =>
{
var dbSetClause = a as DbSetClause;
if (dbSetClause != null)
{
var dbPropertyExpression = dbSetClause.Property as DbPropertyExpression;

if (dbPropertyExpression != null)
{
var edmProperty = dbPropertyExpression.Property as EdmProperty;
if (edmProperty != null && edmProperty.MaxLength != null &&
_typesToTrim.Contains(edmProperty.TypeName))
{
var dbConstantExpression = dbSetClause.Value as DbConstantExpression;
if (dbConstantExpression != null && dbConstantExpression.Value != null)
{
var value = dbConstantExpression.Value.ToString();
if (!string.IsNullOrEmpty(value) &&
value.Length > (int) edmProperty.MaxLength)
{
return DbExpressionBuilder.SetClause(
DbExpressionBuilder.Property(
DbExpressionBuilder.Variable(
updateCommand.Target.VariableType,
updateCommand.Target.VariableName),
dbPropertyExpression.Property.Name),
EdmFunctions.Trim(
dbConstantExpression.Value.ToString()
.Substring(0, (int) edmProperty.MaxLength)));
}

}
}
}
}

return a;
}));

var newInsertCommand = new DbUpdateCommandTree(
updateCommand.MetadataWorkspace,
updateCommand.DataSpace,
updateCommand.Target,
updateCommand.Predicate,
new ReadOnlyCollection<DbModificationClause>(finalSetClauses),
updateCommand.Returning);

interceptionContext.Result = newInsertCommand;
}
}

 

 

HTTP Error 500.19 – Related configuration data for the page is invalid – Visual Studio

After you get latest from your repository you see the following error:

HTTP Error 500.19

The requested page cannot be accessed because the related configuration data for the page is invalid.

ConfigError.pngSometimes, the reason is because wrong config file path was commited to the repository and Visual Studio couldn’t pick up the right one on your machine just yet.

How to fix:

  1. Stop debugging
  2. Right click on your project that you try to run and click Unload Project
  3. Right click on your project and click Reload Project.

Unload.png

The error should be gone.

SaveChanges() always returns 0 – Entity Framework

Today, I experienced a bug in the application where SaveChanges() always returned 0 even though records were updated successfully in the database.

According to EF documentation, SaveChanges() should return a number of affected rows.

Solution:

In my case, I had child method that already called SaveChanges(). So, it means that only the first SaveChanges() will have correct return.

Here’s what happened in my case:

Context.Students.Add(Student);

Context.SaveChanges();  //Returns 1

Context.SaveChanges(); //Returns 0

If you experience similar issues just make sure that you only have Context.SaveChanges() once.

How to remove un-removable resources from Azure.

Recently, I’ve had an issue when I couldn’t remove Tinfoil Security 3rd party addon from my Azure subscription. When I tried to remove it from Extensions, it failed with “Operation couldn’t be completed” error.

So, in order to solve the error I tried to use PowerShell ISE and it worked!

This applies to all resources that do not have a delete button or have errors when you try to remove them.

You will need to perform the following steps:

RemoveResourcePowershell.png

 

Login-AzureRmAccount -SubscriptionId {your_id}

Select-AzureRmSubscription -SubscriptionId {your_id}

Remove-AzureRmResource -ResourceId {Your full resourcename. Can be copied from the portal}

After these steps are done you will need to refresh the portal and see this resource gone. You might want to give it a couple of minutes.

 

How to remove extensions (3rd party addons) from the app service in Azure

Azure provides a marketplace with different 3rd party extensions available: NewRelic, SendGrid, Tinfoil Security, etc. After installation, you usually get a free trial but then you will be charged monthly for these services. If you no longer wish to use these extensions you can remove them through the portal directly. (Or through the PowerShell)

Here’s how you can do this from the portal:

  1. Navigate to the app service:

Screenshot_1.png

2. Click extensions

3. Click delete.

Screenshot_2.png

And you are done. I couldn’t find this in the docs but it was an easy fix.

Azure Function: Publish Failed in Visual Studio 2017 (Solved)

So, I’ve been trying to publish an Azure Function app using the latest Visual Studio with built-in support for Azure functions (Visual Studio 2017 preview 3)

When I clicked deploy button I received bland “Publish Failed” without any details.

publish.png

failed.png

In my case the solution was simple: I had to close Fiddler. I used fiddler to test my Azure HTTP functions and didn’t close it. VS couldn’t connect to the server, thus the error.

After closing fiddler and making sure internet is working, everything works again.