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 Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam Tjai
Try brackets around the table name?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
|||You can also use the quotename() function
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
Ekrem nsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name?
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

BCP in of hex data

Can anyone please tell me if it is possible to bcp in a file with hex
data into SQL Server 2000?

I have a file that the characters appear as blocks when viewed in
notepad but appear as hex values in a hex editor. I have put just one
of these characters (hex 1A) into a file and tried to bcp it in to a
table with one column that is of type binary using a format file. I
can't get it to work.

Can anyone please help?

Thank you
TomHi

You don't give the BCP commands that you are using, but if you create the
format file by exporting the data then the same format file should work when
importing it.

create table mybinarydata ( col1 varbinary(4), col2 binary(4) )

INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 1234, 5678 )
INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 2345, 6789 )

bcp "test..mybinarydata" out mybinarydata.bcp -T -S MyServer

Format file if you take all defaults:

8.0
2
1 SQLBINARY 1 4 "" 1 col1
""
2 SQLBINARY 2 4 "" 2 col2
""

If you open the data file you should notice it is not readable.

If you use

bcp "test..mybinarydata" out mybinarydata.bcp -c -T -S MyServer

You will not be prompted for a format file and get the data file contains:

00001234 00005678
00002345 00006789

For using format files see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm

HTH

John

"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0404140941.21eb0091@.posting.google.co m...
> Can anyone please tell me if it is possible to bcp in a file with hex
> data into SQL Server 2000?
> I have a file that the characters appear as blocks when viewed in
> notepad but appear as hex values in a hex editor. I have put just one
> of these characters (hex 1A) into a file and tried to bcp it in to a
> table with one column that is of type binary using a format file. I
> can't get it to work.
> Can anyone please help?
> Thank you
> Tom|||Hi

You don't give the BCP commands that you are using, but if you create the
format file by exporting the data then the same format file should work when
importing it.

create table mybinarydata ( col1 varbinary(4), col2 binary(4) )

INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 1234, 5678 )
INSERT INTO mybinarydata ( col1, col2 ) VALUES ( 2345, 6789 )

bcp "test..mybinarydata" out mybinarydata.bcp -T -S MyServer

Format file if you take all defaults:

8.0
2
1 SQLBINARY 1 4 "" 1 col1
""
2 SQLBINARY 2 4 "" 2 col2
""

If you open the data file you should notice it is not readable.

If you use

bcp "test..mybinarydata" out mybinarydata.bcp -c -T -S MyServer

You will not be prompted for a format file and get the data file contains:

00001234 00005678
00002345 00006789

For using format files see books online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_impt_bcp_9yat.htm

HTH

John

"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0404140941.21eb0091@.posting.google.co m...
> Can anyone please tell me if it is possible to bcp in a file with hex
> data into SQL Server 2000?
> I have a file that the characters appear as blocks when viewed in
> notepad but appear as hex values in a hex editor. I have put just one
> of these characters (hex 1A) into a file and tried to bcp it in to a
> table with one column that is of type binary using a format file. I
> can't get it to work.
> Can anyone please help?
> Thank you
> Tom