SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 bcp or DTS tools to extract some data from a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
138 Posts

Posted - 08/22/2012 :  19:37:55  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 08/22/2012 :  19:44:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 08/22/2012 19:45:36
Go to Top of Page

alejo46
Posting Yak Master

Colombia
138 Posts

Posted - 08/23/2012 :  17:34:21  Show Profile  Reply with Quote
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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 08/23/2012 :  18:06:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

alejo46
Posting Yak Master

Colombia
138 Posts

Posted - 08/24/2012 :  21:20:49  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 08/25/2012 :  13:15:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

alejo46
Posting Yak Master

Colombia
138 Posts

Posted - 08/25/2012 :  19:37:42  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 08/25/2012 :  20:01:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000