?
Solved

Data from multiple databases

Posted on 2005-03-10
23
Medium Priority
?
226 Views
Last Modified: 2012-08-14
I need to create a table on my server that is comprised of data from another server. Here's the thing, the new data has to come from 2 different databases on the other server. Is it possible to import data from 2 databases. If so, could someone give me an example statement. Thanks.
0
Comment
Question by:Mike Miller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 3
  • +2
23 Comments
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13510331
Most queries you see use a simple table name, like:

   select * from Customers

Technically the table has an owner, which is usually the DBO alias, so the query could also look like:

  select * from dbo.Customers

If the table exists in another database on the same server, you'd use a three-part name:

   use MyDatabase
   select * from YourDatabase.dbo.Customers

If the table is found on a different server you'd use a four-part name:

   select * from YourServer.YourDatabase.dbo.Customers

The trick is registering the remote server as something the local SQL Server can query. This can get very involved if the remote server uses different security, for example, exists in a different domain and/or is administered by different users.  

-- Craig Yellick
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 13510332
Hi mwmiller78,
you have to use 3 part names in your select. that's it :)

i.e.

Select  t1.*, t2.*
From
db1.dbo.table1 t1 LEFT JOIN db2.dbo.table2 t2  ON .....


If you are not using  dbo make sure that you pot 2 periods

.
db1..t1


Cheers!
0
 

Author Comment

by:Mike Miller
ID: 13510615
Here's what I tried...Where am I going wrong?

SELECT     [F088SQL1].[InventoryPlanning].[MRPHeader].[ProductNbr], [F088SQL1].[InventoryPlanning].[MRPHeader].[Kardex],
                      [F088SQL1].[InventoryPlanning].[MRPHeader].[OnhandQty], [F088SQL1].[InventoryPlanning].[MRPHeader].[BinLocationId],
                      [F088SQL1].[mfg_DATAWH].[Parts].[PART_DESC], [F088SQL1].[mfg_DATAWH].[Parts].[PSC]
FROM         [F088SQL1].[InventoryPlanning].[MRPHeader] LEFT JOIN
                      [F088SQL1].[mfg_DATAWH].[Parts]
WHERE     [F088SQL1].[InventoryPlanning].[MRPHeader].[ProductNbr] LIKE [F088SQL1].[mfg_DATAWH].[Parts].[PART_NUMBER]
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Expert Comment

by:Navicerts
ID: 13510669
Not sure that it makes a diffrence, but i would have done it more like this...


SELECT     [F088SQL1].[InventoryPlanning].[MRPHeader].[ProductNbr], [F088SQL1].[InventoryPlanning].[MRPHeader].[Kardex],
                      [F088SQL1].[InventoryPlanning].[MRPHeader].[OnhandQty], [F088SQL1].[InventoryPlanning].[MRPHeader].[BinLocationId],
                      [F088SQL1].[mfg_DATAWH].[Parts].[PART_DESC], [F088SQL1].[mfg_DATAWH].[Parts].[PSC]
FROM         [F088SQL1].[InventoryPlanning].[MRPHeader] LEFT JOIN
                      [F088SQL1].[mfg_DATAWH].[Parts]
ON     [F088SQL1].[InventoryPlanning].[MRPHeader].[ProductNbr] = [F088SQL1].[mfg_DATAWH].[Parts].[PART_NUMBER]
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13510692
The column names are handled differently from the table names.  With columns, either the name is unambigious and needs no qualifier, or has the table name (or alias) of the table from which it should come.

So your problem is that the columns should include only the table name and column name.

Start with a very simple query against a remote table, then move to JOINS.

  select * from [F088SQL1].[InventoryPlanning].[MRPHeader]

-- Craig Yellick
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13510721
I just noticed that you're using a three-part name.  The fragment below --

         ... from [F088SQL1].[InventoryPlanning].[MRPHeader]

-- reads "from MRPHeader table that is owned by the InventoryPlanning user from the F088SQL1 database".  Doesn't sound right, I think you mean:

    ... from [F088SQL1].[InventoryPlanning].dbo.[MRPHeader]

which reads, "from the dbo owner's MRPHeader table in the InventoryPlanning database that's located on the F088SQL1 server".

-- Craig Yellick
0
 
LVL 7

Expert Comment

by:Navicerts
ID: 13510729
Or maybe like this.


SELECT    
                      IP.[ProductNbr],
                      IP.[Kardex],
                      IP.[OnhandQty],
                      IP.[BinLocationId],
                      MFG.[PART_DESC],
                      MFG.[PSC]
FROM        
                       [InventoryPlanning].[MRPHeader] As IP
LEFT JOIN
                       [mfg_DATAWH].[Parts] As MFG
ON    
                      IP.[ProductNbr] = MFG.[PART_NUMBER]
0
 
LVL 7

Expert Comment

by:Navicerts
ID: 13510760
I think CraigYellick just figured out what was bothering me about it, give hm the points i couldn't figure out hwat looked wrong :)
0
 

Author Comment

by:Mike Miller
ID: 13510942
Except that I'm not the dbo on any of the databases on F088SQL1, so I get this

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I run this:

SELECT     *
FROM         [F088SQL1].[InventoryPlanning].dbo.[MRPHeader]

I have access to the table though.
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13511079
That's what I meant when I wrote, "The trick is registering the remote server as something the local SQL Server can query. This can get very involved if the remote server uses different security, for example, exists in a different domain and/or is administered by different users."

Using SQL Enterprise Manager, drill into the tree

    (Your Local Server)
        Security
           Linked Servers

Right-click on the Linked Servers icon and add a new linked server.  Enter the remote server name and select "SQL Server" as the server type, then examine the Security tab and configure it as needed. If you have the juice on the remote server, select "be made using the login's current security context".

Security is the big hassle.

-- Craig Yellick
0
 

Author Comment

by:Mike Miller
ID: 13511209
I already have a linked server to F088SQL1.
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13511302
The "annon" part of the error message makes me think that the linked server's security config is set wrong. If you know for sure your user account has access to the MRPHeader table then set the security config to "be made using the login's current security context".

-- Craig Yellick
0
 

Author Comment

by:Mike Miller
ID: 13511349
That's how it is set up.
I can access the table fine if I manually go through EM.
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13511377
Hmm. What's the excution context of the query? Does it work in Query Analyzer when using integrated security?

If your user account has permissions, yet the error message reports NT AUTHORITY\ANONYMOUS LOGON, then somewhere along the line your identity is being dropped.

-- Craig Yellick
0
 

Author Comment

by:Mike Miller
ID: 13511495
When I return all rows from the table I did notice that the statement it's using is
SELECT * from dbo.MRPHeader

as opposed to

SELECT * from MRPHeader

I ran

select * from MRPHeader in query analyzer and it worked.

0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13511551
The real test in Query Analyzer is to issue the query that's usign the four-part table name. You'll need to connect QA to the local server and issue the statement against the remote server. If it works in QA then you know the execution context is not providing your identity.

For example, if the query is coming from a web application it's almost certainly not passing your user identity through to the web page unless someone explicitly configured the web site to do that.

I'd expect both of these to work in QA when connected to the remote server.

   SELECT * from MRPHeader
   SELECT * from dbo.MRPHeader

This is the one that's the real test when executed from the local server against the remote.

   SELECT * from [F088SQL1].[InventoryPlanning].dbo.[MRPHeader]


-- Craig Yellick
0
 

Author Comment

by:Mike Miller
ID: 13511632
From my server:

Query - F088SQL1.InventoryPlanning.EPM-NA\srmsql

I ran

SELECT * from [F088SQL1].[InventoryPlanning].dbo.[MRPHeader]

and it worked fine.

One thing...I'm logged in as srmsql to my server not EPM-NA\srmsql
make a difference?
EPM-NA is our domain
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13511695
If I'm reading this correctly, you're connected to F088SQL1 and issued a 4-part query against that same server. This isn't really testing anything since the query would work fine without linking a server and doing a 4-part name.

We should step back and make sure we're all on the same page.  Server F088SQL1 obviously contains the InventoryPlanning database and MRPHeader table. Those are the only components discussed so far. What is the name of the OTHER server and database that needs to be involved in the query?

I see a reference to a table named mfg_DATAWH. If that table is on the F088SQL1 server then you don't need a linked server and a 4-part name.  A 3-part name will do, and will not introduce the more complicated security problems.

-- Craig Yellick
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13512318
I believe this is what Craig is suggesting:

SELECT      m.[ProductNbr],
      m.[Kardex],
        m.[OnhandQty],
        m.[BinLocationId],
        p.[PART_DESC],
        p.[PSC]
FROM      [InventoryPlanning]..[MRPHeader] m
      LEFT JOIN [mfg_DATAWH]..[Parts] p
WHERE      m.[ProductNbr] LIKE p.[PART_NUMBER]

If it is correct, please do not award me the points.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 13512340
And you could also write it simpler this way:

SELECT     m.[ProductNbr],
     m.[Kardex],
        m.[OnhandQty],
        m.[BinLocationId],
        p.[PART_DESC],
        p.[PSC]
FROM     [InventoryPlanning]..[MRPHeader] m
     LEFT JOIN [mfg_DATAWH]..[Parts] p On m.[ProductNbr] LIKE p.[PART_NUMBER]
0
 

Author Comment

by:Mike Miller
ID: 13516289
I need to access the MRPHeader table in the InventoryPlanning database on server F088SQL1 and the Parts table in the Mfg_DATAWH database on server F088SQL1. I'm trying to import all of the data from certain fields from both databases (where a particular field matches) into a table (I'll call it tblImport) in the PhysicalIventory database on server USSRM-FP04.
0
 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 1000 total points
ID: 13516832
When you wrote, "create a table on my server that is comprised of data from another server" that sent me down the path to having you mess with 4-part names. If both databases are on the same server then you don't need 4-part names, 3-part names will handle it nicely.

Above, acperkins has examples of the join and also illustrates using table aliases to cut down on the lengthy 3-part names.

-- Craig Yellick
0
 

Author Comment

by:Mike Miller
ID: 13517015
I wasn't sure how to split. I hope this is cool.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question