Efcore: 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 Mailhttps://go.microsoft.com/fwlink/?LinkId=550986 for Windows 10

From: Erik Ejlskov [e-mail]
Sent: 08 September 2020 11:24
To: dotnet/[e-mail]
Cc: [e-mail]; [e-mail]
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 GitHubhttps://github.com/dotnet/efcore/issues/22424#issuecomment-688634816, or unsubscribehttps://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 Mailhttps://go.microsoft.com/fwlink/?LinkId=550986 for Windows 10

From: Shay [e-mail]
Sent: 08 September 2020 03:42
To: dotnet/[e-mail]
Cc: [e-mail]; [e-mail]
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 docshttps://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 GitHubhttps://github.com/dotnet/efcore/issues/22424#issuecomment-688525108, or unsubscribehttps://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 Androidhttps://aka.ms/ghei36

---

From: Shay Rojansky [e-mail]
Sent: Tuesday, September 8, 2020 8:37:22 PM
To: dotnet/efcore [e-mail]
Cc: himanshukodwani [e-mail]; Author [e-mail]
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 pagehttps://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 GitHubhttps://github.com/dotnet/efcore/issues/22424#issuecomment-688941191, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJ7O4IUB427LSDURZBYKFI3SEZCCVANCNFSM4Q4X2S7A.