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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Dec 17, 2007
Hello,
How can I link server to a 32bit ORACLE from a 64bit MSSQL?
LiJun
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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