I've a credit card applicatoin that utilizes MS SQL Server that I'll have to perform a bulk place from the file. The adhering point would be that the database and my application is going to be located on separate servers. What's the easiest method to perform a bulk place across a network? Two ideas I'd develop to date:
In the application server, share a directory the db server will find, and perform the import utilizing a bulk place statement in the remote file
Run an FTP server in the db server - once the import is carried out, simply ftp the file towards the db server and perform the import utilizing a bulk place in the local file (I'm leaning towards this method).
Can other people let me know if there's an easy method to get this done, or maybe not, which helps make the most sense, and why?
I have tried it before, and attempted both options.
Ultimately, Used to do the alternative of preference 1. Share a directory around the DB server the application will find. It's not necessary to cope with bandwidth issues throughout the majority place.
The FTP server option works if you are particularly worried about security or transferability.
Your final option (be cautious) is by using DTS having a localized SQL server. It may be safer. Should you choose it wrong, it will be a smaller amount efficient.
When the file is sufficiently small then your ftp option may go (you'll have a duplicate inside your db box). However aren't seeing a problem to complete option 1) for those who have a gigabit network between your two having a single hop.
I don't know about 2k08 as you will find some additional utilities copying files between servers. FTP would be the faster of these two, since it does not make use of the window's file system to transfer the file. (there's overhead, but unless of course the file is big it may be minimal). You will find other benefits of not utilizing a share, like the remote server using the file crashes mid-access.
I disagree with cmartin about adding a wide open share towards the db server. Generally you won't want to open file shares towards the db server because it is generally considered a burglar risk, and lots of places will not take. That being stated it might negate you getting transfer the file to a different place to make use of the bulk import.
I am still searching for a method to do that in MS SQL, but things i did in MySQL ended up being to save the CSV like a BLOB inside a temporary table, run Choose ... INTO DUMPFILE inside a directory close to the DB server, then run the standard LOAD DATA statement on that local file.
I believe spWriteStringToFile is going to do it.
EDIT: I am onto something.
comm.CommandText = @"EXEC spWriteStringToFile @data, 'c:\datadumps', 'data.csv'; BULK INSERT my_table FROM 'c:\datadumps\data.csv';"; comm.Parameters.AddWithValue("data", File.ReadAllText(path)); comm.ExecuteNonQuery();