SQL Server Error: "Cannot use empty object or column names"

Question

I get the following error:

Cannot use empty object or column names. Use a single space if necessary.
Msg 1038, Level 15, State 3, Line 1

and the query command looks like:

SELECT TOP 100 PERCENT
  [].[cms_page].[pa_id], [].[cms_page].[pa_key], 
  [].[cms_page].[pa_title], [].[cms_page].[pa_keywords], 
  [].[cms_page].[pa_description], [].[cms_page].[pa_header], 
  [].[cms_page].[pa_created], [].[cms_page].[pa_modified], 
  [].[cms_page].[pa_language] FROM [cms_page] 
WHERE 
  [cms_page].[pa_key] = @pa_key0 
ORDER BY 
  [pa_id] ASC;

Strange indeed. Why does this happen? I'm using SubSonic 2.1.

Connectionstring:

<add name="OCDB" connectionString="Network Library=DBMSSOCN;Data Source=127.0.0.1,1433;Initial Catalog=test_db;User ID=test;Password=testpwd"/>

Edit: Well the solution was just to simply generate and rebuild the Data Access Layer and I was good to go.

Answer 1

You seem to be using a 3 part name with part of it empty, i.e. '[].'

Answer 2

It looks as though the query text is being constructed with an empty table schema.

Those empty [] square brackets should contain something like "dbo" to make the query syntactically valid. I don't know enough about SubSonic to give you a code sample though.

Answer 3

I'm not familiar with SubSonic, but have you tried a simpler query to test if you have your syntax correct? Does this query even work in SQL Server (Management Studio / Query Analyzer)?

Just looking at this from the perspective of SQL Server, you are using way too many brackets. If I was writing that query in SQL Server, it would look more like what I wrote below. I'm not sure about the variable @pa_key0, is this query part of a stored procedure or does SunSonic replace this variable when the query is ran?

SELECT
  pa_id, 
  pa_key, 
  pa_title, 
  pa_keywords, 
  pa_description,
  pa_header, 
  pa_created,
  pa_modified, 
  pa_language 

FROM 
  cms_page

WHERE 
  pa_key = @pa_key0 

ORDER BY 
  pa_id ASC;

Answer 4

I think you need to set the schema for Subsonic to use. This thread seems to have some information:

http://stackoverflow.com/questions/755073/subsonic-how-to-use-sql-schema-owner-name-as-part-of-the-namespace