0

I have a stored procedure in SSMS call sp_testInventoryIssues as follows:

USE [ADMIN]
GO
/****** Object:  StoredProcedure [dbo].[sp_testInventoryIssues]    Script Date: 06/19/2015 16:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_testInventoryIssues]
AS BEGIN
SELECT 'I,MRO,' + Loc + ',,' + Part + ','
    + RTRIM(CAST(Qty AS CHAR)) + ',MRO,' + WhereUsed + ',' + RTRIM(CAST(CONVERT(char(10), GETDATE(), 126) AS CHAR))
    + ',EA,,,,,,,00-00-0000-00,,,,,,,' + EmpNum
FROM ADMIN.DBO.inventory_issues
WHERE Exported = '0'
UPDATE ADMIN.DBO.inventory_issues
SET Exported = '1'
WHERE Exported = '0'
DECLARE @sql VARCHAR(4000)
SELECT @sql = 'bcp "exec admin..sp_ReportInventoryIssues" queryout \\test01\testqdls\invtrans\testfile.csv -c -t, -T -S'
    + @@SERVERNAME
EXEC ADMIN..xp_cmdshell @sql
END

I am trying to use the BCP utility to export my data into a .csv file. Everything is working fine except the exporting, instead of writing to a .csv file, the data is written in a grid in SSMS. Any help or suggestions?

0

It looks like the only thing I can see to change is .csv to .txt such as:

'bcp "exec admin..sp_ReportInventoryIssues" queryout 
\\test01\testqdls\invtrans\testfile.txt -c -t, -T -S'+ @@SERVERNAME

source

EDIT: My first answer is way off, beg your pardon. I think it may have to do with quotes for the executable string:

set @sql = 'bcp "exec admin..sp_ReportInventoryIssues" queryout 
"\\test01\testqdls\invtrans\testfile.csv" -c -t, -T -S'+ @@SERVERNAME

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.