sql server 2005 express - want to connect to remote sql server

Hi,

i have sql server 2005 express installed and i'd like to connect to a remote sql server db. how do i do this from within the studio environment?

LVL 1
bede123Asked:
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.

Máté FarkasDatabase Developer and AdministratorCommented:
First you should create a linked server, then you can use it in any query:
EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME_OR_IP', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SERVERNAME_OR_IP', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'sapassword'
GO

SELECT * FROM [SERVERNAME_OR_IP].YourDb.dbo.YourTable

Open in new window

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
bede123Author Commented:
ok so how doe this look?

EXEC muradb.dbo.sp_addlinkedserver @server = N'78.xxx.148.116', @srvproduct=N'SQL Server'
GO

EXEC muradb.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'allied16', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxx', @rmtpassword = N'xxx'
GO

SELECT * FROM [78.xxx.148.116].Muradb.dbo.YourTable


i get this:

Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server '78.xxx.148.116' already exists.
OLE DB provider "SQLNCLI" for linked server "78.xxx.148.116" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "78.xxx.148.116" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].




so apart from the bit that says: "this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections"

is everything else ok?

I'm going to speak to my host to allowe me remote acccess so hopefully that will fix:

"this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections"
0
Máté FarkasDatabase Developer and AdministratorCommented:

EXEC master.dbo.sp_addlinkedserver @server = N'78.xxx.148.116', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'78.xxx.148.116', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxx', @rmtpassword = N'xxx'

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

bede123Author Commented:
that just gives me:

Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server '78.xxx.148.116' already exists.
0
Máté FarkasDatabase Developer and AdministratorCommented:
Then you should run first:
EXEC master.dbo.sp_dropserver @server=N'78.xxx.148.116', @droplogins='droplogins'

Open in new window

0
bede123Author Commented:
ok thanks, that seems to go through ok and came back as command succesfully exicuted.


should this line have the remote details?

EXEC master.dbo.sp_addlinkedserver @server = N'78.xxx.148.116', @srvproduct=N'SQL Server'
GO

and should this line have the local details?

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'78.xxx.148.116', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxx', @rmtpassword = N'xxx'
 
Toggle HighlightingOpen in New WindowSelect All
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 2005

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.