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');
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..
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
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
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
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.
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
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.
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
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.
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)
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.
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?
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
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?
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
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
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.
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.
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)
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?
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 ?
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.
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 ?
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