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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
157 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
38200 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
157 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
38200 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
157 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
38200 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
157 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
38200 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  
 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.1 seconds. Powered By: Snitz Forums 2000