November 19, 2013 at 11:06 pm
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
November 20, 2013 at 2:03 pm
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