Solved

How to transfer data from sql server without linked server

Posted on 2011-02-18
5
438 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot Rows To Columns 10 65
SQL Server 2012 r2 Make faster Temp Table 17 113
why sql server only update some statistics in the database ? 3 32
SQL Recursion 6 16
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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