November 10, 2017 at 1:02 am
I am trying to export some table to csv using bcp, I have to do it query-like so this has to be the only way around.
So far,I managed to do it without headers(col names), issue occurs after I try to add columnHeaders.
I receive some strange output that I know nothing about and that says nothing with the error itself.
DECLARE @table_name VARCHAR(50);
DECLARE @columnHeader VARCHAR(8000);
DECLARE @raw_sql VARCHAR(8000);
SET @table_name ='clients'
SELECT @columnHeader = COALESCE(@columnHeader + ',', '') + QUOTENAME(C.COLUMN_NAME , '''')FROM [dbo].INFORMATION_SCHEMA.COLUMNS AS
C WHERE C.TABLE_NAME = @Table_Name
select @raw_sql = 'bcp "select '+ @columnHeader +' name, uniqClientID,registrationNumber,name,vatNumber, CAST(legalEntity AS varchar(10)) AS legalEntity from [dbo]..Clients" ' +' queryout C:\ClientsHeaderF.csv -c -t -T -S' + @@servername
EXEC xp_cmdshell @raw_sql
Output I get.
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
November 10, 2017 at 3:52 am
I managed to execute this, but now I get ONLY headers printed out. No data...
DECLARE @table_name VARCHAR(50);
DECLARE @query varchar(1000)
DECLARE @columnHeader VARCHAR(8000);
DECLARE @raw_sql VARCHAR(8000);
SET @table_name ='clients'
SELECT @columnHeader = COALESCE(@columnHeader + ',', '')+ QUOTENAME( C.COLUMN_NAME ,
'''') + ' AS ' + QUOTENAME( C.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE C.TABLE_NAME = @table_name
PRINT @columnHeader
SELECT @query = 'select'+ @columnHeader +' union all select uniqClientID,registrationNumber,name,vatNumber, CAST(legalEntity AS varchar(100)) as legalEntity ,
entityTypeCode, CAST(Residency AS varchar(100)) as residency, entityStatusCode, entitySizeCode, entitySectorCode,entityBusinessCode ,
entityNaceCode, clientInternalRatingCode,clientExternalRatingCode ,externalRatingAgencyCode,entityGCC ,countryCode,regionCode,
cityCode,departmentCode, dateOfRegistration,watchListStatus,riskStatus,regulatoryRiskCategory,
CAST(grossRevenues AS varchar(200)) as grossRevenues,CAST(netRevenues AS varchar(200)) as netRevenues,
registredExpenditure,dateOfFinancialData,totalExposureGCC,CAST(blockedAccount12M as varchar(200)) as blockedAccount,
CAST(blockedAccountCurrent as varchar(200)) as blockedAccountCurrent ,CAST(totalOnbalanceExposure as varchar(200)) as totalOnBalanceExposure,
CAST(totalOffbalanceExposure as varchar(200)) as totalOffBalanceExposure,CAST(overdueAmount as varchar(200)) as overdueAmount ,
CAST(maximumCurrentDPD as varchar(200)) as maximumCurrentDpd,CAST(maximum12mDPD as varchar(200)) as maximum12mDPD ,
CAST(maximumLifetimeDPD as varchar(200)) as maximumLifetimeDPD , datum from [dbo]..Clients'
select @raw_sql = 'bcp "' + @query +'" queryout E:\myfile.csv-c -t -T -S' + @@servername
--PRINT @raw_sql
EXEC xp_cmdshell @raw_sql
November 12, 2017 at 8:03 pm
Print @query and try to execute it in SSMS, without bcp part.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply