Solved

bcp Rows Taking Too Long To Show Up

Posted on 2003-12-09
7
451 Views
Last Modified: 2012-08-13
Hello.

When using bcp.exe to insert ~5000 rows from a file into a SQL Server table, sometimes it takes a while before all the rows are visible for queries.  Currently I'm using a shell statement in VB to execute the bcp, and have the program simply wait 10 seconds before continuing, but this is unreliable.

The question is this:
Is there a way to ensure the bcp has finished and all the rows are available without knowing the number of rows that should be inserted, thus allowing other queries to be exeucted?

Thanks,
Paul
0
Comment
Question by:paulott
  • 3
  • 2
  • 2
7 Comments
 
LVL 58

Expert Comment

by:amit_g
ID: 9908332
Use bulk insert command and then it will be synchronous and the call will not return unless all the rows are inserted. Check BOL for the syntax of bulk insert.
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 125 total points
ID: 9908754
I do not think either bcp ot bulk insert return a value that you can use, when you use these commands with ShellExecuteA api. I would use ShellExecuteEx with WaitForSingleObject win API.

This is how I do in PowerBuilder, I did not do something like this in VB. But the same idea should work.

Integer WAIT_TIMEOUT =  258 //&H102
Function ulong WaitForSingleObject (ulong hHandle, ulong dwMilliseconds) Library "KERNEL32.DLL"

Add this l  after the call to ShellExecuteEx
Do
     retval = WaitForSingleObject(lst_shellexecuteinfo.hProcess, 0)
Loop While retval = WAIT_TIMEOUT

MsgBox "Done"

Once bcp or bulk inserts completes I would definitely compare rowcount in file versus inserted rowcount in my code.

Alternative way.
Create a command file on the fly that inserts the bulk insert command as the first command and "echo done > c:\done.bat" as the second command into a command file. Once you create the command file execute this command file using ShellExecuteA function. Once bcp complete echo command will create done.bat file.

DO While FileExists('c:\done.bat')
 ' Sleep for a second
Loop

When this file exists you know that BCP/BULK INSERT has completed. Now, compare inserted rowcount with file rowcount.

Execute other commands after bcp is complete.

HTH

Namasi Navaretnam


0
 
LVL 1

Author Comment

by:paulott
ID: 9953198
Hey guys. Thanks for the help.

I guess no more solutions are coming.

amit_g:
I tried the bulk insert instead of the bcp.exe but this required that the server with SQL Server to have a UNC for the data files. I think this would require mapping on the server all the possible computers that might run this program, and that is a little more involved than what I was looking for. Thanks anyway.

namasi_navaretnam:
I was curious if there was a tool or mechanism in SQL Server of which I was not aware, but I appreciate your suggestions. I do think I am going to have to use a API. I'll probably do the one as outlined here:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_11842158.html

    Declare Function GetProcessVersion Lib "kernel32" (ByVal ProcessId As Long) As Long
____________________
    Dim lProcId As Long
    lProcId = Shell(PathName, WindowsStyle)
   
    'wait for it to finish.
    Do Until GetProcessVersion(lProcId) = 0
        DoEvents
    Loop
____________________

If there are no more comments, I think I'll post a new question for you two and give you each 50 points and an "A" for the suggestions, and have the moderators close this one.

Thanks again.

- Paul
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 58

Assisted Solution

by:amit_g
amit_g earned 125 total points
ID: 9953608
You can use UNC path (//ServerName/ShareName) in the bulk insert. There is no need to map a drive. Obviously you have taken another approach and if it works it works.

You don't have to post another question with points. Post a question in community support to close this question and refund your points. Make sure that you ask to PAQ this question and not delete it.
0
 
LVL 1

Author Comment

by:paulott
ID: 9953788
Hey amit_g.

> > You can use UNC path (//ServerName/ShareName) in the bulk insert. There is no need to map a drive.  < <

The problem is that the files I'm inserting aren't on the Server. They're going to be scattered around on various computers on the network.  When I try to use bulk insert, I *think* the SQL Server computer has to have access to all of these computers that have the files.  Does that sound right?

Honestly, I'm not much of a network guy.
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9954368
Yes it would.

How have you done it now? Does every client machine has bcp.exe?
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9954774
Paul,

Thats all the the suggestions I have.  I am sure this will work. I have very similar code in PowerBuilder that works without any troubles.

Declare Function GetProcessVersion Lib "kernel32" (ByVal ProcessId As Long) As Long
____________________
    Dim lProcId As Long
    lProcId = Shell(PathName, WindowsStyle)
   
    'wait for it to finish.
    Do Until GetProcessVersion(lProcId) = 0
        DoEvents
    Loop
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now