Recently tried to covert one of my database table pub(authors) in Oracle9i
schema using SQL SERVER's "Import and Export Data". The convertion was succesful nut when i logged in Oracle and tried to select that authors table its show me error...
oracle shows its a table when i issue...
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
authors TABLE / * this table comes from SQL server */
but when i issued as..
SQL> select * from authors;
select * from authors
*
ERROR at line 1:
ORA-00942: table or view does not exist
So, Please help me anyone.I will greatfull to him...Thnaks a Lot.
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 ?
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".
Here in Belgium, we work with a comma as decimal seperator, also in all the web apps... I've tried to update a money table on the sql with following statement update parts set article = '" & art & "' and price= cast(" & p & " as decimal(5,2)) where supid=202 in this example p is a variable and contains figures like 5,7 This statement always give following error Incorrect syntax near the keyword 'as' does some have an idea how to fix this one ?
Actually i tried to convert the datetime value in yyyymmdd format from the int data type field.But except the first record it is looking fine.i cann't understand why this is happening? can you help me to resolve this problem?
Select ABECDT,case when ISDATE(cast(ABECDT as varchar))=1 then cast(ABECDT as varchar) when len(cast(ABECDT as varchar))=8 then right(cast(ABECDT as varchar),4)+''+left(cast(ABECDT as varchar),2)+''+substring(cast(ABRDAT as varchar),3,2) when len(cast(ABECDT as varchar))=7 then right(cast(ABECDT as varchar),4)+'0'+left(cast(ABECDT as varchar),1)+''+substring(cast(ABRDAT as varchar),2,2) when len(cast(ABECDT as varchar))=6 then case when right(cast(ABECDT as varchar),2)<50 then '20' else '19' end +right(cast(ABECDT as varchar),2)+''+substring(cast(ABECDT as varchar),3,2)+''+left(cast(ABECDT as varchar),2) else NULL end from parm
My lookup shows up with this error when i map id column
TITLE: Microsoft Visual Studio ------------------------------ The following columns cannot be mapped: [COPY OF IID, Id] One or more columns do not have supported data types, or their data types do not match. ------------------------------ BUTTONS: OK ------------------------------
Hi, I have an error: "*erro while update quantity. Error converting data type nvarchar to int "while i try update data through form page. Does anybody have any idea how can i correct the error?? I didnt try two methods but both given same error and failed update: - 1) Dim sqlcomm As New SqlCommand(sSaveQuote, rConnect)..... sqlcomm.Parameters.AddWithValue("@employeeID", sUserID) sqlcomm.Parameters.AddWithValue("@quantity", txtquantity.Text)
2) Error converting data type nvarchar to int ?? Dim rConnect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("CRNS_CustomerConnectionString").ConnectionString) Dim command As SqlCommand = New SqlCommand("UpdateOrder", rConnect) command.CommandType = Data.CommandType.StoredProcedure If OrderInfo.Verified.ToUpper = "True" Or OrderInfo.Verified = 1 Then command.Parameters.Add("@Verified", Data.SqlDbType.Bit) command.Parameters("@Verified").Value = 1 Else command.Parameters.Add("@Verified", Data.SqlDbType.Bit) command.Parameters("@Verified").Value = 0 End If command.Parameters.Add("@Comment", Data.SqlDbType.VarChar, 100) command.Parameters("@Comment").Value = OrderInfo.Comment command.Parameters.Add("@ProductID", Data.SqlDbType.Int) command.Parameters("@ProductID").Value = OrderInfo.ProductID command.Parameters.Add("@OrderDate", Data.SqlDbType.SmallDateTime) command.Parameters("@OrderDate").Value = OrderInfo.OrderDate Cheers:) Christe
HI, I'm encouter some data convertion string problem especially during database update use asp.net (vb.net). Please help advice how can i solve the error?? I guess integer value need convert to string before able execute update function.. The error i get: - 1) failed to convert nvarchar to int 2) Convertion string is not in correct format... let say i drag a textbox (txtquantity) and allow input and click update (with call procedure) updatexx set quantity=2 select from YY. Wondering if i missed some important code?? Another data converting errorsituation is: let say i create a class call "AA" then have properties quantity as string, sProductID as string i retrieve the data ........function get info(by ProductID as string) If reader.Read() Then OrdInfo.quantity = reader("Quantity").ToString() OrdInfo.ProductID = reader("ProductID").ToString() ..... sub update BB (byval aa as AA) ... command.Parameters.Add("@Quantity", Data.SqlDbType.Int) command.Parameters("@Quantity").Value = AA.quantity command.Parameters.Add("@ProductID", Data.SqlDbType.Int) command.Parameters("@ProductID").Value = AA.sProductID Cheers:)
I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get data in 5 decimal points? Your help with example would be appreciated. aspx code: <asp:GridView ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt" OnLoad="GridView1_Load" > <Columns> <asp:TemplateField HeaderText="Show"> <ItemTemplate> <asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" /> </ItemTemplate> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:TemplateField> <asp:BoundField DataField="Latitude" HeaderText="Latitude ( ° )" > <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="Longitude" HeaderText="Longitude ( ° )" > <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField>
Hi Guys: I want to convert a String from .net to money in sql server 2005. I use VB.net and sql 2005 stored procedures too. I don’t care if this convert is done in the .net side or in sql 2005 side. This is the exactly case: as I am in south America, for currency or money data type the people write 1234,12 this is similar to the US format 1234.12 (we change the . for the , ) So I want let the people to introduce our format 1234,12 and in the database convert it to 1234.12My problem is that I pass it like string and in the database I do CONVERT(MONEY,RTRIM(LTRIM(@payment))) But it converts my number 1234,12 in 123412.00 and this two number are not equivalent. Somebody can help me please? thanks ! Marcos
Hi, I am trying to update my database i am using the select statement: SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment]; however when i try to update this i am not able to select my date and time columns. Thanks Mike
I had the following user defined function working on a test server.
CREATE FUNCTION dbo.GetFirstDayOfWeek (@WeekNo Integer, @YearNo VarChar(4)) RETURNS DateTime AS BEGIN DECLARE @Date VarChar(10) DECLARE @FirstDayOfWeek DateTime
SET @Date = '01/01' + @YearNo SET @Date = CONVERT(VarChar(10), CONVERT(DateTime, @Date,101) - (DATEPART(DW, @Date)-1),101) SET @FirstDayOfWeek = DateAdd(WW, @WeekNo-1, @Date)
RETURN @FirstDayOfWeek
END
It should return the date of the first day in the week when provided with a week number and year, however, the test server has been rebuilt and after transferring my database over, this function no longer works. I now get the error message "Syntax error converting DateTime from character string".
The database is exactly how it was on the previous test server build - could my problem be caused by the server being setup differently (i.e. different regional settings and therefore different date format) or am I completely missing something in the SQL?
Unfortunately I do not have enough rights on the server to check the SQL Server settings!
Dear all,Tables: COMPANY: COM_ID, COM_NAME, .....PROPERTY: PRP_ID, PRP_NAME, PRP_DATATYPE_ID, PRP_DEFAULT_VALUE( nvarchar)COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE(nvarchar)Use: Without adding new field the user can add new properties to thecompanies just by adding a new property in table PROPERTY and mapping thenew property to a companie. CPRP_VALUE contains all kind of datatypes but isstored as text.Problem: when I query the database ( SP, views, etc) I have problems withfloats and date bacause in the interface ( Access2000.adp) :[color=blue]> the float-format is 0,11 and in TSQL is 0.11>the date-format is DD/MM/YYYY and in TSQL it is YYYY-MM-DD[/color]Can I convert the data within the Stored Procedure for selecting and sortingon those fields.for example: How to sort on CPRP_VALUE containing floatvalues stored asnvarchar ??The client wants to stay with the comma-format because it is common-usedhere.The format can be TSQL-format in the resultset of a Stored Procedure withoutchanging the format in the database because this is used for the output tothe clients.[color=blue][color=green][color=darkred]>>> Same problem with dates !!![/color][/color][/color]thanks,Filip
I'm encouter some data convertion string problem especially during database update use asp.net (vb.net). Please help advice how can i solve the error??
I guess integer value need convert to string before able execute update function..
The error i get: -
1) failed to convert nvarchar to int
2) Convertion string is not in correct format...
let say i drag a textbox (txtquantity) and allow input and click update (with call procedure) updatexx set quantity=2 select from YY.
Wondering if i missed some important code??
Another data converting errorsituation is: let say i create a class call "AA" then have properties quantity as string, sProductID as string
i retrieve the data
........function get info(by ProductID as string)
If reader.Read() Then OrdInfo.quantity = reader("Quantity").ToString() OrdInfo.ProductID = reader("ProductID").ToString()
Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)
'AlertMessage = "City Added"
Catch ex As Exception
AlertMessage = ex.ToString
End Try
Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError
I have a column age ( smallint) in derived transformation .. I would likt to convert it to A or B which is char(1) hwo can i convert smallint to char(1) in derived column? age > 17 ?"A" : "B"
My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.
In my source file i got a date column which is in ISO standards ex: 20050131
I have taken source flat file data type as database date [DT_DBDATE] and in
destination table i declared data type as datetime.
When i start debugging i am getting an error saying that data conversion is not possible.
Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.
I am facing the problem with datatype conversions, the scenario is using derived column transformation for add additional columns and then later i am trying to impliment Slowly changing dimensions(SCM) in my job, while mapping the columns at SCM from Input to Output it gave the error like suppose if i am using the numeric(3,0) at SRC system then i converted it into single byte unsigned at derived column and it recognized at SCD but the job fail while run the package it gave the error as task can not able to conversted given data type to target system dtata type.. ifi am not given the single byte unsigned data type at dervied column at the level of SCD mapping the input to output its not accapring this mapping and return the error as can be convert from system.decimal to system.byte..
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.
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.
