Help with frustrating BCP call and stored procedure params

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GICSPFExtractCreateBatchHdrFile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GICSPFExtractCreateBatchHdrFile]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GICSPFExtractCreateBatchHdrFile(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@SPFWeeklyUpdID INT)
AS
BEGIN
DECLARE @SQl VARCHAR(4000)

SET @sql = 'bcp "Exec GICSPF..GICSPFExtractSelectBatchHdrFile '''
+ @CompanyID
+ ''','''
+ @DivisionID
+ ''','''
+ @DepartmentID
+ ''','''
+ CONVERT(NVARCHAR(10),@SPFWeeklyUpdID)
+ '''" queryout c:\Send\SPF\BatchHdr'
+ @DepartmentID
+ '.GEN -c -t"\t" -U%%%%% -P%%%%% -S'
+ @@SERVERNAME

PRINT @SQL

EXEC master..xp_cmdshell
@sql
END

GO

bbaley (1/22/2009)

Hi all,
I am having a brainurism trying to get a call to stored procedure embedded in the param string for BCP...
(I always have trouble with all those single double triple quotes.... sheesh)

when I parse the string as a select, it looks ok "to me"
but sql server reports;
"Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@pc".

I have found 6,592 examples of calling a stored procedure in a BCP param list, but not a SINGLE on where there are parameters passed to the SP.... (weird)

At it's prettiest so far, it (cmd string) equates to:
Exec Master..xp_Cmdshell 'bcp "exec ultipro_aso2.dbo.gnsa_AEC_401k '20090116','AEC'" queryout "c:\EDI\401k\AEC\bcp401k.txt" -c -T'

which looks good to me and seems to match all examples I have found of building the string....
(I don't have problems without the sp params)

version 1
----------------------------------
select @sql = 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp "exec ultipro_aso2.dbo.gnsa_AEC_401k @pc, @pg"'
+ ' queryout'
+ ' "c:\EDI\401k\AEC\bcp401k.txt"'
+ ' -c'
+ ' -T'
+ ' -S' + @@servername
+ ''''
----------------------------------
returns: Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable

version 2
-----------------------------
set @cmd = char(39) + @pc + char(39) + ',' + char(39) + @pg + char(39)
set @sql = 'Exec Master..xp_Cmdshell ''bcp "exec ultipro_aso2.dbo.gnsa_AEC_401k '+@cmd+'" queryout "'+@FileName+'" -c -T'''

----------------------------------
returns: Msg 102, Level 15, State 1, Line 1 - Incorrect syntax near '20090116'.

Does anyone have experience or examples of making this work - or maybe point out where I am being an idiot ?

thanks!