bcp or DTS tools to extract some data from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp or DTS tools to extract some data from a table

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-08-22 : 19:37:55

good evening

ive got 2 question

can i use the bcp program to extract a portion of table ?

ie bcp.exe "select * from database..table where datestart >=20120801 and datestart <=20120820" queryout "c:\nw_table.txt" –n –T

2. or can i use the bulk copy from DTS and what is much more efficient
to use bcp or bulk copy ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-22 : 19:44:45

Yes you can use bcp to extract just a portion, you just pass it a query like you've shown. You can also use the -F and -L switches to get a portion of it without having to pass a query.

They are both efficient. I prefer bcp due to how lightweight it is and not having to click around. I'm a command-line type girl.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alejo46
Posting Yak Master

157 Posts

Posted - 2012-08-23 : 17:34:21

thanks a lot for your support, but i tried this coommand but failed:

bcp "select * from dm1_elite..HECHOS_MOVTO_PREPAGO where hor_proceso >='20120201' and hor_proceso <='20120229'" out "\\datamartsql\Historicos\HECHOS_MOVTO_PREPAGO_20120201_20120229.txt" -c -t"|" -Sdatama

the above bcp command yields me an error:
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'select * from dm1_elite'. Login fails.

its likely to be an autentication problem, so i suppose before running this query ive got to create a DB conexion ? right ?
so in new with DTS and i dont the conexion command like for authentication.

thanks for your help in advanced

rtsql -T

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-23 : 18:06:11

You need to specify -T or -U/-P in order to authenticate to datama. -T means to use Windows authentication. -U/-P means to use SQL authentication.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alejo46
Posting Yak Master

157 Posts

Posted - 2012-08-24 : 21:20:49

I already added the swith -T without success yielding the same error.

bcp "select * from dm1_elite.dbo.HECHOS_MOVTO_PREPAGO where hor_proceso >='20120201' and hor_proceso <='20120229'"
out "\\datamartsql\Historicos\HECHOS_MOVTO_PREPAGO_20120201_20120229.txt" -c -t"|" -Sdatamartsql -T

what did i do wrong ? - thanks in advanced

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-25 : 13:15:30

I just realized your command is wrong, needs to say queryout instead of out. You specify out when you are bcp'ing out the table. You are bcp'ing out a query, so you need queryout.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alejo46
Posting Yak Master

157 Posts

Posted - 2012-08-25 : 19:37:42

OK you're right, i corrected the query replacing the parameter out with queryout, i tested it and it worked fine for me

Thanks a lot for your support and your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-25 : 20:01:08

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources