Avatar of tgeorgeSD
tgeorgeSD

asked on 

If I set ANSI_NULLS and ANSI_WARNINGS to create stored procedure with view created on linked server, why do I get an error that I must set this settings on?

MS SQL 2000 SP, linked server, CR 8.5.0.218
My stored procedure, when it's created sets the settings on.
SP references a view created from Pervasive linked server. The view only has 2 columns, vendor_key and vendor name, with data in both columns. I'm selecting the Vendor_name field in my select clause
The join in the stored procedure could create NULL values because not all detail lines will have the vendor_code in the SQL table joined to the pervasive APVEND table with the vendor_key.
Becaause of the possibility of NULL in vendor name, is this my problem? Is there a way to correct with a where clause?

I put a basic snippet below of what's in my SP

(see below)
However, when I use the SP as my basis in CR, I get the SQL server messages that I need to use ANSI_NULLS on pfwlinkserver_apvend.

I've read several questions that seemed similar, and all reference what I think I've already done, but I can't get mine to work. What am I missing? The code works in QA, but I understand that the settings are on, and I checked this. So I'm missing an important part of my problem. Any help would be appreciated.

Thanks
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET ANSI_WARNINGS ON
GO
 
Select 
  --Linked server view
  pfwlinkedserver_apvend.vendor_name
from
  left outer join on pfwlinkedserver_apvend on patrxdet_vw.vendor_code = pfwlinkedserver_apvend.vendor_key

Open in new window

DatabasesMicrosoft SQL ServerCrystal Reports

Avatar of undefined
Last Comment
tgeorgeSD

8/22/2022 - Mon