Undeclared variable error messege problem

  • I defined a variable

    and want to right its value to text file I used this code for transfer

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000),

    @CTX_TRAILER char(2000)

    set @CTX_TRAILER ='HIHIHI'

    SET @FileName = 'D:\alianz\output\CEMTXOUT_ALIANZ'

    SET @bcpCommand = 'bcp " select @CTX_TRAILER " queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'

    EXEC master..xp_cmdshell @bcpCommand

    select @bcpCommand

    the above code always give error Must declare the variable '@CTX_TRAILER' any advice please, this is a sample code that I will use to build large one

  • Try this:

    SET @bcpCommand = 'bcp " select ' + @CTX_TRAILER +' " queryout "'

    Why aren't you using the T-SQL "Backup database" command with a SQLCMD ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (7/6/2009)


    Try this:

    SET @bcpCommand = 'bcp " select ' + @CTX_TRAILER +' " queryout "'

    Why aren't you using the T-SQL "Backup database" command with a SQLCMD ?

    It not owrk I tried '+ @Variable+' , I didn't understand T-SQL "Backup database" command with a SQLCMD

  • 1. The first SET @bcpCommand is malformed, it needs to be

    SET @bcpCommand = 'bcp "select ''' + @CTX_TRAILER +'''" queryout "'

    2. @CTX_TRAILER is char(2000) and @bcpCommand varchar(2000) therefore it is too small to contain the command and the command will be truncated thus causing an error

    based on the definition you posted @bcpCommand needs to be at least varchar(2083)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • I displayed the output and it work but when trbasfer it give this error

    Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

    do u have any idea to fix it

  • Make sure the destination folder exists and that the SQL Server service account has write access.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply