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.
| Author |
Topic |
|
ghanson
Starting Member
2 Posts |
 Posted - 2010-01-27 : 09:04:24
|
| I am very new to this and trying to understand if a bulk import can load data from a different server on the same farm? I notice that the comments I get from other sites is “Load Comma Delimited File Into SQL Server”. Our hosted web site has 2 servers a Web server and SQL server. I am working on importing into the SQL server from a csv file on the WEB server. Is this possible or do I have to move the csv file over to the SQL server? I am told that the bulk import will only run from the SQL server and the csv file must reside on the SQL server. Both servers are behind the same firewall. I don’t think it is good practice to SFTP into the SQL server from outside the firewall, but want to use the bulk import to load the SQL database.Gary J. Hanson |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 09:08:42
|
| Nah ... they are leading you a Song and a Dance !!but it will most likely run better / faster if everything happens on the SQL box.If its difficult to get the CSV file across to the SQL box then have the task run on SQL Server with a network UNC path to the CSV file on the web box.If that's too hard then install SQL Client Tools on the Web box (which will include BCP) and run BCP on the Web box (pointing it at the SQL Server machine and appropriate login - or use Windows Authentication) |
 |
|
|
ghanson
Starting Member
2 Posts |
Posted - 2010-01-27 : 09:55:01
|
Thanks Kristen, thats what I suspected. They are trying to take advantage of my ignorance. Happens quite a lot here.quote:
Originally posted by Kristen
Nah ... they are leading you a Song and a Dance !!but it will most likely run better / faster if everything happens on the SQL box.If its difficult to get the CSV file across to the SQL box then have the task run on SQL Server with a network UNC path to the CSV file on the web box.If that's too hard then install SQL Client Tools on the Web box (which will include BCP) and run BCP on the Web box (pointing it at the SQL Server machine and appropriate login - or use Windows Authentication)
Gary J. Hanson |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 11:47:06
|
| Pleasure. Always like to see 2-bit Consultants and unimaginative DEVs getting some comeuppance! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-01-27 : 12:16:30
|
| Hey, if it's difficult to Copy a file, how do you think the load is gonna be?You then have to worry more about permissions, etc.And is the csv a comma delimited file? Is it QUOTE identified?Any comma's (commas'?) in the data itself?Are you going to use bcp, DTS or SSIS?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 12:39:24
|
| "if it's difficult to Copy a file, how do you think the load is gonna be?"I'm with you on that, but I was figuring that there might be issues allowing SHARES to be made to Xfer files between WEB and SQL boxes. (Web box is probably open to outside world, maybe they get nervous about a SHARE on the SQL box that is visible on the Web box ... ??)If WEB box can select data from the SQL box, then there is already a connection possible, so running BCP there might be "politically easier" than getting the files across to the SQL box ... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:08:41
|
| I'm missing something Brett - but as ever I appreciate your insight.On the Web box I can do:bcp MyDatabase.dbo.MyTable in MyDataFile.CSV -c -t , -r \n -SMyServerName -UMyUserID -PMyPasswordand that will squirt MyDataFile.CSV directly into MyTable on the SQL Box, no? |
|
|
|
|
|
|
|
|
Bulk Import