bcp utility stringing

ok. so, i've gotten the bcp utility to work and now i'd like to string all the prompts together and place them in a job to be scheduled to autorun. i don't have much experience in this arena and appreciate any help or suggestions. I'd love to be able to run (from the command prompt) a file takes care of synchronizing all my tables.

I have an additional concern. I'd like to retain the integrity of my primary keys but I notice that if i delete the information then add new, my primary key starts where it leaves off (in other words, if del. then synch. a table with 6 records, after the synch. my primary key increments starting at7). Is there a way, using the bcp util. to update records based on the primary key, copy the key verbatim, or a way to configure the table to help out with this? I know if i drop and recreate the tables as part of the job, i'm good to go but is there away around that step?

Hi DJ,

You could write all your bcp commands into a script file and then use the Windows Task Scheduler to call the bcp utility and pass in the script file. There should be informaiton on how to pass a file to bcp in Books Online, once you have it working manually, it's pretty straight forward to do the same thing using Task Scheduler.

You use of the work "job" indicates you might be familiar with SQL Agent, which is used in other Editions of SQL to schedule tasks. SQL Agent is not included in SQL Express, so that is not available to you in the Express context.

As far as your question about Primary Keys, the behavior you're seeing is by design. By definition, a Primary Key value is never repeated once used. Under some conditions there is cause to override this behavior. One way to do that is to use SET IDENTITY_INSERS ON, which is documented at http://msdn2.microsoft.com/en-us/library/ms188059(SQL.90).aspx. You can also check out the topic about keeping identity values during a bulk insert at http://msdn2.microsoft.com/en-us/library/ms186335(SQL.90).aspx.

Regards,

Mike Wachal

Batch Process

I have a problem running a batch file, now the problem is that when i run the batch file the command prompts the user for an input, but I have all the output of the bat file going into a log file. So when i run the bat file the process just sits there until i hit the 'y' key or unless i nput sumthin manually. This is a problem becuase this batch file is running on the UAT server as a job and there is no one there to input once the job is running. The commnd in the batch only requires an input once a month.

for eg if run: launch_scrt.bat and i want to put sum parameters such as 'y' or 'n' to avoid the manual input once the job is running. Any ideas?

Thanks in advanceyou can have environment variable, parameter file, or the presence/abscence of the file to replace those values.|||I may be able to help you if you provide a little code as where it gets stuck for response ?|||For example:

Say my batch test.bat file contains the following:

del test.txt /P

now this command asks for confirmation from the user whether to delete the file or not and it could wait for input forever. So is there way to have 'y' or 'n' in the batch file that could be changed depending on the user running this batch process?|||/P is used to Prompt "y", "N" .. remove that and if that doesn't work use /Q

: /Q Quiet mode, do not ask if ok to delete on global wildcard|||Originally posted by aashu
/P is used to Prompt "y", "N" .. remove that and if that doesn't work use /Q

: /Q Quiet mode, do not ask if ok to delete on global wildcard

but i want to use /P and send 'y' or 'n' as a parameter.|||but i want to use /P and send 'y' or 'n' as a parameter.

Now you are probably getting into a realm where you should use a better scripting-language. Not too many are available in Windows environments but there are bound to be some. Python, for example.|||Well i got simple script in my bat process:
---------
REM ..Batch file...begins
REM ......Delete file......

if %1 == N goto Bottom
if %1 == Y goto TOP

:TOP
echo delete
del rundts.bat /q
GOTO END

:Bottom
echo dont delete
GOTO END

:END
REM ..Batch file...End
----------

but now the problem is that the command I'm using instead of del is a different exe and this program is being run through command prompt. This program generates hostnames. But once every month the hostnames are to be downloaded from the interent. So once every month some one has to sit on the server where this process is running and enter 'y' or 'n'. Before downloading the hostnames from the internet it prompts the user for a 'y' or 'n'. So onec a month this has to be done manually (which is a pain). So thats why I wanted to write a script so that this can be avoided.|||why don't to keep 2 batch files one with Y and another with N