Linking Server problem

Hi masters:
I get the following error when i try to create a stored procedure that query
an SQL linked Server...
[SQLSTATE 42000]
"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."
this is my stored procedure script
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
The funny thing is that the same code run without problems when i query the
linked server from the Query Analyzer... and i don't get the above error.
Please help
Thanks
Rodrigo
Hi rodrigo,
Stored procedure run locally means it will execute on the server were u
will create it .
so u cannot use linkedserver in a procedure.
u have to run it outside the procedure.
if u wanted to schedule it write ur code in dts or job window.
hope this help
from
killer
|||No, this is wrong (please verify your guesses before providing them).
The stored proc will run on the SQL instance on which it was created,
that's true, but that does not mean you cannot access data through
linked servers on that SQL instance via a stored proc. You can, in
fact, use both the 4 part notation (eg. select * from
MyRemoteServer.MyRemoteDB.Mike.MyFunkyTable) and the OPENQUERY()
function within a proc.
However, when you execute a distributed query, as you are trying to do
with the linked server stuff, all the SQL instances involved have to
deal with nulls & warnings the same way so they all behave
consistently. This means, if the remote server(s) is a SQL instance,
then the remote server & the calling instance both have to have the same
values for the ANSI_NULLS & ANSI_WARNINGS settings. Both servers have
to have them enabled (turned on).
ANSI defaults are session specific. Also, the ANSI_NULLS default that
was in force at the time you created the proc is saved with the proc and
used when the proc is executed. So, when you create or alter the proc
(with the linked server reference) make sure that "SET ANSI_NULLS ON" is
there before the ALTER PROC or CREATE PROC statement (just to be sure).
What was probably the case for you was that the proc was created with
ANSI_NULLS off, but the Query Analyzer session you were using had set
ANSI_NULLS on. So when you executed the SQL statement in the QA session
it used ANSI_NULLS on but when you executed the proc from that same
session it used the ANSI_NULLS value that was saved with the proc (ie.
ANSI_NULLS off). That's my guess.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
doller wrote:

>Hi rodrigo,
>Stored procedure run locally means it will execute on the server were u
>will create it .
>so u cannot use linkedserver in a procedure.
>u have to run it outside the procedure.
>if u wanted to schedule it write ur code in dts or job window.
>hope this help
>from
>killer
>
>
|||You should put the Ansi_nulls and warnings settings in your sp...you can use
something like this in EM's sp-property window if that's what you're using:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE TEST1 AS
select * from openquery(srvmis,'select * from PC_agrupa_cde')
GO
Greets, Lee-Z
"Rodrigo" <Rodrigo@.discussions.microsoft.com> wrote in message
news:F1CE4631-A9E7-4CB9-A560-9D1EDAE0774F@.microsoft.com...
> Hi masters:
> I get the following error when i try to create a stored procedure that
> query
> an SQL linked Server...
> [SQLSTATE 42000]
> "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."
> this is my stored procedure script
> CREATE PROCEDURE TEST1 AS
> select * from openquery(srvmis,'select * from PC_agrupa_cde')
> GO
> The funny thing is that the same code run without problems when i query
> the
> linked server from the Query Analyzer... and i don't get the above error.
> Please help
> Thanks
> Rodrigo