January 22, 2009 at 11:07 am
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!
January 22, 2009 at 11:57 am
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....
January 22, 2009 at 2:06 pm
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
END
GO
Tell me if it helps,
Cheers,
J-F
January 22, 2009 at 2:12 pm
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 😉
June 23, 2011 at 6:04 pm
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