Using SQL Server 2008 BCP utility to export a stored procedure (simple select)

Question

I am trying to run the BCP utility from SQL Server 2008.

I created a very simple stored procedure called GET_GL_ACCOUNTS

here is the stored procedure:

ALTER PROCEDURE [dbo].[GET_GL_ACCOUNTS]
AS
     SELECT DISTINCT 
         RTRIM (s1.SGMNTID) AS 'AccCode',
         RTRIM (s1.DSCRIPTN) AS 'CodeDesc', 
         CASE
            WHEN  s1.SGMTNUMB = '1' THEN '1' 
            WHEN s1.SGMTNUMB = '2' THEN '2'
            WHEN s1.SGMTNUMB = '3' THEN '110'
            WHEN s1.SGMTNUMB = '4' THEN '4'
            WHEN s1.SGMTNUMB = '5' THEN '120'
         END AS 'AccountType_id',
         CASE 
            WHEN s1.SGMTNUMB = '2' THEN LEFT(s1.SGMNTID, 2)
            ELSE 'DEFAULT'
         END AS 'AccGroupName'
     FROM 
         GL40200 s1

     UNION 

     SELECT  
         REPLACE ([ACTNUMBR_1]+'-'+ [ACTNUMBR_2]+'-'+ [ACTNUMBR_3]+'-'+[ACTNUMBR_4]+'-'+  [ACTNUMBR_5],' ', '') AS 'AccCode',
         '' AS 'CodeDesc',
         '0' AS 'AccountType_id',
         'Default' AS 'AccGroupName'
     FROM 
         GL00100 a

Here is my BCP command:

EXEC xp_cmdshell 'bcp 'EXEC NCOA.dbo.GET_GL_ACCOUNTS' QUERYOUT "E:\dbexport\gl.txt"  -c -t, -T -S'

When I execute this through SQL Server Management Studio I am getting the following error message:

Msg 8146, Level 16, State 1, Procedure GET_GL_ACCOUNTS, Line 0
Procedure GET_GL_ACCOUNTS has no parameters and arguments were supplied.

Do I need parameters and arguments to run this?

Answer 1

It looks like your issue is how the statement is quoted. Try this instead:

EXEC xp_cmdshell 'bcp "EXEC NCOA.dbo.GET_GL_ACCOUNTS" QUERYOUT "E:\dbexport\gl.txt"  -c -t, -T -S'