0

Can you run Distinct() on XML? I ask because if you look at this garbage data, it produces this error

output
SQLState = 37000, NativeError = 402
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types char and xml are incompatible in the add operator.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

How can I succesfully run this bcp statement with Distinct and Cast to XML?

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(DISTINCT(pinkpurpleblue) As XML) FROM @TestData''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql 

Even with this syntax I get the same error, and (I think) I am only attempting to cast a nvarchar column to xml in this example

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Create Table #Test123
(
  pinkpb varchar(max)
)

Insert Into #Test123 (pinkpb)
Select DISTINCT(pinkpurpleblue) FROM @TestData

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpurpleblue     As XML) FROM #Test123''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql 
  • Are you sure it's the same error? – Erik Darling Nov 10 '16 at 22:22
2

You have 2 issues that I can see:

  1. You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
  2. Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "' 
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql 

DROP TABLE ##distinctValues

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.