BCP Save XML Query Results to a file

Last Post 27 Jul 2011 11:40 AM by cpacosta. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
New Member
New Member

27 Jul 2011 11:40 AM

What am I not seeing? I can use the same query with osql and get a file but then I limited on size (that is what I am expericing even with -w option at maximum).

bcp "SELECT 'MO' AS [Check/Branch_x0020_ID], 'LO' AS [Check/Account_x0020_ID], pmt.[OVERPMTAMT] AS [Check/Check_x0020_Amount], adr.[METRO2_FIRST_NAME] + ' ' + ISNULL(adr.[METRO2_MIDDLE_NAME],'') + ' ' + adr.[METRO2_LAST_NAME] AS [Check/Payee_x0020_Name], adr.[STREET] AS [Check/Payee_x0020_Address_x0020_Line_x0020_1], adr.[CITY] + ', ' + adr.[STATE] + ' ' + adr.[ZIP] AS [Check/Payee_x0020_Address_x0020_Line_x0020_2], (acc.R_BK+ acc.R_BR + acc.R_CUS + acc.R_LN) AS [Check/Custom_x0020_1], 'Customer Refund' AS [Check/Custom_x0020_2], 'Overpayment' AS [Check/Custom_x0020_3], pmt.[ID] AS [Check/Custom_x0020_4] FROM COLLECT_ETL.cbt.OVER_PMTS pmt INNER JOIN COLLECT_ETL.dbo.SCSQL51_KEYS acc ON pmt.SHAW_KEY = acc.R_KEY INNER JOIN COLLECT.dbo.E003_AUXS adr on pmt.SHAW_KEY = adr.USER_KEY WHERE pmt.RETURN_REASON like '%Successful completion%' AND CONVERT(varchar(10), pmt.[CLEAR_DT], 101) = CONVERT(varchar(10), GETDATE() , 101) AND adr.RELATIONSHIP = 'BORR' AND acc.R_BK = '001' ORDER BY pmt.[ID] FOR XML PATH(''), ROOT ('Checks'), TYPE " QUERYOUT \\Cbtdevshaw01\D$\SHAWCOLL\ COLL51lz\TROY_GROUP\Exports\Troy.XML -w -T -SCBTDEVSQL01\DEV

Acceptable Use Policy