RE: stored proc ansi_nulls help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Pablo (Pablo_at_discussions.microsoft.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 11:00:03 -0700

Hi,

You also need to have
ANSI_NULLS = ON
ANSI_WARNINGS = ON
on connection which execute your SP.
Check it by 

----
DBCC USEROPTIONS
EXEC  [dbo].[spTEST] 'localhost'
---
Regards,
Pablo
"Josh" wrote:
> When I execute a call to a stored proc that calls openrowset to another server I get the following error:
> 
> Server: Msg 7405, Level 16, State 1, Line 1
> 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.
> 
> I put the ansi nulls and ansi warnings in when building the proc but am still recieving the error.  Why?
> 
> 
> Script to create proc via query analyzer:
> 
> SET ANSI_NULLS ON 
> go
> SET ANSI_WARNINGS ON 
> GO
> CREATE PROCEDURE spJOBSTAT_STATUS_LOOKUP
> 
> @SRV_NA sysname
> 
>  AS
> 
> declare @strSQL varchar(5000)
> 
> 
> set @strSQL = ' select job_id, originating_server, name, enabled, last_run_date, last_run_time, last_run_outcome   from  OPENROWSET(''MSDASQL'', '
> set @strSQL = @strSQL + ' ''DRIVER={SQL Server};SERVER='+@SRV_NA+';UID=blah;PWD=A4$$q1@dm1n'', '
> set @strSQL = @strSQL + ' '' set fmtonly off exec msdb..sp_help_job'')'
> 
> --select @strSQL
> exec(@strSQL)

Relevant Pages

  • Re: Interesting Problem regarding Parameters
    ... > reasons, however we have one slight problem we would very much like to ... > logging to our database connection object. ... > query that was actually executed on the server, what I mean by that is: ... > //Set the query with parmater then execute reader ...
    (microsoft.public.dotnet.framework.adonet)
  • Interesting Problem regarding Parameters
    ... My company is currently migrating to .Net from PHP for a number of reasons, ... database connection object. ... What our current database class will do (after executing the query) is logg ... //Set the query with parmater then execute reader ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: PEAR MDB2 Unknown Error
    ... I'm bumping into an issue with my installation of the MDB2 package ... I'm trying to execute a query, ... is created in the parent class' constructor and a connection *is* ... I just can't seem to query against it. ...
    (comp.lang.php)
  • Re: Lost connection
    ... therefore is not possible to handle every query. ... If this is your case too then you can use the beforeExecute event of this object to check for connection problems and sort them out. ... try to execute a small dummy query something like ...
    (borland.public.delphi.database.ado)
  • Re: Lost connection
    ... therefore is not possible to handle every query. ... connection problems and sort them out. ... disable the beforeExecute event handler by setting the event to nil; ... try to execute a small dummy query something like ...
    (borland.public.delphi.database.ado)