Solved

How to transfer data from sql server without linked server

Posted on 2011-02-18
5
437 Views
Last Modified: 2012-05-11
I have setup a SQL 08 express database locally.  I want to load a table in my express database based on data in a database on a server. I have read access to the server (via windows authentication), but setting up linked server on that server is not an option.  What is the best method to use (opendatasource, openquery, openrowset, etc) in order to query the server and then insert the results into my sql express  database.   Please provide syntax for using the commands if possible.
0
Comment
Question by:BostonMA
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34930069
You can use BCP  to send data out/in from SQL.
0
 
LVL 4

Author Comment

by:BostonMA
ID: 34930079
BCP is not supported in sql express
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 34930103
Ooops...sorry
Can you try pipe results out into a file like


exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output

then read that file in:

declare @filename sysname
set @filename = 'c:\date.txt'
Create table #tempfile (line varchar(8000))
exec ('bulk insert #tempfile from "' + @filename + '"')
select * from #tempfile
drop table #tempfile
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 34931376
>> but setting up linked server on that server is not an option.  <<
You do not have to setup the linked server on that server, you can just create one on the SQL Server Express box.
0
 
LVL 4

Author Closing Comment

by:BostonMA
ID: 34942916
Both of the solutions were helpful in setting up my solution.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question