July 6, 2009 at 5:26 am
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
July 6, 2009 at 5:34 am
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
July 6, 2009 at 5:46 am
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
July 6, 2009 at 6:54 am
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.
July 6, 2009 at 6:59 am
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
July 6, 2009 at 7:48 am
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