Linked Server defaults NULL (in SQL Server) to 0 (in Oracle)

Last Post 28 Jun 2004 04:22 AM by naminha. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
naminha
New Member
New Member

--
28 Jun 2004 04:22 AM
Hi,

I have a linked Server in SQL Server that talks with Oracle. I send in the name of the linked server as an argument into a procedure to populate data from MS SQL Server to Oracle.

I have the insert into Oracle table as

set @insert_string = 'insert openquery(' + @in_link + ',''
select pgm_plan_title,
pgm_plan_desc,
pgm_plan_owner_id,
create_date,
ms_plan_id,
approval_state,
fgbs_id
from program_plan where 1=0'')
values ('+
''''+ @proj_name + '''' + ',' +
''''+ @proj_name + '''' + ',' +
convert(char,@in_person_id)+ ',' +
''''+@sysdate+'''' + ',' +
convert(char,@proj_id) + ',' +
convert(char,'0') + ',' +
convert(char,@fgbs_id)+')'

exec (@insert_string)

@in_link is passed in as an argument into the procedure

ISSUE: If @fgbs_id is NULL in SQL Server, it populates (defaults NULL TO ZERO) 0 in Oracle. How do I make sure NULL is inserted in Oracle as well. Inserting 0 violates integrity constraints (foreign key constraint) and the insert fails.

Thank you so much for your response(s) in advance.

Navin

You are not authorized to post a reply.

Acceptable Use Policy