Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Posts
    10

    Unanswered: using parameters within a BCP command

    Hi all,

    i was wondering if you could help me with a problem i have. I have created a stored procedure for the purpose of loading and transforming data from a different system.

    I want to export this data out and am looking at using the BCP command as follows:

    exec master..xp_cmdshell 'BCP "exec frmpc.dbo.testbut" QUERYOUT e:\data.csv -c -T -t,'

    This command works fine and i can get data out the way i want to. There are several things i want to do:

    1. for the data path (shown as e:\data.csv) - i want to be able to build through a series of parameters the correct file name (like division, month, year 01sept2009)

    I have tried to create a variable (declared) and then used the set command to set the contents of the variable, but i just keep getting an error must declare the scalar variable

    any help you could give would be appreciated.

    many thanks

    chappo

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just build up an NVARCHAR variable to the string you want to execute and then execute that.

    Easy peasy
    (although be careful it can't be injected)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Like this, in case what I've described is what you say you have tried:
    Code:
    DECLARE @string AS NVARCHAR(4000)
    
    SELECT    @string = 'BCP "exec frmpc.dbo.testbut" QUERYOUT e:\data.csv -c -T -t'
    
    exec master.dbo.xp_cmdshell @string
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2005
    Posts
    10
    thanks for the help - unfortunately its a bit beyond me -

    here is what ive tried

    create proc testbut
    as
    set nocount on
    declare @div nvarchar(10)
    declare @mth nvarchar(10)
    declare @year nvarchar(10)
    declare @divmy nvarchar(50)
    declare @bcpcmd nvarchar(50)

    set @div='01'
    set @mth='jan'
    set @year='2009'
    set @divmy = 'E:\' +@div+@mth+@year+'.CSV'
    set @bcpcmd = 'BCP "exec frmpc.dbo.testbut" QUERYOUT @divmy -c -T -t,'

    DECLARE @dataload table ( [ac_code] [nvarchar](50) NULL, [Value] [Money] null

    insert into @dataload select * from dataload2

    --
    select * from @dataload
    GO
    EXEC testbut

    --BCP Test
    exec master..xp_cmdshell @bcpcmd

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rule #1 - if you are ever building up stuff dynamically, print it out. You will spot the error immediately then.

    Code:
    PRINT @bcpcmd
    --exec master..xp_cmdshell @bcpcmd
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2005
    Posts
    10
    Hi,

    thanks for your help so far - its leading me in the right direction! - below is the sp im creating for the BCP command, but i am still struggling to get the exec xp_cmdshell command to recognise the variable (get a scalar variable error) in the EXEC part - having read up it seems to be because i am running dynamic sql (whatever that is!) -

    any suggestions as to where to go on from here?

    Code:
    Use FRMPC
    GO 
    Create PROC testbut
    AS
    Set NoCount ON
    DECLARE @Div nvarchar(10)
    DECLARE @Mth nvarchar(10)
    DECLARE @Year nvarchar(10)
    DECLARE @DIVMY nvarchar(100)
    DECLARE @BCPCMD nvarchar(4000)
    DECLARE @BCPCMD1 nvarchar(4000)
    DECLARE @BCPCMD2 nvarchar(4000)
    Declare @SQLString nvarchar(4000)
    
    SET @DIV = 'DIV01'
    SET @mth = 'JAN'
    SET @year = '2009'
    SELECT @DIVMY = '"E:\ADPDataload\'+@DIV+@mth+@year+'.csv"'
    SELECT @BCPCMD1 = 'BCP "exec frmpc.dbo.testbut" QUERYOUT '
    SELECT @BCPCMD2 = ' -c -T -t,'
    SET @BCPCMD = 'EXEC master..xp_cmdshell '+@BCPCMD1+@DIVMY+@BCPCMD2
    
    Set @SQLString = N'Exec master..xp_cmdshell @BCPCMD1, @Divmy, @BCPCMD2'
    
    DECLARE @ADPDataload Table (
    	[AC_Code] [nvarchar](50) NULL,	[Value] [money] NULL,	[Month] [nvarchar](50) NULL,
    	[Year] [nvarchar](50) NULL,	[ADP_Desc] [nvarchar](50) NULL ) 
    
    -- Inserting data from the 
    ..
    --script for selecting end results
    
    PRINT @BCPCMD
    PRINT @sqlstring
    GO 
    EXEC testbut
    
    --BCP test
    --PRINT @bcpcmd
    --EXEC SP_executesql N'@BCPCMD' 
    --exec master..xp_cmdshell N'@BCPCMD'
    
    DROP PROC testbut
    GO

    the bcpcmd and sqlstring give the following results:

    Code:
    EXEC master..xp_cmdshell BCP "exec frmpc.dbo.testbut" QUERYOUT "E:\ADPDataload\DIV01JAN2009.csv" -c -T -t,
    Exec master..xp_cmdshell @BCPCMD1, @Divmy, @BCPCMD2
    the bcpcmd is correct (and this is the command i want to execute) - where as the sqlstring doesnt recognise the @'s

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Whoever told you to use sp_executesql for this was wrong. You've added stuff that I don't understand the reasoning for.

    Try this for now:
    Code:
    Use FRMPC
    GO 
    Create PROC testbut
    AS
    Set NoCount ON
    DECLARE @Div nvarchar(10)
    DECLARE @Mth nvarchar(10)
    DECLARE @Year nvarchar(10)
    DECLARE @DIVMY nvarchar(100)
    DECLARE @BCPCMD nvarchar(4000)
    DECLARE @BCPCMD1 nvarchar(4000)
    DECLARE @BCPCMD2 nvarchar(4000)
    Declare @SQLString nvarchar(4000)
    
    SET @DIV = 'DIV01'
    SET @mth = 'JAN'
    SET @year = '2009'
    SELECT @DIVMY = '"E:\ADPDataload\'+@DIV+@mth+@year+'.csv"'
    SELECT @BCPCMD1 = 'BCP "exec frmpc.dbo.testbut" QUERYOUT '
    SELECT @BCPCMD2 = ' -c -T -t,'
    SET @BCPCMD = @BCPCMD1+@DIVMY+@BCPCMD2
    
     -- Inserting data from the 
    ..
    --script for selecting end results
    
    PRINT @BCPCMD
    
    EXEC master..xp_cmdshell @BCPCMD
     
    --BCP test
    --PRINT @bcpcmd
    --EXEC SP_executesql N'@BCPCMD' 
    --exec master..xp_cmdshell N'@BCPCMD'
    GO
    EXEC testbut

    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    whenever I have a proc that builds up a string to be executed, I always add a @debug bit=0 param where if it's true, all it does is PRINT the string and doesn't EXEC or xp_cmdshell anything.

    useful for debugging so you don't have to keep commenting/uncommenting the PRINT command.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Totally agreed - me too - in fact I'm debugging a sproc right now by that very method!
    Don't want to give away all our secrets in one go though eh
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2005
    Posts
    10
    hi pootle - no-one told me to use sp_executesql - i was trying various commands to complete the job

    thanks for your help - it worked a treat - silly when you look at it really, as the problem was that i was using the bcp outside of the GO

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - I think you were overcomplicating things
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •