How to insert one row from from another server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to insert one row from from another server?

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-01-10 : 17:38:46

Hello:

I would like to insert one row to the table1 on serverOne, but this row data is from another server: ServerTwo.table2.

How to insert this row to existing table?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-10 : 19:22:04

Are the servers linked?

If so

INSERT INTO yourTable (fieldList)
SELECT (fieldList) FROM ServerName.DatabaseName.SchemaName.TableName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:06:51

else you can use OPENROWSET if its for an adhoc transfer


INSERT INTO table1 (fieldList)
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=ServerTwo;Trusted_Connection=yes;',
     'SELECT (fieldList) FROM DatabaseName.SchemaName.table2') AS a;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-01-11 : 15:26:48

Hello:

Thanks for the help.

I am trying to use openrowset.

INSERT INTO table1
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=ServerTwo;Trusted_Connection=yes;',
'SELECT * FROM DatabaseName.SchemaName.table2' where ID in (''222'')') AS a;

However, I got Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

How to fix it?

Thank you very much again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 08:43:00

seems like you dont have access to ServerTwo?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-01-12 : 11:06:37

Thanks!

I could use my window authentication to log in this server from management studio. Do I need to set up special login and password for openrowset?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 11:42:18

Make sure SPN is registered on both servers for the SQL Service account, and that the service account is trusted for delegation in Active Directory.

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-01-12 : 12:42:27

Thanks for the information. I am kind new for this. Could you tell me the detailed step to do SPN is registered on both servers for the SQL Service account, and how to check that the service account is trusted for delegation in Active Directory.

Your help is highly appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 12:56:29

http://msdn.microsoft.com/en-us/library/ms191153.aspx

Need to be domain admin to perform these actions.

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-01-20 : 10:41:05

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources