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

x
?
Solved

Select Multiple databases/servers

Posted on 2009-04-27
9
Medium Priority
?
493 Views
Last Modified: 2012-05-06
Hello Experts,

I have a SQL 2008 instance and a SQL 2005 instance, I am attempting to create a view with data from both databases.

below is the script i am trying to use with no luck

the error is

Msg 208, Level 16, State 1, Line 1
Invalid object name 'CMA_spatial.dbo.customers_lut_location'.

please help

Cheers Trent
Select a.*, b.*
    from CMA_projects.CMA_projects.customers_lut_location a
    join   CMA_spatial.dbo.customers_lut_location b
      on  b.LocalityID = a.LocalityID

Open in new window

0
Comment
Question by:Trentos
  • 5
  • 4
9 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 24242970
You will need to have linked servers and should use Fully Qualified names, even with the local databases. You als have to specify the Schema of the table on the remote service.
Select a.*, b.*
    from [MyServer\MyInstsnce].CMA_projects.dbo.customers_lut_location a
    join [MyRemoteServer\MyInstsnce].CMA_spatial.dbo.customers_lut_location b
      on  b.LocalityID = a.LocalityID

Open in new window

0
 

Author Comment

by:Trentos
ID: 24243429
how do i link the servers
 
0
 

Author Comment

by:Trentos
ID: 24243506
The error i am getting now is

Msg 7202, Level 11, State 2, Line 1
Could not find server '210.50.193.168\THR-VPS-SQL' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Select a.*, b.*
    from [210.50.193.168\THR-VPS-SQL].CMA_projects.dbo.customers_lut_location a
    join [116.240.200.8\SQLEXPRESS].CMA_spatial.dbo.customers_lut_location b
      on  b.LocalityID = a.LocalityID

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 38

Expert Comment

by:Jim P.
ID: 24243809
The easier way is to do it from the GUI --

Connect to the server that you want as the "primary" server.

Then drill-down and Server Objects --> Linked Servers. Then right-click and New Linked Server. Follow the GUI.
0
 

Author Comment

by:Trentos
ID: 24244655
I tried that as well but the server i am connecting to is 2005 amd I do not know what to use in the following fields
Product Name:
Data Source:
Provider String:
Catalogue : CMA_projects
 
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24244739
You should be able to just select the SQL Server button option and then if you are using SQL Authentication under the Security, select the Be made using this security context and put the SA user and password for the remote server.
0
 

Author Comment

by:Trentos
ID: 24249247
is there a chance that you could send a screen shot on what to fill in??
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 total points
ID: 24250182
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24259513
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

577 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