October 21, 2005 at 9:14 am
Hi
I'm using SQL Server 2000, which pulls data from an Oracle server for certain requirements.
I need to use a function to find an employee number which is stored in the Oracle database.
The SQL checks fine in the function dialog box but will not save as it produces the error message 'Heterogeneous queries require Ansi_Nulls and Ansi_Warnings to be set for the connection...'
I have tried to 'set Ansi_Nulls on' but it doesn't like it. I have has a look at BOL, but still don't quite understand this. The function I am attempting to create is below. If anybody can offer a suggestion, I would be very grateful.
Thanks
Paul
CREATE FUNCTION fn_FindPTid (@CC varchar, @GC varchar)
RETURNS int
AS
BEGIN
DECLARE @FindPT int
set @FindPT = (select Empnumber
from OPENROWSET('MSDAORA',
'students';'HHH';'HHH','select Event_Number,Person_code,Type,Session_Code,Person_Function
from FES.EVENT_DETAILS') as ED_link
inner join tempebs_pt
on ed_link.person_code=tempebs_pt.person_code
where event_number in (select event_number
from OPENROWSET('MSDAORA',
'students';'rep';'rep','select Event_Number,uio_group_code, uio_ui_code from FES.EVENT_DETAILS')
where uio_group_code= @GC
and uio_ui_code= @cc)
and type ='person'
and session_code='05/06'
and person_function='grp tut')
RETURN(@FindPT)
END
October 21, 2005 at 10:45 am
These links should help you...
http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;296769
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=148030#bm148031
A.J.
DBA with an attitude
October 24, 2005 at 8:24 am
Hi
Thanks very much for your help with this.
Your comments given in the link you included saved the day. I created the function in Query analyser and it worked fine. I did try to Set Ansi_nulls in the function dialog, but it didn't want to know.
I much appreciate your time and help.
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply