Heterogeneous Query and ANSI_NULLS/_WARNINGS

I'm trying to create a stored proc that creates a link to a remote server and executes
other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
I'm having trouble creating these sub-procs, I get the error message that states
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 these settings in a usp?
After the procs are created and I create the link at runtime can I expect some problems?
Any suggestions/links would be greatly appreciated.
I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
'Ack!'
troy
Troy,
The settings need to be set when you create your stored
procedures - not in the stored procedures but for the
connection creating the stored procedures - so that the
stored procedures are created with these settings. Something
along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 9 Apr 2004 13:06:06 -0700, "Troy"
<anonymous@.discussions.microsoft.com> wrote:

>I'm trying to create a stored proc that creates a link to a remote server and executes
>other stored procedures as needed. Each of the sub-procs will be running Heterogeneous Queries.
>I'm having trouble creating these sub-procs, I get the error message that states
> 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 these settings in a usp?
>After the procs are created and I create the link at runtime can I expect some problems?
>Any suggestions/links would be greatly appreciated.
>I am also an Application developer who gets roped into this DBA stuff as needed. I'm the closest they have here
>to a DBA and I cant find any decent answers in 'books on-line' or my books on-desk.
>'Ack!'
>troy
|||Thanks 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?
imalik
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message177518.html
|||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?

Quote:

Originally Posted by Sue Hoegemeier

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?

|||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:
|||

Quote:

Originally Posted by Sue Hoegemeier

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:

I fix my problem by setting ANSI_NULL/WARNINGS in my ODBC source. Thx anyway