Data from multiple databases

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.
Mike MillerSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig YellickDatabase ArchitectCommented:
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
Michael_DCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NavicertsCommented:
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
Craig YellickDatabase ArchitectCommented:
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
Craig YellickDatabase ArchitectCommented:
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
NavicertsCommented:
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
NavicertsCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
Craig YellickDatabase ArchitectCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
I already have a linked server to F088SQL1.
0
Craig YellickDatabase ArchitectCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
That's how it is set up.
I can access the table fine if I manually go through EM.
0
Craig YellickDatabase ArchitectCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
Craig YellickDatabase ArchitectCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
Craig YellickDatabase ArchitectCommented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike MillerSoftware EngineerAuthor Commented:
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
Craig YellickDatabase ArchitectCommented:
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
Mike MillerSoftware EngineerAuthor Commented:
I wasn't sure how to split. I hope this is cool.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.