Adding data form one server to another.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding data form one server to another.

Author

Topic

txuky-n
Starting Member

2 Posts

Posted - 2007-11-22 : 06:32:33

hello:

i'm trying to write data from one database located in one server to another located in other diferent server. I can select data from the source database, but i can't insert them in the other one. The code that I was trying is:

INSERT INTO server1.bd1.dbo.tipo_dia
values (2,2,2,"dato")

i've tryed with select into, but the table alreay exists. I've tryed with insert into, too, but the answer is always the same:
"more than the maximum number of prefixes".

Could you help me????

thank you

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-22 : 06:38:31

Can you see the other server via the linked server? Can you select from it? Have you tried with a field list?

txuky-n
Starting Member

2 Posts

Posted - 2007-11-22 : 06:49:43

no, i can´t. I've done:
EXEC sp_addlinkedserver '192.168.20.36', N'SQL Server'
GO
use bd1
insert into [bd1].[dbo].[Tipo_dia]
select * from [192.168.20.36].loc2005.dbo.tipo_dia

and the answer is
OLE DB provider "SQLNCLI" for linked server "192.168.20.36" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "192.168.20.36" 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 87, Level 16, State 1, Line 0
SQL Network Interfaces: Connection string is not valid [87].

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-22 : 07:25:53

You haven't provided any login details to the linked server, look up sp_addlinkedsrvlogin. Also if you can get to the server, check the surface area configuration allows remote connections.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources