Fetch data from Remote server - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Fetch data from Remote server

Author

Topic

reddy_vam
Starting Member

43 Posts

Posted - 2013-06-05 : 00:37:42

I have a requirement to fetch data from a remote server and store it in the local DB on local server, is there any way I can retrieve the data without using Linked servers or OPENROWSET functions ?

I want to write a query something like below,

Select * into #temp
Exec (select * from Remoteserver.Database.dbo.table)

Appreciate your help.

Thanks,
Vamshi

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 00:47:41

You can use Import/Export Wizard to store from another server

--
Chandu

reddy_vam
Starting Member

43 Posts

Posted - 2013-06-05 : 10:39:47

I want to embedded this code into another stored procedure, so Import/Export is not an option for me

Thanks,
Vamshi

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-05 : 10:54:42

Not sure why you do not want to use Linked server or OpenRowSet? How about the OpenDataSource function? Does it also prohibbited in your case? :)

Cheers
MIK

reddy_vam
Starting Member

43 Posts

Posted - 2013-06-05 : 12:36:08

OPenrowset functions are disabled in our environment.

is there anyway we can accomplish this using Xp_cmdshell/OSQL/SQLCMD/BCP comamnds ? If so anyone can give me a example query for that ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 00:53:55

quote:
Originally posted by reddy_vam

OPenrowset functions are disabled in our environment.

is there anyway we can accomplish this using Xp_cmdshell/OSQL/SQLCMD/BCP comamnds ? If so anyone can give me a example query for that ?

You can enable OPENROWSET/OpenDataSource Option...
http://bimoss.wordpress.com/2010/01/07/how-to-enable-openrowset-opendatasource-in-sql-server-2008/

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 01:14:11

quote:
Originally posted by reddy_vam

OPenrowset functions are disabled in our environment.

is there anyway we can accomplish this using Xp_cmdshell/OSQL/SQLCMD/BCP comamnds ? If so anyone can give me a example query for that ?

You can
1. bcp out to file from remote server and bcp/BULK INSERT back in
2. Script out the data from remote server using generate scripts,save as file and then run it in local server

Is this for a one off activity or is this something that needs to be done periodically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources