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
RodrigoHi 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|||This is a multi-part message in MIME format.
--060600060209060500070603
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
>
--060600060209060500070603
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No, this is wrong (please verify your guesses before providing
them).<br>
<br>
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.<br>
<br>
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).<br>
<br>
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).<br>
<br>
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.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
doller wrote:
<blockquote
cite="mid1123641009.412382.267360@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">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
</pre>
</blockquote>
</body>
</html>
--060600060209060500070603--|||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
> Rodrigosql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment