bcp Rows Taking Too Long To Show Up

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
LVL 1
paulottAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
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
namasi_navaretnamCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paulottAuthor Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

amit_gCommented:
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
paulottAuthor Commented:
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
amit_gCommented:
Yes it would.

How have you done it now? Does every client machine has bcp.exe?
0
namasi_navaretnamCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.