I have a linked Server (SMARTLINK_STATS06) on SqlServer 2005. The linked server is an Oracle Rdb database. In a SqlServer 2005 Database I created synonyms for tables using the linked server.

When I try to create a new project from the database I get the following exception:

Index #0 Message: Invalid object name 'SMARTLINK_STATS06.sys.objects'. Number: 208 Source: .Net SqlClient Data Provider

Invalid object name 'SMARTLINK_STATS06.sys.objects'.

-----[Core exception]-------------------- at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.WrapAndThrowSqlException(SqlException ex) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.RetrieveAllTablesViewsSynonymsAndFields(SqlConnection connectionSqlServer) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties) at SD.LLBLGen.Pro.Gui.Forms.NewProjectWizard.CreateNewProject() -----[InnerException]-------------------- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.RetrieveAllTablesViewsSynonymsAndFields(SqlConnection connectionSqlServer)

SD.LLBLGen.Pro.DBDrivers.SqlServerDBDriver .Net SqlClient Data Provider

Are you logged into the db with a user that has access rights to the sys.objects table?

I tried using 'sa' just to be make sure I had access, but the problem is still the same.

What is it looking for when it is searching for 'SMARTLINK_STATS06.sys.objects'. SMARTLINK_STATS06 is my linked server, which resides in the sys.servers table, so I am unsure as to what it is exaclty looking for, is it parsing the synonym incorrectly?

Can you give some more information as to what SQL it is executing aginst the database so that I can check it out locally.

In the routine RetrieveAllTablesViewsSynonymsAndFields, where the crash occurs, it retrieves synonym definitions for real tables. It does this with the following SQL: SELECT name, base_object_name FROM sys.synonyms WHERE is_ms_shipped = 0 AND schema_id = ( SELECT schema_id FROM sys.schemas WHERE name = '<schema>')

where <schema> thus is SMARTLINK_STATS06

It then also tries to retrieve the fields for these synonyms. SELECT '<catalog>' AS TABLE_CATALOG, s.name AS TABLE_SCHEMA, t.name AS TABLE_NAME, c.name as COLUMN_NAME FROM <catalog>.sys.key_constraints k inner join <catalog>.sys.tables t on k.parent_object_id = t.object_id inner join <catalog>.sys.schemas s on t.schema_id = s.schema_id inner join <catalog>.sys.index_columns i on t.object_id = i.object_id and k.unique_index_id = i.index_id inner join <catalog>.sys.columns c on i.column_id = c.column_id AND t.object_id = c.object_id WHERE k.type='PK' AND s.name = '<schema>' AND t.name = '<table>'

where <catalog> is the catalog in where a synonym was found, <schema> the schema of the synonym and <table> the table the synonym is a synonym of.

It must be the second query.

I used the SQLServer profiler to capture the SQL sent by LLBLGen Pro to investigate what was happening.

1. This query returns the synonyms, it works OK.

SELECT name, base_object_name FROM sys.synonyms WHERE is_ms_shipped = 0 AND schema_id = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')

GLOBAL_STATISTICS [SMARTLINK_STATS06]...[GLOBAL_STATISTICS] COURSE_OUTCOMES [SMARTLINK_STATS06]...[COURSE_OUTCOMES] COURSE_STATISTICS [SMARTLINK_STATS06]...[COURSE_STATISTICS] DEPARTMENT_STATISTICS [SMARTLINK_STATS06]...[DEPARTMENT_STATISTICS]

1. This query attempts to return the meta-data for synonyms base table, and this is where the error is being generated.

SELECT S.name AS schema_name, O.name AS object_name, O.type AS object_type, C.*, TY.name AS data_type_name, TY.is_user_defined, TY.is_assembly_type FROM SMARTLINK_STATS06.sys.objects O INNER JOIN SMARTLINK_STATS06.sys.columns C ON O.object_id = C.object_id INNER JOIN SMARTLINK_STATS06.sys.schemas S ON O.schema_id = S.schema_id INNER JOIN SMARTLINK_STATS06.sys.types TY ON C.system_type_id = TY.system_type_id AND C.user_type_id = TY.user_type_id WHERE S.name IN ('') AND O.name IN ('') ORDER BY schema_name, object_name, C.column_id

Msg 208, Level 16, State 1, Line 1 Invalid object name 'SMARTLINK_STATS06.sys.objects'.

What is happening is that I am using a remote server (SMARTLINK_STATS06) in the synonym name and your code is parsing it as the database_name instead. e.g. server_name.database_name.schema_name.object VS database_name.schema_name.object

Are you only supporting synonyms for other local SqlServer databases or is it the intent of this release to support remote servers in synonyms as well?

Aha! The code indeed doesn't take into account the fact that a servername might prefix the full object name (catalog.schema.objectname). It's the only spot where it has to use object names this way.

I can change the indexes and queries so they handle 4-part names. Though keep in mind that the entities will only work with the synonyms.

Sounds good!

The other burning question I have is the fact that the linked server (SMARTLINK_STATS06) is NOT a SQLServer database. As you need to retrieve the meta-data of the underlying table I imagine that you would not support linking to any remote server other than another SQLServer database?

For example I may want to link to an Oracle database to retrieve my table.

It's my understanding that SqlServer is virtualizing the remote server, as if the contents of that remote server is part of teh sqlserver instance. So if you use T-SQL statements, you should be able to do so on that remote server THROUGH the sqlserver. Am I correct in this? If this doesn't work, you indeed need to use ODP.NET and directly target Oracle.

It's best to target a system directly instead of through another db in most cases. Like accessing sqlserver through access etc.

I have a hard time setting up the oracle linked server in sqlserver 2005 to test any query. I've added my 9i box to sqlserver 2005 as a linked server, using the MSDAORA provider (oracle's OraOLEDB also didn't work), and I don't seem to be able to query any table in teh oracle box, because you've to specify 4 parts of the name. So if I create a synonym: CREATE SYNONYM [dbo].[Emp] FOR M2000.oramoron2000.HR.Employee where M2000 is the name of the linked server in sql2005, oramoron2000 is the TSN name, HR is the schema and EMployee is the table name, I always get an error when I query this synonym: Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server "M2000". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Understandable, as Oracle doesn't support catalogs, but how do you specify the name in the 4 parts that it is understandable? I can't seem to get it to work.

Also, how it seems now, it won't work what you want to achieve, because sqlserver 2005 doesn't do any virtualizing: it simply passes on the query, so objects like sys.objects which isn't present in oracle, won't magically appear in the linked server.

I think this is going to be difficult. I would imagine that you would have the same trouble with Oracle to SqlServer database links? But SQLServer does provide some procedures to retrieve meta-data from a linked server which may be of some use:

EXEC sp_tables_ex @table_server = N'SMARTLINK_STATS06'

EXEC sp_primarykeys @table_server = N'SMARTLINK_STATS06', @table_name = N'GLOBAL_STATISTICS'

EXEC sp_indexes @table_server = N'SMARTLINK_STATS06', @table_name = N'GLOBAL_STATISTICS'

EXEC sp_foreignkeys @table_server = N'SMARTLINK_STATS06', @pktab_name = N'GLOBAL_STATISTICS'

You end up restricting linked servers to only other SQLServer servers, same for Oracle; or not allow them at all (trap four part synonym usage).

but I do have a work around that satisfies my current situation. I create a new SQLServer database as a 'compilation' only database. It contains all of the required tables from my linked server (with their primary keys and foreign keys, etc..) Its only purpose is to allow LLBLGen to gather the tables meta-data without any “issues”. I create my project against this ‘compilation’ database, generate my project code…

Now when I compile the code I can change the catalog to the “real” database with the linked server tables… I’ve tried it and it seems to work OK. I can’t think of any problems with this, can you?

Thanks for your efforts, maybe this is feature for a future version.

That indeed would work, as long as yuo don't have to make a call to the linked server: so only synonyms should be used.

The problem is that I have to use the oracle driver to read meta-data while using the SQLServer driver as it's connected through that driver. This is awkward also because it's unknown what the type of the linked server is. So if you want to access the oracle tables, either connect to oracle directly or use the trick you described and solely use synonyms.

I have a similar error when refreshing a catalog which has views that uses synonyms for tables in a different db on the same sql server.

The message I get is: The given key was not present in the dictionary.

And the stack trace:

-----[Core exception]-------------------- at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.CreateTableSynonym(String catalogName, String schemaName, String tableName) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.RetrieveAllTablesViewsSynonymsAndFields(SqlConnection connectionSqlServer) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerSchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.SqlServer.SqlServerDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties) at SD.LLBLGen.Pro.Gui.Forms.MainWindow.RefreshCatalogs(Boolean unattended, Hashtable catalogsToRefresh) at SD.LLBLGen.Pro.Gui.Forms.MainWindow.OnProjectExplorerCatalogRefreshClicked(Object sender, CatalogRefreshEventArgs e) at SD.LLBLGen.Pro.Gui.Forms.ProjectExplorer._menuItemCatalogRefresh_Click(Object sender, EventArgs e) at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam

The sql server version is: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

and my LLBLGen is version 2.6

Any ideas?

Please check these threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14522

Also Frans has posted the following message in response to a similar HelpDesk thread.

Otis wrote:

It's likely that a synonym in your catalog points to a view that's not reachable, e.g. a view on a linked server. Is that the case?

You could compile the sourcecode of the SqlServer driver from the 2.0 SDK sourcecode (available in the customer area) and run that debug build (remove the line with the strong name key in the assembly info) so we could obtain the line number where it goes wrong and you could place a breakpoint on it to see which synonym goes wrong. With that information we can dig deeper to see what we can do about it to prevent the driver from crashing.

(edit). the routine where the crash occurs assumes that a synonym name is present in a list first obtained from the catalog schema. This is done because the call to the routine is done in a piece of code which is looping over the fields for the synonym. These fields are obtained from the db using a complex join. Apparently the schema and /or table/view for a field isn't in the set of tables/views read from the catalog. So a synonym points to a table/view which is said to be in the schema but that table /view isn't in the list of tables/views read from the db schema earlier.

So in short: the data should be there, as the dictionary is filled with the same data that is used to build the query for the fields.

The only reason I can think of is that the table/view is thrown out due to another exception or issue. Could you check the ApplicationOutput window in the designer as well to see if there's a table/view reported to be ignored?

Thanks for prompt reply Walaa.

Unfortunately the thread doesn't help since I have a different scenario. I have database A which has view VwA which uses a synonym pointing to database B on the same server. View and synonym are both in the dbo schema.

I tried profiling while refreshing the catalog and one thing I noticed was that first some information about the synonyms was fetched and then information about the linked tables.

What stroke me was that the table names didn't have the same case in the result sets e.g. the following synonym data:

ccs_sy_therapy_eventtypes   [therapy_dailytest_3_5].[dbo].[dp_px_eventtypes]

but when loading the data about the table the value of the "object_name" column was

dp_px_eventTypes

Note the capital "T".

By default Dictionaries are case sensitive but sql server isn't so I guessed that was the reason why the lookup failed and changing the table name in the synonym proved me right

So problem fixed, thanks for your help.

That's indeed the case .