BCP with query

[frangac]

Hi all,

Could anyone please assist me with the following cmd

Select "bcp " + db_name()
+ ".." + name +
" out c:\temp\" + name + ".txt /c -t\t -SserverName
-UuserID -Ppassword /b1000"
from sysobjects
where type = "U"
order by name

I am trying to run the below but with errors.

Select "bcp " + audit_db()
+ ".." + FILE_AUDIT +
" out FILE_AUDIT_040715".txt -SCHRIS_D
-Usa -Pchris"
from audit_db..FILE_AUDIT
where EVENT_DT < '2004/02/01'

Many Thanks
Chris

 

[ChrisCarroll]

Depending on how your server is set up, your errors may be a result of using double quote " instead of single quote '

I guess you are trying to produce a list of lines that will become bcp commands to run from a windows cmd prompt?

Chris

 

[frangac]

Hi Chris,

The error mesg is 'audit_db' is not a recognized built-in function name.
So how would your cmd be?

Thanks
Chris

 

[ChrisCarroll]

If audit_db is the name of a database in which you keep audit info, then I think you want

Select 'bcp audit_db..FILE_AUDIT ' +
' out FILE_AUDIT_040715.txt -SCHRIS_D -Usa -Pchris ' +
' from audit_db..FILE_AUDIT' +
' from sysobjects ' +
' where type = ''U'' ' +
' order by name '

 

[ChrisCarroll]

Sorry, mistake there;

Select 'bcp audit_db..FILE_AUDIT ' +
' out FILE_AUDIT_040715.txt -SCHRIS_D -Usa -Pchris ' +
' from sysobjects ' +
' where type = ''U'' order by name '

is what you're original SQL snippet was trying to do - generate a list of bcp commands that can be run from Windows.
On the other hand

select * from audit_db..FILE_AUDIT
where EVENT_DT < '20040201'

might be the SQL code that will list all your events in FILE_AUDIT table before 2004/02/01

 

[frangac]

Hi Chris,

Thanks for your input, but no output file?????

1> Select 'bcp audit_db..FILE_AUDIT ' + ' out FILE_AUDIT_040715.txt SCHRIS_D -Usa -Pchris ' + ' from sysobjects ' + ' where audit_db..FILE_AUDIT.EVENT_DT < "2004/05/01"'
2> go

If I select where audit_db..FILE_AUDIT.EVENT_DT < "2004/05/01", it returns with 200 rows

Thanks Again
Chris


-----------------------------------------------------------------------------------------------------------------------------------------------------

bcp audit_db..FILE_AUDIT out FILE_AUDIT_040715.txt -SSYB_DRMS_TRG -Usa -Puiop12 from sysobjects where audit_db..FILE_AUDIT.EVENT_DT < "2004/05/01"

(1 row affected)
1>

 

[ChrisCarroll]

Chris,
Do you know what you are trying to achieve?
If so, can you explain what it is.
Chris

 

[frangac]

Chris,

I am "trying" to bcp out from a select statement. The reason for this is that the table contains a lot of data and it is not possible to bcp out the whole table.

Thanks
Chris

 

[ChrisCarroll]

Gotcha.
The original select at the top of your post is an SQL statement designed to generate lots of lines beginning with "bcp ..." which can then be cut and paste into a windows command line or DOS shell

Since you only want to copy from 1 table, you don't need to use the select to generate your bcp command. You can just type it at the windows command

So: open a dos window.
type your bcp command :

bcp "select * from audit_db..FILE_AUDIT where EVENT_DT < '2004/05/01'" out FILE_AUDIT_040715.txt 
-SSYB_DRMS_TRG -Usa -Puiop12

if you get error messages, try

bcp /?

for help, and take it from there.