Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: BCP in stored procedure

    Hi,
    I am using the following statements in a atored procedure to be executed in SQL Server 2000.

    SET @QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @L_CURRENTDATE" queryout '+@L_OUT_PATH+@L_OUT_FILENAME+'" -c -q'

    SET @QUERY = 'execute master.dbo.xp_cmdshell '+''''+@QUERY+''''

    EXECUTE SP_EXECUTESQL @QUERY,N'@L_CURRENTDATE DATETIME',@L_CURRENTDATE

    I get the following error:
    SQLState = 37000, NativeError = 137
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@L_CURRENTDATE'.
    SQLState = 37000, NativeError = 8180
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

    Can someone help me out?

    Regards,
    Bharathram G

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by bharathram2911
    Hi,
    I am using the following statements in a atored procedure to be executed in SQL Server 2000.

    SET @QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @L_CURRENTDATE" queryout '+@L_OUT_PATH+@L_OUT_FILENAME+'" -c -q'

    SET @QUERY = 'execute master.dbo.xp_cmdshell '+''''+@QUERY+''''

    EXECUTE SP_EXECUTESQL @QUERY,N'@L_CURRENTDATE DATETIME',@L_CURRENTDATE

    I get the following error:
    SQLState = 37000, NativeError = 137
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@L_CURRENTDATE'.
    SQLState = 37000, NativeError = 8180
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

    Can someone help me out?

    Regards,
    Bharathram G
    It seems many things are missing,plz provide full code of the stored pro.
    Joydeep

  3. #3
    Join Date
    Feb 2006
    Posts
    5
    Hi,
    Following is the full code:
    create procedure dbo.test as
    begin
    DECLARE @L_CURRENTDATE DATETIME
    DECLARE @L_OUT_PATH VARCHAR(100)
    DECLARE @L_OUT_FILENAME VARCHAR(100)
    DECLARE @QUERY NVARCHAR(4000)

    SET @L_CURRENTDATE = GETDATE()
    SET @L_OUT_PATH = '"C:\'
    SET @L_OUT_FILENAME = 'out.txt'

    SET @QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @L_CURRENTDATE"'
    SET @QUERY = 'bcp "'+@QUERY+'" queryout '+@L_OUT_PATH+@L_OUT_FILENAME+'" -c -q'
    SET @QUERY = 'execute master.dbo.xp_cmdshell '+''''+@QUERY+''''
    EXECUTE SP_EXECUTESQL @QUERY,N'@L_CURRENTDATE DATETIME',@L_CURRENTDATE

    END

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by bharathram2911
    Hi,
    Following is the full code:
    create procedure dbo.test as
    begin
    DECLARE @L_CURRENTDATE DATETIME
    DECLARE @L_OUT_PATH VARCHAR(100)

    Hi ,
    Try this ...
    create procedure dbo.test as
    begin
    DECLARE @L_CURRENTDATE DATETIME
    DECLARE @L_OUT_PATH VARCHAR(100)
    DECLARE @L_OUT_FILENAME VARCHAR(100)
    DECLARE @QUERY NVARCHAR(4000)

    SET @L_CURRENTDATE = GETDATE()
    SET @L_OUT_PATH = 'C:\'
    SET @L_OUT_FILENAME = 'out.txt'

    SET @QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
    print @query

    SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@L_CURRENTDATE)

    SET @QUERY = 'bcp "'+@QUERY+'" queryout '+@L_OUT_PATH+@L_OUT_FILENAME+' -c -P '
    print @query
    SET @QUERY = 'execute master.dbo.xp_cmdshell '+''''+@QUERY+''''


    EXECUTE SP_EXECUTESQL @QUERY

    end
    Joydeep

Posting Permissions

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