obsolete ntext on [grate].[ScriptsRun].[text_of_script]

[OzBob]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like The Microsoft docs docs.microsoft.com/ntext are reporting the use ntext should be avoided and will be removed in future versions of SQL Server.

tSQL SQL Server:

ALTER TABLE [grate].[ScriptsRun] DROP COLUMN [text_of_script] 
ALTER TABLE [grate].[ScriptsRun] ADD [text_of_script] nvarchar(max)

Note: i'd fork and PR but i can't get the src to build.

[erikbra]

Hi, @OzBob, thanks for your input! I think @RachelAmbler already has this covered with #246 . And, wouldn't the drop/alter solution here kill all existing data? I'd prefer keeping existing data, and changing the datatype of the column instead.

A quick question about having problems building, where did you get stuck? Could we improve the documentation on building grate yourself locally, maybe?

[RachelAmbler]

Hi, @OzBob, thanks for your input! I think @RachelAmbler already has this covered with #246 . And, wouldn't the drop/alter solution here kill all existing data? I'd prefer keeping existing data, and changing the datatype of the column instead.

A quick question about having problems building, where did you get stuck? Could we improve the documentation on building grate yourself locally, maybe?

You are correct - that was indeed one of the changes I put in.

[OzBob]

Fair point on the loss-of-data, here's a loss-less method:

ALTER TABLE [grate].[ScriptsRun] ADD [text_of_script2] nvarchar(max);
UPDATE [grate].[ScriptsRun] SET [text_of_script2]=convert(nvarchar(max),[text_of_script])
ALTER TABLE [grate].[ScriptsRun] DROP COLUMN [text_of_script] 
ALTER TABLE [grate].[ScriptsRun] ADD [text_of_script] nvarchar(max)
UPDATE [grate].[ScriptsRun] SET [text_of_script]=[text_of_script2]
ALTER TABLE [grate].[ScriptsRun] DROP COLUMN [text_of_script2] 

SSMS doesn't like executing that all at once, but run one line at a time works fine.

How do you manage migrations?

[wokket]

I understand the desire to move to a non-deprecated datatype, and would support us moving to nvarchar for freshly created grate tables (as originally raised by @RachelAmbler).

I will point out that sql statements that aren't semicolon terminated have also been deprecated for at least 14 years, and I don't see everyone freaking out about that in day to day life 🤣

Maybe we don't need to worry about migrating existing tables yet until we get a clear statement from MS that the datatype will cease to function in version x?

[RachelAmbler]

Fair point on the loss-of-data, here's a loss-less method:

ALTER TABLE [grate].[ScriptsRun] ADD [text_of_script2] nvarchar(max);
UPDATE [grate].[ScriptsRun] SET [text_of_script2]=convert(nvarchar(max),[text_of_script])
ALTER TABLE [grate].[ScriptsRun] DROP COLUMN [text_of_script] 
ALTER TABLE [grate].[ScriptsRun] ADD [text_of_script] nvarchar(max)
UPDATE [grate].[ScriptsRun] SET [text_of_script]=[text_of_script2]
ALTER TABLE [grate].[ScriptsRun] DROP COLUMN [text_of_script2] 

SSMS doesn't like executing that all at once, but run one line at a time works fine.

How do you manage migrations?

The issue as I see it here is adding extra functionality to grate just for SQL Server. grate is by its very design a homogeneous application. I'd be worried by adding such functionality in that it 'skews' the app into treating some RDBMS's as 'higher class' citizens than others.

Whilst I agree that this can be seen to be a slightly annoying issue to deal with, what I personally feel we should do is deal with situations like these outside of grate - perhaps by documentation and associated scripts. Especially as these issues are 'one and done' for each repo.

[erikbra]

Solved in #501 - much easier to do stuff like this after #482 :)