Invalid object name information_schema.routines

Community Answers
1

Hi, when i do generatechangelog on mssql, i get

Caused by: liquibase.exception.LiquibaseException: liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: Error executing SQL select ROUTINE_CATALOG AS OBJECT_CATALOG, ROUTINE_SCHEMA AS OBJECT_SCHEMA, ROUTINE_NAME AS OBJECT_NAME, sql_modules.definition AS OBJECT_BODY, ‘VALID’ as STATUS, uses_quoted_identifier FROM [information_schema].[routines] LEFT OUTER JOIN sys.sql_modules on object_id=object_id(‘dbo.SP_Get_SystemList’) where routine_type = ‘PROCEDURE’ AND ROUTINE_SCHEMA=‘dbo’: Invalid object name ‘information_schema.routines’.

error. I couldn’t find the solution, how can I solve it?

2

Hi @emre.karahan . Which version of Liquibase are you using? What version of MSSQL Server are you using? Typically we see this kind of error when you are using a different version or if the user running the command does not have access to run Liquibase against the database to gather information from a generateChangeLog command.
And thanks for joining our community.

4

Hi @MikeOlivas,
I use.
SqlServer : Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 < X64> (Build 14393: ) (Hypervisor)

Liquibase: 4.8.0

and my parameters are as below.

changeLogFile=dbchangelog.xml
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
# classpath=C:\Program Files\Microsoft JDBC DRIVER 10.2 for SQL Server\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre8.jar
url=jdbc:sqlserver://**********:1433;databaseName=xxxx
username=xxxxxxx
password=xxxxxx
liquibaseProLicenseKey= ***

Thank you.

5

Hi @emre.karahan I don’t see anything wrong with the info you have provided so far. Is this a permissions issue based on the user connecting? Can you connect with a different user that has the correct privileges and see if the same error occurs?

6

Hi @MikeOlivas, thank you for your answer.
actually I also tried with sa user and got the same result.

7

@emre.karahan Another potential root cause of this might be the DB collation. Can you check this (the DB collation) and add it here (mostly if it is case sensitive or not). Thanks!

8

Thanks. @EduardUta.
My database collation is Turkish_CI_AI.
In fact my problem is case sensitive problem.

The table name in the database is INFORMATION_SCHEMA.ROUTINES, but liquibase is querying the information_schema.routines table. and it throws an invalid object error.

Thanks.

1 Like
9

Thanks for the feedback @emre.karahan This really helps others that run into the same issue. The default for SQL Server is case insensitive.

2 Likes