Solved

Heterogeneous Queries, Linked servers, Crystal

Posted on 2014-09-11
16
501 Views
Last Modified: 2014-09-16
I have written a stored procedure  Crystal report.  The stored proc has a select..... with  an inner join to a linked server table (not a view).   I create the SP with the setting of ANSI_NULLS and ANSI_WARNINGS ON as most of the threads here say to do.  The SP runs great in SSMS (Management studio).     But when I go to run it in Crystal, I get the familair "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection....."
I have gone to the server I am linking to, and set the instance > properties > connections, and checked ansi warnings and ansi nulls.    still get the error.

This is all running under SQL Server 2012 (Express)  64 bit.
0
Comment
Question by:fhcdaver
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 50 total points
ID: 40318682
Don't activate in the instance. Do that when you create the SP:
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON 

CREATE PROCEDURE ProcedureName
 (...)

Open in new window

0
 

Author Comment

by:fhcdaver
ID: 40319524
Hi,

it IS in the SP create:

SET ANSI_NULLS ON
set ansi_warnings on
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[p_....]

Again, the proc runs OK if I run it in SQLManagement Studio.  It does not run from Crystal XI
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40322602
Can you check in there's a why to configure the connection from CR to MSSQL to SET ANSI_NULLS ON and SET ANSI_WARNINGS ON?
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 50 total points
ID: 40323758
I have no idea if this will help, but I ran into this problem at least once.  The stored procedure was trying to build a temp table using data from an Excel file.  I handled the problem by creating a second stored procedure that built the temp table, and executing that SP from the original SP.  That may not be the best solution, but it worked in that particular situation.

 James
0
 

Author Comment

by:fhcdaver
ID: 40324216
I will try the SP - calling an SP approach.
I'm wondering if the SP should select from a view?
0
 
LVL 34

Expert Comment

by:James0628
ID: 40324369
> I'm wondering if the SP should select from a view?

 Honestly, I have no idea.  I never used views much (I didn't have anything against them.  I just didn't use them).  So I really don't know if that will make a difference.

 James
0
 

Author Comment

by:fhcdaver
ID: 40324419
Nope, even using a view instead of a stored procedure still got me the heterogeneous error.  it's definitely something with how Crystal is using or calling the db and the linked sevrer in particular.  

OK, gonna try the temp table route next.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:fhcdaver
ID: 40324437
OK, so I did an SP that sleelcts from the "home" server and the linked server the data I need, and it writes to a ##temp table.  Then I have an "enveloping" SP that calls this first one, and then returns the results of the select * from ##temp.

It runs great in SSMS.  but, dammit, if Crystal still bombs looking for the heterogeneous query looking for ansi nulls and ansi warnings.

I wonder if there were options in the creation of the linked server that need to be set?
0
 

Author Comment

by:fhcdaver
ID: 40324441
Vitor,

you say

<Can you check in there's a why to configure the connection from CR to MSSQL to SET ANSI_NULLS ON and SET ANSI_WARNINGS ON?>

I'd love to! do any experts out there know how to check the connection?  

It's an ODBC connection
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40324996
I heard about something called Database Expert in Crystal Reports where you can do something like this:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

exec SP_NameHere {@Param1}, {@Param2}

Open in new window

0
 
LVL 34

Expert Comment

by:James0628
ID: 40325023
It runs great in SSMS.  but, dammit, if Crystal still bombs looking for the heterogeneous query looking for ansi nulls and ansi warnings.

 I'm not sure what else to tell you.  I sounds like you're doing the same thing, and it worked for me, but, FWIW, that was years ago.

 You could try Vitor's suggestion.  You can create a "Command" in CR, which is just a query that you enter manually, and apparently you can execute a SP from a Command.  Unfortunately, changing the datasource like that may require recreating the report from scratch.  I'm not sure.

 IAC, to test the idea, you could create a new report, use "Create New Connection", select your db connection and there should be an "Add Command" option.  That should open the Command editor window, where you would enter something like the lines that Vitor posted.  Note that if the SP has any parameters, the corresponding report paramaters should be created in that window.  Unless CR has changed, you can only use parameters in a Command that were created in the Command editor.  Once you've created a parameter, you can double-click on it to add it to the Command.

 Then I guess you'd just need to save the Command, put some fields on the report and try running it.

 James
0
 

Author Comment

by:fhcdaver
ID: 40325671
I will try these things.  
I am wondering if the creation of the Linked Server needed to have these ANSI parameters set.  That seems to be the place where Crystal is falling over.    It's the Linked Server.     But what I don't know is how to either delete and re-create with proper parameters, or somehow have Crystal "override" these linked server parameters so that it's happy.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40325698
You can try to create the linked server inside the SP but I don't know if it will helps:
exec sp_addlinkedserver
@server = 'ServerName\InstanceName',
@srvproduct=N'',
@provider=N'SQLNCLI'

Open in new window

0
 
LVL 100

Accepted Solution

by:
mlmcc earned 400 total points
ID: 40325786
What database connection method are you using in Crystal?

If ODBC, check the connection and set those options.

mlmcc
0
 

Author Comment

by:fhcdaver
ID: 40325979
Mike,

You did it again.  Thanks.

Yes, it was the ODBC.  So a few things:
1).  I tried setting BOTH ANSI boxes in the ODBC early on, and still had the failure.   But I think that was prior to setting the ANSI's in the SP create.  
2).  The same ODBC works fine in many other Crystals I have.  This is the first, however, with a linked server table join.
3). I ONLY set the ODBC "Use ANSI nulls, paddings and warnings.  
report runs!

thank you all.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now