x

Using bcp in a loop causing performance issues

Hi,

I am trying to extract data from table into a multiple text files using bcp utility. The problem is that bcp hung when it is sequentially executed in a loop (in real life example it is a fetch).

The final message is :

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

During the query execution sometimes spatial result window will be briefly shown but I cannot switch to it.

The problem can be replicated using this simple example:

 DECLARE @sql varchar(2000), @n1 int 
 
 CREATE TABLE ##TEST_BCP (ID INT IDENTITY, LINE VARCHAR(10))
 INSERT INTO ##TEST_BCP (LINE) VALUES ('TEST1')
 
 set @n1 = 1
 
     while @n1 <= 1000
         begin
             
             SET @sql = 'bcp "SELECT LINE FROM tempdb.dbo.[##TEST_BCP] ORDER BY ID" ' + 'queryout C:\temp\file_' + cast(@n1 as varchar) + '.htm -w -T  -S ' + @@servername
             print (@sql)
 
             exec master.dbo.xp_cmdshell @sql
             set @n1 = @n1 + 1
         end

Would anyone know how can I make the above procedure work? It behaves the same on 2008, 2012 and SQL 2014.

Thanks in advance,

more ▼

asked Apr 01, 2017 at 03:48 PM in Default

avatar image

dimitar
1

Is this error message when you're running the batch in SSMS?

Apr 03, 2017 at 11:30 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57

asked: Apr 01, 2017 at 03:48 PM

Seen: 50 times

Last Updated: Apr 03, 2017 at 11:30 AM

Copyright 2018 Redgate Software. Privacy Policy