Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Data from multiple databases

Posted on 2005-03-10
23
Medium Priority
?
227 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
  • 9
  • 8
  • 3
  • +2
23 Comments
 
LVL 11

Expert Comment

by:Craig Yellick
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
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.

 
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:Craig Yellick
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:Craig Yellick
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:Craig Yellick
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:Craig Yellick
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:Craig Yellick
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:Craig Yellick
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:Craig Yellick
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:Craig Yellick
Craig Yellick 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

581 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