SQL Server 问题之 排序规则(collation)冲突
一、写在前面 最近公司进行开发环境升级,数据库也准备了一个新的服务器,一切准备好后开始数据迁移,采取的方式为对现有Database(现有服务器Windows Server 2003 + SQL Server 2005)进行Back up,然后在新服务器(Windows Server 2008 R2 + SQL Server 20
一、写在前面
最近公司进行开发环境升级,数据库也准备了一个新的服务器,一切准备好后开始数据迁移,采取的方式为对现有Database(现有服务器Windows Server 2003 + SQL Server 2005)进行Back up,然后在新服务器(Windows Server 2008 R2 + SQL Server 2008 R2)上再Restore Databse,一切似乎挺顺利的。但是当从Web Server开始访问的时候出现错误Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
二、问题分析
上面出现的问题是由于排序规则冲突(collation)而引发,具体说来就是数据库所使用的语言的排序规则冲突,美国服务器,查看数据库所(老服务器)使用的排序规则如下所示
而新服务器上数据库的排序规则为SQL_Latin1_General_CP1_CI_AS(就不上图了),由此可以看出排序规则不同,此即问题所在。
那么为什么数据库的排序规则会不同呢?换句话说数据库的排序规则是怎么来的?原来这里的排序规则是在安装SQL Server数据库实例时配置/指定的,香港虚拟主机,默认的排序规则将基于操作系统的区域语言设置,我们新服务器OS的区域语言为US - English,而在安装SQL Server数据库实例时我们又没有配置排序规则(采取了默认的方式),香港服务器,所以才导致最终数据库的排序规则为SQL_Latin1_General_CP1_CI_AS
三、解决问题
下面列出解决此问题可能的方式以及说明
1. 在SQL语句中强行指定排序规则,类似的SQL语句如下所示 (COLLATE Chinese_PRC_CI_AI_WS)
SELECT A.ID, B.NO FROM TABLEA A INNER JOIN TABLEB B ON A.NAME=B.NAME COLLATE Chinese_PRC_CI_AI_WS

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article will introduce how to sort pictures according to shooting date in Windows 11/10, and also discuss what to do if Windows does not sort pictures by date. In Windows systems, organizing photos properly is crucial to making it easy to find image files. Users can manage folders containing photos based on different sorting methods such as date, size, and name. In addition, you can set ascending or descending order as needed to organize files more flexibly. How to Sort Photos by Date Taken in Windows 11/10 To sort photos by date taken in Windows, follow these steps: Open Pictures, Desktop, or any folder where you place photos In the Ribbon menu, click

Outlook offers many settings and features to help you manage your work more efficiently. One of them is the sorting option that allows you to categorize your emails according to your needs. In this tutorial, we will learn how to use Outlook's sorting feature to organize emails based on criteria such as sender, subject, date, category, or size. This will make it easier for you to process and find important information, making you more productive. Microsoft Outlook is a powerful application that makes it easy to centrally manage your email and calendar schedules. You can easily send, receive, and organize email, while built-in calendar functionality makes it easy to keep track of your upcoming events and appointments. How to be in Outloo

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

The NumPy library is one of the important libraries in Python for scientific computing and data analysis. However, sometimes we may need to uninstall the NumPy library, perhaps because we need to upgrade the version or resolve conflicts with other libraries. This article will introduce readers to how to correctly uninstall the NumPy library to avoid possible conflicts and errors, and demonstrate the operation process through specific code examples. Before we start uninstalling the NumPy library, we need to make sure that the pip tool is installed, because pip is the package management tool for Python.

In our work, we often use wps software. There are many ways to process data in wps software, and the functions are also very powerful. We often use functions to find averages, summaries, etc. It can be said that as long as The methods that can be used for statistical data have been prepared for everyone in the WPS software library. Below we will introduce the steps of how to sort the scores in WPS. After reading this, you can learn from the experience. 1. First open the table that needs to be ranked. As shown below. 2. Then enter the formula =rank(B2, B2: B5, 0), and be sure to enter 0. As shown below. 3. After entering the formula, press the F4 key on the computer keyboard. This step is to change the relative reference into an absolute reference.

To solve the problem that jQuery.val() cannot be used, specific code examples are required. For front-end developers, using jQuery is one of the common operations. Among them, using the .val() method to get or set the value of a form element is a very common operation. However, in some specific cases, the problem of not being able to use the .val() method may arise. This article will introduce some common situations and solutions, and provide specific code examples. Problem Description When using jQuery to develop front-end pages, sometimes you will encounter

Sorting methods in excel: 1. Single column sorting; 2. Multiple column sorting; 3. Custom sorting. Detailed introduction: 1. Single-column sorting is the most common sorting method. It sorts according to a selected column; 2. Multi-column sorting refers to sorting data in multiple columns, usually sorting according to a certain column first. On the basis of, sort by another column; 3. Custom sorting, allowing users to define the sort order according to their own needs.

WPS is a very complete office software, including text editing, data tables, PPT presentations, PDF formats, flow charts and other functions. Among them, the ones we use most are text, tables, and demonstrations, and they are also the ones we are most familiar with. In our study work, we sometimes use WPS tables to make some data statistics. For example, the school will count the scores of each student. If we have to manually sort the scores of so many students, it will be really a headache. In fact, we don’t have to worry, because our WPS table has a sorting function to solve this problem for us. Next, let’s learn how to sort WPS together. Method steps: Step 1: First we need to open the WPS table that needs to be sorted
