Adding " to field data

Transact-SQL
1

I have the following and would like to add quotes to the filed data. The TS_Description field for example.

Eg.""
So If the field data was equal to 'some text'. The output would be "some text"

Set @sql = (

'exec master..xp_cmdshell ''bcp "Select TS_TEST_ID, Full_Path, QCFullPath, AL_FATHER_ID, AL_FATHER_DESCRIPTION, TS_SUBJECT, AL_DESCRIPTION, TS_RESPONSIBLE ,TS_CREATION_DATE, ST_STEP_ORDER, TS_NAME, TS_Description, ST_Description, Test_Data, ST_Expected From [UofA].[dbo].QCOutput Order By Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,TS_NAME,ST_STEP_ORDER" queryout "'+@OutputLocation+'" -c -t"|" -T -S "'+@MachineName+'"'''
)

Exec(@sql)

2 
Select ''"'' + TS_TEST_ID + ''"'' ,
3

The suggestion provides the following error;

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''+TS_NAME+''.

4

please post your full query

5

--Run query
Set @sql = (

		'exec master..xp_cmdshell ''bcp "Select TS_TEST_ID, Full_Path, QCFullPath, AL_FATHER_ID, AL_FATHER_DESCRIPTION, TS_SUBJECT, AL_DESCRIPTION, TS_RESPONSIBLE ,TS_CREATION_DATE, ST_STEP_ORDER, ''"''+TS_NAME+''"'', ''"''+TS_Description+''"'', ''"''+ST_Description+''"'', ''"''+Test_Data+''"'', ''"''+ST_Expected+''"'' From [UofA].[dbo].QCOutput Order By Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,TS_NAME,ST_STEP_ORDER" queryout "'+@OutputLocation+'" -c -t"|" -T -S "'+@MachineName+'"'''
		
			)

Exec(@sql)
6

looks like you need another set of single quote. Try

''''"'''' + TS_NAME + ''''"''''
7

why are you making it so complicated ?

why not execute the cmdshell directly ?

select @cmd= 'bcp "Select TS_TEST_ID, . ...." 

exec master..xp_cmdshell @cmd
8

Would it be a whole lot easier to use QUOTENAME?

9

Actually, as khtan inferred, the whole thing is a bit insane. Let's fix it.

First, write the SELECT as a view. For discussion purposes, we'll name the view "TheView". Call the view whatever suites you. Creation of the view also allows you to test and tune the query before you go anywhere near BCP or dynamic SQL. Once you get to the dynamic SQL, it's going to save on a shedload of problems.

    USE UofA;
GO
 CREATE VIEW dbo.TheView AS
 Select  TS_TEST_ID
        ,Full_Path
        ,QCFullPath
        ,AL_FATHER_ID
        ,AL_FATHER_DESCRIPTION
        ,TS_SUBJECT
        ,AL_DESCRIPTION
        ,TS_RESPONSIBLE
        ,TS_CREATION_DATE
        ,ST_STEP_ORDER
        ,TS_NAME        = QUOTENAME(TS_NAME       ,'"')
        ,TS_Description = QUOTENAME(TS_Description,'"')
        ,ST_Description = QUOTENAME(ST_Description,'"')
        ,Test_Data      = QUOTENAME(Test_Data     ,'"')
        ,ST_Expected    = QUOTENAME(ST_Expected   ,'"')
   From [UofA].[dbo].QCOutput
  Order By Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,TS_NAME,ST_STEP_ORDER
;

Once that's done and with the help of a bit of T-SQL prestidigitation, the rest becomes an easy to read and troubleshoot cake-walk.

DECLARE @SQL VARCHAR(8000);

 SELECT @SQL =  REPLACE(REPLACE(
                    'BCP "SELECT * FROM UofA.dbo.TheView" queryout <<@OutputLocation>> -c -t"|" -T -S <<@MachineName>>'
                ,'<<@OutputLocation>>',QUOTENAME(@OutputLocation,'"')
                ,'<<@MachineName>>'   ,QUOTENAME(@MachineName   ,'"')
;
EXEC xp_CmdShell @SQL
;

As a bit of a sidebar, I never use TOP 100%. There are too many problems that people have had with it over time.