[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

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
0
paulott
Asked:
paulott
  • 3
  • 2
  • 2
2 Solutions
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now