Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Script generation fails on case sensitive coalitions with SQL Server #347

Closed
danielmarbach opened this issue Mar 28, 2019 · 0 comments

Comments

@danielmarbach
Copy link
Member

commented Mar 28, 2019

Who's affected

All customers using case-sensitive coalitions

Symptoms

When using case sensitive coalitions the script execution fails with the following exception

Invalid object name ‘information_schema.columns’

Workaround

Generate the scripts once, disable script generation and then modify the mentioned queries against information_schema.columns by using the proper casing for both the table name and the columns

Analysis

Case sensitive coalitions don't know what information_schema.columns is because the database table is actually named INFORMATION_SCHEMA.COLUMNS.

The script generation needs to be adjusted

For case insensitive coalitions information_schema.columns and INFORMATION_SCHEMA.COLUMNS are equivalent and thus the capital cased one should be used as a default.

select * from information_schema.columns

image

vs

image

or the proof that they return the same result

select * from information_schema.columns
except
select * from INFORMATION_SCHEMA.COLUMNS

image

Given that the column names are capital cased as well it makes sense to also capital case the column names to make it properly work

Taking a quick glance it looks like other tables like sys.indexes are not affected. It seems like it really only affects information_schema.columns

As an example

from information_schema.columns
    where
        table_name = @tableNameWithoutSchema and
        table_schema = @schema and
        column_name like 'Correlation_%'{builder}

should be changed to

from INFORMATION_SCHEMA.COLUMNS
    where
        TABLE_NAME = @tableNameWithoutSchema and
        TABLE_SCHEMA = @schema and
        COLUMN_NAME like 'Correlation_%'{builder}

@dvdstelt dvdstelt added Bug and removed Bug labels May 3, 2019

@WilliamBZA WilliamBZA assigned WilliamBZA and unassigned WilliamBZA Jul 9, 2019

@WilliamBZA WilliamBZA added this to the 4.6.0 milestone Jul 26, 2019

@WilliamBZA WilliamBZA added the Bug label Jul 26, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.