|
|
Hi All.
I Have a SQL Server.This server have link server to Oracle DB.
I can select anything from oracle DB through this SQL server such as.
Select * from [ORacleDBName]..[SIDName].[OracleTable]
But now I don't know how to update oracle db from SQL.
Pleas help me.
|
|
|
|
|
|
I also want to create a new table in oracle DB through SQL Server such as.
Select * from SQL Server into OracleLinkDB.
|
|
|
|
|
I would suggest you study the documentation for both Oracle and SQL.
|
|
|
|
|
If you follow the link Richard supplied and look down the left menu - 18 items down you will find some interesting reading.
You need to learn to do some thinking and research on your own!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select * from SQL Server into OracleLinkDB is SQL Server Syntax.
Oracles syntax is CREATE TABLE new_table AS (SELECT * FROM old_table);
I'd recommend checking out techonthenet.com[^] for comparing the syntax of different databases.
|
|
|
|
|
I Mean I Want to update Oracle DB through SQL Server Management Studio via Link Server.
|
|
|
|
|
Hello all. I have the following Stored Procedure:
ALTER PROCEDURE [EventPortion].[24Start]
AS
IF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
Begin
WAITFOR DELAY '00:00:01.000';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
I want this code to drop the 3 tables if they exist. This works.
Then run the first Select Into.
IntegratedTest1.Event.MOE_2_4_DE_4_Q2 depends on (Selects from) 244Q1. The problem that I am having is that the second Select Into is being executed and I am getting a binding error that 244Q1 doesn't exist. So, the While Is Null Wait For loops are not working. If I just execute the Wait For 1 second delay, that does take 1 second to execute. If I just run the first select into and the 1 second delay, it takes about 1 second to execute. But if I run the first and second select into with the delays in between, there is no delay. I immediately get the binding error.
Is there anything I can do about this seemingly odd behavior?
Thanks,
David
Edit: I figured out a solution and wanted to post it.
I modified the stored procedure to:
ALTER PROCEDURE [EventPortion].[24Start]
AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[245Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[245Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[245Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q3
I didn't mention before that this stored procedure is run from a C# front-end. I have code which extracts the SQL from the stored procedure and passes it to the following method:
private void ParseStartProcedure(string spText)
{
spText = spText.Replace("\r", " ");
spText = spText.Replace("\t", " ");
spText = spText.Trim();
string[] queryPieces = Regex.Split(spText.ToLower(), " as ");
string[] queryLines = Regex.Split(queryPieces[1], "\n");
for (int i = 0; i < queryLines.Length; i++)
{
if (queryLines[i] != "")
{
string query = queryLines[i];
SqlCommand cmd = new SqlCommand(query, m_dbSettings.sqlMetadataDbConn);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string message = ex.Message;
}
}
}
}
modified 22-Sep-16 19:41pm.
|
|
|
|
|
Dear all,
Currently, I need to create a database for control Process in manufacturing.
One Item have got many LotNo,
One LotNo thought many Process(ex: Process A, Process B,Process C...)
with every Process maybe yes or maybo no use Material
How can I build database for:
Select all Lot no and Process by ItemNo
Slect all Process and status in every Process by LotNo
Input ItemNo show all information about Material used on every Process
This is my database:
Item(Id_Item,ItemNo,ItemName)
Lot(Id_Lot,Item_Id,LotNo)
Process(Id_Process, ProcessNo, ProcessName)
Lot_Process(Id,Lot_Id,Process_Id)
Material(Id,Process_Id,MaterialName, Quantity)
|
|
|
|
|
I am importing Data from excel-sheet to our sql server which comes from another system. Example In our sql server database Client name is Zee Entertainment Limited but in Another System Client Name is Zee Ent. ltd. or any other name format. In this way there are 10 thousands of records which is having data variations and We can not change our data which is connected to another business.
Kindly give me solution.
Thanks & Regards
Shankar Chaurasia
|
|
|
|
|
Create a mapping table that maps the company name from the 2 source systems to your system.
This will work but is a solution fraught with disaster, presumably there is no control of the source systems input (excel as a data source  ) so whenever a user miss types a company name your mapping goes into the garbage.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would take a look at fulltext search.
You can add synonyms to the index, and it can also search for proximity terms (spilling errors).
The subject is much to big to cover in a forum answer, but you can start reading here[^] to get a feeling for what can be done.
|
|
|
|
|
Hello,
I'm having some problems getting an update running. I have done similar updates some times without problems (MySql instead of MariaDB). Maybe I'm too tired today to see the problem, it was a long working day...
Maybe someone can illuminate me 
Table:
CREATE TABLE ps_data.test (
Test_ID int(11) NOT NULL AUTO_INCREMENT,
Idx int(11) NOT NULL,
Grp int(11) DEFAULT NULL,
PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
Sample-Data:
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);
Update commands (none working):
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1
UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1
The first update command produces even a null result on the MAX() function. Strange...
Best Regards,
Raul
modified 17-Sep-16 15:09pm.
|
|
|
|
|
Have you tried the update statement as a select just to see if it returns the expected value.
I would also change select * from test to select Idx from test
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
yes, normally I would use Select Idx, but on samples I use the most simplified syntax.
The bad news is that the first two examples work on MySql but not on MariaDB. The third syntax doesn't work on any DBMS, but I never used it and got it yesterday from the internet because I was desperated.
I always thought that MariaDB is fully compatible to MySql, but this is unfortunatelly not true. Really bad news for those who migrated a productive system from MySql to MariaDB and are using systax like that 
|
|
|
|
|
In terms of db object like table,store proc,view ect what are difference is there between sql server express and sql server localdb ?
What we can do with sql express and what we can't with localdb? What missing in localdb compared with sql express db?
please share the knowledge.
tbhattacharjee
|
|
|
|
|
Tridip Bhattacharjee wrote: please share the knowledge.
Has already been shared. Google, and you'll find blogs/articles like this[^] one.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi
I'm running the below query, I want to get records for multiple fundCodes in this case Bateleur and Tower Fund.I'm only getting records for Bateleur only from 2016-04-20 to 2016-04-29.
Thank you
Declare @FundCodes VarChar(Max)='Bateleur|Tower Fund',
@StartDate DateTime='2016/04/20',
@EndDate DateTime='2016/04/29',
@Zero integer=1,
@LongShortAll integer = 0,
@PhysEff integer = 0
select fulldate into #TableDates
from AssetData.dbo.Calendar cal where cal.FullDate between @StartDate and @EndDate
and IsWeekDay = 1 and IsHoliday = 0
SET NOCOUNT ON;
declare @NextDate DateTime
set @NextDate = @StartDate
declare @TempValues table(FundCode varchar(200),EffectiveDate DateTime, Value Float)
declare @fundcodestable table (ID int, fundcode varchar(300))
declare @Val float
declare @DateCounter int = 0
select @DateCounter = count(Fulldate) from #TableDates
declare @Date date
while @DateCounter <> 0
begin
set @Date = (select top 1 fulldate from #TableDates order by 1)
insert into @fundcodestable
select pn, s
from StagedFundReportingData..FnxSplit('|', @fundcodes)
Declare @counterFundId int = 0
select @counterFundId = count(fundcode) from @fundcodestable
declare @fundC varchar(300)
while @counterFundId <>0 and @NextDate<=@EndDate
begin
set @fundC = (select top 1 fundcode from @fundcodestable)
Set @Val = 0
if @Zero = 0
begin
if @LongShortAll = 0 begin
exec GetFundValue @fundC, @NextDate, @Val output
end else begin
if @LongShortAll = 1 begin
if @PhysEff = 0 begin
exec GetFundValueLongOnly @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueLongOnly @fundC, @NextDate, @Val output
end
end else begin
if @PhysEff = 0 begin
exec GetFundValueShortOnly @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueShortOnly @fundC, @NextDate, @Val output
end
end
end
end
else
begin
If @LongShortAll = 0 begin
exec GetFundValue_ZeroFutures @fundC, @NextDate, @Val output
end else begin
if @LongShortAll = 1 begin
if @PhysEff = 0 begin
exec GetFundValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
end
end else begin
if @PhysEff = 0 begin
exec GetFundValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
end else begin
exec GetFundUnderlyingEffectiveValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
end
end
end
end
Set @Val = isnull(@Val,0)
if @Val <> 0
begin
insert into @TempValues (FundCode,EffectiveDate, Value) Values (@fundC,@NextDate, @Val)
end
if datepart(dw,@NextDate) = 7
begin
Set @NextDate = DATEADD(Day,2,@NextDate)
end
else
begin
if datepart(dw,@NextDate) = 6
begin
Set @NextDate = DATEADD(Day,3,@NextDate)
end
else
begin
Set @NextDate = DATEADD(Day,1,@NextDate)
end
end
end
delete #TableDates where FullDate = @NextDate
select @DateCounter = @DateCounter-1
end
Select * from @TempValues order by FundCode
drop table #TableDates
|
|
|
|
|
And what is your question?
|
|
|
|
|
Just a suggestion. Print the results from the following
insert into @fundcodestable
select pn, s
from StagedFundReportingData..FnxSplit('|', @fundcodes)
|
|
|
|
|
Thank you Jschell, I managed to fix the problem.
|
|
|
|
|
Hi,
I have a Oracle DB in which all the information is stored and this information is been consumed by different teams and below is the two options i have to decide on :
1. Write a Stored procedure and give the stored procedure to the respective teams and they can call the stored procedure.
2. Write a stored procedure wrap it as an API and expose the API, different team will call the API exposed and API will in turn hit the SP and return the response to the team.
Like to know what is the PRON and CRONS with these options and what is the best possible solution to go with.
Thanks
|
|
|
|
|
|
We are working on separate computers in different locations so we are not connected to a network.
We are using SQL Server 2016 Express to develop a C# application in Visual Studio (college project).
My group mate sent me the (.bak) and (.mdf) files of the database he was working on so I can view it and make the necessary updates. I saw the data in the files after restoring and attaching them respectively. Some columns that were in (.bak)file were not in the (.mdf) file, and I need the (.mdf) file to be updated/contain the same data with the .bak file so I can add a datasource to the application for easy retrieval of data.
What could possibly have been the problem?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
