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
You can use DTS to pull the .CSV file into your SQL Server from your
Web Server or Remote PC? Failing that, can also use BCP (command line
option that supports BULK INSERT's). DTS may be preferable, if it's
regular operation yo ucan set up a SQL Agent Job to carry it out
automatically.
Regards
ALI
Francis wrote:
> 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
|||You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
a share on the web server, you can reference that file in the BULK INSERT
command using a UNC. For example
BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
SQL/Server will have to be running under an account that has read access to
the share.
HTH,
Mike
"Francis" <f@.f.com> wrote in message
news:epmT1iBbFHA.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 guys,
I decided to use another method, using the SQLDMO COM object. My code
is as follows in c#, just in case someone has the same problem:
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_CommaDelimited Char;
//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();
Michael Abraham wrote:
> You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
> a share on the web server, you can reference that file in the BULK INSERT
> command using a UNC. For example
> BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
> SQL/Server will have to be running under an account that has read access to
> the share.
> HTH,
> Mike
> "Francis" <f@.f.com> wrote in message
> news:epmT1iBbFHA.2756@.tk2msftngp13.phx.gbl...
>
>
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Sunday, March 11, 2012
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 advanceYou can use DTS to pull the .CSV file into your SQL Server from your
Web Server or Remote PC? Failing that, can also use BCP (command line
option that supports BULK INSERT's). DTS may be preferable, if it's
regular operation yo ucan set up a SQL Agent Job to carry it out
automatically.
Regards
ALI
Francis wrote:
> 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|||You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
a share on the web server, you can reference that file in the BULK INSERT
command using a UNC. For example
BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
SQL/Server will have to be running under an account that has read access to
the share.
HTH,
Mike
"Francis" <f@.f.com> wrote in message
news:epmT1iBbFHA.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 guys,
I decided to use another method, using the SQLDMO COM object. My code
is as follows in c#, just in case someone has the same problem:
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();
Michael Abraham wrote:
> You can use the BULK INSERT T-SQL command. If the file to be uploaded is
in
> a share on the web server, you can reference that file in the BULK INSERT
> command using a UNC. For example
> BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
> SQL/Server will have to be running under an account that has read access t
o
> the share.
> HTH,
> Mike
> "Francis" <f@.f.com> wrote in message
> news:epmT1iBbFHA.2756@.tk2msftngp13.phx.gbl...
>
>
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 advanceYou can use DTS to pull the .CSV file into your SQL Server from your
Web Server or Remote PC? Failing that, can also use BCP (command line
option that supports BULK INSERT's). DTS may be preferable, if it's
regular operation yo ucan set up a SQL Agent Job to carry it out
automatically.
Regards
ALI
Francis wrote:
> 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|||You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
a share on the web server, you can reference that file in the BULK INSERT
command using a UNC. For example
BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
SQL/Server will have to be running under an account that has read access to
the share.
HTH,
Mike
"Francis" <f@.f.com> wrote in message
news:epmT1iBbFHA.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 guys,
I decided to use another method, using the SQLDMO COM object. My code
is as follows in c#, just in case someone has the same problem:
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();
Michael Abraham wrote:
> You can use the BULK INSERT T-SQL command. If the file to be uploaded is
in
> a share on the web server, you can reference that file in the BULK INSERT
> command using a UNC. For example
> BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
> SQL/Server will have to be running under an account that has read access t
o
> the share.
> HTH,
> Mike
> "Francis" <f@.f.com> wrote in message
> news:epmT1iBbFHA.2756@.tk2msftngp13.phx.gbl...
>
>
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 advanceYou can use DTS to pull the .CSV file into your SQL Server from your
Web Server or Remote PC? Failing that, can also use BCP (command line
option that supports BULK INSERT's). DTS may be preferable, if it's
regular operation yo ucan set up a SQL Agent Job to carry it out
automatically.
Regards
ALI
Francis wrote:
> 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|||You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
a share on the web server, you can reference that file in the BULK INSERT
command using a UNC. For example
BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
SQL/Server will have to be running under an account that has read access to
the share.
HTH,
Mike
"Francis" <f@.f.com> wrote in message
news:epmT1iBbFHA.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 guys,
I decided to use another method, using the SQLDMO COM object. My code
is as follows in c#, just in case someone has the same problem:
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();
Michael Abraham wrote:
> You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
> a share on the web server, you can reference that file in the BULK INSERT
> command using a UNC. For example
> BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
> SQL/Server will have to be running under an account that has read access to
> the share.
> HTH,
> Mike
> "Francis" <f@.f.com> wrote in message
> news:epmT1iBbFHA.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
>
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 advanceYou can use DTS to pull the .CSV file into your SQL Server from your
Web Server or Remote PC? Failing that, can also use BCP (command line
option that supports BULK INSERT's). DTS may be preferable, if it's
regular operation yo ucan set up a SQL Agent Job to carry it out
automatically.
Regards
ALI
Francis wrote:
> 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|||You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
a share on the web server, you can reference that file in the BULK INSERT
command using a UNC. For example
BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
SQL/Server will have to be running under an account that has read access to
the share.
HTH,
Mike
"Francis" <f@.f.com> wrote in message
news:epmT1iBbFHA.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 guys,
I decided to use another method, using the SQLDMO COM object. My code
is as follows in c#, just in case someone has the same problem:
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();
Michael Abraham wrote:
> You can use the BULK INSERT T-SQL command. If the file to be uploaded is in
> a share on the web server, you can reference that file in the BULK INSERT
> command using a UNC. For example
> BULK INSERT <table> FROM '\\WEBSERVER\SHARE\filetobeuploaded.csv' ...
> SQL/Server will have to be running under an account that has read access to
> the share.
> HTH,
> Mike
> "Francis" <f@.f.com> wrote in message
> news:epmT1iBbFHA.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
>
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.)
>
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.)
>
Thursday, March 8, 2012
bulk insert fails
I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).
Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):
BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)
I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.
Thanks!
|||
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).
Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):
BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)
If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.
If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.
My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.
I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.
I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.
Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).
Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).
Can someone provide instructions?
Thanks!
Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?
of course, with the user who execute the bulk insert.
Did you alread verify the share and NTFS permition?
|||Yes, I can access file. I have given both that user and the sql service user account full access.Note that this user has no problem running the same bulk load from my old 2000 server.
I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.
I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?
|||
When a told NTFS permission I,d mean the file system permission.
|||Yes, the user has full network and local permission to the file.|||
Can you try to make lower the authentication level of NTLM protocol?
I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
bulk insert fails
I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).
Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):
BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)
I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.
Thanks!
|||
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).
Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):
BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)
If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.
If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.
My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.
I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.
I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.
Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).
Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).
Can someone provide instructions?
Thanks!
Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?
of course, with the user who execute the bulk insert.
Did you alread verify the share and NTFS permition?
|||Yes, I can access file. I have given both that user and the sql service user account full access.Note that this user has no problem running the same bulk load from my old 2000 server.
I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.
I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?
|||
When a told NTFS permission I,d mean the file system permission.
|||Yes, the user has full network and local permission to the file.|||
Can you try to make lower the authentication level of NTLM protocol?
I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
Friday, February 24, 2012
Bulk Insert
Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith
Bulk Insert
Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
--
Anith
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
--
Anith
Bulk Insert
Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
Prabhat
If you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
Prabhat
If you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith
Thursday, February 16, 2012
bulding client/server apllication by using VB2005.net and SQL Server 2005
Hi
What is the ideal method that I can build client/server application by using IIS when I make my programme by using VB2005.net and SQL Server 2005 as database
thanks and waiting your replay
What do you mean by "ideal method" ?Do you need a tutorial ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Mr. Jens K. Suessmeyer
Thanks for your replay I mean by ideal method any subject or Book explain step by step or tutorial How I can build my programme to client/server if a tutorial is available that's write for me
Thanks and waiting your replay
|||There are several good video tutorials at this site concerning both ASP.NET and SQL Server.
Perhaps some of them will be useful for you.
http://www.asp.net/learn/videos/default.aspx?tabid=63
|||Thanks for your advise and now I am living with Asp.net AJAX and I wish that is great vedios be useful for me
Subscribe to:
Posts (Atom)