I just migrated a specific package from our old to our new enviorment. The one thing that changed between the old and the new enviorment is that in the old enviorment the connection manager was setup with a linked-server. In the new enviorment we connect through ODBC.
One of the SQL commands failed on the new enviorment. It was something in the scope of:
SELECT * FROM view
It did throw this error:
Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "The batch could not be analyzed because of compile errors.". An OLE DB record is availible.
Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Invalid object name 'name of view'.".
I remembered some similiar issue from the past and tried supplying a schema name.
SELECT * FROM schema.view
Lo and behold, it works now.
This leaves me with the question: Why doesn't the old enviorment throw an error when no schema is supplied while the new enviorment does?
It turned out some of the permissions weren't set properly on this specific database. It is possible to query without a schema (I did specify it though just to be sure to get the right view).
User contributions licensed under CC BY-SA 3.0