Post reply

bcp Unable to resolve column level collations

  • July 25, 2008 at 8:47 am

    #194631

    I have a stored procedure which formats data as xml, I am trying to then write that farmatted data to a file (.xml) and ftp the resulting file and archive off a copy of the file. I want this fully automated, so my approach has been a stored procedure to select and format the data, a stored procedure to manage the FTP process and a 'master' stored procedure to manage the whole process, including writing out the .xml file.

    Individually, each stored procedure works as it should. The issue I am having is when I try to create the xml file through bcp.

    Declare @XML as varchar (1024), @No as Varchar(16), @cmdstr as varchar(256), @FilePath as varchar(28)

    Set @No = '0000285232'

    set @filepath = 'C:\test.xml'

    Select @No

    SET @cmdstr = 'bcp "EXECUTE devdataw..dbo.usp_test' + char(32) + char(39)+ @No + char(39) + char(32) + char(34)+ char(32) +'queryout' + char(32) + @filepath + ' -T -c'

    Select @cmdstr

    EXEC master..xp_cmdshell @cmdstr

    When trying to execute this the following error is returned:

    Starting copy...

    SQLState = HY000, NativeError = 0

    Error = [Microsoft][SQL Native Client]Unable to resolve column level collations

    SQLState = 01000, NativeError = 0

    Warning = [Microsoft][SQL Native Client][SQL Server]0

    NULL

    BCP copy out failed

    NULL

    When executing the stored procedure devdataw..dbo.usp_test in isolation, including passing in the parameters the sp executes and returns a very nicely formatted xml.

    Any guidance would be appreciated. Thanks in advance.

  • July 28, 2008 at 8:11 am

    #849209

    Hi,

    Looking at the error I would guess BCP is having trouble converting between codepages.

    Could you try adding "-C RAW" to the BCP command line string and tell us what happens?

    Regards, Rob

  • July 28, 2008 at 8:27 am

    #849216

    Thanks for that,

    The same error is returned using the -C RAW switch, I have also tried it with -C OEM and specifying the codepage explicitly.

    Any thoughts?

    Cheers!

  • July 28, 2008 at 11:32 am

    #849328

    The stored procedure you call to actually create the XML, what type of resultset does it return?

    If you use the varchar data type, try replacing that with nvarchar.

    Regards, Rob

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply