BCP for .xml Export Error

  • Hi All,

    I'm trying to export some tables from SQL Server 2012 into .xml format in a local drive and have run into a problem when reading the file in Excel for example.

    The code I'm currently using is:

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(8000)

    SET @FileName = 'D:\SQLextracts\dbo.vwPOLICIES_Test'+'.xml'

    SET @bcpCommand = 'bcp "SELECT pvTransactionID, pvPolicyID, pvParentPolicyID, pvProductTypeName, pvClientID, pvBindRequestDate, pvEffectiveDate, pvBasePremiumAmt, pvCommissionAmount, pvAdminFee, pvStampDutyAmount, pvFireServicesLevy, pvBrokerCommission, pvBroker, pvProviderPolicyRef, pvTermExpiryDate FROM UnderwriterCENTRAL_Test.dbo.vwPOLICIES FOR XML AUTO, ROOT(''dbo.vwPOLICIES''), ELEMENTS" QUERYOUT "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -r -t'

    PRINT @bcpCommand

    EXEC master..xp_cmdshell @bcpCommand , No_output

    go

    I've gone through each of the fields exported and the column pvInsuredName is what's causing the error. If I include column pvInsuredName in the script I end up with the Excel error message 'invalid file reference. the path to the file is invalid, or one or more of the referenced schemas could not be found' .

    I'm guessing there's an issue with some of the characters contained within the column pvInsuredName causing the error however I'm sure how to fix it.

    Any help is greatly appreciated.

    Thanks and regards,

    Nathan

  • I would assume an invalid character as well. Start with SELECT TOP 10, and if that works, TOP 100, then TOP N, etc. Once you find a record with a bad character search for other records with the same bad character. Correct accordingly. You have a BORING task ahead of you. =) enjoy.

Viewing 2 posts - 1 through 1 (of 1 total)

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