Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Openquery with dynamic SQL

Author  Topic 

zero1de
Posting Yak Master

105 Posts

Posted - 2013-01-10 : 07:48:05
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

212 Posts

Posted - 2013-01-10 : 23:09:42
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
Posting Yak Master

105 Posts

Posted - 2013-01-16 : 07:01:57
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
   

- Advertisement -