Performance implications for using OPENQUERY in a view
I am using an EasySoft ODBC driver to link a SQL Server 2008 R2 Express instance to Interbase and I am having some difficulty with getting metadata from the remote server. From looking about on the net the main sugestions all mention using OPENQUERY instead of the four part linked server syntax. 

E.G. My current (problematic) approach is...

    CREATE VIEW [LIVE].[vwPRDETS]
    AS
    
    SELECT *
    FROM [LBLIVE]...[PRDETS] WITH (NOLOCK)

But on some tables I get the error when calling the view...

> Msg 7353, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
> linked server "LBLIVE" supplied inconsistent metadata. An extra column
> was supplied during execution that was not found at compile time.

Also, some views I cannot even create because I get the following...

> Msg 7315, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
> linked server "LBLIVE" contains multiple tables that match the name
> ""SYSDBA"."AUDIT_LBABKP"".

Although there is only one of the tables mentioned.

The alternative approach from searching the net seems to be more like...

    SELECT *
    FROM OPENQUERY(<linked sevrer>, 'SELECT <column list> FROM MyTable')

So, my question is, if I use OPENQUERY in my view definition will SQL Server be able to optimise the resulting SQL being sent to Interbase? Or is there really not much difference between the two approaches?







Performance implications for using OPENQUERY in a view
===

I am using an EasySoft ODBC driver to link a SQL Server 2008 R2 Express instance to Interbase and I am having some difficulty with getting metadata from the remote server. From looking about on the net the main sugestions all mention using OPENQUERY instead of the four part linked server syntax. 

E.G. My current (problematic) approach is...

    CREATE VIEW [LIVE].[vwPRDETS]
    AS
    
    SELECT *
    FROM [LBLIVE]...[PRDETS] WITH (NOLOCK)

But on some tables I get the error when calling the view...

> Msg 7353, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
> linked server "LBLIVE" supplied inconsistent metadata. An extra column
> was supplied during execution that was not found at compile time.

Also, some views I cannot even create because I get the following...

> Msg 7315, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
> linked server "LBLIVE" contains multiple tables that match the name
> ""SYSDBA"."AUDIT_LBABKP"".

Although there is only one of the tables mentioned.

The alternative approach from searching the net seems to be more like...

    SELECT *
    FROM OPENQUERY(<linked sevrer>, 'SELECT <column list> FROM MyTable')

So, my question is, if I use OPENQUERY in my view definition will SQL Server be able to optimise the resulting SQL being sent to Interbase? Or is there really not much difference between the two approaches?






