I have an oracle procedure that needs to be converted to ms-sql.
lXMLContext := DBMS_XMLQuery.newContext("select * from table");
-- Setup parameters on how XML is to be constructed.
DBMS_XMLQuery.useNullAttributeIndicator(lXMLContex t,FALSE);
DBMS_XMLQuery.setRaiseNoRowsException(lXMLContext, FALSE);
DBMS_XMLQuery.setRaiseException(lXMLContext,TRUE);
DBMS_XMLQuery.propagateOriginalException(lXMLConte xt,TRUE);
DBMS_XMLQuery.setRowTag(lXMLContext,lWS.wsXMLRecor d.wsViewName);
DBMS_XMLQuery.setDateFormat(lXMLContext,lDateMask) ;
Any one can help in constructing a similar pattern in ms-SQL or atleast tell me what is all these doing?
I am coding this using extended stored procedure in c#.
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query? Thanks.
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------ BUTTONS:
OK ------------------------------ For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
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 ?
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient. Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails. I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000
My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference: ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object." ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0 We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side. I would welcome and appreciate any input. fudata
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
I currently have a DTS package that will transfer tables from an Oracle db to SQL 7. I am using the Oracle ODBC connection and have connected with the owners login and password. When I run the package through DTS, it runs successfully. However, when I try to run it as a scheduled job, it fails each time. I assume that this has something to do with connecting to Oracle. It's hard to figure out because SQL says only that the job failed. Has anyone else run into this issue?
I need to write a direct pull dts package from an oracle database to a SQL database. What version of sql do I use? PSQL or TSQL for the queries and updates?
Hi, have been asked the question by a customer, if the 2 RDBMS )Oracle and MSQ SQL 2000) can run ok on the same server (assuming the specs are adequate). I know MS recommend SQL run by itself, but is there anyone out there that can tell me anything about how this could/would work?? Your help would be invaluable Thanks
I want to import a table called emp(user name scott/tiger) from oracle database to MSSQL database
Step 1 login to scott/tiger in oracle
Step 2 Create a table called emp
create table emp (empno int, empname varchar(20))
Step 3 insert into emp (empno, empname) values(1,'aaa')
insert into emp (empno, empname) values(2,'bbb')
insert into emp (empno, empname) values(3,'ccc')
Step 4 Now I want to import this table to mssql using DTS.
I have used Data Transmission task in DTS for source query. I have given "select * from scott.emp"
I am able to get the exact result set. I am able to get the same values in MSSQL.
Step 5 Now I am creating another user called "test" and give access to "emp" table of "scott" user.
Step 6 Now i want to pass on "test" instead of "scott" in the Data Transmission Task.(i.e). "SELECT * FROM test.emp" instead of "scott.test". I want to pass "test" dynamically.
My client require e-commerce website and expected to have more than 100,000 products online. All products currently manage on Oracle Application server which is behind the firewall.
Initially all products will be uploaded to online ms sql server and then whatever the products get change on oracle should be updated online on ms sql db.
We need proposed solution under secure and easy method to keep online products DB according to the oracle system.
All this process should be Automatic and Oracle application server send only updated data to MS SQL SERVER because client has more then 100,000 products on their ORACLE Application Server.
After install I can't connect to the database I create. I get the error message invalid username/password I always use the same password during setup etc but it never works after the database is created.
I have noticed that I get an error message mid way through the install though, it looks like this: http://www.leighrogers.co.uk/RESOURCES/error.gif
i am copy one table from oracle to sql but oracle table has 6 million rows when i am copy data at that time my package went to suspend mode so what should i need to fast and simple process to cpy form oracle to sql.
Is it possible to copy data automatily, once a day, from a Oracle db into a SQL db ? The command has to come from de SQL server (2000) On the server we have install Orcale client.
Hi All, when i am executing follwing sql task from ssis. follwing delete statment will run on oracle
Delete from DW_ADVENTRUREWORKS.EMPLOYEE; Delete from DW_ADVENTUREWORKS.DEPARTMENT;
I am getting following error. failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
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
I recently got a new job at a new company. My previous experience had all been with Oracle DB's but the new place uses Sql Server 2k. I have a few general questions about how to do stuff in SqlServer...
1) how do I create a sequence and a trigger? 2) I know SqlServer probably doesnt have anything like Packages, so are procedures local to schemas? and if so, are schema's nestable in any way? 3) Can anyone reccomend a good book on T-SQL?
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.
I'm not a tech guy, but a Product Manager, trying to make a decision on what database to go with for an application I'm building and am looking for analysis on it, specifically advantages/ disadvantages of SQL Server vs. Oracle db. ANy suggestions appreciated.
Outline of application: - must handle high transaction volume. (avg is 2,000/day, high end is 500,000/day, less than 10% of customers). - application is sold to mid market (avg. sell is $50k) so selection must be cost efffective. - Probably building in Java so it will run quote anywhere unqoute.
