Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2002-04-08 : 16:02:31
|
| I have made a stored proc that gets a list of files to import using XP_CMDSHELL 'DIR \\PATH\*.TXT /b /od'The the XP_CMDSHELL Results are used in a Cursor that uses XP_CMDSHELL to run a VB App (I exported a DTS Package into VB).The Cursor Loops through all files and does a bulk insert on them.The problem is: The cursor is not waiting for the app to finish and launcing more than one instance of the App which is causing the process to hang.I tried to add WAITFOR DELAY '00:00:15' but apparently it is not meant to be used in a cursor because I get syntax errors. For those of you familair with VB the WAITFOR DELAY is very similair to DOEVENTS.The question is: Have any of you ever used WAITFOR DELAY and if so can it be used in a cursor? Also is there something else that might accomplish what I'm trying to do??Thanks in advance...DanielSQL Server DBA |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-08 : 16:15:51
|
First, WAITFOR DELAY is really an analog of the Win32 API function Sleep(). DoEvents simply stops the current thread's execution and yields its remaining time slice to the OS. WAITFOR DELAY is a blocking call which suspends the thread's execution for a set period of time, whereas DoEvents will return control to the calling thread almost immediately.Second, are you using 7.0 or 2000? If 2000 then I would recommend you use a DTS package for the task. The problem can be expressed more simply by having a parent package use an ActiveX script to gather the list of files to process, and then in a loop call a child package with a global variable set to the filename.From BOL:quote:
xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.
Given that, your xp_cmdshell call should not return until the vb app finishes execution. Maybe more details would help me get a better picture of your problem.setBasedIsTheTruepath<O> |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-04-08 : 16:34:23
|
Ah ok... So actually since XP_CMDSHELL has not yet finished it has not launced the app twice.This is SQL2k.I tried a DTS package with global variables but I just couldnt get it to work the way I wanted... (probably user error  )Anyway I'll look at my vb code.BTW I said its similar to the DOEVENTS. I didnt say they were the same but thanks for the info about sleep.DanielSQL Server DBA |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-04-08 : 17:46:09
|
| I found the problem... The text file doesnt have all the columns that another one of them did. This is by design of the files and cannot be changed. The files contain all sorts of data some have 8 columns some have 21 columns some have 13 columns but the data has to be all in the same file. So the Package fails on the transformations because the current file had a max of 13 columns and not 21. It did have both 8 and 13 but not the 21 column records... So I guess I will need to look into doing this some other way.DanielSQL Server DBAEdited by - sqlserverdba_dan on 04/08/2002 17:48:31 |
|
|
|
|
|
|
Using WAITFOR DELAY in a cursor