February 19, 2008 at 6:42 am
It wold be of great help if some body can tell me the syntax or the way that i shlould write my select query in bcp command.
Create Procedure BCP_Text_File
(
@table varchar(100),
@Cusip varchar(100),
@direction varchar(5),
@FileName varchar(100)
)
as
--set @FileName = 'C:\Result.txt'
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @cmd varchar(1000)
set @cmd = 'bcp " Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from '+db_name()+'..'+@table+' where cusip='+@Cusip+' " '+@direction+' '+@FileName+' -T'
print @cmd
exec master..xp_cmdshell @cmd
end
else
Select 'The table '+@table+' does not exist in the database'
when executnig this Sp__
Exec BCP_Text_File 'tLoanVectors', '86359B6G1', 'out', 'C:\testzz.txt'
am getting error saying...
An error occurred while processing the command line.
NULL
I know that I am wrong while building the command string there ...in Select query
it would of great help if some body can help me...
Thanks,
February 19, 2008 at 7:11 am
Shiv (2/19/2008)
It wold be of great help if some body can tell me the syntax or the way that i shlould write my select query in bcp command.Create Procedure BCP_Text_File
(
@table varchar(100),
@Cusip varchar(100),
@direction varchar(5),
@FileName varchar(100)
)
as
--set @FileName = 'C:\Result.txt'
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @cmd varchar(1000)
set @cmd = 'bcp " Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from '+db_name()+'..'+@table+' where cusip='+@Cusip+' " '+@direction+' '+@FileName+' -T'
print @cmd
exec master..xp_cmdshell @cmd
end
else
Select 'The table '+@table+' does not exist in the database'
when executnig this Sp__
Exec BCP_Text_File 'tLoanVectors', '86359B6G1', 'out', 'C:\testzz.txt'
am getting error saying...
An error occurred while processing the command line.
NULL
I know that I am wrong while building the command string there ...in Select query
it would of great help if some body can help me...
Thanks,
Hello Shiv,
Were you able to print the bcp command and then execute that statement from the command prompt, so that you can know the exact error from the execution. I think the SQL syntax is wrong. Please go thru the syntax from BOL
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
You can have a look at BOL @ http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Hope this helps.
Thanks
Lucky
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply