Search code examples
asp.netsql-servervb.netcollationcase-sensitive

Error 'Invalid object name INFORMATION_SCHEMA.TABLES'

I'm getting an error when running the following code thru an ASP/ C# .NET page:

 SELECT 1 FROM [database].INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='example_table'

The code runs perfectly when I run it in the sql management studio query window. It's also only broken for 1 specific server - when I run on a different server with a different connection string, it works fine.

I've scoured the internet, and all I can seem to find as an answer is that there are issues with case sensitivity, but I'm using all uppercase, and the table in question is using SQL_Latin1_General_CP1_CI_AS collation anyway.

Based on all this, I'm pretty sure it's some kind of permissions issue, but I'm not really sure where to look.

The error I'm getting:

Invalid object name 'database.INFORMATION_SCHEMA.TABLES'.

The connection string:

<add name="connectionName" connectionString="Data Source=servername; Initial Catalog=master; User ID=user; Password=pw" providerName="System.Data.SqlClient"/>

UPDATE:

Some more information: I had adapted this code from some older VB stuff that basically does the same thing. The VB code runs the same exact query on the same servers and databases, but it works.

The VB code:

Dim connStr As String = ConfigurationManager.ConnectionStrings("connectionName").ConnectionString
Dim objConnection As SqlConnection = New SqlConnection(connStr)
Dim objCommand As SqlCommand = New SqlCommand("SELECT 1 FROM [database].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='example_table'", objConnection)
objCommand.CommandType = CommandType.Text

objConnection.Open()

The C# and VB code both use the exact same connection string from web.config, so I suppose that leads me to doubt that it would be a permissions issue. Still no clue what the problem is though.

Solution

  • After a bunch more debugging, I was able to determine that the code was actually running on the wrong server. So, not really any way to find the bug based on what I'd posted, but I think Tony was on the right track based on the back-and-forth.