0

The following lines work fine in SQL server and export CSV as desired except for the commented line(--) If I try to export using select * things work fine, but there are some custom outputs I need, hence using the commented select statement. It gives syntax error and the reason is also clear in the part where I have used REPLACE there are unclosed quotes. I am setting a varchar variable @qry hence already the query is in single quotes. The occurrence of quotes in between is terminating the query. How do I assign @qry this complete query without termination in middle?

BEGIN
Declare @custid as int
Declare @qry as varchar(8000)
Declare @cmd varchar(8000)

set @trcustomerid = 1234

set @qry = 'set nocount on; SELECT * FROM [AbcSQL].[dbo].[Customer] WHERE trcustomerid= '+ cast(@custid as nvarchar(10))+'' 


--set @qry = 'set nocount on; SELECT [LocationID],[LocationCode],[Description], convert(varchar, [CreateDate], 121) AS [CreateDate], REPLACE([Eligible], '"', '""')AS [Eligible] FROM [AbcSQL].[dbo].[Customer] WHERE trcustomerid= '+ cast(@custid as nvarchar(10))+'' 

SET @cmd ='sqlcmd -s, -S serverdetails, -W -Q "'+@qry+'" | findstr /v /c:"-" /b > "c:\Data\trCustomer.csv"'
EXEC master..xp_cmdshell @cmd
END
Aayush
  • 11
  • 1
  • 5
  • To escape a single quote in sql server you double it. – Dale K May 03 '21 at 07:11
  • 1
    Does this answer your question? [Escaping single quote in SQL Server](https://stackoverflow.com/questions/15408986/escaping-single-quote-in-sql-server) – Dale K May 03 '21 at 07:13
  • See also [Writing select result to a csv file](https://dba.stackexchange.com/q/23566/127602) over on https://dba.stackexchange.com/ – Andreas May 03 '21 at 07:17

0 Answers0