Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

How to transfer data from sql server without linked server

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
BostonMA
Asked:
BostonMA
  • 2
  • 2
2 Solutions
 
lcohanDatabase AnalystCommented:
You can use BCP  to send data out/in from SQL.
0
 
BostonMAAuthor Commented:
BCP is not supported in sql express
0
 
lcohanDatabase AnalystCommented:
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
 
Anthony PerkinsCommented:
>> 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
 
BostonMAAuthor Commented:
Both of the solutions were helpful in setting up my solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now