Undeclared variable problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Undeclared variable problem

Author

Topic

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-07-06 : 07:12:33

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:46:33

@CTX_TRAILER is your problem.
use ...' + @CTX_TRAILER + '...
so that @bcpCommand is complete ready for use.

No, you're never too old to Yak'n'Roll if you're too young to die.

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-07-06 : 07:49:39

quote:
Originally posted by webfred

@CTX_TRAILER is your problem.
use ...' + @CTX_TRAILER + '...
so that @bcpCommand is complete ready for use.

No, you're never too old to Yak'n'Roll if you're too young to die.

will not work the output will be just

bcp " select HIHIHI

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:56:28

Make @CTX_TRAILER VARCHAR instead of CHAR.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:57:55

And think about: if you need really 2000 byte for ctx_trailer then it doesn't fit to bcpCommand with 2000 byt too.

No, you're never too old to Yak'n'Roll if you're too young to die.

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-07-06 : 08:25:19

quote:
Originally posted by webfred

And think about: if you need really 2000 byte for ctx_trailer then it doesn't fit to bcpCommand with 2000 byt too.

No, you're never too old to Yak'n'Roll if you're too young to die.

thanks for the help but it give

invalid column name HIHIHI , I need to transfer this word to the text file

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 08:31:05

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000),
@CTX_TRAILER varchar(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'

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 08:33:30

after select 3 single quotes and after @ctx_trailer + also 3 single quotes!

No, you're never too old to Yak'n'Roll if you're too young to die.

Ali.M.Habib
Yak Posting Veteran

54 Posts

Posted - 2009-07-06 : 08:47:30

quote:
Originally posted by webfred

after select 3 single quotes and after @ctx_trailer + also 3 single quotes!

No, you're never too old to Yak'n'Roll if you're too young to die.

wow worked very well but another question
the new error be :
Unable to open BCP host data-file
do u know how to fix that

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 08:57:06

Maybe that is a permission problem to the file system but I don't know how to fix it - sorry.
Maybe you can google that error message or someone from this forum knows that problem...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources