|
Thank you for your answer Richard MacCutchan. I have my own notes about Information System concept (I wrote it from there), I invented a case study related to the concept, to know if I understand well the concept or not.
|
|
|
|
|
Hi all.
I Have a SQL server 2008 DB.I also have some Oracle Database.Now I Want to get data from Oracle DB to SQL server DB.
I also read some post about link server but I still not success connecting to Oracle DB.
For example some information about oracle DB that I can connect by using SQL developer
IP:10.149.35.132
Username:abc
pass:bcd.
Instance:bms
|
|
|
|
|
Instead of posting your connection information try letting us know how you tried to connect and what went wrong
|
|
|
|
|
Thanks for your reply.
I Have try some action to add linked server to Oracle DB but I failed.If anybody have done please help me.
Chat with me and i will give you Teamviewer ID to see.
Thanks.
|
|
|
|
|
THe error here
The linked server has been created but failed a connection test. Do you want to keep the linked server?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
SQL Server Network Interfaces: Connection string is not valid [87].
OLE DB provider "SQLNCLI10" for linked server "BIM" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "BIM" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 87)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=87&LinkId=20476
|
|
|
|
|
You've provided the wrong credentials to connect
|
|
|
|
|
I can connect to Oracle now.The first Oracle DB is OK, but now I want to connect to two or more oracle at the same time.
The second ORalce is fail.
|
|
|
|
|
|
I Can connect to one OracleDB now.Tomorrow I Will try the link you post and connect to the second Oracle DB and report here.Thanks for your help.
|
|
|
|
|
The tnsnames.ora is here
PMCalculate =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.138)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = NMS)
)
)
BIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nms)
)
)
BIM is OK, PMCalculate is fail
Do I need to restart something each time I add a DB to tnsnames.ora
|
|
|
|
|
I didn't think you had to do a restart each time, but it might be worth trying.
But first, if you take BIM out of the .ORA does PMCalculate still fail?
I notice SERVICE_NAME is in capitals for PMCalculate but lower-case for BIM ... is there a case-sensitivity issue here?
Are you sure that the Oracle Server on Host 192.168.2.138 is listening on the default port (1521)? (Reference in case you need to check ... Managing Oracle Database Port Numbers[^])
Beyond that I'm running out of ideas, sorry.
|
|
|
|
|
Thanks you.
The other is not Service Name, SID.
Everything is ok now.
|
|
|
|
|
Hi All,
I have two tables 1st table is Employee (Id, Name, Level) another one is EmployeeRelationShip (Id, EmployeeId, ManagerId), in Employee table all level 1's are Managers and Level 2's are workers.
In EmployeeRelationShip table all Employees would have their ManagerId as Manager's Employee Id. Now for this structure I want to Order by Name for all managers, again under each manager I want to order all his Employees order by name again?
Can anybody please help me in writing a script for me for this scenario? Any help may be a code snippet or a link or even a suggestion would be much helpful. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Let us assume that you have following data in Employee table
Id Name Level
1 zzzz 1
2 nnnn 1
3 dddd 1
4 df 2
5 bc 2
6 za 2
7 az 2
8 ff 2
9 ef 2
and these data in EmployeeRelationShip table
Id EmployeeId ManagerId
1 4 1
2 5 1
3 6 2
4 7 2
5 8 3
6 9 3
This query will give results ordered by Manager Name and then by Employee Name:
select e.Name as ManagerName, b.Name as EmpName from
EmployeeRelationShip r inner join
Employee e on e.Id = r.ManagerId
inner join Employee b on b.Id = r.EmployeeId
order by e.Name, b.Name
And the output will be:
ManagerName EmpName
dddd ff
dddd ef
nnnn za
nnnn az
zzzz df
zzzz bc
Hope this provides an idea for you to solve your problem.
|
|
|
|
|
OP asked for Quote: I want to Order by Name for all managers, again under each manager I want to order all his Employees order by name again?
Your query is only listing the workers
|
|
|
|
|
You can use a recursive CTE to traverse the employee hierarchy - there a good example on the MSDN site - Recursive Queries Using Common Table Expressions[^]
Yours is a little more complicated because you have the extra table, which could have been replaced by a single column on the Employee table "ReportsTo". It is also a little more complicated because you want the Managers in Name order followed by the Employees (for each Manager) also in Name order.
This query seems to do what you want (I've used the sample data provided in the earlier response)
;WITH emps AS
(
SELECT e.Id, e.[Name], e.[Level], er.ManagerId as ReportsTo, em.Name as ManagerName
FROM Employee e
left outer join employeeRelationship er on er.EmployeeId = e.id
left outer join employee em on er.ManagerId = em.id
), Emp_CTE AS (
SELECT Id, [Name], [Level], ReportsTo,
MtoE = CAST(CAST(isnull(ManagerName,'') AS CHAR(30)) + '/' + CAST([Name] AS CHAR(30)) AS VARCHAR(MAX))
FROM emps e
WHERE ReportsTo IS NULL
UNION ALL
SELECT e.Id, e.[Name], e.[Level], e.Id as ReportsTo,
MtoE = MtoE + '/' + CAST(e.[Name] AS CHAR(30))
FROM emps e
INNER JOIN Emp_CTE ecte ON ecte.Id = e.ReportsTo
)
SELECT Id, [Name], [Level]
FROM Emp_CTE
order by MtoE
Giving the results you asked for:
Id Name Level
3 dddd 1
9 ef 2
8 ff 2
2 nnnn 1
7 az 2
6 za 2
1 zzzz 1
5 bc 2
4 df 2
The explanation:
The first CTE emps is just to simplify the table structure as I can't use a left outer join in the recursive CTE that follows it. If you run that query by itself you get the following
Id Name Level ReportTo ManagerName
1 zzzz 1 NULL NULL
2 nnnn 1 NULL NULL
3 dddd 1 NULL NULL
4 df 2 1 zzzz
5 bc 2 1 zzzz
6 za 2 2 nnnn
7 az 2 2 nnnn
8 ff 2 3 dddd
9 ef 2 3 dddd
The second CTE Emp_CTE is a recursive query that first gets only the Managers (marked as the "Anchor member" in the comment in the code). Those results are then fed into the "Recursive member" of the CTE until all of the employees have been processed.
The "clever" bit is the derived column MtoE that "tracks" the levels of recursion, capturing first the Manager's name and then the Workers' name. I've cast the individual names to CHAR(30) to ensure that the subsequent ORDER BY works properly - i.e. making sure all of the names are the same length, padded by spaces if necessary. You might need to change the 30 to a larger number if you have some long names. Also note in the Anchor member I've cast the combination of names to be a VARCHAR as the column grows in length the further down the recursion you have to go (e.g. if there was a level 3 employee).
The very last bit just captures the data from Emp_CTE and ORDERs it BY the derived column so the output appears in name order.
|
|
|
|
|
Greetings,
I need to know how the recursive common table expression work behind the scene specifically when the recursive member returned with many result entries not a single result entry (i.e., single row) for example, the anchor member gets only one manager, and the first recursive member gets 3 employees follow that manager and they are, the 3 employees, in their turn are also managers to other employees etc. I hope that my question is clear
|
|
|
|
|
You might want to share the code for the recursive CTE that you are referring to
|
|
|
|
|
WITH TradingReceivableAccounts (AccountID, AccountCode, AccountName, GeneralAccount, AccountBranch, NatureOfAccount, AccountType, DebtorValue, CreditorValue, [Entry], [Description]) AS
(SELECT at.Account_ID,
at.Account_Code,
at.Account_Name1,
at.Account_Isleaf,
aib.AccountBranch_BranchID,
aib.AccountBranch_Natural,
aib.AccountBranch_Type,
ISNULL(aib.AccountBranch_LocalDebit, 0) AS DebtorValue,
ISNULL(aib.AccountBranch_LocalCredit, 0) AS CreditorValue,
CAST(NULL AS NVARCHAR(250)) AS [Entry],
N'TradingReceivableAccounts' AS [Description]
FROM AccountTree AS at INNER JOIN Account_InBranch AS aib
ON at.Account_ID = aib.AccountBranch_AccountID
AND aib.AccountBranch_Natural = 1
AND aib.AccountBranch_Type = 2
AND aib.AccountBranch_BranchID = 1
UNION ALL
SELECT ac.Account_ID,
ac.Account_Code,
ac.Account_Name1,
ac.Account_Isleaf,
CAST(0 AS INT) AS AccountBranch,
CAST(0 AS BIT) AS NatureOfAccount,
CAST(0 AS TINYINT) AS AccountType,
jd.JournalDet_Debit,
jd.JournalDet_Credit,
CAST(jh.Journal_Code AS NVARCHAR(250)),
N'TradingReceivableAccounts' AS [Description]
FROM TradingReceivableAccounts AS CTE INNER JOIN AccountTree AS ac
ON CTE.AccountID = ac.Account_ID
INNER JOIN Journal_Details AS jd
ON jd.JournalDet_AccountID = ac.Account_ID
INNER JOIN Journal_Head AS jh
ON jh.Journal_ID = jd.JournalDet_HeadID
AND jh.Journal_BranchID = CTE.AccountBranch
AND jh.Journal_Date < N'02/18/2016')
The above query have the same idea. The anchor member returns a specific types of financial accounts and the recursive member gets all the payments\receipts transactions associated with each account. For example, lets say the anchor query returns the accounts 1, 2, 3, and 4 and with each account returned the recursive member will return all the payments\receipts associated with the account. I need to know how it will work behind the scene.
|
|
|
|
|
You have already largely described what will happen "behind the scenes".
The Anchor member - i.e. the bit of the query after AS and before UNION ALL, is going to return a set of data in which you are interested.
The Recursive member - i.e. the bit after the UNION ALL, keeps calling itself until it has used all of the data from the anchor.
Recursion is usually used when there is a hierarchy or a sequence involved. Perhaps the simplest example I've seen is this SQL SERVER - Simple Example of Recursive CTE - Journey to SQL Authority with Pinal Dave[^]
However, looking at your query and from your description it strikes me that all you need is a simple join rather than a recursive CTE.
[EDIT] - I gave this some more thought and would like to offer this example of what is going on under the covers of an rCTE.
Imagine a simple table (I'm using a cut down version of the table in the Northwind sample database)
use Sandbox
if exists (select * from sysobjects where id = object_id('dbo.Employees') )
drop table "dbo"."Employees"
GO
CREATE TABLE "Employees" (
"EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
"LastName" nvarchar (20) NOT NULL ,
"FirstName" nvarchar (10) NOT NULL ,
"Title" nvarchar (30) NULL ,
"ReportsTo" "int" NULL
)
I've populated this with the same data as the sample database...
INSERT "Employees" VALUES('Davolio','Nancy','Sales Representative',2),
('Fuller','Andrew','Vice President, Sales',NULL),
('Leverling','Janet','Sales Representative',2),
('Peacock','Margaret','Sales Representative',2),
('Buchanan','Steven','Sales Manager',2),
('Suyama','Michael','Sales Representative',5),
('King','Robert','Sales Representative',5),
('Callahan','Laura','Inside Sales Coordinator',2),
('Dodsworth','Anne','Sales Representative',5)
And created this rCTE query to traverse the hierarchy
;WITH Emp_CTE AS (
SELECT EmployeeID, ReportsTo, LastName, FirstName, Title, 1 as RLevel
,MtoE = CAST(isnull(ReportsTo,0) AS VARCHAR(MAX)) + '/' + CAST(EmployeeID AS VARCHAR(MAX))
FROM Employees WHERE ReportsTo IS NULL
UNION ALL
SELECT e.EmployeeID, e.ReportsTo, e.LastName, e.FirstName, e.Title, RLevel + 1
,MtoE=MtoE + '/' + CAST(e.EmployeeID AS VARCHAR(MAX))
FROM Employees e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ReportsTo
)
SELECT EC.EmployeeID, EC.ReportsTo, EC.LastName, EC.FirstName, EC.Title, RLevel, MtoE
FROM Emp_CTE EC
If you examine the results
Emp.Id Manager LastName FirstName Job Title Level RecursionPath
2 NULL Fuller Andrew Vice President, Sales 1 0/2
1 2 Davolio Nancy Sales Representative 2 0/2/1
3 2 Leverling Janet Sales Representative 2 0/2/3
4 2 Peacock Margaret Sales Representative 2 0/2/4
5 2 Buchanan Steven Sales Manager 2 0/2/5
8 2 Callahan Laura Inside Sales Coordinator 2 0/2/8
6 5 Suyama Michael Sales Representative 3 0/2/5/6
7 5 King Robert Sales Representative 3 0/2/5/7
9 5 Dodsworth Anne Sales Representative 3 0/2/5/9 Those last two columns sort of describe the goings-on in the background. You can add something similar to your own query to see what is going on (I haven't used your query because there is no sample data for me to set up the appropriate tables)
modified 21-Feb-16 11:50am.
|
|
|
|
|
Greetings,
I am so grateful for your interesting in answering me  thanks a lot. Recall I know how the recursive CTE works behind the scene in is simplest for when the anchor query will have a single result each time, for example, Employee A is the CEO, Employee B reports to Employee A, Employee C reports to Employee B, Employee D reports to Employee C, and Employee E reports to Employee D. So the chain of management will look like:
B reports to A
C reports to B
D reports to C
E reports to D
But what if the management was looking like this: (A, B) -> (C, D, E) -> (F, G, H, I, J) where
C, D report to A
E report to B
F reports to C
G, H report to D
I, J report to E
So the first anchor member will return A and B those will be passed to the recursive member to get the C, D, and E etc. what I need to know is that is the mechanism of the recursion will go through A until it encounter NULL then go through B all way until it encounter NULL. I hope you got what I mean.
1- A, B
2- C, D
3- F
4- G, H
5- E
6- I, J
Is that how it will go?!
|
|
|
|
|
Depends on the database, Sql-server is breadth first, so it will return A,B,C,D,E,F,G,H,I,J.
Oracle on the other hand can do both Breadth first or Depth first via a parameter, and in Depth first it would return A,C,F,D,G,H,B,E,I,J.
How other databases handle things I don't know, but Breadth first is a good guess.
<edit>You can quite easy get the result you want by adding a simple order by recursionpath or order by level</edit>
modified 22-Feb-16 3:33am.
|
|
|
|
|
This is really something that you need to observe for yourself.
Try adding the following data to my sample table from my solution:
INSERT "Employees" VALUES('1Davolio','Nancy','Sales Representative',11),
('1Fuller','Andrew','Vice President, Sales',NULL),
('1Leverling','Janet','Sales Representative',11),
('1Peacock','Margaret','Sales Representative',12),
('1Buchanan','Steven','Sales Manager',11),
('1Suyama','Michael','Sales Representative',15),
('1King','Robert','Sales Representative',15),
('1Callahan','Laura','Inside Sales Coordinator',11),
('1Dodsworth','Anne','Sales Representative',15)
Run the query unchanged and observe the results:
2 NULL Fuller Andrew Vice President, Sales 1 0/2
11 NULL 1Fuller Andrew Vice President, Sales 1 0/11
10 11 1Davolio Nancy Sales Representative 2 0/11/10
12 11 1Leverling Janet Sales Representative 2 0/11/12
14 11 1Buchanan Steven Sales Manager 2 0/11/14
17 11 1Callahan Laura Inside Sales Coordinator 2 0/11/17
13 12 1Peacock Margaret Sales Representative 3 0/11/12/13
1 2 Davolio Nancy Sales Representative 2 0/2/1
3 2 Leverling Janet Sales Representative 2 0/2/3
4 2 Peacock Margaret Sales Representative 2 0/2/4
5 2 Buchanan Steven Sales Manager 2 0/2/5
8 2 Callahan Laura Inside Sales Coordinator 2 0/2/8
6 5 Suyama Michael Sales Representative 3 0/2/5/6
7 5 King Robert Sales Representative 3 0/2/5/7
9 5 Dodsworth Anne Sales Representative 3 0/2/5/9
|
|
|
|
|
Compensating unfair downvote.
|
|
|
|
|
Thank you!
Sometimes I think people think we are psychic 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
