Bulk Logging - Took longer.

Hello,
We have a job that runs for a duration - n minutes.
There are quite a few stored procedures in it that use the 'select into'
statement.
Tried switching over to 'Bulk logging' to reduce the duration of the run.
Observed that the run actually took n+30 minutes.
Our expectation was that it would be either n or n minus something.
Did not expect the duration to increase.
What could the reason be behind this?
Cheers
SQLCatZ
SQLCatz
Have you looked at an execution plan of the SELECT * INTO .. statements
Do you have any WHERE conditions in the source table? How big is the
table/s? Have you seen optimizer was available to use index to produce the
query?
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:092FC6D0-FC93-4143-9886-4D1C0D61CED7@.microsoft.com...
> Hello,
> We have a job that runs for a duration - n minutes.
> There are quite a few stored procedures in it that use the 'select into'
> statement.
> Tried switching over to 'Bulk logging' to reduce the duration of the run.
> Observed that the run actually took n+30 minutes.
> Our expectation was that it would be either n or n minus something.
> Did not expect the duration to increase.
> What could the reason be behind this?
> Cheers
> SQLCatZ
>
|||Uri,
I have'nt looked at the execution plans as yet.
But can confirm that the source table has a where clause and the columns in
it are part of existing indexes.
Cheers!
SQLCatZ
sql

Bulk Logging - Took longer.

Hello,
We have a job that runs for a duration - n minutes.
There are quite a few stored procedures in it that use the 'select into'
statement.
Tried switching over to 'Bulk logging' to reduce the duration of the run.
Observed that the run actually took n+30 minutes.
Our expectation was that it would be either n or n minus something.
Did not expect the duration to increase.
What could the reason be behind this?
Cheers
SQLCatZSQLCatz
Have you looked at an execution plan of the SELECT * INTO .. statements
Do you have any WHERE conditions in the source table? How big is the
table/s? Have you seen optimizer was available to use index to produce the
query?
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:092FC6D0-FC93-4143-9886-4D1C0D61CED7@.microsoft.com...
> Hello,
> We have a job that runs for a duration - n minutes.
> There are quite a few stored procedures in it that use the 'select into'
> statement.
> Tried switching over to 'Bulk logging' to reduce the duration of the run.
> Observed that the run actually took n+30 minutes.
> Our expectation was that it would be either n or n minus something.
> Did not expect the duration to increase.
> What could the reason be behind this?
> Cheers
> SQLCatZ
>|||Uri,
I have'nt looked at the execution plans as yet.
But can confirm that the source table has a where clause and the columns in
it are part of existing indexes.
Cheers!
SQLCatZ

Bulk Logging - Took longer.

Hello,
We have a job that runs for a duration - n minutes.
There are quite a few stored procedures in it that use the 'select into'
statement.
Tried switching over to 'Bulk logging' to reduce the duration of the run.
Observed that the run actually took n+30 minutes.
Our expectation was that it would be either n or n minus something.
Did not expect the duration to increase.
What could the reason be behind this?
Cheers
SQLCatZSQLCatz
Have you looked at an execution plan of the SELECT * INTO .. statements
Do you have any WHERE conditions in the source table? How big is the
table/s? Have you seen optimizer was available to use index to produce the
query?
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:092FC6D0-FC93-4143-9886-4D1C0D61CED7@.microsoft.com...
> Hello,
> We have a job that runs for a duration - n minutes.
> There are quite a few stored procedures in it that use the 'select into'
> statement.
> Tried switching over to 'Bulk logging' to reduce the duration of the run.
> Observed that the run actually took n+30 minutes.
> Our expectation was that it would be either n or n minus something.
> Did not expect the duration to increase.
> What could the reason be behind this?
> Cheers
> SQLCatZ
>|||Uri,
I have'nt looked at the execution plans as yet.
But can confirm that the source table has a where clause and the columns in
it are part of existing indexes.
Cheers!
SQLCatZ

Bulk Load from Stored Procedure

All of the examples of Bulk Load that I have found use VBScript to Envoke
the bulk load. Is it possible to write and SQL Stored procedure to
accomplish the task? An example of such a procedure would be greatly
appreciated.
Thanks
you could use the OA series of extended sps to do that .
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in
message news:OCn2ObGNEHA.624@.TK2MSFTNGP11.phx.gbl...
> All of the examples of Bulk Load that I have found use VBScript to Envoke
> the bulk load. Is it possible to write and SQL Stored procedure to
> accomplish the task? An example of such a procedure would be greatly
> appreciated.
> Thanks
>

Bulk Insert won't compile if file doesn't exist, no Try Catch either

I first noticed that the try/catch did not seem to work when the text file did not exist and I tried to execute this block of code from a stored proc.

Then, when I tried to compile the proc using a file that did not exist at the time of compilation, I can't even get it to compile.

If I compile, then remove the file, the try/catch does not work.

TRUNCATE TABLE LoadDaily;

BEGIN TRY

BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
BATCHSIZE = 100,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0A',
FIRSTROW = 2
);

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

END CATCH

Instead of saying "compile" I mean that I can't use either ALTER PROCEDURE or CREATE PROCEDURE when the text file does not exist.

Fortunately, using EXEC works and the try/catch works as directed.

This will be run from a service so I don't think I will have any permissions issues.

I tested in SQL 2000 and did not have any problems editing the SP using EM with a non-existant text file.

It does not work if the format file is missing either.
TRUNCATE TABLE LoadDaily;

BEGIN TRY

DECLARE @.sql nvarchar(300), @.Fname nvarchar(300)

SET @.Fname = 'c:\_temp\daily.txt'

SET @.sql = 'BULK INSERT LoadDaily FROM ''' + @.Fname + ''''
+ ' WITH '
+ ' ( '
+ ' BATCHSIZE = 100, '
+ ' FIELDTERMINATOR = ''|'', '
+ ' ROWTERMINATOR = ''0x0A'', '
+ ' FIRSTROW = 2'
+ ' );'

EXEC (@.sql)

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

END CATCH

|||This is a known limitation. TRY...CATCH cannot catch all errors. Some of the errors that happen at compile time can be caught only in the outer TRY..CATCH. This is what you kind of achieved by using dynamic SQL for the BULK INSERT. Please see the TRY...CATCH topic in BOL for more details.|||Sorry, this is a level 16 error so it should be caught by definition (BOL says the range is 10 to 20).

I think I'm more concerned about the regression error. I may be doing something wrong because the environment has changed but I found no way to create the Stored Procedure if the the text file does not exist. I went back and tested it in SQL 2000 and it worked fine.

Since there is now "run as" functionality with EXECUTE, it won't be a big deal but the issue should be addressed.

The new OPENROWSET has the same problem for the text file and the format file.|||Not all errors of a particular severity can be caught. In your examples, these errors are raised at compile time so there is no code execution involved at all for the TRY...CATCH to work. OPENROWSET(BULK) and BULK INSERT pretty much uses the same infrastructure so both will error out if the data file/format file is invalid or missing.|||

OK, let's forget the Try/Catch issue for now.

Just see if you can compile the test proc below in SQL 2005 when daily.txt does NOT exist.

I just found an interesting side effect: Make sure that LoadDaily is a valid table. If LoadDaily is invalid, the code WILL compile.

Now that's really weird.

CREATE PROCEDURE test
AS

BEGIN
BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
END
GO

|||The reason is deferred name resolution. If any object is missing at the time of creation of a stored procedure then the statement is not compiled or parsed completely (note that the syntax is still validated) and deferred until run-time or execution. This explains the behavior you are seeing. This is also true for say references to temporary tables - this is the reason why you can reference temporary tables that are not created in a SP and call it later from another that creates the temporary table or a trigger referencing a temporary table created by a stored procedure that issues the insert/update/delete statement. This is a behavior that we introduced from SQL Server 7.0. Note that this can lead to performance issues due to recompilation of the entire stored procedure at run-time when it hits the statement. This is alleviated to some extent with statement level recompilation in SQL Server 2005. For a more complete discussion, please refer to the whitepaper http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.|||

Deferred name resolution really DOES explain why it worked in 2000.

It seems to be partially broken now:
If the table is VALID but the text file is NOT VALID then the proc will NOT compile.

I can't believe that this was the intention. This is simply an external text file so it should not affect compilation.

|||Unfortunately, this is a side-effect of BULK INSERT using the OPENROWSET(BULK) infrastructure internally. OPENROWSET(BULK) validates parameters at compile-time itself and hence the error. This behavior is same as other OPENROWSET commands. For example, if you try OPENROWSET with Jet provider and missing/invalid mdb file you will get same behavior in SQL Server 2000/2005. The reality is that most people will use dynamic SQL since the filenames cannot be parameterized directly so they will not hit the issue.sql

bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.
easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>
|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?

> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/te...l
t.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?

> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Bulk Insert statement

Hi,

I was using a BULK INSERT statement in a stored procedure.Could any one help me out on one prob.I wanted to let the user select the file he wants to update and then i want to pass this address as a parameter in the stored procedure.

Suppose there is a parameter @.loc,so i want to use this parameter as

Bulk insert TableName from @.loc with(fieldterminator=',')
plz help me out thxI haven't test this but you may take help of Dynamic SQL, http://www.sommarskog.se/dynamic_sql.html fyi.|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task|||Did you even try dynamic sql as suggested to you?
declare @.fn varchar(255)
set @.fn='\\mypc\tmp\tmp1.txt'
exec ('bulk insert mytable from '''+@.fn+''' with(fieldterminator='','')')|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task
u haven't replied to Satya's solution.Then how we know that u got the answer or not?|||Even here u are giving the static location of the file from which u want the bulk insert the data from.What i wanted was to allow the user to define the path of the file and i wanted to pass this path as a parameter .In the example u have given its seems to me the same thing.May be if you could explain me more as i am a newbie.Sorry for the inconvienience.|||also i did try this code-:
@.loc nvarchar (50)
Bulk Insert Tablename From '''@.loc'' with(fieldterminator='','')
but i get an error while saving this stored procedure which says
"Cant find @.loc"|||post ur stored procedure|||Even here u are giving the static location
No, I am using a variable. Here are some code for you to play with
Note: UNC path used for the load as the server is not running on my local PC.
C:\tmp>echo aaa,bbb >tmp.txt

C:\tmp>more tmp.txt
aaa,bbb

C:\tmp>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql" -U sa -S devdb
Password:
1> use tempdb
2> go
1> create proc pdreyer_load
2> @.filename varchar(255)
3> as
4> exec ('bulk insert #t1 from '''+@.filename+''' with(fieldterminator='','')')
5> go
1> create table #t1 (f1 varchar(10),f2 varchar(10))
2> exec pdreyer_load '\\pdreyer\tmp\tmp.txt'
3> select * from #t1
4> go
f1 f2
---- ----
aaa bbb

(1 row affected)
1> drop table #t1
2> drop procedure pdreyer_load
3> go
1> exit

C:\tmp>del tmp.txt|||Is the source file changed all the time?

Bulk Insert Runs Twice!

I have a simple Bulk Insert statement I want to run in a stored procedure.
Here it is:
Set @.bulk_cmd = 'BULK INSERT MyTable
FROM ''C:\mydump.txt''
WITH (FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
EXEC(@.bulk_cmd)
It runs fine but does the insert twice. There are 35 records in the file,
you can watch it load 35 records twice. The records are field terminated wit
h
a tab, and row terminated with CR\LF. I have tried numerous other terminatio
n
characters and all does the same. The actual txt file was created using DTS
from a table.
I am using SQL Server 2000.
Any ideas would be appreciated.
MGAre you checking the target table to confirm that it really is inserting the
rows twice, or do you think that's what is happening because you see this in
the messages:
(35 rows affected)
(35 rows affected)
You see it twice because the top message is from the BULK INSERT inside the
sp, and the bottom is the sp reported how many rows where affected. If you
want to only see the message once, put SET NOCOUNT ON at the top of your sp.
"mgcap" wrote:

> I have a simple Bulk Insert statement I want to run in a stored procedure.
> Here it is:
> Set @.bulk_cmd = 'BULK INSERT MyTable
> FROM ''C:\mydump.txt''
> WITH (FIELDTERMINATOR = ''\t'',
> ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
> EXEC(@.bulk_cmd)
> It runs fine but does the insert twice. There are 35 records in the file,
> you can watch it load 35 records twice. The records are field terminated w
ith
> a tab, and row terminated with CR\LF. I have tried numerous other terminat
ion
> characters and all does the same. The actual txt file was created using DT
S
> from a table.
> I am using SQL Server 2000.
> Any ideas would be appreciated.
> MG|||Mark,
Many thanks. I feel like a goof. That was it. The real issue was that I was
running the routine numerous times, not truncing the table every time, then
seeing more record in the table that 35. That along with the double message
threw me. I should have known better.
Thanks,
Mark
"Mark Williams" wrote:
> Are you checking the target table to confirm that it really is inserting t
he
> rows twice, or do you think that's what is happening because you see this
in
> the messages:
> (35 rows affected)
> (35 rows affected)
> You see it twice because the top message is from the BULK INSERT inside th
e
> sp, and the bottom is the sp reported how many rows where affected. If you
> want to only see the message once, put SET NOCOUNT ON at the top of your s
p.
>
> --
>
> "mgcap" wrote:
>

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?
Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:
> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

Bulk Insert into SQL Server Table With XML

Hey There,

Here, is the example of Bulk Insert into SQL Server Table.

From Application you have to pass a XML string to a Stored Procedure and it will insert all data into table using that XML.

Example SP.

CREATE PROCEDURE StoredProcName

(

@.strXML varchar(8000)

)

AS

Declare @.intPointer int

exec sp_xml_preparedocument @.intPointer output, @.strXML

INSERT into tbl_plnd_insertion

SELECT Column1, Column2, Column3, Column4, Column5

FROM OpenXml(@.intPointer,'/root/tbl_plnd_insertion',2)

WITH (Column1 varchar(20) '@.Column1' , Column2 varchar(20) '@.Column2', Column3 varchar(20) '@.Column3' , Column4 varchar(50) '@.Column4', Column5 varchar(50) '@.Column5')

exec sp_xml_removedocument @.intPointer

Thanks !!!!!

In SQL Server 2000, Text data type is reasonable to use for huge data,

@.strXML Text--varchar(8000)

This is not a bulk insert, it is reading the data from the XML string into a table. sp_xml_preparedocument is expensive, it will use lot of memory & resource when the HUGE data parsed.

In SQL Server 2005, the best option is using XML datatype. You don’t need expensive sp_xml_preparedocuemnt to parse your XML string into selectable values, no need OpenXML also.

Sample,

Code Snippet

Declare @.xml as XML

set @.xml = '<root><student name="Stud1" sex="m"/><student name="Stud2" sex="f"/></root>'

Select

[Table].[Column].value('./@.name', 'varchar(100)') as [Name],

[Table].[Column].value('./@.sex', 'char') as [Sex]

from

@.xml.nodes('/root/student') as [Table]([Column])

|||

Hey

I am really appreciating your input… J

I did not describe all details in my code.

Thanks

BULK insert from a text file name stored into a variable

Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify
bulk insert... from 'myfilename.txt'
BUT my filename will always change and I store it into a variable @.filename

When I try to run the bulk insert instruction ... from @.filename it doesn't work..
do you know why?

Thank you in advanceAccoding to the destructions for BULK INSERT (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_4fec.asp), it takes a literal string for the file name. To sidestep this requirement, you can resort to dynamic SQL using the EXECUTE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4vxn.asp) character string syntax. It isn't pretty, but it should get the job done for you.

-PatP

'Bulk insert' error message

Dear All
I am carrying out a bulk insert from a csv file into a
SQL Server table using the 'Bulk insert' statement within
a stored procedure. This operation works okay whenever I
manually run this sp on the server. However it generates
the error message shown below whenever I call this sp
from a VB6 form. The db I am using is a SQL7 db which I
have restored on a SQL2000 db XP Prof machine, thereby
making it a SQL2000 db.
Thanks for your help.
Olu
-- start error message --
'A different operation is preventing this operation from
being executed'.
-- end error message --Olu,
Silly question, but what data access method are you employing in your VB6
app?
James Hokes
"Olu Falowo" <ofalowo@.hotmail.com> wrote in message
news:01a901c3ce3b$4af10e50$a301280a@.phx.gbl...
> Dear All
> I am carrying out a bulk insert from a csv file into a
> SQL Server table using the 'Bulk insert' statement within
> a stored procedure. This operation works okay whenever I
> manually run this sp on the server. However it generates
> the error message shown below whenever I call this sp
> from a VB6 form. The db I am using is a SQL7 db which I
> have restored on a SQL2000 db XP Prof machine, thereby
> making it a SQL2000 db.
> Thanks for your help.
> Olu
> -- start error message --
> 'A different operation is preventing this operation from
> being executed'.
> -- end error message --|||James,
The code I am refering to is inherited (i.e. written by
someone else). It refers to a DSN, which I have just
discovered, is wrongly configured. I have re-configured
this DSN and the system works okay. I would have expected
the error message to have narrowed down this problem!!
Anyway thanks very much for your time.
Olu
>--Original Message--
>Olu,
>Silly question, but what data access method are you
employing in your VB6
>app?
>James Hokes
>"Olu Falowo" <ofalowo@.hotmail.com> wrote in message
>news:01a901c3ce3b$4af10e50$a301280a@.phx.gbl...
>> Dear All
>> I am carrying out a bulk insert from a csv file into a
>> SQL Server table using the 'Bulk insert' statement
within
>> a stored procedure. This operation works okay whenever
I
>> manually run this sp on the server. However it
generates
>> the error message shown below whenever I call this sp
>> from a VB6 form. The db I am using is a SQL7 db which I
>> have restored on a SQL2000 db XP Prof machine, thereby
>> making it a SQL2000 db.
>> Thanks for your help.
>> Olu
>> -- start error message --
>> 'A different operation is preventing this operation
from
>> being executed'.
>> -- end error message --
>
>.
>

BULK INSERT crashes Stored Procedure ?

I'm trying to BULK INSERT from a series of files, but the files may or may
not exist.
Problem is that if a BULK INSERT command fails (because the file doesn't
exist), the next line of code in the SP is never reached!
>> Server: Msg 4860, Level 16, State 1, Line 1
>> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
>> CODE:
>> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
>> + @.path + @.fn + ''''
>> + ' WITH
>> (
>> FIELDTERMINATOR = ''|''
>> ,ROWTERMINATOR = ''\n''
>> ,TABLOCK
>> )
>> '
>> EXEC( @.cmd ) ; -- BOMBS
>> -- EXEC sp_executesql @.cmd ; -- BOMBS
Naturally I've tried setting XACT_ABORT OFF (among other various
superstitious rituals). Nothing works.
TIA,
--joe
PS,
I don't have control of this server & the current admin & users aren't very
technical... As such I want to avoid using xp_cmdshell... similarly, I don't
want to use a linked text server, and DTS is a problem b/c the file names
change along with the aforementioned administrative issues. I would like to
have something that works stand alone that can run w/out intervention and a
minimum of admin.I googled around for this problem, and it appears that "It's supposed to
work that way" is the answer. Is this the only answer, that this class of
error throws a "fatal" & immediately short-circuits further execution of the
remainder of the SP?
This has become a show stopper at the moment.
I'm trying to run through a directory of ASCII files, so having a fatal on
just one file is a poor option when there are 20 files to import. I'd use
xp_cmdshell, but its permissions seem to get turned off whenever a patch is
applied, and that doesn't guard me against a bad 0-byte ASCII file (no
CR/LF).
Is there perhaps a way to set up DTS so it handles dynamic file names when
importing from ASCII? Any suggestions?
Thanx,
-joe
PS, Just to reiterate from my previous post, I thought of using a linked
OLEDB text server, but that requires the right OLEDB driver and permissions,
along with a capable DBA watching over things (plus if the file doesn't
exist, will the OLEDB driver throw the same kind of fatal?). Using bcp in
DOS from a scheduler doesn't solve the dynamic file name problem (oh yeh,
try programming DOS batch files... it can be done, BUT!!!), and implementing
client software (perl, WSE, etc.) on the server or workstation creates
complexity & other headaches. Please, oh dear God, Why ME!!!!' Tell them
to go back to the @.#$ mainframe, hell, they were better off with 3x5
cards!!!!!!
Previous n.g. thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=28fd4c29145420ba&seekm=OQmAG3VcBHA.1848%40tkmsftngp05#link12
thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3BF9B2DC.65712B51%40drew.edu&rnum=1&prev=/groups%3Fq%3Dhandling%2Berrors%2BCould%2Bnot%2Bbulk%2Binsert.%2BFile%2Bgroup:microsoft.public.sqlserver.programming%2Bgroup:microsoft.public.sqlserver.programming%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3D3BF9B2DC.65712B51%2540drew.edu%26rnum%3D1
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||I just came up with a ghastly hack for a solution... a tad dangerous
perhaps...
In the SP, I log the file in a log table before I run the BULK INSERT. The
SP checks in the log table see if an attempt has been made on that file name
before, and will loop if an attempt has already been made.
I then set up a 2-step job on the server. Both steps call the same SP, and
step 1 calls step 2 on failure, and step 2 calls step 1 on failure. Until
the SP runs out of possible file names (filename is based on date in a
xxxxmmddyy.txt format), it'll keep BULK INSERTing files it finds and failing
on ones it doesn't (the SP succeeds when it can exit without error, when it
runs out of names, the maximum of which is based upon today's date).
Finally, both job steps (whichever one happens to be running at the time)
exit the job on success (which is again, running out of file names to look
for).
As I said, a ghastly hack.
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||Before you start using Bulk Insert, can you confirm if the file really exists , you might want to use the command "xp_fileexist"|||"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> Before you start using Bulk Insert, can you confirm if the file really
exists , you might want to use the command "xp_fileexist"
No such xp_ on their machine, although xp_cmdshell is there, and worked at
one point in time. Plus, the admin situation is problematic such that xp_*
priveleges are revoked when service packs get applied.
This has to work w/out any special priveleges or assignments, just a job
that works reliably (nightly) w/out user intervention.
Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so even
if I knew the file existed, it doesn't protect the batch process from
interruption.
I *did* find a workaround to this problem. See my last reply to myself in
the thread. It's too bad that MS never got around to fixing these issues
(poor T-SQL error handling) until Longhorn.
Thanks,|||just a fyi, enhanced error handling has nothing to do with longhorn. it's to
do with yukon.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > Before you start using Bulk Insert, can you confirm if the file really
> exists , you might want to use the command "xp_fileexist"
> No such xp_ on their machine, although xp_cmdshell is there, and worked at
> one point in time. Plus, the admin situation is problematic such that xp_*
> priveleges are revoked when service packs get applied.
> This has to work w/out any special priveleges or assignments, just a job
> that works reliably (nightly) w/out user intervention.
> Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
even
> if I knew the file existed, it doesn't protect the batch process from
> interruption.
> I *did* find a workaround to this problem. See my last reply to myself in
> the thread. It's too bad that MS never got around to fixing these issues
> (poor T-SQL error handling) until Longhorn.
> Thanks,
>
>|||ahhhh, you are my oj in the mourning... and yukon correct me any time you
want when i get the code names for MS products mixed up
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"oj" <nospam_ojngo@.home.com> wrote in message
news:e3vmQWbqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> just a fyi, enhanced error handling has nothing to do with longhorn. it's
to
> do with yukon.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
> news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> > "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> > news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > > Before you start using Bulk Insert, can you confirm if the file really
> > exists , you might want to use the command "xp_fileexist"
> >
> > No such xp_ on their machine, although xp_cmdshell is there, and worked
at
> > one point in time. Plus, the admin situation is problematic such that
xp_*
> > priveleges are revoked when service packs get applied.
> >
> > This has to work w/out any special priveleges or assignments, just a job
> > that works reliably (nightly) w/out user intervention.
> >
> > Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> > BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
> even
> > if I knew the file existed, it doesn't protect the batch process from
> > interruption.
> >
> > I *did* find a workaround to this problem. See my last reply to myself
in
> > the thread. It's too bad that MS never got around to fixing these issues
> > (poor T-SQL error handling) until Longhorn.
> >
> > Thanks,
> >
> >
> >
>|||The SP "xp_fileexist" exists in Master db, not in the User DB.|||Thanks, I gather that it's new in SS2K?
It's not listed in the SS2K BOL in the T-SQL ref. Doesn't require additional
rights, great!
It still doesn't solve the problem with any other kind of fatal error in
BULK INSERT (a 0-byte file, for instance).
The bottom line is that MS SQL 7's & 8's error handling isn't very capable
and I'll still have to either go w/ a hacked workaround in the job manager
or a workaround in client software (Perl, WSE...).
What really makes me wonder is, why in the first place was BULK INSERT
implemented to throw fatal errors *at all?* It's an import utility, that's
all it is - it's not a table reference that is the subject of
latent/deferred binding. Makes me wonder about the underpinning API, the
(familiar) scent of old Sybase code lurking under the hood.
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:8AD1A570-60F4-48CF-B368-73369419CC27@.microsoft.com...
> The SP "xp_fileexist" exists in Master db, not in the User DB.|||Just to let you know you're not alone,
I posted this a few hours before you.
http://communities.microsoft.com/newsgroups/previewFrame.asp?ICP=msdn&sLCID=
us&sgroupURL=microsoft.public.sqlserver.programming&sMessageID=%253CuaBAWQSq
DHA.1884@.TK2MSFTNGP09.phx.gbl%253E.
At the moment I'm using this technic: in the sp that needs to bulk insert I
assign the text of the format file to a variable and create the file on the
fly (using a wrapper to the file system object), something like this:
/*********/
declare @.txt varchar(8000)
declare @.FormatFilePath varchar(255)
declare @.path varchar(255)
set @.txt='8.0
1
1 SQLCHAR 0 0 "\r\n" 2 Code ""
'
select @.path='Target.dat', @.FormatFilePath='c\winnt\temp\Target.fmt'
exec @.Res=dbo.st_WriteToFile @.FormatFilePath, @.txt --This is a FSO wrapper
that creates or overwrites a file with the specified text
if @.Res<>0
goto ErrorHandler
create table Target(
PKCheck int Identity(1,1),
Code char(15)
)
exec(N'bulk insert Target from '''
+ @.path
+ N''' with (FORMATFILE='''
+ @.TempDirPath
+ N''', TABLOCK)')
/**********/
It works fine, but of course if the data file or the table change their
structures you have to modify the sp, and you can't use third parties format
file without checking for their correctness.
I' was trying to generalize this approch by adding error controls when I
stumbled into your same problem.
Now I'm thinking of using BCP's format option via xp_cmdshell to produce a
valid format file, but of course I have to take into account the cases when
the number of table fields and file fields and their mutual positions
differ, but the problem of using format files not created by you remains.
Still working on.
Salvor
--
++++++++++++++
To e-mail me,
remove ".NO_SPAM" from my e-mail address

Bulk insert

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running execute proc_TestIT '11113333', 'V', 'Tony Jones , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@.Locker_No Varchar(10),
@.Locker_Type Varchar(10),
@.Member_Full_Name Varchar(30)

AS
declare @.Location Varchar(06)
set @.Location=' '
Begin
BEGIN TRANSACTION
SET @.Location = (select Location from tblLockers where Locker_No=@.Locker_No)
IF (@.Locker_Type='P')
begin
Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@.Locker_No
end
IF(@.Locker_Type='V')
begin
insert into tblLockerIssues_VIP values(@.Locker_No,@.Location,'')
end
COMMIT TRANSACTION
RETURN
End
GO

There are several basic building blocks that you can use:

Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure|||

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files, I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB.

Cheers,

Manfred

BULK Insert

Hi,
I have a stored prod that's doing a bunch of BULK INSERTs. The
problem I'm having is if one the BULK INSERTs fails, then the rest of
the stored proc isn't executed. I'd like it to proceed to the end and
then I can take care of the failed section.
I don't want any transactions here. Tried using SET XACT_ABORT OFF
but didn't help.
Anybody know how to do this?
Cheers
SudheshA suggestion...break it up into pieces via a DTS package. Pieces can be
part of a transaction or not.
HTH
Jerry
"Sudhesh" <Sudhesh@.mail.com> wrote in message
news:1147986812.212738.78350@.j73g2000cwa.googlegroups.com...
> Hi,
> I have a stored prod that's doing a bunch of BULK INSERTs. The
> problem I'm having is if one the BULK INSERTs fails, then the rest of
> the stored proc isn't executed. I'd like it to proceed to the end and
> then I can take care of the failed section.
> I don't want any transactions here. Tried using SET XACT_ABORT OFF
> but didn't help.
> Anybody know how to do this?
> Cheers
> Sudhesh
>|||We'll I have over 200 tables, and I'm really scripting the code for the
SP. So breaking it up isn't really an option.
What happened to all the GURU's of SQL?
Sudhesh

Bulk import stored procedure

I am trying to devise the best way to perform the following:
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name of
the file can change. I have made a simple inserst statement that works when I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create the
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdby,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David