ansi null and ansi warnings

I did a search on these two set commands.  I found that you need to do a stored procedure to set them.

If I am creating views that crystal reports tells me these have to be set (heterogeneous stuff) then would I execute the sp, that sets them, inside the create view as ... go lines?  Is that what will make it set it for that particular view?

Here is the sp I created.  I don't know if it is right or not.  It is just from what I pulled/extracted from answers (at least as far as I could translate them).

CREATE PROCEDURE [dbo].[newSET4CR] AS

set ansi_nulls on
set ansi_warnings on

exec SET4CR

set ansi_nulls on
set ansi_warnings on
GO

I call one witin another (could not understand why) so please indicate the correct method.

Thanks
itatahhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you created a stored procedure with the output, use that one directly with crystal reports instead of doing a select * from view
0
itatahhAuthor Commented:
So, are you indicating I should create a view but have a stored procedure referencing the view?  And in the sp have the ansi settings set?

If my view name is Report115, and it has field1 and field2 in the select, how would I create an sp for crystal to reference?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no. no need to use a view at all, directly use the procedure from crystal reports
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

itatahhAuthor Commented:
SO the create procedure can produce the same results in SQL as the view?  So, should I just take the text in View and replace the Create View with Create Procedure and let it use the same Select and joins and where syntax that is in the View?

Here is one View that I am using.  It is referencing three (3) other views that use an "OpenDataSource" function.  (The real data I am looking at is DBF files.  However, I am finding it very difficult to link to those files directly inside CR.  So I made the choice to access it via SQL.  Also, some of the tables referenced in the View IS an SQL table.

CREATE VIEW dbo.VestaAR4VI1
AS

SELECT  A.[KEY] AS BATCH, A.BILLKEYDET AS VINVNO, B.PROVADDRES AS DIVISION, CAST(A.CLIENT_NBR AS DECIMAL(9,0)) AS CLIENT_NBR,
A.CLIENT_NAM, CONVERT(DATETIME,C.CLM_DATE,102) AS INV_DATE, A.HCPCS,
CAST(A.UNITS AS DECIMAL(7,2)) AS UNITS,
CAST(A.RATE AS DECIMAL(7,2)) AS RATE,
CAST(A.AMOUNT AS DECIMAL(14,2)) AS AMOUNT
FROM dbo.VestaDetail AS A
INNER JOIN
dbo.VestaClients AS B
ON A.CLIENT_NBR = B.CLNTNUMBER
INNER JOIN
dbo.VestaHeader AS C
ON A.[KEY] = C.[KEY]

GO

Should I take it and do this:

CREATE PROCEDURE dbo.VestaAR4VI1
AS

set ansi_nulls on
set ansi_warnings on

SELECT  A.[KEY] AS BATCH, A.BILLKEYDET AS VINVNO, B.PROVADDRES AS DIVISION, CAST(A.CLIENT_NBR AS DECIMAL(9,0)) AS CLIENT_NBR,
A.CLIENT_NAM, CONVERT(DATETIME,C.CLM_DATE,102) AS INV_DATE, A.HCPCS,
CAST(A.UNITS AS DECIMAL(7,2)) AS UNITS,
CAST(A.RATE AS DECIMAL(7,2)) AS RATE,
CAST(A.AMOUNT AS DECIMAL(14,2)) AS AMOUNT
FROM dbo.VestaDetail AS A
INNER JOIN
dbo.VestaClients AS B
ON A.CLIENT_NBR = B.CLNTNUMBER
INNER JOIN
dbo.VestaHeader AS C
ON A.[KEY] = C.[KEY]


set ansi_nulls off
set ansi_warnings off

GO

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Should I take it and do this:
yes, that will work fine
0
Craig YellickDatabase ArchitectCommented:
Personally, I strive to write code that works the same way independent of various ANSI settings. Instead of always obsessing about which setting is on or off, I write statements that work the same way no matter what the setting.

This means, for example, never using operators like =, <> etc on nulls and instead either using the Is Null operator or converting nulls to a reasonable alternate value through the IsNull( ) function.

In this way, you can make the settings part of the Server and/or Workstation connection configuration and ignore it in your views, stored procedures etc.

If Crystal is bugging you about the settings, do you really want to visit every single view and worry about settings?  

-- Craig
0
itatahhAuthor Commented:
Okay,  I have written and created the stored procedure.  Now, how do I select it in Crystal Reports?  I still just see the views when I connect.
0
itatahhAuthor Commented:
I have tried numerous ways and here is where I have gotten to:

1.  I created views to
a) get ahold of the data external to sql;
b) perform joins on the nultiple files for which a view was created in 'a)';
c) organize and select records out of the results

2.  Crystal Reports let me point at the view, but gave me the ANSI Null type related warnings and such.  SO

3.  I asked a questions about the settings commands for the ANSI stuff.  I was told that I needed to create a procedure instead of a view.  So I did.  However, I am not able to see the procedure as one of my selections to pull data from when I am trying to create a Crystal Report report.

4.  I read about functions instead of procedures.


Is it realy a case of changing the where statements from <> to isnull()?  I don't even have conditionals (where or having) statements in my views - at least not the one I am testing.

Help...

I need to have a crystal report created looking at this data.  I can view the results in QA - so I know the views along with the OpenDataSource works.  Can't I call a procedure inside a view so that it tunrs the ANSI stuff on and off?
0
itatahhAuthor Commented:
Alright, I realize I missed something.  I didn't have the options set to show stored procedures in Crystal Reports.  So now I see the SPs.

However, it is giving me an error when I try and connect to the SP.  It is telling me to check the SQL server log for the error.  I cannot get the log to show me what error it occurring.

Well....

I just came across an answer - maybe.  The error it is giving me is written to the .err file under the Windows\Crystal folers.  It is saying the same thing about the heterogeneous bit.

Well, it looks like even the OpenDataSource Views will have to be changed to procedures.  I was hoping to only have one single View for each external database.

If I created a stored procedure for each table, instead of the Views, would I be able to call them from another procedure?  (I think so but I would probably have to create the parameters to receive the fields the called procedure is intended to get for me.)
0
itatahhAuthor Commented:
HERE is a summary of what I have ran into along with current status and question on direction:

I have .dbf files that I am using "OpenDataSource" to connect to.  I am then creating views that joins the resulting data.

I am then trying to attach to the view from Crystal Reports.

It keeps giving me the heterogeneous/ANSI Null/ANSI Warning related error message.

I then created a procedure, in which I set the the ansi settings to on.

However, it is now giving me the same error, but this time in the '\windows\crystal reports\sqlerr.log' file.  It seems to need the settings to be run even on the OpenDataSource command.

So, I then merged the commands to 1) turn the ANSI settings on; 2) Run the OpenDataSource on all of the pertinent files; 3) perform the join.

However, I do not know how to address the data obtained in each OpenDataSource command.  i.e., If I ran the OpenDataSource command against FileA and FileB (two different lines) then how would I perform a join in the same procedure?

Here is the source:

set ansi_nulls on
--GO
set ansi_warnings on
--GO
set quoted_identifier on
--GO

--CREATE PROCEDURE dbo.spVestaAR4VI2
ALTER PROCEDURE dbo.spVestaAR4VI2
AS

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...DETAIL Detail

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...CLIENTS Clients

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...HEADER Header

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...VISITS Visits

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...PROVIDER Provider

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...COUNTY County

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...SCHEDULE Schedule

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...TSHEET TSheet

SELECT  *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="V:\DATA\AHH";Extended Properties=dBASE IV;')...RS RS

-------------------------------------------------------------------------------------------------------
SELECT  A.[KEY] AS BATCH, A.BILLKEYDET AS VINVNO, B.PROVADDRES AS DIVISION,
CAST(A.CLIENT_NBR AS DECIMAL(9,0)) AS CLIENT_NBR,
A.CLIENT_NAM, CONVERT(DATETIME,C.CLM_DATE,102) AS INV_DATE, A.HCPCS,
CAST(A.UNITS AS DECIMAL(7,2)) AS UNITS,
CAST(A.RATE AS DECIMAL(7,2)) AS RATE,
CAST(A.AMOUNT AS DECIMAL(14,2)) AS AMOUNT
FROM Detail AS A
INNER JOIN
Clients AS B
ON A.CLIENT_NBR = B.CLNTNUMBER
INNER JOIN
Header AS C
ON A.[KEY] = C.[KEY]

GO

When it runs it tells me it cannot find tables with the name I gave the ODS (OpenDataSource) tables.

I am hoping that I do not have to have multiple procedures that I have to call inside another procedure while passing the data back and forth.

If I do, do I have to identify every field in the ODS tables and then receive them back?  Could someone give me an example of how to:

1) Create a procedure that, when called, will return data to the procedure it was called from
2) Create a procedure that would call the procedure(s) create on step 1
3) Let the results from the multiple procedure calls be used in a join statement

WHILE

a) Having the ANSI settings set correctly
b) Having the ODS not have to open all of the tables in the subdirectory specified in the comman line, but only the table referenced at the end (this is due to the fact that it takes SO LONG for it to build a relationship with the tables when it does so for every table on every use of the ODS command)

Any ideas?
0
coeurvertCommented:
Using Crystal XI against SQL 2008R2 I was able to run a report from a view using OleDB(ADO) provider.
0
itatahhAuthor Commented:
coeurvert,

This is a old question for a system that does not exist anymore.  I did not think that this was still open.

Anyway, thanks for your comment, but I am going to reclose this ticket.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.