0

This whole stored procedure is to generate a csv file into the mention folder where file is getting generated but the headers are missing.

--Adding missing headers into the file----
DECLARE @columnHeader VARCHAR(8000)
SELECT @columnHeader = 


'''RecordID'',''MSISDN'',''Amount'',''ReferenceID''
 ,''SessionID'',''Action'',''RequestDate'',''ResponseDate''
,''StatusID'',''Channel'',''CallBackRequestDate'',''CallBackResponseDate''
,''CallBackReferenceID'''


  Select @cmd = 'BCP '+'"Select '+ @columnHeader +' UNION ALL SELECT convert(varchar(20), 
  [RecordID]),convert(varchar(20),[MSISDN]),convert(varchar(20),[Amount]),convert(varchar(20), 
  [ReferenceID]),convert(varchar(20),[SessionID]),convert(varchar(20), 
  [Action]),RequestDate,ResponseDate,convert(varchar(20),[StatusID]),convert(varchar(20), 
  [Channel]),CallBackRequestDate,CallBackResponseDate,CallBackReferenceID From [LinkVisaCard].[dbo]. 
  [Transaction]"'+ ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerName
  PRINT @cmd
  EXEC master..xp_cmdshell @cmd;

So I tried to use above query inorder to add missing headers where i am getting below error: error: SQLState = 22008, NativeError = 241 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string. SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Unable to resolve column level collations please do the needfull

   ALTER PROCEDURE [dbo].[GenerateCDR]
   AS
   Begin
   DECLARE @sqlCommand varchar(8000)
   DECLARE @filePath VARCHAR(125)
   DECLARE @fileName VARCHAR(125)
   DECLARE @todaysDateFormatted VARCHAR(30)
   DECLARE @ServerName varchar(50)

   -- generate the dynamic file name based on the date and time
   SELECT @todaysDateFormatted = CONVERT(VARCHAR, GETDATE(), 112) + '_' + CAST(DATEPART(HOUR, GETDATE()) 
   AS VARCHAR) + '_' + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_' + CAST(DATEPART(SECOND, 
   GETDATE()) AS VARCHAR)
   -- ALTER the file name
   SET  @fileName ='Tansactions_'+  @todaysDateFormatted + '.csv';

   SET @ServerName = 'TRUSTACCTEST';

   DECLARE @cmd varchar(8000)

   -----Copy the table records into TransactionsData folder with .csv file----
   Select @cmd = 'BCP '+
   '"Select * From [LinkVisaCard].[dbo].[Transaction]"'
    + ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerName
   PRINT @cmd
   EXEC master..xp_cmdshell @cmd;
   PRINT @cmd

   ------Adding missing headers into the file----------
   DECLARE @columnHeader VARCHAR(8000)
   SELECT @columnHeader = 
 '''RecordID'',''MSISDN'',''Amount'',''ReferenceID'',''SessionID'',''Action'',''RequestDate'',''ResponseD 
  
 ate'',''StatusID'',''Channel'',''CallBackRequestDate'',''CallBackResponseDate'',''CallBackReferenceID'''


      Select @cmd = 'BCP '+'"Select '+ @columnHeader +' UNION ALL SELECT convert(varchar(20), 
      [RecordID]),convert(varchar(20),[MSISDN]),convert(varchar(20),[Amount]),convert(varchar(20), 
      [ReferenceID]),convert(varchar(20),[SessionID]),convert(varchar(20), 
      [Action]),RequestDate,ResponseDate,convert(varchar(20),[StatusID]),convert(varchar(20), 
      [Channel]),CallBackRequestDate,CallBackResponseDate,CallBackReferenceID From [LinkVisaCard].[dbo]. 
      [Transaction]"'+ ' QUERYOUT C:\TransactionsData\' +@fileName +' '+ '/c /t"|" /T'+@ServerName
      PRINT @cmd
      EXEC master..xp_cmdshell @cmd;
      END
New contributor
sowmya sirsi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • 1
    Sorry but I have no idea what problem you're encoutering or what your question is. Please format your question so that all queries are formatted. C# is irrelevant here, I think. Please tag for your SQL Server version too. – Colin 't Hart yesterday
  • I am getting the error"Conversion failed when converting date and/or time from character string." once I run the which contains bcp – sowmya sirsi yesterday
  • start by commenting out all but the first statement & then adding in subsequent statements until you localise which statement is actually failing – Stephen Morris - Mo64 yesterday

Your Answer

sowmya sirsi is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.