Insert Date Values From SQL Server 2005 To ORacle 10g
Nov 21, 2006I am trying to insert records with a datetime field type from a SQL Server to an Oracle table using SSIS.
Any suggestions to get it to work?
ADVERTISEMENT
How Can I Insert Date Values From Char Values
Jan 4, 1999My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?
Thank's
Insert Values Into Both Table At The Same Time Using Sql Server 2005
Nov 26, 2007hi all,
In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.
table 1:
tid--> int,identity,primary key
tname-->varchar(200)
table 2:
sid-->int,identity,primary key
tid-->fk (this tid is set as foreign key for the tid in table1)
now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..
pls help me..
thanks
swapna
Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)
Apr 30, 2015So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
Linked Server To Oracle Doesn't Fetch Correct Values
Nov 1, 2004I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0 rows..whereas the oracle returns 4 rows..Can some one tell me what will be the problem
SET QUOTED_IDENTIFIER OFF
declare @sql varchar(750)
select @sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed', 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null ORDER BY A.CNUMBER
" + '")'
EXEC (@SQL)
thanks
Priya
Replicate Oracle Date From SQL Server
Oct 25, 2001I want to update Oracle table from SQL Server 7. I need to read data from a table in SQL Server and want to update Oracel table accordingly. Please help.
Thnaks in advance.
Oracle To Sql Server Date Conversion
Mar 12, 2008Hi Gurus,
I need to convert this statement to sql server.
SELECT to_date('24-08-2007 13:11:12','dd-mm-yyyy hh24:mi:ss'),to_date('24-aug-2007 13:11:12','dd-mon-yyyy hh24:mi:ss') from dual
can anyone help please.
ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER
Jul 20, 2005I am having trouble creating an INSTEAD OF trigger in SQL Server toreplicate a BEFORE UPDATE trigger from ORACLE.Here is a sample of the ORACLE BEFORE UPDATE trigger:CREATE TRIGGER myTRIGGER ON MYTABLEbegin:new.DT := SYSDATE;if :new.NM is NULL then:new.NM := USER;end if;end myTRIGGER;It seems as though I have to jump through hoops in SQL Server AND Icannot come up with correct results.Here is a snippet from SQL SERVER (this is what I figured I needed todo after reading various articles,questions):CREATE TRIGGER myTRIGGER on THETABLEINSTEAD OF UPDATEASSELECT * INTO #MYTABLE FROM INSERTEDUPDATE #MYTABLE SET DT = GETDATE()UPDATE #MYTABLE SET NM = USER WHERE NM IS NULLUPDATE THETABLESETDT = (SELECT DT FROM #MYTABLE),NM = (SELECT NM FROM #MYTABLE)WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)Can anyone please shed some light on this? Thanks in advance.
View 4 Replies View RelatedConversion Of Oracle Date Time To Sql Server Date Time In SSIS
Jun 30, 2007This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
T-SQL (SS2K8) :: OPENQUERY Syntax To Insert Into Server Table From Oracle Linked Server
Aug 28, 2014I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedT-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2
Apr 30, 2015table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Native Oracle SQL -&&> SQL Server 2005 CE .sdf File -&&> Using Visual Studio 2005?
May 23, 2007I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:
select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table
There's also a CASE statement in there with some other data transformations.
Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.
For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.
I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!
Can anyone help?
thanks,
Jon
Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql
Apr 29, 2007ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View 1 Replies View RelatedSQL Server 2008 :: Get Max And Min Date Corresponding Values
Aug 26, 2015I have two tables.
Emp:
id | Deptno | sdate | edate | locid
1 | 10 |2014-09-19 |2009-09-25 | 100
1 | 10 |2014-11-06 |2009-11-06 | 100
1 | 10 |2014-12-11 |2014-12-14 | 100
1 | 10 |2009-07-01 |2009-07-20 | 100
2 | 20 |2007-07-11 |2007-07-17 | 104
Empl1:
Id | Deptno | edate | refid | refid1
1 | 10 | 2014-11-06 | 201 | 204
1 | 10 | 2014-12-13 | 204 | 202
1 | 10 | 2014-12-12 | 207 | 124
1 | 10 | 2003-05-04 | 105 | 103
2 | 20 | 2007-07-14 | 102 | 106
I am trying to combine these two tables into the output below:
Id | Deptno | locid | current_refid | current_refid1 | last_refid | last_refid1
1 | 10 | 101 | 201 | 204 | 204 |202
2 | 20 | 104 | 102 | 106 | 102 |106
The tables share the common columns id and deptno. To get the above result, the emp1.edate must be between emp.sdate and emp.edate.
If that condition is met, we need to retrieve the refid and refid1 values corresponding to the lowest edate as last_refid and last_refid1. Then we need the refid and refid1 values corresponding to the emp1.edate between emp.stdate and emp.edate to be retrieved as the current_refid and current_ refid1.
select a.id,a.deptno,a.locid,b.refid,b.refid1
from
emp b
[code]...
SQL Server Insert Multiple Values
Dec 30, 2004I am dynamically creating a sql insert query from a web page that can potentially have multiple values to be inserted into a table. The query I have right now my query is shown below. When it runs, it only inserts one record (the values 24,3). Any ideas what I am doing wrong?
INSERT INTO [tblWebPageByRank]
([WebPageID],[RankID])
Values (24,3,24,5)
Problem Inserting Integers And Date In A Sql Server 2005 Datatable Row And Selecting It Afterwards Based On The Date
May 4, 2007Hi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())�
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
Oracle And SQL Server 2005
Oct 20, 2007I have more than 8 GB Data in oracle and Everyday we have to check some data in oracle but it takes times due to lot of data. So what i was looking that data that we need can import in SQL Server database and do checking purpose. But I was looking some command that connect to oracle brings its data and import in SQL Server. For that we will fire SQL statement to oracle through asp.net and insert into SQL Server I will fire differencial data insert covery only. is there any way or idea to do that please advice me.
View 3 Replies View RelatedSQL Server 2005 Vs Oracle
Apr 6, 2006Anyone know where I can find some good resources to help us choose betweenSQL and Oracle ( Progress Openedge as well ) . Any comments on what youwould choose ?? We are creating a new Warehouse Management System which wilmanage our very large inventory.Anyway comments suggestions welcomeThanksPaul
View 42 Replies View RelatedOracle .dmp To SQL Server 2005
Aug 28, 2007I am trying to import an Oracle .dmp database into SQL Server 2005, what would you suggest as the fastest and easiest way to do this? DTS/SSIS doesn't have an instant solution and the script I tried to use gave me an error...
Msg 3241, Level 16, State 0, Line 1
The media family on device '<DATABASE>' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I would like a GUI based way to do this if at all possible. Thanks
-Kyle
DTS From Oracle To SQL Server 2005
Mar 13, 2008Hello guys,
I'm relatively new to DTS and I was trying to import data from the oracle database to SQL server database based on some parameter from the destination table.
i.e select * from SourceTable where SourceTable.Column1 > (select max(Column1) from DestinationTable)
This tries to search DestinationTable from the source database(Oracle)
How shoud I go to solve this problem?
Any Hint is greatly appreciated....
SQL Server 2005 64-bit && Oracle
Feb 21, 2008
OK new Windows 2003 Enterprise 64-bit with MS SQL Server 2005 64-bit.. Now I am migrating the DTS packages from our olld Windows 2000 Standard with MS SQL 2000 (all 32-bit). I am not moving the packages to SSIS yet just want to move the legacy DTS jobs over... However whenever I try and connect to the Oracle DB I got ORA-12154: TNS:could not resolve the connect identifier specified.
I have the Oracle 10g 32-bit & Oracle 10g 64-bit clients installed and I can run SQL+Plus and do everything just fine. It is just in MS SQL 2005 I can not. Even in SSIS I get the ORA-12154 error. I can create an ODBC and test it and it works just fine... Has anyone here ever fixed this? Some say it is b/c of the ('s around x86 in program files and I have done their suggestion in making a ProgramFilesx86 directory and copy the visual stupid & sql directories there... still nothing. I have one of my DBA's searching Oracle for any patches but thought I would ask and see.
Thanks,
Billy S.
SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values
Jan 13, 2015I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
SQL Server 2005 Versus Oracle
Jun 18, 2007If there is someone who is intimate with both sqlserver and oracle tell me the main differences between the two?
what features are not available in either one? is one better for certain situations over others?
Tutorial For DTS From Oracle To SQL Server 2005
Jan 9, 2008Hi,
I want the tutorial for developing Integration Service for copying the database from Oracle 9i to SQL Server 2005.
If anybody knows then pls give me the steps..
Thanx in advance.
Tuning On Oracle 10g And SQL Server 2005
May 4, 2006Does anyone have some more detailed information about how Oracle and MSimplement / allow Tuning on Oracle 10g and SQL Server 2005 and thedifferences between them?Which of them, In a deep comparison about it, allow better tuning andwhy.Regards,Marcio Evangelista
View 3 Replies View RelatedReplication From Oracle 10g R2 To SQL Server 2005 SP2
Oct 6, 2007Hello All,
I created all the role and logins as described in oracleadmin.sql file and were able to query Oracle tables.
But when I try to crate publisher on Oracle server from sqL server I get 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+Management+Studio&ProdVer=9.00.3186.00&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'oracle_dev' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Failed to execute the HREPL.INITPUBLISHER request to Oracle Publisher 'ORACLE_DEV'. Verify that the Oracle package code exists on the Publisher, and that the replication administrative user account has sufficient permissions.
Changed database context to 'master'.
OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "One or more errors occurred during processing of command.".
OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "ORA-06550: line 1, column 8:
PLS-00201: identifier 'HREPL.INITPUBLISHER' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
".
Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'ORACLE_DEV'. (Microsoft SQL Server, Error: 21651)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=21651&LinkId=20476
--------------------------------------------------------------------------------------------------------------------------------------------------------
I searched the web and could not find any related info, where is this package can I run it manually?
Any help appreciated.
Equivalence Of Oracle's LEAST In SQL Server 2005
Nov 20, 2006Greetings, i just want to know what would be the equivalent sentence of
LEAST (IBA_MPACCIONESXREQ.FECHAFINALAXR,SYSDATE)
in SQL Server 2005. I suposse that i could just use MIN function in this case?
Thank you in advance,
Fernando
Oracle's TO_CHAR, What In SQL Server 2005?
Nov 22, 2006Greetings,
In oracle i use the following sentence TO_CHAR(Table1.DateIn, ,'mm/yyyy') within a query. I need how to replace this for using it in SQL Server. I tried to use the SQL Server CONVERT function but nothing work. Could you help me?
Thank you in advance,
Fernando
Publishing From Oracle To Sql Server 2005
Sep 13, 2005HI guys,
View 3 Replies View RelatedSql Server 2005 Sp2 And Oracle Access
Nov 21, 2006When I try to create a model in Report Builder based on a connection to Oracle 10g database I get the following error.
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
Is this a bug in the CTP version or am I missing something?
Migrating From Oracle To SQL Server 2005
May 30, 2006I am migrating a database from Oracle to SQL Server 2005. I have a problem when migrating code from PL-SQL (oracle labguage) to T-SQL (sql server language). My problem is: there is pseudo-column in oracle called level that return the level from a register in a tree view of a select (a parent-child relationship). I would want to know how to translate that level column to T-SQL. Thanks!
View 3 Replies View RelatedWhat Are The Differences Between MS SQL Server 2005 && Oracle 9i ?
Aug 5, 2006Hi guyz,
I have basic knowledge of Sql Server 2005 and now i wanna move ahead in Oracle 9i !
So, i have 2 questions here -
a)Whats the Difference between Sql Server 2005 and Oracle 9 i ?
Note: Please keep the discussion general so that student like me can understand. I have never seen Oracle but the industry requirements suggest that Oracle is way better than than SQL Server 2005. But thats what i think
b)I am Running Windows XP SP 2 and i would like to practise Oracle 9i. So, Where i can download it for free ? I know i have checked there website but they don't mention the difference between each version. Oracle does't market their products as well as Microsoft.
Please , I am Student .. so i request you to make the discussion general .
Thank you for your time.
SQL Server 2012 :: Date Sequence Missing Values?
Apr 27, 2015Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'
-- test data
;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL
[code]....