CODEWITHSUNDEEP
mysqlsql-servercsvexport-to-csvbcp
KevinC
KevinC

Reputation: 71

BCP SQL exporting to CSV

Im trying to run a Store procedure query to export to CSV file using bcp as a daily task on SQL server

using a normal query works fine for example

select @csv = 'bcp "select * from Table" queryout '+@FileAndPath2+' -c -t, -T -S' +@@servername

However when I add my query which is a list of transactions data within a date range it seems to crash

@p_companyId uniqueidentifier = '189be460-99d1-42e9-b4ed-8de6f8724ce8',
@p_Path           varchar(300) = 'C:\temp\','
@p_Date datetime = getutcdate

 set @FileAndPath2=@p_Path + CONVERT(nvarchar(30), @p_Date, 112) + '_' + CONVERT(varchar(36), @p_companyId) + '_transactionslog.csv';

   declare @csv varchar(8000)
    declare @csvSQL varchar(8000)
    set @csvSQL = 'SELECT TOP (100) [KICSDEV].dbo.MOVIEDETAIL.Title , [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.MemberId, [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.CreateDateTime as ''DateTime'' FROM [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG INNER JOIN [KICSDEV].dbo.MOVIEDETAIL ON [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.MovieDetailId = [KICSDEV].dbo.MOVIEDETAIL.MovieDetailId INNER JOIN [KICSDEV].dbo.MEMBER ON [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.MemberId = [KICSDEV].dbo.MEMBER.MemberId INNER JOIN [KICSDEV].dbo.CINEMA ON [KICSDEV].dbo.MEMBER.CinemaId = [KICSDEV].dbo.CINEMA.CinemaId WHERE ([KICSDEV].dbo.CINEMA.CompanyId = '+ @p_companyId + ' and [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.CreateDateTime >= ' + @p_Date +' and [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.CreateDateTime < DATEADD (day , 1 , '+@p_Date+'))'
    select @csvSQL
select @csv = 'bcp "'+ @csvSQL +'" queryout '+@FileAndPath2+' -c -t, -T -S' +@@servername
    exec master..xp_cmdshell @csv

When I run it comes up as "The data types varchar and uniqueidentifier are incompatible in the add operator." error

When i change the Company to the string instead of the variable in the query it works fine but errors on this.

    set @csvSQL = 'SELECT TOP (100) [KICSDEV].dbo.MOVIEDETAIL.Title , [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.MemberId, [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.CreateDateTime as ''DateTime'' FROM [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG INNER JOIN [KICSDEV].dbo.MOVIEDETAIL ON [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.MovieDetailId = [KICSDEV].dbo.MOVIEDETAIL.MovieDetailId INNER JOIN [KICSDEV].dbo.MEMBER ON [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.MemberId = [KICSDEV].dbo.MEMBER.MemberId INNER JOIN [KICSDEV].dbo.CINEMA ON [KICSDEV].dbo.MEMBER.CinemaId = [KICSDEV].dbo.CINEMA.CinemaId WHERE ([KICSDEV].dbo.CINEMA.CompanyId = ''189be460-99d1-42e9-b4ed-8de6f8724ce8'' and [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.CreateDateTime >= ' + @p_Date +' and [KICSDEV].dbo.MEMBERMOVIEPURCHASELOG.CreateDateTime < DATEADD (day , 1 , '+@p_Date+'))'

Conversion failed when converting date and/or time from character string.

I think it something to do with all the delimiters and data types.

Upvotes: 0

Views: 2161

Answers (1)

Jasper Schellingerhout
Jasper Schellingerhout

Reputation: 1090

Several problems here. Think of the problem this way. You are building a command line parameter for a command line utility, so everything has to be built into a string.

Step 1: Make sure everything is a string before concatenating the query

You are missing some casts Cast(@p_companyId as VarChar(36)) and CAST( @p_Date as VarChar(25)) you also need to quote the cast of the company id and dates in the formatted string. I recommend making a new variable to have the UTC date as a string @p_DateAsStr varchar(25) = CAST( @p_Date as VarChar(25) ), instead of repeating that over and over.

Step 2: String values in the query need to be quoted

Since you are calling BCP you have to format the query as a string, string parameters need to be quoted. You have to use the single quotes because the string to BCP is in double quotes, for instance:

set @csvSQL = 'SELECT .... WHERE CompanyId = '''+ Cast(@p_companyId as VarChar(36)) + ''....'

Step 3: Convert any strings in the query back to native types as needed by built in functions

We are OK with the GUID specified as a string (if we quote it), but for DataAdd we need to convert back from string to date Like this

CreateDateTime < DATEADD (day , 1 , CAST(''' +@p_DateAsStr+''' as DateTime))

[Update] added a quoted string for the date

Upvotes: 1

Related Questions