Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with frustrating BCP call and stored procedure params Expand / Collapse
Author
Message
Posted Thursday, January 22, 2009 11:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:04 AM
Points: 14, Visits: 131
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!
Post #641900
Posted Thursday, January 22, 2009 11:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:04 AM
Points: 14, Visits: 131
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....

Post #641947
Posted Thursday, January 22, 2009 2:06 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
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
Post #642057
Posted Thursday, January 22, 2009 2:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:04 AM
Points: 14, Visits: 131
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
Post #642065
Posted Thursday, June 23, 2011 6:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 194, Visits: 153
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!


Ashfaq Chougle.
Database Administrator
IPS Group Inc.
Post #1130886
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse