Fix Error : Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1250_CI_AS” in the equal to operation.

Reason

When I was running a quarry with a JOIN, I got an error which says “Cannot resolve the collation conflict

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1250_CI_AS” in the equal to operation.

0-error

Image 1 : Error

Then I looked what is the reason for the error and I got the reason is Collations are not matching in tables that I have joined.

So here I’ll show you that How was the error occurred and I I did solved the error…

First I’ll create two tables “Customer” and “Person”.

1-tables

Image 2 : Sample Tables Customer/Person

Now I’m going to JOIN these two tables by CustomerName of customer table and CustomerName of Person table.

Before that, I’ll add different collations to the name fields in both tables.

First will change the collation of “Person” table..

Go the the designer of the table and select “Collation” menu. Then we can see a dialague box to select a Collation.

2-cp1

Image 3 : How to change the collation of a column

3-select-collation

Image 4 : Select SQL_Lathin1_General_CP1_CI_AI for CustomerName in Person Table

Then lets change the collation of the CustomerName in Customer table..

4-cp-1250

Image 5 : Select SQL_Lathin1_General_CP1250_CI_AS for CustomerName in Customer table

Yes. Now we have done the changes to both tables. So Now we are ready to run the quarry with the JOIN…

5-error

Image 6 : Error happen when run the quarry

Oh ! It gives an error …

What is the difference.. ?

SQL_Lathin1_General is common for both collation types.

CI means Case Insensitive. (we can use CS to make it case sensitive)

AI means Accent Insensitive and AS means Accent Sensitive

Then the next difference is CP1 and CP1250..

What is CP1 and CP1250 ?

Both of CP1 and CP1250 are code page architectures..

According to Microsoft technical page , CP1 is code page 1252 and CP1250 is code page 1250 which are…Rules to define how the bit pattern of the characters are sorted and compared. 

Learn more about code Pages @technet.microsoft.com

So Lets Solve the error..

What we have to do is add Collation as “DATABASE_DEFAULT” for both table in the JOIN. Below image describes how to do it..


SELECT * FROM [dbo].[Customer] c
INNER JOIN [dbo].[Person] p
ON c.[Name] COLLATE DATABASE_DEFAULT =p.[CustomerName] COLLATE DATABASE_DEFAULT 

6-result

Image 7 : Solve the error using DATABASE_DEFAULT

Now the error is not there and the quarry gives the result successfully.

Thanks !


To Test the result, Create a Database “TestDB” and please use below script to create a the tables and get sample data that I have added.


USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[CountryID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Customer] ON

INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (1, N'Buddhima', 24, 1)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (8, N'Chrystophe', 45, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (9, N'Patel', 30, 2)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (10, N'Nikolas', 20, 3)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (11, N'Ann', 35, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (12, N'Mike', 50, 4)
SET IDENTITY_INSERT [dbo].[Customer] OFF
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (1, N'Buddhima')
INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (2, N'Ann')
INSERT [dbo].[Person] ([PersonId], [CustomerName]) VALUES (3, N'Mike')
SET IDENTITY_INSERT [dbo].[Person] OFF

 

Fix Error : “Could not find a part of the path” While Publishing ASP.NET Core MVC Application To the Target Platform of Windows 7 x64/x86

Reason :

I’m working on a ASP.Net Core MVC application and I Published one of my ASP.Net Core MVC app into a Server which is running Windows Server 2008 R2.

But when I released the application live, it didn’t work on the server but it works fine on my local machine. My Local machine has Windows 10.

Then I looked for a solution and I got the reason. The reason Is I have published the app for the target platform of Windows 10. But the Server that the site has hosted has Windows Server 2008 R2 (Windows 7 Configurations)

Then what I had to do I was : Publish the app for the target platform of windows 7 (Windows Server 2008 R2).

Then I added target platforms to to the project.json file in my .Net Core MVC app. I published it again.

But I wasn’t published and it gave me an error..

Could not find a part of the path ‘F:\Projects\TestWebCore\src\TestWebCore\bin\Release\netcoreapp1.0\win7-x64\TestWebCore.dll’.

Then I looked for a solution and I found it is an error in tooling.

http://stackoverflow.com/questions/39023224/error-could-not-find-a-part-of-the-path-while-publishing-net-core-application

Solution :

The Solution is we have to publish the .Net Core applicaion using command prompt. 

I did it and my app was published successfully.

 

Here I’ll show you how the error occurred and How I did fix it.

First I’ll create a sample .Net Core MVC app with a .Net Core Portable Class Library(PCL).

You can See by Clicking..

How to Create a ASP.Net Core MVC app

Here is the project I have created..

The solution consists of one ASP.Net Core MVC project and a .Net Core PCL project.

1-project-flow

Image 1 : Solution Structure

Then I have changed the Index.cshtml file and added a simple html content to show you ..

2-change-index-page

Image 2 : Sample HTML content in Index.html file

Lets run the app to see how it looks like.

The app will run with the localhost and you could see the content I have added to the html body. I have used a Bootstrap template to the project. So you can see it is responsive.

3-index-page

Image 3 : Run The app in localhost

So Its running well and and the next step is Publish the app.

Publish The app

Then you try to publish an app for the first time, you will see a window like below..

5-publish-without-target-framework

Image 4 : Default Publish Window

Here you can see only one menu which is “Profile”. You can’t publish the app without selecting a profile. So we have to create a publish profile.

for that click on “Custom” button and you will see a dialague box to enter the publish profile name.

6-create-a-publish-profile

Image 5 : Create a Publish Profile

I’ll create it as “MyPublishProfile”. And click “OK”. Then the profile will be created and you will see new menus in the left hand side now.

7-selet-profile-menu

Image 6 : New menus will appear when Publish Profile created.

Then go to connection menu and select a folder to publish the web application. And Select the publish method as “File system”.

9-select-a-publish-location

Image 7 : Connection menu(Select Target Publish Folder)

Then go to the settings menu and you can select the target frameworks to publish the app.

8-target-runtme-any-settings

Image 8 : Default Settings Menu.

But according to above image, you can’t see a Target Runtime. 

By default it has set to “Any“. 

So I’m adding some target runtimes to project to show how the error is happening.

We have to add those settings to the project.json file in the web application project.

To see How to add Target Runtimes to project.json  Click Here…

If you have already added target  Runtimes, Now we are ready to publish the app.

So Right Click and Select Publish in the context menu and the publish window will open.

Publish to the Target Runtime of Windows 10

Then Click “Settings” and Select “Win10-x64” from the drop down list.

5-select-target-platform-10

Image 9 : Select Windows 10 as Target Runtime

Then Click Preview menu and verify is the details correct before publish your app.

6-conform-details-before-publish

Image 10 : Verify Publish information before publish the app

Then Click “Publish” and the application will publish to the selected folder. You will see publish details output window.

7-success-win-10

Image 11 : App has publish successfully for the Target Runtime of  Windows 10 x64

Then check the selected publish folder and you will see compiled files has published to the folder.

The “TestWebCore” app has publish to the target runtime of “Windows 10 64bit” successfully.

How The Error Occurred…

Publish to the Target Runtime of Windows 7

This is same as we published the app for Windows 10. But the only difference is we have to change the target runtime to “Win7-x64

11-publishing-to-win-764

Image 12 : Select Windows 7 x64 as Target Runtime

Then Click “Publish”.

When I did it, I got below error.

Could not find a part of the path ‘F:\Projects\TestWebCore\src\TestWebCore\bin\Release\netcoreapp1.0\win7-x64\TestWebCore.dll’.

12-error

Image 13 : Error Publishing app to Windows 7

How to Fix the Error

To publish without an error, what I did is ..

Run the Publish command in Command Prompt.

So Here is the way I did ..

13-publish-command

Image 14 : Run Publish command in Command Line

When you press “Enter” when type the publish command, you will see the application publish successfully.

14-published-to-win7-64

Image 15 : Successfully Published to Windows 7 x64

You can see the published files in the publish folder.

This Error is not happening

References…

To Learn More about Target Runtimes/Frameworks Click…

Publish Commands for .Net Core in Command Line Click…

How to Publish Self Contained Application…

Thanks !

How to add Target Runtimes to project.json in ASP.Net Core Project

To See how to create a .Net core app, Click here …

First Open the project and you can see there is a JSON file called “project.json”

3-app-created

Then click it and you will see the JSON content in that file.

The default JSON content like below.


{
"dependencies": {
"Microsoft.NETCore.App": {
"version": "1.0.1",
"type": "platform"
},
"Microsoft.AspNetCore.Diagnostics": "1.0.0",
"Microsoft.AspNetCore.Mvc": "1.0.1",
"Microsoft.AspNetCore.Razor.Tools": {
"version": "1.0.0-preview2-final",
"type": "build"
},
"Microsoft.AspNetCore.Routing": "1.0.1",
"Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
"Microsoft.AspNetCore.Server.Kestrel": "1.0.1",
"Microsoft.AspNetCore.StaticFiles": "1.0.0",
"Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
"Microsoft.Extensions.Configuration.Json": "1.0.0",
"Microsoft.Extensions.Logging": "1.0.0",
"Microsoft.Extensions.Logging.Console": "1.0.0",
"Microsoft.Extensions.Logging.Debug": "1.0.0",
"Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
"Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0"
},

"tools": {
"BundlerMinifier.Core": "2.0.238",
"Microsoft.AspNetCore.Razor.Tools": "1.0.0-preview2-final",
"Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
},

"frameworks": {
"netcoreapp1.0": {
"imports": [
"dotnet5.6",
"portable-net45+win8"
]
}
},

"buildOptions": {
"emitEntryPoint": true,
"preserveCompilationContext": true
},

"runtimeOptions": {
"configProperties": {
"System.GC.Server": true
}
},

"publishOptions": {
"include": [
"wwwroot",
"**/*.cshtml",
"appsettings.json",
"web.config"
]
},

"scripts": {
"prepublish": [ "bower install", "dotnet bundle" ],
"postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
}
}

When we collapse the JSON content, we can see what are the main properties of the file..

12-default-project-json-file

So according to above image, there is no property to declare Target Runtimes.

So We have to add them manually according to our requirement.  I’m using windows based machines, so I’ll add windows operating systems as target runtimes.

13-add-runtimes-to-project-json


"runtimes": {
"win10-x64": {},
"win81-x64": {},
"win8-x64": {},
"win7-x64": {},
"win7-x86": {}
}

When you add this code, you could see the NuGet package references are restoring automatically in the right hand side .

14-restoring-packages

After It has completed, you are ready to publish the app.

Then Right click on the project and click “Publish”.

You will see the Publish window. And Go to the settings menu and you will see those runtimes has added to the target runtimes drop down list.

15-target-runtimes-in-publish

Important.

If you want to delete old published files when you are publishing a new version, Check the

“Delete all existing files prior to publish” check box..

16-delete-existing-files

Thanks !

How To Create .Net Core MVC Project

Open The Visual Studio 2015

Then File->New Project

->Select .Net Core

-> Select ASP.Net Core Web Application

-> And Add a name to the project (I have added “MyCoreApp2”)

1-create-project

Then Click “OK” Button And It will go to another window to select the project template.

Select “Web Application” and click OK.

2-select-web-application

Then Your Project will be created.

3-app-created

You will see the references are restoring and after it completed, Press F5. Then your new project will run with the default template.

4-default-app-runs

 

 

 

Fix Error : Incorrect syntax near ‘*=’. In SQL Server (SQL JOINS)

Reason :

I have Migrated  one of my Database from SQL Server 2000 to SQL Server 2016.  And when I was running my applications after the migration of databases, I got some exceptions and then I looked what are the reasons for them..

The Error message was : 

Incorrect syntax near ‘*=’.

I have created a Sample Database with two tables to show you how it occurred and how to fix it.
Here I have wrote a sample quarry to show you how the error has occurred.


SELECT
ID, Name, Age,
CountryName, CountryCapital
FROM Customer cu,Country c
WHERE
cu.CountryID *= c.CountryID

This quarry works in SQL Server 2000 but, it gives and error in SQL Server 2016.

1-qry-sql-2000

Image 1 : LEFT JOIN in SQL SERVER 2000 using  “*=”

2-sql-2016-qry-error

Image 2 : LEFT JOIN in SQL SERVER 2016 using “*=”

Then I looked what is the reason and I got the reason for the error.

The reason is SQL Server 2016 does not support to write quarries with JOINS using ” =*” and “*=” .

But in SQL Server 2000, we can write quarry  with JOINS using  ” =*” and “*=” .

According to above quarry, there is a Left Join.

You can lean more about SQL JOINS using below reference..

w3schools.com/sql/sql_join

Solution :

  1. A one solution is we can change the compatibility level of the database in SQL Server 2016 using database properties.
  2. But the Solution I used is use  “LEFT JOIN” instead of  “*=” .

Then the error has fixed..

But for more information I’ll show you how we can write SQL JOINS in SQL SERVER 2000 and LATER VERSIONS of SQL SERVER 2005 to SQL SERVER 2016.

LEFT JOIN/LEFT OUTER JOIN

LEFT JOIN we can also call as LEFT OUTER JOIN. But both of them gives us the same result. These are use before the WHERE statement.

In SQL Server 2000, we can write LEFT JOIN as “*=”.  But this is using after the WHERE statement.

Then result shows us all records in the LEFT hand side tables and matching values in RIGHT hand side table.

3-using-left-outer

Image 3 : Difference ways to use LEFT JOIN in SQL SERVER 2000

4-using-left-outer

Image 4 : LEFT JOIN in SQL SERVER 2016

RIGHT JOIN/RIGHT OUTER JOIN

RIGHT JOIN  we can also call as the RIGHT OUTER JOIN. But both of them gives us the same result.

In SQL Server 2000, we can write RIGHT JOIN as “=*”.

Then result shows us all records in the RIGHT hand side tables and matching values in LEFT hand side table.

4-ridgt-outer-2000

Image 5 : Different ways to use  RIGHT JOIN in SQL SERVER 2000

5-right-outer-2016

Image 6 : RIGHT JOIN in SQL SERVER 2016

INNER JOIN

INNER JOIN shows only the matching values in both tables. This join we can use in both SQL SERVER version in the same way.

6-inner-join-2000

Image 7 : INNER JOIN in SQL SERVER 2000

7-inner-join-2016

Image 8 : INNER JOIN in SQL SERVER 2016

FULL JOIN/FULL OUTER JOIN

Using FULL JOIN it shows all matching and outmatching  values in both tables. It shows outmatching values as NULL.

FULL JOIN we can use in both SQL SERVERS in the same way.

8-full-join-2000

Image 9 : FULL JOIN in SQL SERVER 2000-2016

 

Thank You !

IF YOU WANT TO TEST THIS, PLEASE CREATE A DATABASE “TestDB”

OR run below quarry.


CREATE DATABASE [TestDB]

and then run this quarry. There and sample data to text your quarries.


USE [TestDB]
GO
/****** Object: Table [dbo].[Country] Script Date: 1/2/2017 6:18:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
[CountryID] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](50) NULL,
[CountryCapital] [varchar](50) NULL
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Customer] Script Date: 1/2/2017 6:18:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[CountryID] [int] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Country] ON

INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (1, N'Sri Lanka', N'Sri Jayawardhanapura')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (2, N'India', N'New Dhilli')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (3, N'Beljium', N'Bruselles')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (4, N'United Kingdom', N'London')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (5, N'United States of America', N'Washington, D.C.')
INSERT [dbo].[Country] ([CountryID], [CountryName], [CountryCapital]) VALUES (6, N'Japan', N'Tokyo')
SET IDENTITY_INSERT [dbo].[Country] OFF
SET IDENTITY_INSERT [dbo].[Customer] ON

INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (1, N'Buddhima', 24, 1)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (8, N'Chrystophe', 45, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (9, N'Patel', 30, 2)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (10, N'Nikolas', 20, 3)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (11, N'Ann', 35, NULL)
INSERT [dbo].[Customer] ([ID], [Name], [Age], [CountryID]) VALUES (12, N'Mike', 50, 4)
SET IDENTITY_INSERT [dbo].[Customer] OFF