?
Solved

How to select from database on a remote machine using IP address

Posted on 2011-04-29
6
Medium Priority
?
494 Views
Last Modified: 2012-05-11
Can someone tell me how to select data from a database on a remote machine when I only have the IP address of the remote machine?

I am using this syntax and it is not working:

select *
from 10.1.9.31.dbo.substitutions

The IP address is 10.1.9.31.

thanks.
0
Comment
Question by:jbaird123
  • 3
  • 2
6 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35492557
You have to use that IP address to connect to the server and select the database before you can make a query.  It's not used in the query itself.
0
 

Author Comment

by:jbaird123
ID: 35493063
Hi DaveBaldwin:

I can use this syntax successfully with queries when I'm referencing the server by machine name (though I did leave out the database name in the original post).

Example:

select * from ServerName.DatabaseName.SchemaOwner.TableName

This works and allows me to select data from a table in a remote database - but the problem is that it only works if I am referencing the server by machine name.  If I change to use the IP address instead of the server name, I get an error.

Please advise.

Thanks.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 35493283
I'm not sure this will work, but give it a try

select *
from [10.1.9.31].yourdatabase.dbo.substitutions


If not you can try with OPENDATASOURCE

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=10.1.9.31\yourSQLinstance;Integrated Security=SSPI')
    .yourdatabase.dbo.substitutions
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35493418
Honestly never heard of that before but here is the Microsoft page:  http://msdn.microsoft.com/en-us/library/ms187879.aspx   It doesn't say anything about using IP addresses though.  
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35493495
When I put "10.202.46.33\SQLEXPRESS", then it works with an IP address on SQL Express 2005.
0
 

Author Closing Comment

by:jbaird123
ID: 35493536
This worked:

select *
from [10.1.9.31].yourdatabase.dbo.substitutions
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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