SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Openquery with dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
71 Posts

Posted - 01/10/2013 :  07:48:05  Show Profile  Reply with Quote
I have tried all the possibilities to write data fromn a MS SQL Server via trigger to a Oracle table.The only possible way is to use OpenQuery with dynamic SQL. But I just have no idea of dynamic SQL!

My question is who can help me to get a working dynamic SQL, witch give parameter to a Oracle Function(this Function is a synonum for the the SQL Trigger)

CREATE TRIGGER [dbo].[TRG_INSUPDDEL_FRANCE1] ON [dbo].[FRANCE]
FOR UPDATE
AS

insert into openquery([VPHIN],'select DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from BRIX.T_SI_CHANGED_BK')
Select 'I' as DS_TYPE,
as DWDOCID,
as DW_DOC_ID,
as PRODUNIT,
as DW_ARCHIVE,
as DWMODDATETIME
from inserted

prett
Posting Yak Master

USA
192 Posts

Posted - 01/10/2013 :  23:09:42  Show Profile  Visit prett's Homepage  Reply with Quote
Please check this link: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

This link has full overview of dynamic SQL.
Go to Top of Page

zero1de
Yak Posting Veteran

Germany
71 Posts

Posted - 01/16/2013 :  07:01:57  Show Profile  Reply with Quote
I think so in this style it would work but I have no idea with dynamic SQL can anyone help me here?
What is missing in the trigger?


CREATE TRIGGER [dbo].[trg_log_changes_france4]
ON [dbo].[TEST]
FOR INSERT, DELETE, Update
AS

declare @DWDOCID int
declare @DWMODDATE DATETIME
declare @DWPRODUNIT INT
declare @TYPE CHAR(1)
declare @DT_STATUS datetime
declare @PRS_DT_BIRTH


set @DWDOCID = (SELECT DWDOCID FROM inserted)
set @DWMODDATE = (SELECT DWMODDATETIME FROM inserted)
set @DWPRODUNIT = (SELECT PRODUNIT FROM inserted)


IF EXISTS(SELECT * FROM Inserted)
BEGIN
declare @query NVARCHAR(500);
set @query ='INSERT INTO OPENQUERY([VPHIN], 'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED'';
SELECT @DT_STATUS=Getdata(), @PRD_DT_BIRTH=Getdata(), @DS_TYPE='I', @DWDOCID, @DWPRODUNIT, @DWMODDATE FROM Inserted
execute sp_executesql @query

End
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000