Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to 3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgent
Kitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:

> I receive near about 50 text files every month each has near about 1lakh to 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire file.
> That is i dont want the process to stop just because few lines were corrupt.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent
|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
[vbcol=seagreen]
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your problem
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:

Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to 3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgentKitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:
> I receive near about 50 text files every month each has near about 1lakh to 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire file.
> That is i dont want the process to stop just because few lines were corrupt.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your problem
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:
> > I receive near about 50 text files every month each has near about 1lakh to 3
> > lakh rows. they come in various formats (few with , delimiters, few with
> > field qualifiers, few with mix and match of both, and few files with fixed
> > format string)
> > so i use bulk insert to copy them into sql server using format files.
> > Everything works fine so far.
> > the problem is, in those txt files some times few lines can be either too
> > long or too short. in that case bulk insert totally rejects the entire file.
> > That is i dont want the process to stop just because few lines were corrupt.
> > is there anyway through which i can insert the data by skipping those few
> > corrupt lines.
> >
> > Thanks for any help in advance.
> > This is urgent

Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to
3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgentKitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:

> I receive near about 50 text files every month each has near about 1lakh t
o 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire fil
e.
> That is i dont want the process to stop just because few lines were corrup
t.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
[vbcol=seagreen]
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your probl
em
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:
>sql

Bulk GRANT statements

On occassion I have to set close to 100 seperate
permissions on various objects, and obviously that gets
tedious with enterprise manager.
In my Oracle days I would 1) create a SELECT statement
that prints out the GRANT statements 2) output the SELECT
to a file, and 3) execute the file in the Oracle
equivalent of Sql Query Analyzer.
I know how to create the GRANT statements (1), but I dont
know how to perform 2 and 3 (or whatever is the better
method) in SQL Server 2000.
Any help would be greatly appreciated.
MarkOnce you have granted the permissions using Enterprise
Manager, you can output them to a file using the script
utility in the tools menu. You can then open the script,
edit it, and execute it in Query Analyzer.
Bill
>--Original Message--
>On occassion I have to set close to 100 seperate
>permissions on various objects, and obviously that gets
>tedious with enterprise manager.
>In my Oracle days I would 1) create a SELECT statement
>that prints out the GRANT statements 2) output the
SELECT
>to a file, and 3) execute the file in the Oracle
>equivalent of Sql Query Analyzer.
>I know how to create the GRANT statements (1), but I
dont
>know how to perform 2 and 3 (or whatever is the better
>method) in SQL Server 2000.
>Any help would be greatly appreciated.
>Mark
>.
>