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

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million row
s
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time th
e
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server
.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is bei
ng
> truncated? How is the BCP process (the one that is misbehaving) called?
Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million rows
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time the
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!
No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!
|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is being
> truncated? How is the BCP process (the one that is misbehaving) called? Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

Batch files

Is there a way to run sql queries using a bat file. I
have some bat files I currently have scheduled to run and
would like to run some queries inside of it.
Thanks,
RandyRandy,
Refer 'osql' in BooksOnLine.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Randy" <sparks70b@.yahoo.com> wrote in message
news:0c1401c35d01$5a6bcab0$a601280a@.phx.gbl...
> Is there a way to run sql queries using a bat file. I
> have some bat files I currently have scheduled to run and
> would like to run some queries inside of it.
> Thanks,
> Randy

Batch file to restart SQL

Hey all.. I am wanting my SQL Server installation to restart its services at a time I define in the Scheduled Tasks feature of the server its on

I need help with command line commands to:
- Stop SQL Server Agent service
- Stop and Start SQL Server service
- Restart SQL Server Agent service

Thanks in advance

from the command prompt:

>net stop MSSQLSERVER
>net start MSSQLSERVER

>net stop / start SQLSERVERAGENT

Batch file to fire Scheduled Job

Hi there,
I am trying to write a batch file that can run a scheduled Job that is
already written in SQL server. I know how to run a package using DTSRun
command from a dos prompt, but, is there any command to fire a scheduled
job. or run a stored procedure.
Thanks in advance,
Sree
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!you can use sp_start_job along with OSQL commandline utility
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"sk" <someone@.hotmail.com> wrote in message
news:%23BpimNekEHA.3536@.TK2MSFTNGP12.phx.gbl...
>
> Hi there,
> I am trying to write a batch file that can run a scheduled Job that is
> already written in SQL server. I know how to run a package using DTSRun
> command from a dos prompt, but, is there any command to fire a scheduled
> job. or run a stored procedure.
> Thanks in advance,
> Sree
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Use osql utility.
Sample syntax:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan ''TEST'''"
>--Original Message--
>
>Hi there,
>I am trying to write a batch file that can run a
scheduled Job that is
>already written in SQL server. I know how to run a
package using DTSRun
>command from a dos prompt, but, is there any command to
fire a scheduled
>job. or run a stored procedure.
>Thanks in advance,
>Sree
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

Batch file to fire Scheduled Job

Hi there,
I am trying to write a batch file that can run a scheduled Job that is
already written in SQL server. I know how to run a package using DTSRun
command from a dos prompt, but, is there any command to fire a scheduled
job. or run a stored procedure.
Thanks in advance,
Sree
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
you can use sp_start_job along with OSQL commandline utility
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"sk" <someone@.hotmail.com> wrote in message
news:%23BpimNekEHA.3536@.TK2MSFTNGP12.phx.gbl...
>
> Hi there,
> I am trying to write a batch file that can run a scheduled Job that is
> already written in SQL server. I know how to run a package using DTSRun
> command from a dos prompt, but, is there any command to fire a scheduled
> job. or run a stored procedure.
> Thanks in advance,
> Sree
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!