Quotename in Openquery I am using bcp to export query results to CSV file. The query works perfectly as below:

declare @sqlcmd varchar(6500)

set @sqlcmd='bcp "SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(LinkedServerName, '
           +'''SELECT [column1] FROM Hist '
           +'WHERE DateTime &gt;= DateAdd(hh,-24,GetDate()) '
           +'AND DateTime &lt;= GetDate()'')" '
           +'queryout C:\Temp.csv -T -c -t,'

exec xp_cmdshell @sqlcmd


After I added quotename function to column1, the below query doesn't work:

declare @sqlcmd varchar(6500)

set @sqlcmd='bcp "SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(LinkedServerName, '
           +'''SELECT quotename([column1]) FROM Hist '
           +'WHERE DateTime &gt;= DateAdd(hh,-24,GetDate()) '
           +'AND DateTime &lt;= GetDate()'')" '
           +'queryout C:\Temp.csv -T -c -t,'

exec xp_cmdshell @sqlcmd


Result:

Error Message Incorrect syntax near '('


I would appreciate it if anyone can give me some advise. Thanks
It sounds like the linked server may not have a QUOTENAME function. Perhaps it's not a SQL Server itself, or is older than SQL 2008?

Check what that server is, and the functions it can perform. Note that:

QUOTENAME([column1])


is equivalent to:

'[' + REPLACE(column1, ']', ']]') + ']'


I think most SQL variants will have some version of REPLACE.

UPDATE: Based on you comments, you actually wanted to format column1 for inclusion in the CSV file, assuming it may have commas in it.

To do that, you need to put double quotes around the contents of column1, and replace any double quote characters in column1 with two double quotes (which acts as an escape).

So, again, assuming that the destination server does have some form of the REPLACE function, you should be able to use:

'"' + REPLACE([column1], '"', '""') + '"'


An alternative:  If there's no equivalent to the REPLACE function on the remote server, you could try setting up an SSIS package to do the job. You can replace column1 in the data flow with the above, using a Derived Column component. However, this is probably not a great option if you're not familiar with SSIS packages already.