Showing posts with label analyser. Show all posts
Showing posts with label analyser. Show all posts

Tuesday, March 20, 2012

Query to linked server:Oracle; problems with ANSI_NULLS;ANSI_WARNINGS

When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error
when saving this procedure. (Not when compiling; it has no errors)

Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query.

When I create a dynamic SQL statement then I can save this stored procedure
when I run the stored procedure this same error happens.

What do I have to do.

Arno de Jong, The Netherlands"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bq9lqp$qag$1@.reader11.wxs.nl...
> When I perform a query on a linked Oracle server in the Query analyser I
> have no
> prboblem' to perform this query.
> However, when I create this query in a stored procedure I get a
compilation
> error
> when saving this procedure. (Not when compiling; it has no errors)
> Server: Msg 7405, Level 16, State 1, Line 1
> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
> to be set for the connection. This ensures consistent query semantics.
> Enable these options and then reissue your query.
> When I create a dynamic SQL statement then I can save this stored
procedure
> when I run the stored procedure this same error happens.
> What do I have to do.
> Arno de Jong, The Netherlands

Have you tried putting SET ANSI_NULLS ON and SET ANSI_WARNINGS ON at the
start of your stored procedure, ie. in the procedure code itself? QA sets
these on automatically, but other connections may not.

Simon

Saturday, February 25, 2012

Query time massively different between App and QA

We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or...
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants :rolleyes: )

The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984

It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.Mmmmmmm, looks to me like your application is likely to be the root cause. How does Siebel access the DB? Named Pipes, TCPIP?|||I'm told Siebel uses Names Pipes.|||Mmmmm, seen named pipes cause an issue before, I think the app ends up scanning through several ports before finding the correct one?

Will do some head scratching|||There are a number of tools that use an older TDS library (some even using DB-Library) that get lousy performance from queries that run well in Query Analyzer. It seems to me that Siebel can be fixed by simply upgrading the client machine's MDAC (http://msdn.microsoft.com/data/mdac/default.aspx), but I don't regularly run Siebel so I'm not certain of that.

-PatP|||Now that is a smart idea. JamesB, you may want to check which version of MDAC the client machines have. I think the current version of MDAC is 2,7. 2.8 has apparently just been released but judging from all the woes on the forum I doubt that would be the one to use at the moment.

Ideally, the client and server MDACs should be the same version.