Advertisement

07.24.2008 at 12:31AM PDT, ID: 23591257
[x]
Attachment Details

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?

Asked by tgeorgeSD in MS SQL Server, Crystal Reports Software, Btrieve 4GL Database

Tags: Heterogenous queris must have ANSI_NULL set 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.

ThanksStart Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
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
[+][-]07.24.2008 at 03:15AM PDT, ID: 22077491

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.24.2008 at 08:03AM PDT, ID: 22079948

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.24.2008 at 09:34AM PDT, ID: 22080996

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.24.2008 at 02:42PM PDT, ID: 22083823

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.24.2008 at 06:44PM PDT, ID: 22085330

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.24.2008 at 10:41PM PDT, ID: 22086192

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.25.2008 at 07:38PM PDT, ID: 22093529

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.26.2008 at 06:32AM PDT, ID: 22094989

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.28.2008 at 09:32AM PDT, ID: 22104606

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, Crystal Reports Software, Btrieve 4GL Database
Tags: Heterogenous queris must have ANSI_NULL set on..........
Sign Up Now!
Solution Provided By: tgeorgeSD
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628