1

I am trying to crate a CSV export file using BCP with a the datetime stamp in the file name. Everything appears correct but I keep getting errors around the + sign. 

 BEGIN
    SET NOCOUNT ON
    DECLARE @mydate DATETIME
    DECLARE @filename VARCHAR(40)
    SET @mydate = GETDATE()
    SET @filename = 'C:\TEMP\TEST-'+CONVERT(varchar,FORMAT(@mydate,'yyyyMMdd- 
    hhmmss'))+'.csv'

    exec master..xp_cmdshell 'bcp "select 
    CHAR(34)+""SampleNo""+CHAR(34),CHAR(34)+""Analysis 
    Code""+CHAR(34),CHAR(34)+""Analyte Name""+CHAR(34),CHAR(34)+""Old 
    Result""+CHAR(34),CHAR(34)+""New 
    Result""+CHAR(34),CHAR(34)+""ChangeDate""+CHAR(34)+CHAR(44) union all 

SELECT CHAR(34)+SAMPNO+CHAR(34), CHAR(34)+ACODE+CHAR(34), 
   CHAR(34)+ANLNAME+CHAR(34), CHAR(34)+OLDRESULT+CHAR(34), 
   CHAR(34)+NEWRESULT+CHAR(34), CHAR(34)+CHANGEDATE+CHAR(34)+CHAR(44) 
FROM TEST.dbo.MODIFY_EXPORT" QUERYOUT ' +  @filename + ' -S SERV2012R2 -U sa -P password -c -t","'

    END;
heejin ghim
  • 41
  • 7
  • What error exactly? What plus sign exactly? And which SQL is that? – Sentry Sep 17 '19 at 08:33
  • @Sentry SQL 2017 Error: Msg 102, Level 15, State 1, Line 22 Incorrect syntax near '+'. – heejin ghim Sep 17 '19 at 08:39
  • So the + before @filename is where I'm getting the squiggly red line. – heejin ghim Sep 17 '19 at 08:41
  • 2
    Pass your Query into variable , then pass this variable as parameter to `exec master..xp_cmdshell`, so instead of `exec master..xp_cmdshell 'bcp "select .... and so on` try `exec master..xp_cmdshell @Statement`, as @Statement is nvarchar(4000) – ahmed abdelqader Sep 17 '19 at 11:21

0 Answers0