question

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)|||Take a look in Books On Line at a topic called Linked Servers. Although I am not 100% sure that they are supported in Express|||Using linked servers (first create it using te linked server procedures for that) you can issue this query:

SELECT *
FROM [Linkedservername].[ku.univ].dbo.Courses

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de
|||

I think I'm not using the right syntax

I've created the linked server by:

sp_addlinkedserver @.server='REMOTESRV', @.provider='SQLOLEDB',@.srvproduct='',@.datasrc='129.113.271.58\ku.univ'

The log was: Command(s) completed successfully.

I then tried to issue an SQL Statement as you have adviced:

SELECT *

FROM [REMOTESRV].[ku.univ].dbo.Courses

but the log was:

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" 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 65535, Level 16, State 1, Line 0

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

I know that the server allows remote connections: when I connect to it using Object browser I can issue INSERT queries - no problem

Maybe I have a problem with syntax

Thanx a lot

|||Check out the 'Linked Servers' topic in the Books Online|||

Hi,

this here:

,@.datasrc='129.113.271.58\ku.univ'

Should be the instance to connect to, so normally this has to be written in the syntax of:

Servername\Instancename,Portnumber (where as the servername can be exchanged by the ip adress and the portnumber (can be left out if you didn′t choose another one that the default one [or you have SQl Server 2005 at the other end using dynamic port and installed the SQL Server Browser]) and the instancename (which can be left out if you choose the default instance on the server.

So in your case for the default port and the default instance, this should be just:

,@.datasrc='129.113.271.58'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, Jens

Thanx a lot for helping, I've changed the @.datasrc as you wrote but got a different error this time:

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "remotesrv2" 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].

I'd really appreciate your help on this one.

Thanx in advance

|||

Other Ideas, anybody?

I'm pretty stuck here, I've read the documentation, did as it said but still grt an error.

Thanx!

|||

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

HTH