Column, parameter, or variable #1: Cannot specify a column width on data type text.

[himanshukodwani]

I get this error when running Update-Migration.

///Error message Start

Applying migration '20200906170827_V1.0.0.1'.
Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [AccountGroups_DBSet] (
[GroupName] TEXT(50) NOT NULL,
[GroupParent] TEXT(50) NULL,
[BalanceSheetSide] TEXT(255) NULL,
[Description] TEXT(255) NULL,
[IsHidden] INTEGER NOT NULL,
[GroupEnd] TEXT(50) NULL,
[IsUnderBudgetControl] INTEGER NOT NULL,
[BudgetAmount] TEXT(64) NULL,
[IsUnderLocationControl] INTEGER NOT NULL,
[Location] TEXT(50) NULL,
[TxnID] TEXT(36) NOT NULL,
[SearchKey] TEXT(150) NULL,
[RecordVersion] INTEGER NOT NULL,
[CreatedBy] TEXT(50) NOT NULL,
[LastUpdatedBy] TEXT(50) NULL,
[CreatedOn] TEXT(48) NOT NULL,
[LastUpdatedOn] TEXT(48) NULL,
[Company] TEXT(100) NULL,
CONSTRAINT [PK_AccountGroups_DBSet] PRIMARY KEY ([GroupName])
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Column, parameter, or variable #1: Cannot specify a column width on data type text.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in ......More stack trace omitted

Error Number:2716,State:1,Class:16
Column, parameter, or variable #1: Cannot specify a column width on data type text.

///Error message End

But when I generate SQL Script using dbContext.Database.GenerateCreateScript(), the generated script is different and also works when creating database schema manually. Look at what SQL script saved in a file from the same migration looks like :

CREATE TABLE [AccountGroups_DBSet] (
[GroupName] nvarchar(50) NOT NULL,
[GroupParent] nvarchar(50) NULL,
[BalanceSheetSide] nvarchar(255) NULL,
[Description] nvarchar(255) NULL,
[IsHidden] bit NOT NULL,
[GroupEnd] nvarchar(50) NULL,
[IsUnderBudgetControl] bit NOT NULL,
[BudgetAmount] decimal(18,2) NULL,
[IsUnderLocationControl] bit NOT NULL,
[Location] nvarchar(50) NULL,
[TxnID] nvarchar(36) NOT NULL,
[SearchKey] nvarchar(150) NULL,
[RecordVersion] bigint NOT NULL,
[CreatedBy] nvarchar(50) NOT NULL,
[LastUpdatedBy] nvarchar(50) NULL,
[CreatedOn] datetime2 NOT NULL,
[LastUpdatedOn] datetime2 NULL,
[Company] nvarchar(100) NULL,
CONSTRAINT [PK_AccountGroups_DBSet] PRIMARY KEY ([GroupName])
);
GO

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.0)
Operating system:
IDE: (e.g. Visual Studio 2019 16.3)

[roji]

The TEXT type has been deprecated in SQL Server and will be removed in a future version (see the docs), it also does not accept a size. AFAIK EF Core itself never maps to TEXT by default, are you explicitly configuring your column type?

Consider using nvarchar or nchar instead.

[ErikEJ]

Looks like you are mixing SQLite and SQL Server migrations ??

[himanshukodwani]

It’s true, I am targeting multiple database systems. I want the end user of my application to choose the database system, suitable to him. How can I create migrations targeting different database systems and apply them at run time? Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: Erik Ejlskov Jensen<mailto:notifications@github.com> Sent: 08 September 2020 11:24 To: dotnet/efcore<mailto:efcore@noreply.github.com> Cc: himanshukodwani<mailto:himanshukodwani@hotmail.com>; Author<mailto:author@noreply.github.com> Subject: Re: [dotnet/efcore] Column, parameter, or variable #1: Cannot specify a column width on data type text. (#22424) Looks like you are mixing SQLite and SQL Server migrations ?? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#22424 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AJ7O4ITJKX32XQLI3BQ6KJDSEXBIDANCNFSM4Q4X2S7A>.

[himanshukodwani]

No, I am not explicitly configuring my column, but I am targeting different database systems from the same application. Thanks Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: Shay Rojansky<mailto:notifications@github.com> Sent: 08 September 2020 03:42 To: dotnet/efcore<mailto:efcore@noreply.github.com> Cc: himanshukodwani<mailto:himanshukodwani@hotmail.com>; Author<mailto:author@noreply.github.com> Subject: Re: [dotnet/efcore] Column, parameter, or variable #1: Cannot specify a column width on data type text. (#22424) The TEXT type has been deprecated in SQL Server and will be removed in a future version (see the docs<https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15>), it also does not accept a size. AFAIK EF Core itself never maps to TEXT by default, are you explicitly configuring your column type? Consider using nvarchar or nchar instead. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#22424 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AJ7O4IQD6BZWG76PR2BW6ITSEVLFDANCNFSM4Q4X2S7A>.

[roji]

You probably want to take a look at this doc page, about managing migrations in a multi-database scenario.

[himanshukodwani]

Thanks a lot Get Outlook for Android<https://aka.ms/ghei36>

…

________________________________ From: Shay Rojansky <notifications@github.com> Sent: Tuesday, September 8, 2020 8:37:22 PM To: dotnet/efcore <efcore@noreply.github.com> Cc: himanshukodwani <himanshukodwani@hotmail.com>; Author <author@noreply.github.com> Subject: Re: [dotnet/efcore] Column, parameter, or variable #1: Cannot specify a column width on data type text. (#22424) You probably want to take a look at this doc page<https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/providers?tabs=dotnet-core-cli>, about managing migrations in a multi-database scenario. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#22424 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AJ7O4IUB427LSDURZBYKFI3SEZCCVANCNFSM4Q4X2S7A>.