Solved

bcp Rows Taking Too Long To Show Up

Posted on 2003-12-09
7
440 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

15 Experts available now in Live!

Get 1:1 Help Now