Collation error

SQL Server Administration
13

@Sameer Thanks for capturing and posting that query. The problem is that the [collation] column of [syscolumns] has a collation of Latin1_General_CI_AI. It should not have this collation. It should have the same collation as the database default collation, but for some reason it's coming back as Latin1_General_CI_AI. This is not even being set in the syscolumns compatibility view, so I suspect it's a bug in the definition of the result set of that compatibility view. I get the same result on my system using SQL Server 2012, 2017, and 2019.

Unfortunately, this is not something that you can change or fix :crying_cat_face:

The real problem is this 3rd party software. What software is it? How old is it? This query is really bad:

  • It doesn't follow best-practices in several areas
  • It is clearly written for SQL Server 2000
  • It can't handle the CHAR datatype
  • It can't handle the VARBINARY datatype
  • It can't handle the MAX types ( VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) )
  • It cannot properly handle the TIME(n), DATETIME2(n), and DATETIMEOFFSET(n) datatypes.

You will have to find some other way to import the data. I think the Import Wizard can import from Excel.

1 Like