BCP bcp is not recognized

Hi
I am a bit new to SQL Server so please help me
I am trying to run a simple task at a customer but it doesn't work.
bcp isn't recognized.

I'm running this:
exec xp_cmdshell 'bcp "SELECT top 10 * FROM pubs..authors " queryout "C:\Temp\hej.txt" -c -S [ServerName] -T'
and it givs me this errormess.
'bcp' is not recognized as an internal or external command,

It works fine for me when I'm running it on my computer.

Can anyone please inform me what I'm missing. I haven't got a clue.
BR
CiottiIs the folder where bcp.exe is in your path (system) ?|||Hai ciotti,

Where is the "hej.txt" file located ? In the server hard disk or in the client machine hard disk.

When u use xp_cmdshell extended stored procdure, the command will be executed in the server and not in the client machine.

You have said that it works fine in ur system. So did u executed 'bcp' from the command line or using xp_cmdshell ?

If ur machine is the server and u executed it in the command prompt, then fine, u have to include the path of "bcp" in the System PATH.

Hope this helps u...Do let us know what happened.|||You were right. Someone had messed up the system path.
(Don't know who, and I dont care ;) )

Now the bcp command works.
The customer is happy and that makes me happy.
Thank you all for your quick answers.

/Ciotti

bcp - Transaction LogFile Size issue

Hi Experts,
I am having new issue again,
My task is to copy data from one table to another table residing in
different database.
Table happenes to be extreamly large. (Contains around 15 million
rows.)
I tried several ways (SQL query, SSIS packages,etc...)
I found BCP utility suits my requirement. So planned for BCP.
I am trying following ways (Two steps)
1. bcp <MyFirstDB.TableName> out <MyFlatFilePath> -n -T
2.bcp <MySecondDB.TableName> in <MyFlatFilePath> -n -T
(Basically, Copying data from source to flat file and from there to
DestinationTable.)
Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
like hell on second command. It grows upto 8 GB (max. free space That
I have on disk ).
My datatransfer will be incomplete because of no space on HardDisk.
PLease let me know, where I am going wrong,if you have better method,
how can i optimize my data transfer. (My log file grows by 2 MB, not
with %ge)
Thank you in advance,
Sriharsha Karagodu.
Are you changing the second database's recovery to bulk?
On Mar 17, 9:56Xam, sriharsha.karag...@.gmail.com wrote:
> Hi Experts,
> I am having new issue again,
> My task is to copy data from one table to another table residing in
> different database.
> Table happenes to be extreamly large. (Contains around 15 million
> rows.)
> I tried several ways (SQL query, SSIS packages,etc...)
> I found BCP utility suits my requirement. So planned for BCP.
> I am trying following ways (Two steps)
> 1. bcp X<MyFirstDB.TableName> out <MyFlatFilePath> -n -T
> 2.bcp X<MySecondDB.TableName> in <MyFlatFilePath> -n -T
> (Basically, Copying data from source to flat file and from there to
> DestinationTable.)
> Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
> like hell on second command. It grows upto 8 GB (max. free space That
> I have on disk ).
> My datatransfer will be incomplete because of no space on HardDisk.
> PLease let me know, where I am going wrong,if you have better method,
> how can i optimize my data transfer. (My log file grows by 2 MB, not
> with %ge)
> Thank you in advance,
> Sriharsha Karagodu.
|||On Mar 17, 6:59Xpm, Sean <ColdFusion...@.gmail.com> wrote:
> Are you changing the second database's recovery to bulk?
> On Mar 17, 9:56Xam, sriharsha.karag...@.gmail.com wrote:
>
>
>
>
>
>
> - Show quoted text -
Actually I read about Changing the Recovery Property. But Where Do I
Get that option?
When I Do Property of Database--> options-->recovery, This will have
three options like,
TonrnPageDetection,
CheckSum,
None.
So, Not sure where will get option to change the recovery to BULK.
please guide me.
|||You can do it visually, but the syntax goes like this:
ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
Also, to see what the current recovery model is run: SP_HELPDB
[database name]
1. Run SP_HELPDB [database name], and note the model used.
2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
3. Run SP_HELPDB [database name] to double check the settings
4. Run bcp import
5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
6. SP_HELPDB [database name] to double check
|||Shriharsha,
When BCPing in so much data, it is also good to use the batch size operator
to control the transaction size. Such as:
bcp ... -b 50000
This will break up your bcp into about 300 batches, which will speed it up
and gives you more transaction log control. You could then (if necessary)
run extra BACKUP LOGs during the bcp in.
RLF
"Sean" <ColdFusion244@.gmail.com> wrote in message
news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
> You can do it visually, but the syntax goes like this:
> ALTER DATABASE [database name]
> SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
> Also, to see what the current recovery model is run: SP_HELPDB
> [database name]
> 1. Run SP_HELPDB [database name], and note the model used.
> 2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
> 3. Run SP_HELPDB [database name] to double check the settings
> 4. Run bcp import
> 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> 6. SP_HELPDB [database name] to double check
>
|||Thanks Rusell and Sean,
I have changed the model to "BULK_LOGGED" and used -b attribute in bcp import.
Smaller the batch size, faster is the data transfer
"Russell Fields" wrote:

> Shriharsha,
> When BCPing in so much data, it is also good to use the batch size operator
> to control the transaction size. Such as:
> bcp ... -b 50000
> This will break up your bcp into about 300 batches, which will speed it up
> and gives you more transaction log control. You could then (if necessary)
> run extra BACKUP LOGs during the bcp in.
> RLF
> "Sean" <ColdFusion244@.gmail.com> wrote in message
> news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
>
>

bcp - Transaction LogFile Size issue

Hi Experts,
I am having new issue again,
My task is to copy data from one table to another table residing in
different database.
Table happenes to be extreamly large. (Contains around 15 million
rows.)
I tried several ways (SQL query, SSIS packages,etc...)
I found BCP utility suits my requirement. So planned for BCP.
I am trying following ways (Two steps)
1. bcp <MyFirstDB.TableName> out <MyFlatFilePath> -n -T
2.bcp <MySecondDB.TableName> in <MyFlatFilePath> -n -T
(Basically, Copying data from source to flat file and from there to
DestinationTable.)
Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
like hell on second command. It grows upto 8 GB (max. free space That
I have on disk :) ).
My datatransfer will be incomplete because of no space on HardDisk.
PLease let me know, where I am going wrong,if you have better method,
how can i optimize my data transfer. (My log file grows by 2 MB, not
with %ge)
Thank you in advance,
Sriharsha Karagodu.Are you changing the second database's recovery to bulk?
On Mar 17, 9:56=A0am, sriharsha.karag...@.gmail.com wrote:
> Hi Experts,
> I am having new issue again,
> My task is to copy data from one table to another table residing in
> different database.
> Table happenes to be extreamly large. (Contains around 15 million
> rows.)
> I tried several ways (SQL query, SSIS packages,etc...)
> I found BCP utility suits my requirement. So planned for BCP.
> I am trying following ways (Two steps)
> 1. bcp =A0<MyFirstDB.TableName> out <MyFlatFilePath> -n -T
> 2.bcp =A0<MySecondDB.TableName> in <MyFlatFilePath> -n -T
> (Basically, Copying data from source to flat file and from there to
> DestinationTable.)
> Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
> like hell on second command. It grows upto 8 GB (max. free space That
> I have on disk :) ).
> My datatransfer will be incomplete because of no space on HardDisk.
> PLease let me know, where I am going wrong,if you have better method,
> how can i optimize my data transfer. (My log file grows by 2 MB, not
> with %ge)
> Thank you in advance,
> Sriharsha Karagodu.|||On Mar 17, 6:59=A0pm, Sean <ColdFusion...@.gmail.com> wrote:
> Are you changing the second database's recovery to bulk?
> On Mar 17, 9:56=A0am, sriharsha.karag...@.gmail.com wrote:
>
> > Hi Experts,
> > I am having new issue again,
> > My task is to copy data from one table to another table residing in
> > different database.
> > Table happenes to be extreamly large. (Contains around 15 million
> > rows.)
> > I tried several ways (SQL query, SSIS packages,etc...)
> > I found BCP utility suits my requirement. So planned for BCP.
> > I am trying following ways (Two steps)
> > 1. bcp =A0<MyFirstDB.TableName> out <MyFlatFilePath> -n -T
> > 2.bcp =A0<MySecondDB.TableName> in <MyFlatFilePath> -n -T
> > (Basically, Copying data from source to flat file and from there to
> > DestinationTable.)
> > Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
> > like hell on second command. It grows upto 8 GB (max. free space That
> > I have on disk :) ).
> > My datatransfer will be incomplete because of no space on HardDisk.
> > PLease let me know, where I am going wrong,if you have better method,
> > how can i optimize my data transfer. (My log file grows by 2 MB, not
> > with %ge)
> > Thank you in advance,
> > Sriharsha Karagodu.- Hide quoted text -
> - Show quoted text -
Actually I read about Changing the Recovery Property. But Where Do I
Get that option?
When I Do Property of Database--> options-->recovery, This will have
three options like,
TonrnPageDetection,
CheckSum,
None.
So, Not sure where will get option to change the recovery to BULK.
please guide me.|||You can do it visually, but the syntax goes like this:
ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
Also, to see what the current recovery model is run: SP_HELPDB
[database name]
1. Run SP_HELPDB [database name], and note the model used.
2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
3. Run SP_HELPDB [database name] to double check the settings
4. Run bcp import
5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
6. SP_HELPDB [database name] to double check|||Shriharsha,
When BCPing in so much data, it is also good to use the batch size operator
to control the transaction size. Such as:
bcp ... -b 50000
This will break up your bcp into about 300 batches, which will speed it up
and gives you more transaction log control. You could then (if necessary)
run extra BACKUP LOGs during the bcp in.
RLF
"Sean" <ColdFusion244@.gmail.com> wrote in message
news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
> You can do it visually, but the syntax goes like this:
> ALTER DATABASE [database name]
> SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
> Also, to see what the current recovery model is run: SP_HELPDB
> [database name]
> 1. Run SP_HELPDB [database name], and note the model used.
> 2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
> 3. Run SP_HELPDB [database name] to double check the settings
> 4. Run bcp import
> 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> 6. SP_HELPDB [database name] to double check
>|||Thanks Rusell and Sean,
I have changed the model to "BULK_LOGGED" and used -b attribute in bcp import.
Smaller the batch size, faster is the data transfer
"Russell Fields" wrote:
> Shriharsha,
> When BCPing in so much data, it is also good to use the batch size operator
> to control the transaction size. Such as:
> bcp ... -b 50000
> This will break up your bcp into about 300 batches, which will speed it up
> and gives you more transaction log control. You could then (if necessary)
> run extra BACKUP LOGs during the bcp in.
> RLF
> "Sean" <ColdFusion244@.gmail.com> wrote in message
> news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
> > You can do it visually, but the syntax goes like this:
> >
> > ALTER DATABASE [database name]
> > SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
> >
> > Also, to see what the current recovery model is run: SP_HELPDB
> > [database name]
> >
> > 1. Run SP_HELPDB [database name], and note the model used.
> > 2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
> > 3. Run SP_HELPDB [database name] to double check the settings
> > 4. Run bcp import
> > 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> > 6. SP_HELPDB [database name] to double check
> >
>
>

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
encuentra la informacin de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express
> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320-e36f161b834a.htm)
for SQLCMD argument details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
> encuentra la informacin de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE =
> @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobación. No se
encuentra la información de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320-e36f161b834a.htm)
for SQLCMD argument details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobación. No se
> encuentra la información de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE => @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express

Batching a cmd

Hi,
I'd like to do a daily backup of my db using windows programmed task and
running the following batch file:
sqlcmd -S server\db_Express -U username -P password
BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INIT,
NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'dbSQL' )
if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
encuentra la informacin de copia de seguridad para la base de datos
''dbSQL''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE = @.backupSetId,
NOUNLOAD, NOREWIND
GO
exit
but the batch file hangs at exit and doesn't close the session.
What am I doing wrong?
TIA
Ana
SQL2005 Express> but the batch file hangs at exit and doesn't close the session.
This is because you have not specified the script source for the SQLCMD
utility so it is running in interactive mode. With a long script and/or
multiple batches, you can save the script to a file and specify the script
file path via the -i argument. For example:
sqlcmd -S server\db_Express -U username -P password -i
"C:\Scripts\MyBackupScript.sql"
See the Books online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/e1728707-5215-4c04-8320
-e36f161b834a.htm)
for SQLCMD argument details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ana" <ananospam@.yahoo.com> wrote in message
news:O7%23cUkxcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'd like to do a daily backup of my db using windows programmed task and
> running the following batch file:
> sqlcmd -S server\db_Express -U username -P password
> BACKUP DATABASE [dbSQL] TO DISK = N'D:\db05\dbSQL' WITH NOFORMAT, INI
T,
> NAME = N'dbSQL-Complite backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'dbSQL' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'dbSQL' )
> if @.backupSetId is null begin raiserror(N'Error de comprobacin. No se
> encuentra la informacin de copia de seguridad para la base de datos
> ''dbSQL''.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'D:\db05\dbSQL' WITH FILE =
> @.backupSetId, NOUNLOAD, NOREWIND
> GO
> exit
> but the batch file hangs at exit and doesn't close the session.
> What am I doing wrong?
> TIA
> Ana
> SQL2005 Express