Bulk Insert From Client PC

Hi everyone,
I'm having problems with this and hoped you might be able to help. I am
trying a bulk insert of a CSV file to sql server. No problems there,
however, the application i am writing is web based (asp.net), so a
client may upload a CSV from their local PC to a web server. I then need
to upload from the web server to the sql server (a seperate box
entirely). Is there any way of bulk inserting from a remote PC? I've
been looking at this method, or even an api that might do this?
Thank you in advanceMost clients would likely not have the necessary client software to bulk
insert. Besides, if they did, that would increase your attack surface.
A better approach would be to upload the file to the web server and have it
do the bulk insert. Check out BulkCopy in the BOL. (You can use this DMO
object to do what you need.)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Francis" <f@.f.com> wrote in message
news:u046jjBbFHA.2756@.tk2msftngp13.phx.gbl...
Hi everyone,
I'm having problems with this and hoped you might be able to help. I am
trying a bulk insert of a CSV file to sql server. No problems there,
however, the application i am writing is web based (asp.net), so a
client may upload a CSV from their local PC to a web server. I then need
to upload from the web server to the sql server (a seperate box
entirely). Is there any way of bulk inserting from a remote PC? I've
been looking at this method, or even an api that might do this?
Thank you in advance|||Thanks a lot!! It works!! I have to clean up the code a bit, but if
anyone gets the same problem, the code I am using is:
SQLServer server = new SQLServerClass();
_BulkCopy bcp = new BulkCopyClass();
_Database db = new DatabaseClass();
_Table table = new TableClass();
server.EnableBcp = true;
server.Connect( "server" , "user" , "password" );
bcp.DataFilePath = Server.MapPath( "files/data.csv" );
bcp.DataFileType =
SQLDMO_DATAFILE_TYPE.SQLDMODataFile_CommaDelimitedChar;
//bcp.FormatFilePath = @."C:\data.fmt";
bcp.RowDelimiter = @."\n";
bcp.UseExistingConnection = true;
bcp.MaximumErrorsBeforeAbort = 1000;
bcp.UseBulkCopyOption = true;
bcp.IncludeIdentityValues = true;
bcp.UseServerSideBCP = false;
table = server.Databases.Item( "Databasename" , "dbo" ).Tables.Item(
"table" , "dbo" );
table.BeginAlter();
table.ImportData( bcp );
//table.DoAlter();
Tom Moreau wrote:
> Most clients would likely not have the necessary client software to bulk
> insert. Besides, if they did, that would increase your attack surface.
> A better approach would be to upload the file to the web server and have i
t
> do the bulk insert. Check out BulkCopy in the BOL. (You can use this DMO
> object to do what you need.)
>|||Glad to have helped. Consider using Windows authentication.
Also, one potential benefit to this technique is that your web server can
use its anti-virus software to check the uploaded file. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Francis" <f@.f.com> wrote in message
news:uIY2cqEbFHA.3328@.TK2MSFTNGP09.phx.gbl...
Thanks a lot!! It works!! I have to clean up the code a bit, but if
anyone gets the same problem, the code I am using is:
SQLServer server = new SQLServerClass();
_BulkCopy bcp = new BulkCopyClass();
_Database db = new DatabaseClass();
_Table table = new TableClass();
server.EnableBcp = true;
server.Connect( "server" , "user" , "password" );
bcp.DataFilePath = Server.MapPath( "files/data.csv" );
bcp.DataFileType =
SQLDMO_DATAFILE_TYPE.SQLDMODataFile_CommaDelimitedChar;
//bcp.FormatFilePath = @."C:\data.fmt";
bcp.RowDelimiter = @."\n";
bcp.UseExistingConnection = true;
bcp.MaximumErrorsBeforeAbort = 1000;
bcp.UseBulkCopyOption = true;
bcp.IncludeIdentityValues = true;
bcp.UseServerSideBCP = false;
table = server.Databases.Item( "Databasename" , "dbo" ).Tables.Item(
"table" , "dbo" );
table.BeginAlter();
table.ImportData( bcp );
//table.DoAlter();
Tom Moreau wrote:
> Most clients would likely not have the necessary client software to bulk
> insert. Besides, if they did, that would increase your attack surface.
> A better approach would be to upload the file to the web server and have
> it
> do the bulk insert. Check out BulkCopy in the BOL. (You can use this DMO
> object to do what you need.)
>