I've got a stored procedure that I'm issuing a distributed query in. When I
try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada,
yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS ON'
should do the trick. That's all well and good except that when I put this
statement in the SP, I get the same error. Where do I put the statement if
not in the SP. If I put it outside the SP, I still need to issue the
distributed query inside the SP so I still have the same problem?
ToddTry,
set ansi_nulls on
go
create procedure ...
go
AMB
"Todd Bright" wrote:
> I've got a stored procedure that I'm issuing a distributed query in. When
I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada
,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS O
N'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement i
f
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd|||Did you try to edit the procdure in QA rather than in EM (I think you did it
in EM), EM has the default settings to OFF, so if you edit this in EM it
will bring you the error, on th other hand if you do taht in QA via "Alter
procedire...yada..yada" with the setting turned ON it will work for you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Todd Bright" <ToddBright@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DE95BFD5-3039-48CD-B27D-4E4D80F0F4A9@.microsoft.com...
> I've got a stored procedure that I'm issuing a distributed query in. When
> I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON...
> yada,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS
> ON'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement
> if
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd|||Put it outside of the SP :) Like this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ...
GO
Assuming you want ANSI_NULLS and QUOTED_IDENTIFIER off again you can add:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
To the end.
"Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
news:DE95BFD5-3039-48CD-B27D-4E4D80F0F4A9@.microsoft.com...
> I've got a stored procedure that I'm issuing a distributed query in. When
> I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON...
> yada,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS
> ON'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement
> if
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd|||Thanks guys... I remember having to figure this out before, but that was a
few years ago. Now it's like... DUH!
"Todd Bright" wrote:
> I've got a stored procedure that I'm issuing a distributed query in. When
I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada
,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS O
N'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement i
f
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd
