February 18, 2008 at 6:27 am
Hi
I want to export my table data to text file and I wrote an SP like this...
CREATE Procedure BCP_Text_File
(
@table varchar(100),
@Cusip varchar(100),
@direction varchar(5),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name=@table)
Begin
Declare @STR varchar(1000)
set @STR='Exec Master..xp_Cmdshell ''bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from '+db_name()+'..'+@table+' where cusip='+@Cusip+'"'+@direction+''+@FileName+'" -T'''
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'
and later when i execute this SP___
Exec BCP_Text_File 'tLoanVectors','86359B6G1','out','C:\sample.txt'
am getting some 12 lines as output but not creating any out put file in the specified path.
OutPut
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
Please let me know if am wrong any where...
Thanks,
February 18, 2008 at 9:27 am
It's hard to tell if you have single quotes, doubles, multiple singles, etc.
Build the string
select @cmd = 'bcp ' + xxx ...
and then select it out
select @cmd
in a window and examine the command string. You can run that from a command prompt and see if it works. Likely you are not building the string correctly.
February 18, 2008 at 7:43 pm
Likely to be what Steve put.
If you have another DBMS or tools on the server (notably Oracle) you may need to put the full path to the SQL Server BCP program. I have found that if Oracle bcp is around, Oracle tends to put itself before SQL Server in the PATH statement, so if you don't put the full path in when referencing bcp, it tries using the Oracle one.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply