Help with frustrating BCP call and stored procedure params

  • 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!

  • btw - I am assuming it's complaining about the "must declarescalar var..." - because of SCOPE...

    And that is my question... it obviously parses the param string well enough to identify the parameter..

    however, my var is declared BEFORE the @sql string is generated - so i can't figure out how to put it all together....

  • Here, I don't really feel like reading this bcp thing, I know how to work it out, since I've built some myself, but I simply hate it. Here's a piece of code I use, that shows how I sent the parameters to a SP, hope it helps,

    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

    Tell me if it helps,

    Cheers,

    J-F

  • Thanks I'll try that.

    I'm very open to other suggestions...

    the reason for trying the BCP option, is that it has been a small version of hell working with SSIS and it's flat file connections/managers -

    so I was trying to bypass all of that and maintain just my stored procedure where I can very easily specify the fields (cast) and a very simple format file....

    whereas, everytime I make a change in the SSIS method I go through hell and back trying to keep it happy and have to have psuedo/meta files in the output directory, and you can't re-oreder the fields, and so on ad-nauseum....

    I could have written this in Delphi/C#/etc in 1/4 the time and wondering why I didn't.... so I am assuming I'm just missing out on that "one good suggestion" to convince of the right way 😉

  • 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!

    Well the quote problem is always the big problem, I know I have faced it many times.

    Anyways try the following

    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

    + ''''

    ----------------------------------

    The problem with version 1 was you were trying to supply the values @pc and @pg which were not in the scope of the @sql. What I mean is

    DECLARE @pg VARCHAR(10) = 'abc'

    DECLARE @pc VARCHAR(10) = 'pqr'

    DECLARE @sql VARCHAR(MAX)

    SET @sql = 'EXEC prcTest @pg,@pc'

    EXEC @sql

    This Exec @sql statement will always return error because SQL Engine will try to execute only the @sql as a batch and in that batch @pg and @pc are out of scope.

    For version 2

    -----------------------------

    set @cmd = char(39) + char(39) + @pc + char(39) + char(39) + ',' + char(39) + char(39) + @pg + char(39) + char(39)

    set @sql = 'Exec Master..xp_Cmdshell ''bcp "exec ultipro_aso2.dbo.gnsa_AEC_401k '+@cmd+'" queryout "'+@FileName+'" -c -T'''

    ----------------------------------

    The reason for error in this was because the value @pc should be enclosed as ''20090116'' for the quotes to be properly enclosed as per your @cmd string they were just set as '20090116' so closing the first quote before the bcp.

    I hope the solution works for you!:-D

    Ashfaq Chougle.
    Database Administrator
    IPS Group Inc.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply