BCP/DTS/cmdshell problem

Hi
Using 2000
I am writing a .cmd file for Bulk copying data(about 25 tables of 1 million
rows each). I need your help and advice on this
1)is dts faster than BCP converting to flatfiles and again copying to
destination tables.
2) if we write cmdshell and use BCP in that instead of directly using in
.cmd file, will performance be slower. I am new to .cmd so controlling and
error handling will be easier if I write in a sp which uses cmd shell to
extract bcp
3. any resource on writing .cmd files using osql and bcp( templates etc). I
googled but no use.
--
Thanks
DevaHi deva
I think you will get a better answer if you try the DTS discussion forum
"DEva" wrote:

> Hi
> Using 2000
> I am writing a .cmd file for Bulk copying data(about 25 tables of 1 millio
n
> rows each). I need your help and advice on this
> 1)is dts faster than BCP converting to flatfiles and again copying to
> destination tables.
> 2) if we write cmdshell and use BCP in that instead of directly using in
> .cmd file, will performance be slower. I am new to .cmd so controlling and
> error handling will be easier if I write in a sp which uses cmd shell to
> extract bcp
> 3. any resource on writing .cmd files using osql and bcp( templates etc).
I
> googled but no use.
> --
> Thanks
> Deva

Bcp with QA : TABLOCK option

User <Brett Kaiser SQLTeam Scrub> a few days earlier
gave me the TRICK to launch BCP through the QA :

DECLARE @.cmd varchar(8000) ;

SET @.cmd = 'bcp ' + 'TablePerson'
+ ' out ' + 'C:\Program files\MyExportFile.dat'
+ ' -c -t ' + ''
+ ' -r ' + '\n'
+ ' -U ' + 'DBA' +' -P ' + 'SQL' +' -S ' + @.@.servername

EXEC master..xp_cmdshell @.cmd ;

It works GREAT but I'm trying to add the Tablock option at the end to have better performances

+ ' h ' + '"TABLOCK"'

But then I have this error :

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Impossible de trouver la procdure stocke 'TABLOCK'.Originally posted by Karolyn
<SQLTeam Scrub>

Hey...

You can't, and shouldn't

Why do you want to?|||Hem, ... to lock the table while loading the data (?!?)

I use it with the bulk insert and it cuts by 5 the time to load the data

Bulk Insert DB..Tbl
From 'MyPath\MyFile'
With (FieldTerminator = '',
RowTerminator = '\n',
CodePage = 'ACP',
TabLock) ;|||Are you trying to output the ss data to a file (which is what you are doing) ?|||Ok, now we've changed direction...

I would use BULK INSERT...and bcp out...

BOL:

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance.

Which I interprete to mean that you're using the default locking behavior...|||Hem, ... to lock the table while loading the data (?!?)

Change the "out" to "in" if you are loading the data into sql server.|||The BULK INSERT works fine with the TABLOCK option

but the BCP command doesn't recognize this option

OK >>> Bulk Insert ... With (Tablock)
KO >>> EXEC master..xp_cmdshell BCP ... -h "Tablock";

BCP Error

I am trying to bcp data out of one table into another copy of the same table
on another server. When I do I get the following error. The cmd that I am
using is:
BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
It works on 33 of the 35 tables. Both of the tables that fail have a column
called [function] that is char 255. I have tried the command with the '-n'
and without it. Any suggestions?
SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Server
Driver][SQL Server]Incorrect syntax near the keyword 'Function'.You might try the '-q' parameter so that quoted identifiers are used. Also,
save yourself some grief by avoiding reserved words as column names.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:0F740E25-C090-418F-991E-88BAEBFA31EE@.microsoft.com...
>I am trying to bcp data out of one table into another copy of the same
>table
> on another server. When I do I get the following error. The cmd that I am
> using is:
> BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
> It works on 33 of the 35 tables. Both of the tables that fail have a
> column
> called [function] that is char 255. I have tried the command with the '-n'
> and without it. Any suggestions?
> SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Server
> Driver][SQL Server]Incorrect syntax near the keyword 'Function'.

BCP Error

I am trying to bcp data out of one table into another copy of the same table
on another server. When I do I get the following error. The cmd that I am
using is:
BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
It works on 33 of the 35 tables. Both of the tables that fail have a column
called [function] that is char 255. I have tried the command with the '-n'
and without it. Any suggestions?
SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Server
Driver][SQL Server]Incorrect syntax near the keyword 'Function'.
You might try the '-q' parameter so that quoted identifiers are used. Also,
save yourself some grief by avoiding reserved words as column names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:0F740E25-C090-418F-991E-88BAEBFA31EE@.microsoft.com...
>I am trying to bcp data out of one table into another copy of the same
>table
> on another server. When I do I get the following error. The cmd that I am
> using is:
> BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
> It works on 33 of the 35 tables. Both of the tables that fail have a
> column
> called [function] that is char 255. I have tried the command with the '-n'
> and without it. Any suggestions?
> SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Server
> Driver][SQL Server]Incorrect syntax near the keyword 'Function'.

BCP Error

I am trying to bcp data out of one table into another copy of the same table
on another server. When I do I get the following error. The cmd that I am
using is:
BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
It works on 33 of the 35 tables. Both of the tables that fail have a column
called [function] that is char 255. I have tried the command with the '-
n'
and without it. Any suggestions?
SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Se
rver
Driver][SQL Server]Incorrect syntax near the keyword 'Function'.You might try the '-q' parameter so that quoted identifiers are used. Also,
save yourself some grief by avoiding reserved words as column names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:0F740E25-C090-418F-991E-88BAEBFA31EE@.microsoft.com...
>I am trying to bcp data out of one table into another copy of the same
>table
> on another server. When I do I get the following error. The cmd that I am
> using is:
> BCP PricingTables.dbo.OtherAddOn in J:\bcp\OtherAddOn.dat -n -T
> It works on 33 of the 35 tables. Both of the tables that fail have a
> column
> called [function] that is char 255. I have tried the command with the
'-n'
> and without it. Any suggestions?
> SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL
Server
> Driver][SQL Server]Incorrect syntax near the keyword 'Function'.

bcp command

if i use:
--code--
DECLARE @.CMD VARCHAR(8000)
SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' +
@.@.servername + '" -T'
exec master..xp_cmdshell @.CMD
--end code--
how could i put the column headers in this spreadsheet?
or
is there a better way of getting a sql statement to save directly to excel?Have a look here:
76c9997" target="_blank">http://groups.google.de/group/micro...
76c9997
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"UNOTech" <UNOTech@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CDA8B4C3-3CB1-418A-A649-B928CB8FB275@.microsoft.com...
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
> +
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to
> excel?|||I'd advise to do it directly from Excel using the menu choices in Excel. go
to Data...Get Extranal Data...New Database Query... It is very clean. You
will need to set up an ODBC connection to do this.
"UNOTech" wrote:
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
+
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to excel?[/color
]

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