Getting below error from after executing the query from the –Adding missing headers into the file—-
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