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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
ASKER
that just gives me:
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server '78.xxx.148.116' already exists.
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server '78.xxx.148.116' already exists.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_addlinkedser
GO
and should this line have the local details?
EXEC master.dbo.sp_addlinkedsrv
Toggle HighlightingOpen in New WindowSelect All
should this line have the remote details?
EXEC master.dbo.sp_addlinkedser
GO
and should this line have the local details?
EXEC master.dbo.sp_addlinkedsrv
Toggle HighlightingOpen in New WindowSelect All
ASKER
EXEC muradb.dbo.sp_addlinkedser
GO
EXEC muradb.dbo.sp_addlinkedsrv
GO
SELECT * FROM [78.xxx.148.116].Muradb.db
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"