Insert Data To MSSQL Within Trigger From Oracle

Jun 14, 2007

I created a link from Oracle to SQL server.
There is a trigger with insert statment in Oracle side.
I got error message as follow when trigger is invoked:

SQL> insert into test1 values ('zerbra','brazi');
insert into test1 values ('zerbra','brazi')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TRIGGER2", line 4
ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'

my simple trigger is as follows:
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');


END;

View 6 Replies


ADVERTISEMENT

MS SQL Trigger To Insert In ORacle Database Tables

Dec 12, 2005

I have two databases one running of MS SQL Server and other on Oracle..
My requirement is such that, I want an trigger to be written on table in MS SQL Server such that it inserts / updates values of tables in Oracle Database tooo..is there any methodology for the same..

regards.

View 5 Replies View Related

Insert Data To MsSQL Express Database?

Jan 4, 2008

Hello there,I'm to asp.net, so please be patient :DMy question is, how do I simply add some data to my database? - With vb.net code, not a grid view or something like that..I want to connect to my database, insert some data to a table.It shouldn't be that hard?- Hope someone will take the 5 minutes, and help me :)Regards Jeppe

View 16 Replies View Related

DTS - How To Update And Insert Data From SQL To ORACLE ?

Sep 21, 1999

Hi guys,

I have a situation that i have to insert or update
the data from sql server 7.0 to Oracle 8+Database.

the requirement is that
if the data is not in Oracle
then Insert the data
else if the data exists
then Update the data.

I dont know how to use in DTS. DTS Documentation says
that u can use DataQuery but they had not given the example.

Second thing is how to connect to the Oracle
using ACtive X Script - DTS.

Please, Can anyone help me.

Thx in Adv.

Sreedhar Vankayala

View 1 Replies View Related

How To Insert New Data In Trigger

Jun 14, 2007

In oracle, I use the following script in trigger:
INSERT INTO TABLE1 (CITY, STATE) VALUES (:new.city,:new.state);

I am wondering how to insert new data in SQL server. Thanks!

View 5 Replies View Related

Insert Data In Update Trigger

Nov 24, 2003

I have a table called drugs with a cost, date, and drugname field. I have a history table that I want to insert into anytime the fields are changed in the drugs table. I want to capture the old data along with the new cost. is that possible in a update trigger? What I have below doesn't like when it comes to setting the new cost

create trigger Updt_drugs on drugs for update as
-- updates record with sql user and timestamp
--created 1-12-02 tim cronin
declare @newcost money
begin
set @newcost = select cost from inserted
insert into drugsold ([oldcost],[cost],[cdate],[drug])
select [cost],@newcost,[cdate],[drug]
from deleted dt
end

View 1 Replies View Related

Insert Trigger Not Getting Row Data For Email Body

Oct 18, 2007

hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data  information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
 

View 5 Replies View Related

Using Trigger To Insert Data To Different Server Database

Sep 25, 2007

i have 2 server named A and B

in A server have database server and B have database server

in A have database named A1 with table TA1 and in B have database named B1 with table TB1

i want if i insert data into database A1 table TA1 in server A, database B1 table TB1 in server B will insert the same data to

how can do that with trigger or other ways

thx u

View 2 Replies View Related

Trigger Not Execute Some Data Or Insert Not Execute A Trigger For Some Data

Mar 3, 2008

I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.

If insert data one by one to a table trigger fires success.
Please Help me.

View 1 Replies View Related

MSSQL To Oracle

May 19, 2005

hello,
I built my application using SQLClient classes and MSSQL database
i have another version of the database with Oracle, is changing to OracleClient classes the only change i have to do in order to run the program and the connection string?
Thanks

View 1 Replies View Related

ORACLE To MSSQL...

Dec 9, 2001

Hi,
I have to migrate the oracle SQL statement to MSSQL. I want to know how to write a sql which can retrieve:
1. the user table name
2. the user table column name in the user table
3. the type of the user table column

I have already done a Oracle compliant sql :
------------------------------------------------------------
select table_name, column_name,
decode(data_type, 'VARCHAR2', 'java.lang.String',
'VARCHAR', 'java.lang.String',
'CHAR', 'java.lang.String',
'DATE', 'java.sql.Timestamp',
'NUMBER', DECODE(nvl(data_scale,0), 0, 'java.lang.Long', 'java.lang.Double'),
'LONG RAW', 'java.lang.Byte', 'BLOB', 'java.lang.Byte','java.lang.Object')
from user_tab_columns
------------------------------------------------------------

Can anyone tell me how to change it to MSSQL sql which retrieve the same value as the above sql.
Thanks.

View 1 Replies View Related

Oracle DB To MSSQL

Nov 20, 2000

I need to pull data on a regular basis from an Oracle DB table to MSSQL table.
Can anyone help me on how to do it
Thank you.

View 1 Replies View Related

Importing Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table

Oct 6, 2007

Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP

View 1 Replies View Related

Integration Of Oracle 10g With MSSQL

Dec 15, 2006

Hi there all the database master,

i have a doubt here that would like to have some advice from u all. Can i get data from Oracle 10g database if i have an application running on MS SQL ? If can, would it be cumbersome afterall? Please advice.

View 1 Replies View Related

Toad And Oracle Vs. Mssql And ?X?

Jul 20, 2005

hi all ms sql specialists.....i am a newbie on ms sql buti am used to oracle and toad ...and now a question (i guess this was asked xxx times befor but icouldn't find the answer easilly) :is there something powerfull like toad on oracle for mssql server? (notenterprise manager)

View 2 Replies View Related

MSSQL Database Synchronization With Oracle

Jan 13, 2006

Hi all,
Well, I have created an applicatin in asp.net and it connect to MSSQL database, and it work fine.

There is another application which was developed in Oracle and the database is oracle 8i.

Well, wat i want is whenever a form is submitted from the asp.net
application, the data is inserted in the MSSQL server database and the
Oracle8i database also.

Can anyone help me how to proceed with it.

Thnks in advance for your help.
Regards
Vinay

View 1 Replies View Related

Julian Dates: Oracle Vs. MsSQL

May 9, 2008

Im working on a project to convert a C program from OCI(oracle) to ODBC(MsSQL).

One of the select statements uses oracle julian time:

"Select TO_NUMBER(TO_CHAR(TO_DATE('0101'||:styear,'mmddyyyy'),'J')) STYEAR from dual"

Its not a problem converting the statement, except msSQL uses 10/01/1900 as its origin for the julain calendar, where Oracle uses somewhere around 5000BC. my questions is, because this program is made to use oracle julian and need to keep it that way, how i can make the SQL conversion using oracles origin date?

View 13 Replies View Related

MSSQL Linked Server To Oracle RDB

Jul 23, 2005

Hi,I am using MSSQL 2k, and I have a linked server set up to an Oracle RDBversion 7. It goes thru an OLE DB provider for ODBC drivers on a systemDSN, which is using an Oracle RDB ODBC driver version 3.0.2.The problem occurs when I send a query that returns zero rows - queryanalyzer just does not complete nor return. This problem is not seenwhen there are rows being returned.I ran a trace and this is the error message I get-:Non-interface error: OLE DB provider MSDASQL returned an incorrectvalue for DBPROP_CONCATNULLBEHAVIOR which should be eitherDBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULLAnybody who experienced this before has a solution?Thanks,Lawrence

View 2 Replies View Related

Equivalent Of UTL_FILE Of Oracle In MSSQL

Apr 25, 2008

Hi all,

I am currently migrating my database from oracle to MSSQL. My problem is that i have encountered the UTL_FILE function in Oracle and i am not finding its equivalent for MSSQL.

Can anyone help me with this plz? Is there any other way of reading and writting OS files in MSSQL?

Regards
Dhiraj
Software Engineer.

View 3 Replies View Related

Have You Ever Installed MSSQL On A Server With Oracle Already Running ?

Apr 6, 2007

am i going to cause problems if i install MSSQL2005 on a server with Oracle already installed and running ?

thank you

View 6 Replies View Related

Need An Allround DBA (development&&Admin) In MSSQL And Oracle

Jul 23, 2005

All,International company in Australia needs an allround DBA(development&Admin) in MSSQL and Oracle.If you have 5+ experience in development and (not OR) Administration inboth MSSQL and Oracle and like challenges, please send CV toJoin Bytes!.I will send the position description to suitable candidates.Cheers,Yak

View 2 Replies View Related

Read Oracle Sequeneces Into MSSQL Table

Jul 20, 2005

Hi,I'm doing an Orcle to SQL port.Simply all I want to do is run a script / DTS package that reads Oraclesequences value and writes the current value of it in to a MSSQL table - ofSequenceName, ValueCan this be done ? If so how ? (Please provide example)Steve

View 2 Replies View Related

How Can I Link Server To A 32bit ORACLE From A 64bit MSSQL?

Dec 17, 2007

Hello,


How can I link server to a 32bit ORACLE from a 64bit MSSQL?


LiJun

View 1 Replies View Related

REPLICATION BETWEEN MSSQL SERVER And Postgresql/MySQL/ORACLE???

Nov 27, 2006

Hi Friends,
I want to know if there are a manner of replication(two-way) between MSSQL SERVER and (postgresql or mysql or ORACLE).

Thanks

View 3 Replies View Related

Distributed Transaction Trough ODBC From MSSQL To Oracle

Apr 14, 2007


I lost few days to solve a problem regarding the connection to an Oracle database server (10) with goal to execute a distributed transaction.

The environment was:

Windows Server 2003 Standard
MSSQL Server 2000 Standard
Oracle 10g Production Edition

The connection was quite easy to make with the Microsoft driver, but the error was:
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

I tried to use the oracle odbc driver, but all kinds of errors were raised, regarding the TNS name and stuff like this. The real cause was that when you create the linked server you must to check the €œAllow inbound process€? in the provider options.

The Oracle ODBC driver allow distributed transactions.

View 1 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Oct 30, 2007

Table 1





First_Name

Middle_Name

Surname


John

Ian

Lennon


Mike

Buffalo

Tyson


Tom

Finney

Jones

Table 2




ID

F

M

S

DOB


1

Athony

Harold

Wilson

24/4/67


2

Margaret

Betty

Thathcer

1/1/1808


3

John

Ian

Lennon

2/2/1979


4

Mike

Buffalo

Tyson

3/4/04


5

Tom

Finney

Jones

1/1/2000


I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.

View 17 Replies View Related

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

Feb 5, 2008

A





ID

Name


1

Joe


2

Fred


3

Ian


4

Bill


B





ID


1


4

I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?

I believe the trigger should look something like this:

create trigger test_trigger
on a
for insert
as
insert into b(ID)

select i.id
from inserted i
where
--specific USER

View 9 Replies View Related

Database Trigger In MSSQL

Jul 20, 2005

How can i create database level triggers in MS SQL? i m not talkingabout table trigger or view trigger.Khurram.

View 2 Replies View Related

Number/size Of Packets At A Network Level For A MSSQL Query Vs Oracle Vs DB2.

Jul 24, 2007

Hello all,
I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005.
I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's.
In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:

100 query or transaction dump:
mssql=3.7mb
oracl10g=133kb

50,000 query or transaction dump:
mssql=1.8gb
oracl10g=0.6gb
db2=0.6gb

as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.

So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?

Thank you for your time.

View 2 Replies View Related

How Can I Find Invalid Trigger On MSSQL ?

Aug 19, 2003

Hallo,

i looking for something to find invalid trigger on my database. In Oracle i found something. There is a entry for status in user_objects.But i looking for MSSQL.
Have somebody a tip ?

sorry my english is not so good ;-)

View 7 Replies View Related

Trigger Before Delete In Firebird Or Mssql

Jul 20, 2005

Hi everybody!I need create trigger in firebird and mssql.The trigger must do this:If someone try to delete record in table "mast_trans" and if column "ID"is equal 145 delete cannot be executed otherwise perform delete.How can I do this? My books dont have any examples for this :(Thanks for help, greetings, gregory.

View 2 Replies View Related

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

May 8, 2015

we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.

what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?

View 2 Replies View Related

How To Launch An Oracle SP From SQL Trigger?

Aug 16, 2006

I need to know a few things about this..
1. What do I need running or available on SQL Server machine (presuming an Oracle client)
2. Has anyone done this and perhaps can give me some quidance?
3. Any concerns about going from outside a firewall to the Oracle instance?

Architecture is probably going look like this:

SQL -> ON INSERT Trigger -> business logic -> Call Oracle Procedure

Any help is appreciated, thanks!

JTGIS

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved