Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
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.
Any help would be greatly appreciated. Thanks!
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
The Output Column Has A Precision That's Not Valid
Hi, I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error: "The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38". the package runs but returns this column as NULL values earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results. does anybody know why is this happening or have a solution for this error? Thanks
View Replies !
View Related
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
Hi-- I am running SQL Server 2005 on Win2k3: 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". This is how I set up my Linked server: Provider: "Oracle Provider for OLE DB" Product Name: SomeServer Data Source: SomeServer Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword" The query I run is: Select * from [Someserver].[schema or database]..[tbl_name] Any help??? What am i missing?
View Replies !
View Related
Problems With OraOLEDB.Oracle
Good day everyone.... I have installed the OraOLEDB.Oracle Provider on 3 different servers, and have made a link server on all three. On two of the machines, I am successful in retrieving data from the Oracle database. But, on the third machine, I receive this error: Server: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "employee_prod" reported an error. The provider did not give any information about the error. Server: Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "tims2_prod". On the machine which is generating the error, I can use SQL*Plus to connect to the remote database. I can also connect to the database using the "ODBC Data Source Administrator" when I create a system DSN. It would appear that the driver is installed and working correctly, but SQL Server can not access it. I have also uninstalled/installed serveral times. All of our servers are running W2003 Standard X64 with SQL Server 2005 Standard 64-bit Edition. Does anyone have any suggestions?
View Replies !
View Related
OraOLEDB.Oracle Provider Missing
Hi. I am trying to add a linked Oracle Server to our MSSQL 2005 server but am missing the OraOLEDB.Oracle Provider under Server Objects -> Linked Servers -> Providers in the Object Explorer. Also I have considered using a simple OPENROWSET command to attach to the Oracle Instance but unfortunately one of the parameters required is the name of the Provider for the connection which puts me back at square one for resolution. I seemed to have recalled some time back having downloaded the Oracle Driver for MSSQL but I can't for the life of me find the location that I acquired it from before. Does anyone know where I can get the driver or where I can get explicit instructions for installing the driver on SQL Server??? I don't want to install the entire Oracle Client just to acquire the 1 driver I need to accomplish my task. Any input or suggestions would be greatly appreciated.
View Replies !
View Related
'OraOLEDB.Oracle.1' Is Not Registered On Local Machine
Hi all of you, Primary platform is Framework 2.0 running over XP. I've got an issue with a SSIS package which uses Oracle Provider for OleDB: Test connection failed because of an error in initializing provider 'OraOLEDB.Oracle.1' is not registered on local machine Nevertheless, MS Ole DB Provider for Oracle works fine. Let me know where am I failing. Thanks in advance,
View Replies !
View Related
Unable To Initialize OraOLEDB From SQL Server 2005 X64 To Oracle
I have a 64 bit SQL Server 2005 SP2 on a Windows 2003 x64. Thedeveloper said that he needed to use OLE to talk to Oracle so I wentto Oracle and downloaded the 64 bit OraOLDDB driver as recommended ina MS note.I can connect to Oracle via tnsping and using SQLPlus but I cannotcreate a linked db successfully.The linked database errors off with >Cannot initialize data sourcefor OLE provider "OraOLEDB.Oracle" for Lined Server "X" (Microsoft SQLServer error: 7399) << (manually typed)Here is the official OS, SQL Server, and Oracle client information:The OS is Windows Server 2003 Standard x64 edition Service Pack 2(NT 5.2 build 3790)The SQL Server 2005 9.0.3042 (x64)[fromMicrosoft SQL Server Management Studio 9.00.3042.00Microsoft Analysis Services Client Tools 2005.090.3042.00Microsoft Data Access Components (MDAC)2000.086.3959.00(srv03_sp2_rtm.070216-1710)Microsoft MSXML2.6 3.0 6.0Microsoft Internet Explorer6.0.3790.3959Microsoft .NET Framework2.0.50727.42Operating System5.2.3790The OraOLEDB.Oracle driver was installed from the Oracle 64 bitdownload with a database version of 10.2.0.1.0Oracle "tnsping sid " worksSQLPlus (ver 9.2) worksI have serveral linked databases to Oracle that I have built but allof them are 1- on SQL Server 2000 and 2- are 32 bit. If anyone knowsof any additional patches (Windows or Oracle) necessary to resolvethis I would appreciate the information. Also there are potentialfirewall issues though the fact that SQLPus can be used seems to rulethis out, but if there are any ports associated with OLE I would liketo check on them specifically.-- Mark D Powell --
View Replies !
View Related
SQL Server Agent And X64 Using OraOLEDB.Oracle.1: Dtexec From Cmd Works, But Job Fails
I am extracting data from an Oracle database and have installed the latest x64 ODAC. When I run the 64 bit dtexec in cmd the package runs fine with no errors, but when creating and executing a SSIS job in the agent it fails. I've tried creating the job using CmdExec as well and I get the same errors: Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:12:43 AM Error: 2007-08-15 11:12:45.63 Code: 0xC0202009 Source: Load Dimension Data Connection manager "ora" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800703E6. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x800703E6 Description: "Invalid access to memory location.". End Error Error: 2007-08-15 11:12:45.65 Code: 0xC020801C Source: CopyCustomers CustomerSource [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ora" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-08-15 11:12:45.65 The CmdExec job step uses the _exact_ same command as the one I execute successfully in cmd: "c:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW I have managed to find two work arounds, but they are quite ugly: 1) Schedule the package execution using Windows Task Scheduler, basically create a bat file which runs the package. 2) Schedule the package in SQL Server agent, but as T-SQL script and use xp_cmdshell, i.e. EXEC xp_cmdshell 'dtexec /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW' Both work and use the 64 bit dtexec. Not that elegant though.. CmdExec and the 32 bit version of dtexec works, but is not good enough for me since we bought new hardware and 64 bit software just to be able to address more memory. Has anyone managed to execute a SSIS package successfully using the agent and the 64 bit OraOLEDB.Oracle.1? Any help would be greatly appreciated. Thanks! Btw, I am using Windows Server 2003 x64 and SQL Server with SP2.
View Replies !
View Related
Cannot Fetch A Row Using A Bookmark From OLE DB Provider &&"OraOLEDB.Oracle&&" For Linked Server
I have SQL Server 2005 loaded on a 64bit Windows 2003 machine. Additionally, I have created a linked server using the OraOLEDB.Oracle driver (10.2.0.1) to a 32bit Windows 2003 machine that hosts an Oracle 9 db. I am executing a SQL Server 2005 store procedure that updates the Oracle DB. This procedure previously worked 100% of the time on SQL Server 2000 using the Microsoft OLEDB driver for Oracle. We were force to use the OraOLEDB.Oracle driver because SQL Server 2005 no longer supports the MSOLEDB driver for 64 bit machines. Below is an example of the code I am trying to execute: Update LINKED SERVER..SCHEMA.EMPCOMP Set EMPLOY_TYPE = ED.Value_Chg, EMPLOY_TYPE_DATE = getdate() --Select * From LINKED_SERVER..ORACLE_SCHEMA.ORACLE_TABLE C Inner Join SQLSERVER_REPOSITORY_DB.dbo.EMPLOYEE_TABLE E On E.Person_Id = C.Person_Id Inner Join SQLSERVER_TABLE_CURRENT_DB ED on ED.EmpNo = E.EmpNo Where ED.Cat_Id = 3 and ED.HR_Approved_Fl = -1 and ED.Proc_Fl = -99 The following Error occurred 100% of the time until I loaded the oracle Patch 5043675: "Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server" After the patch was loaded some of my update statements suceeded and some did not. There seemed to be no rhyme nor reason as to why they failed. Is there something else I need to do to make these updates work from SQL Server? If not, I am considering creating procedures in Oracle to pull the data instead of pushing it from SQL Server. Any help would be greatly appreciated.
View Replies !
View Related
How To Convert SQL Server Datetime To Oracle Timestamp (or Other Type With Similar Precision)?
In SQL Server I've created a linked server to an Oracle database. I am trying to insert (within the context of an sql server table trigger) an SQL Server datetime to an Oracle column with similar precision. Oracle timestamps are not compatible with sql server datetimes and I don't know how to convert the data (or if I should use a different type of column to store the data in Oracle). I have full control over the structure of the Oracle table so I can use a different type if timestamp is not best, but I need the destination column to have at least the same precision as the sql server datetime value. What is the easiest way to do this?
View Replies !
View Related
Loading To Oracle
Well, I have a SSIS package loading data from SSIS raw file without any transformations. My concern is its taking hell lots of time to load the data (simple source à target ) How can I improve the load speed when my target is oracle? Its talking 9 min to load 67000 records. & I have other tables which have 2-3mm records I€™m very much concerned about the performance. Please help me out. Thank you
View Replies !
View Related
SSIS Loading To Oracle
Hi All, We are considering loading oracle datawarehouse using SSIS as ETL. I would like to know the experiences of the team in doing the same. Please share your experiences on this. Thanks, S Suresh
View Replies !
View Related
Loading Data From Oracle To SQL Server Using SSIS
Ok, we have built a data mart using SSIS etc...for transformations and loading. Our biggest single problem we have currently is loading data from an Oracle server to our SQL server. Some tables from oracle run fine when retrieving the data but there is one particular table that just doesn't load fast enough (9 million records take over 12 hours). It seems that we are idling alot and its not always running. Can anyone help with this problem?
View Replies !
View Related
Error Loading Oracle DATE Data
Hello. I'm trying to put in an SQL server database some data extracted from Oracle Server 9i. During the load process, the "OLE DB Destination" in the task chokes up when it finds a record containing the date '0197-01-01 00:00:00' (i got this by putting the error output to a column of type varchar(50)). I can't use the Condicional Split's date functions to filter this out because they also choke on the strange date. Can anyone give a sugestion? Thanks in advance, Hugo Oliveira
View Replies !
View Related
Loading Sql Files From A Master File Oracle Equivalent
Hi.I need to give my customer an sql file that they can run in query analyzer.All the stuff they need to run is in a set of existing files.I'd like to just tell them to load this file (this is oracle syntax):@file1.sql@file2.sql@file3.sqlis there some way of calling these files (that are in the same dir) from amaster sql file?ThanksJeff Kish
View Replies !
View Related
Loading Data From Same Table (oracle) In To Diff SQL Tables With In Same Package(same Query)
Hi, I have Table A . we already have 80 columns . we have to add 65 more columns. we are populating this table from oracle .and we need to populate those 65 columns again from the same table. Is it a better idea to add those new 65 columns to the same table or new table. If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one. I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View Replies !
View Related
Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1
Hello, 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? Thanks, Philippe
View Replies !
View Related
Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient
Hi, 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 : TITLE: Distributor Properties ------------------------------ 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 ------------------------------ ADDITIONAL INFORMATION: 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 ------------------------------ BUTTONS: OK ------------------------------ Any idea about this error ? Thanks Tarek Ghazali SQL Server MVP.
View Replies !
View Related
Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used
Hi Everyone, 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. Best Regards, Steve Collins
View Replies !
View Related
Oracle Connectivity Patch For Windows 64 Bit SSIS, Oracle Client 10.0.2
Hi, I see many people had problems with connecting to Oracle while creating package on SSIS 2005 on the machine running Windows 2003 Server 64 bit. I have the same problem, but can anyone tell me the exact patch number from Oracle to fix this issue for Oracle Client v10.0.2 We need to connect to Oracle 10g when Oracle Client 10.0.2 installed on Windows 2003 64 bit machine. I know about copying Visual Studio to another folder than default for 32 bit programs which is Program Files(x86), but has anyone got "cleaner" solution with official patch from Oracle for 10g. Regards Bakhodir Makhamadov
View Replies !
View Related
Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle
Hi everybody, 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. Please suggest me wht should i do know? Thanks in advance Regards Arvind L
View Replies !
View Related
Executing Oracle Stored Procedure With Output Parameters Using ADO.NET Connection
I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DBMicrosoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving: [Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works. Any help on this would be most appriciated.
View Replies !
View Related
Call Oracle Stored Procedure With Output Refcursor From Ssis
I would like to call a oracle stored procedure which looks like this Create procedure in oracle sql plus CREATE OR REPLACE PACKAGE GroupsPackage AS TYPE CURSOR_TYPE IS REF CURSOR; PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE); END; / CREATE OR REPLACE PACKAGE BODY GroupsPackage AS PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE) AS BEGIN OPEN results_cursor FOR SELECT GroupID, GroupName, GroupDescription, LastUpdateDate FROM Groups ORDER BY GroupName; END; END; / Execute procedure in oracle sql plus set autoprint on; var x refcursor; groupspackage.usp_selectgroups(:x); end; / This works in sql plus. But when I try to run the same (or tried everything) in the execute statement in ssis using execute sql task, I am not able to make it work. I am getting an error message. Please advice, what am I doing wrong. Or is this possible or is there any other way to get this working.
View Replies !
View Related
Failed To Process Cub With Oracle Datasource Via Oracle OLE DB Provider
Hi everyone, Could you please help me take a look at the error below? OLE DB or ODBC error: Class not registered. Now I can connect to the Oracle datasource successfully via Oracle OLEDB provider, but failed to process it. I just installed the Oracle Client on my machine, should I installed Oracle OLEDB provider separatly? According to the error above, seems that I should register something.... could you tell me which dll I should re-register..... Thanks^_^ Winnie
View Replies !
View Related
New SSRS Install, Works Great Until I Try To Access Oracle Datasource - System.Data.OracleClient Requires Oracle Client Software
re: new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software From my desktop the report accessing an Oracle 9i db with no problem. I've deployed other reports accesing sql server with no problem. Now I deploy the report and try to access my Oracle source and get: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater On the server in question, I know they've installed the oracle client. I can access the db from sql plus with no problem. As the user that I access ssrs credentials, i remote login and am able to write to oracle home.. C:oracleora10gNETWORKADMIN i've confirmed my database is in tnsnames and it works from sqlplus.
View Replies !
View Related
Scale Greater Than Precision - Not A Valid Instance Of Data Type Real
Our shop recently upgraded to MS SQL 2005 server from the prior SQL 2000 product. I receive and error during processing related to inserting a value into a field of datatype real. This worked for years under MS SQL 2000 and now this code throws an exception. The exception states: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 15 ("@TEST"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. This error is caused by inserting several values that fall outside of a range that MS SQL 2005 documentation specifies. The first value that fails is 6.61242e-039. SQL Server 2005 documentation seems to indicate that values for the datatype real must be - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Why doesn't 6.61242e-039 just default to 0 like it used to? I saw an article that might apply, even though I just use a C++ float type and use some ATL templates. Is my question related to this post?http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201636&SiteID=1
View Replies !
View Related
SSIS Issue Data Type Issue While Loading From Oracle To SQL
Hi, I have SSIS package which is extracting data from oracle. I am using always use defualt code page =true and Defualt code page value=1252. While transferring values in to target table the package is success but when I check the decimal values For example I have 488.9602 or 56.908 values in oracle table but when they come to SQL server they are rounded as 488.0000 or 56.000. How can I populate the correct values from source to target The target table has data type decimal(26,4). Also when I check in the preview of source table I am able to see correct values. This is happening during the transfer only.
View Replies !
View Related
A Date Column From Oracle
Hi, I have a column in my database which came from Oracle database. In this Column I have date but the format is strange for me (Example: 105046).How can I convert this date to MM/DD/YY format in sql 2005? Thanks
View Replies !
View Related
Date Column From Oracle
Hi, I have a column in my database which came from Oracle database. In this Column I have date but the format is strange for me (Example: 105046).How can I convert this date to MM/DD/YY format in sql? Thanks
View Replies !
View Related
Urgent. Output Columns Are Not Appearing When I Use OLEDB Data Source With An Oracle Stored Procedure In Dataflow Task
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ? When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
View Replies !
View Related
Which OLE DB Driver To Use For Oracle DB, Microsoft's Or Oracle's?
Hi everybody, I am currently working on this datawarehouse project, which is using source data from several Oracle databases. There are two different choices when picking OLE DB driver: Oracle provider for Ole DB Microsoft Ole DB Provider for Oracle Which one is the best? And which one will be developed further on? I've read treads that adresses part of this issue, but they are adressing a specific problem, and not which to choose generally. I've heard that Microsoft is not developing their driver, and you should therefore choose Oracle's. Can anybody confirm that? Thanks, Nils Mortensen
View Replies !
View Related
Derived Column Based On Result Of Oracle Query
Hi, I need to create a derived column for each row in a SQL dataset. This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0). One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ? Thanks
View Replies !
View Related
Oracle Error &"ORA-12154&" From An Application Which Never Uses Oracle
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
View Replies !
View Related
DataReader Source Error - Cannot Change The Datatype, Precision Or Scale In The Output Columns
I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors). I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message: Property Value is not valid. The details are: Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed. Error at DataReader Source: System.Runtime.InteropServices.COMException (0xC020837D) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) Any help is greatly appreciated. Dave
View Replies !
View Related
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
I am working on an OLAP modeled database. I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff). I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails. The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output. What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column? I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it. Thanks in advance for any assistance you can provide.
View Replies !
View Related
SqlCeException - The Column Name Is Not Valid
Hello Everybody I'm getting a strange error. I have created a Datatable and in this table I have multiple Columns. I have given them all a name and now when I want to put data in the DataTable I get a error. All goes well until I try to Update the Datatable Code Snippet DataRow DrWb = ppcDbDataSet.Item.NewRow(); DrWerkbon["ItemNummer"] = Defines.mcItem.miItemNr; DrWerkbon["WorkerNummer"] = Defines.mcWorker.msWorkerNr; Defines.mcItem.msWorkerNr = Defines.mcWorker.msWorkerNr; DrWerkbon["CustomNummer"] = Defines.mcCustom.msCustomNr; Defines.mcItem.msCustomNr = Defines.mcCustom.msCustomNr; DrWerkbon["WbRedenBezoek"] = edRedenBezoek.Text; Defines.mcItem.msReason = DrWb["WbReason"].ToString(); ppcDbDataSet.Item.Rows.Add(DrWerkbon); ItemTableAdapter.Update(DrWb); ppsDbDataSet.Clear(); ItemTableAdapter.Fill(ppcDbDataSet.Item); At the ItemTableAdapter.Update(DrWb) it gives the error: SqlCeException was unhandled The Column name is not valid. [ Node name (if any) = , Column name = WbReason ] I don't understand why it is giving this error because the name of the column realy is WbReason Does anybody know what to do about this?? Kind regards Korsten
View Replies !
View Related
Oracle
Has anyone noticed that Oracle can be a little bit difficult to work with at times?? Maybe some compatbility issues as well??
View Replies !
View Related
Oracle To SQL
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#. Thanks, Venkat.
View Replies !
View Related
DTS And Oracle
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. how to do this.
View Replies !
View Related
|
TRACKER
