Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server an
d executes
other stored procedures as needed. Each of the sub-procs will be running He
terogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that st
ates
Heterogeneous Queries require ANSINULLS and ANSIWARNINGS be set...
I have the remote server linked when I try to create the procs.
I've tried SET ANSI_NULLS and _WARNINGS with no effect so I'm assuming these
need to be set on the linked server.
I tried EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_NULLS', 'ON'
EXEC sp_serveroption 'sqlSOLOMON', 'ANSI_WARNINGS', 'ON'
but got an 'invalid options' error.
Does this need to be set as server defaults on each server or can I make the
se settings in a usp?
After the procs are created and I create the link at runtime can I expect so
me problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as nee
ded. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books o
n-desk.
'Ack!'
troyThanks Sue, that took care of it.|||Troy / Sue
I'm experiencing the same issue raised in this thread. Sounds like you guys
figured out what the problem was but the solution is not in this thread. Any
chance you can share your wisdom?|||If you have the same issues with a stored procedure, the
answer is in the post in this thread. You need to create the
procedure with the appropriate setting e.g.
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Mon, 10 Jan 2005 04:45:31 -0600, imalik
<imalik.1inb94@.mail.webservertalk.com> wrote:

>Troy / Sue
>I'm experiencing the same issue raised in this thread. Sounds like you
>guys figured out what the problem was but the solution is not in this
>thread. Any chance you can share your wisdom?|||Hi all!
I have the same problem with a view I'm trying to use with Crystal
Reports.
I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
it's still doesn't work...
How can I solve it please?
Sue Hoegemeier;3612624 Wrote:[vbcol=seagreen]
> If you have the same issues with a stored procedure, the
> answer is in the post in this thread. You need to create the
> procedure with the appropriate setting e.g.
> SET ANSI_NULLS ON
> GO
> SET ANSI_WARNINGS ON
> GO
> CREATE PROCEDURE YourStoredProc...etc.
>
> -Sue
> On Mon, 10 Jan 2005 04:45:31 -0600, imalik
> <imalik.1inb94@.mail.webservertalk.com> wrote:
>
> you
zen69|||I'm not sure what you mean by trying to use - what is the
datasource? Is it a stored procedure, a query, the view
itself?
What is the exact error message and when/how do you get it?
Whatever you are executing that involves this view in
Crystal, try executing the same thing in a query tool and
see if you get the same message. Take Crystal out of the mix
to start with.
-Sue
On Wed, 16 May 2007 10:53:15 -0500, zen69
<zen69.2qp5em@.no-mx.forums.yourdomain.com.au> wrote:
[vbcol=seagreen]
>Hi all!
>I have the same problem with a view I'm trying to use with Crystal
>Reports.
>I did recreate my view with ANSI_NULL, ANSI_WARNINGS statements but
>it's still doesn't work...
>How can I solve it please?
>Sue Hoegemeier;3612624 Wrote: