SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with frustrating BCP call and stored procedure params


Help with frustrating BCP call and stored procedure params

Author
Message
bbaley
bbaley
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 132
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!
bbaley
bbaley
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 132
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....
J-F Bergeron
J-F Bergeron
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3161 Visits: 2707
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
bbaley
bbaley
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 132
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 ;-)
Ashfaq Chougle
Ashfaq Chougle
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 163
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search