Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

bcp Rows Taking Too Long To Show Up

Posted on 2003-12-09
7
473 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

856 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