Board index » delphi » Query on Linked MSSQL Servers

Query on Linked MSSQL Servers

I am attempting to execute a simple query against databases on two MSSQL
servers that are setup as linked servers. When I attempt to execute the
query using a tQuery component in a D6 app I receive the following message:

General SQL error.
Heterogeneous queies require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection.

SQL:
select EquityCusip, TrustSymbol, Shares
from [Evaluations].EvalDB.dbo.EquityDetail ed
where ed.EquityCusip in (select Cusip
                                       from EquityCusips
                                       where EquityID = :EquityID)
and ed.TrustSymbol = (select Symbol
                                    from Trust
                                    where TrustID = :TrustID)
and ed.TradeDate = :TradeDate

If I execute the same query from MS Query Analyzer I receive the expected
result set.

Any advice on what is required to get past this?

 

Re:Query on Linked MSSQL Servers


Quote
Bob Deeley wrote:
> I am attempting to execute a simple query against databases on two MSSQL
> servers that are setup as linked servers. When I attempt to execute the
> query using a tQuery component in a D6 app I receive the following message:

> General SQL error.
> Heterogeneous queies require the ANSI_NULLS and ANSI_WARNINGS options to be
> set for the connection.

> SQL:
> select EquityCusip, TrustSymbol, Shares
> from [Evaluations].EvalDB.dbo.EquityDetail ed
> where ed.EquityCusip in (select Cusip
>                                        from EquityCusips
>                                        where EquityID = :EquityID)
> and ed.TrustSymbol = (select Symbol
>                                     from Trust
>                                     where TrustID = :TrustID)
> and ed.TradeDate = :TradeDate

> If I execute the same query from MS Query Analyzer I receive the expected
> result set.

> Any advice on what is required to get past this?

Hi Bob,

As the error says, you need to set ANSI_NULLS and ANSI_WARNINGS to "on"
in Enterprise Manager.  It's interesting that you could execute the
query in the Query Analyzer - when I had this problem I could not
execute it anywhere.

Regards,
Scott

Re:Query on Linked MSSQL Servers


Scott, check the connection options in query analyzer, it typically sets
nulls and warnings on for u.

I agree though i have had extreme trouble with this one several times and
have ended up calling sp's on the remote machine to resolve.

Jez

Quote
"Scott Maskiel" <scott_mask...@somedomain.com.au> wrote in message

news:3dee84de$1@newsgroups.borland.com...
Quote
> Bob Deeley wrote:
> > I am attempting to execute a simple query against databases on two MSSQL
> > servers that are setup as linked servers. When I attempt to execute the
> > query using a tQuery component in a D6 app I receive the following
message:

> > General SQL error.
> > Heterogeneous queies require the ANSI_NULLS and ANSI_WARNINGS options to
be
> > set for the connection.

> > SQL:
> > select EquityCusip, TrustSymbol, Shares
> > from [Evaluations].EvalDB.dbo.EquityDetail ed
> > where ed.EquityCusip in (select Cusip
> >                                        from EquityCusips
> >                                        where EquityID = :EquityID)
> > and ed.TrustSymbol = (select Symbol
> >                                     from Trust
> >                                     where TrustID = :TrustID)
> > and ed.TradeDate = :TradeDate

> > If I execute the same query from MS Query Analyzer I receive the
expected
> > result set.

> > Any advice on what is required to get past this?

> Hi Bob,

> As the error says, you need to set ANSI_NULLS and ANSI_WARNINGS to "on"
> in Enterprise Manager.  It's interesting that you could execute the
> query in the Query Analyzer - when I had this problem I could not
> execute it anywhere.

> Regards,
> Scott

Re:Query on Linked MSSQL Servers


The problem is that in the initialization of Query Analizer it is issued an
SET ANSI_NULLS ON | OFF depending on the settings. I think the problem is
that on of this 2 settings is not the same when executing the query from
Delphi.
    You can try this SQL order in TQuery Sql property:

"SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON
 select EquityCusip, TrustSymbol, Shares
  from [Evaluations].EvalDB.dbo.EquityDetail ed
  where ed.EquityCusip in (select Cusip
                                        from EquityCusips
                                        where EquityID = :EquityID)
  and ed.TrustSymbol = (select Symbol
                                     from Trust
                                     where TrustID = :TrustID)
  and ed.TradeDate = :TradeDate"

SET ANSI_NULLS ON -- or OFF depending on your settings from the linked
server. You can look on the settings from Query Analizer

Best regards,
Bogdan Dragulin b...@1234567ats.minisat.ro
remove the numbers from the address to send me an email

Quote
"Bob Deeley" <bobdee...@nikesec.com> wrote in message

news:3dee33d0$1@newsgroups.borland.com...
Quote
> I am attempting to execute a simple query against databases on two MSSQL
> servers that are setup as linked servers. When I attempt to execute the
> query using a tQuery component in a D6 app I receive the following
message:

> General SQL error.
> Heterogeneous queies require the ANSI_NULLS and ANSI_WARNINGS options to
be
> set for the connection.

> SQL:
> select EquityCusip, TrustSymbol, Shares
> from [Evaluations].EvalDB.dbo.EquityDetail ed
> where ed.EquityCusip in (select Cusip
>                                        from EquityCusips
>                                        where EquityID = :EquityID)
> and ed.TrustSymbol = (select Symbol
>                                     from Trust
>                                     where TrustID = :TrustID)
> and ed.TradeDate = :TradeDate

> If I execute the same query from MS Query Analyzer I receive the expected
> result set.

> Any advice on what is required to get past this?

Other Threads