(…)
EXEC sp_rename ‘[journalentries].[is_temporarily_reopenedtmpfld]’, ‘is_temporarily_reopened’, ‘COLUMN’;
(…)
GO
CREATE INDEX [__genindex16404729] ON [journalentries] ([is_temporarily_reopened]) WHERE [is_temporarily_reopened] = 1
This used to work before, but now it keeps giving me errors about the word GO or the character before it. If I don’t use GO it will say the column does not exist, because it does not take the rename in account for checking the whole statement.
I had the same thing with a script where an index was created using a WHERE statement on a field that was added in the same script.
I ended up creating the index in the next updatescript, but thats only useable if you have another update following soon and are sure this one will not go to a production environement. Not sure if that helps you in this case.
Hi Helena, unfortunately I had the same as you and Redmer.
I solved it with the ‘pre-conversion’ method:
a) Manually add the column + index to the database before the update
b) Modify the update-script to skip creating + renaming the column
For large volumes such as often the case with journalentries, having this pre-conversion procedure often saves a lot of downtime during conversions.
In which context does this happen (deployment parsing or checking the script? the application trying to update the database?) and what is the error that you get?
When this worked in the past, was that with or without the added GO statement in between?
I was comparing apples and oranges. It worked in the past when testing the script locally. It doesn’t work in total, because the GO scope delimiter is not allowed to use in stored procedures.
I could of course use EXEC('CREATE INDEX [__genindex16404729] ON [journalentries] ([is_temporarily_reopened]) WHERE [is_temporarily_reopened] = 1 ')
to make the script run without problems.
The issue/error you get is that the prechecker wants to check conditional indexes on the right column names, but doesnt take the rename into account. So the prechecker is happy when you make the field called tmpfld, but of course runtime this would give an error. Anyting in the EXEC() will not be able to be prechecked, because it is just text to the prechecker and thus it will work at runtime, but I’m not sure that is a route we’d want to go. Maybe the creation of indexes should be in it’s own procedure to not have these scoping issues for the prechecker.
Interesting… this seems to be a limitation in SQL Server.
This works:
CREATE TABLE [test] ([field_a] INT)
GO
ALTER TABLE [test] ADD [field_b_tmpfld] INT
GO
EXEC sp_rename ‘[test].[field_b_tmpfld]’, ‘field_b’, ‘COLUMN’;
CREATE INDEX [__genindex12345] ON [test] ([field_b])
GO
DROP TABLE [test]
This doesn’t:
CREATE TABLE [test] ([field_a] INT)
GO
ALTER TABLE [test] ADD [field_b_tmpfld] INT
GO
EXEC sp_rename ‘[test].[field_b_tmpfld]’, ‘field_b’, ‘COLUMN’;
CREATE INDEX [__genindex12345] ON [test] ([field_b]) WHERE [field_b] IS NOT NULL
GO
DROP TABLE [test]
So it accepts the renamed field as an indexed column, but not as a filter in that same index.
In most cases you could probably work around this by removing the ‘tmpfld’ from the ALTER TABLE statement and removing the EXEC sp_rename.
I like the preconversion method for the big applications.
If it is a smaller database or update, I prefer the EXEC(’ < conditional index > ') method, which adds the least extra characters / things to your updatescript.