Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Undeclared variable error messege problem Expand / Collapse
Author
Message
Posted Monday, July 6, 2009 5:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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
Post #747624
Posted Monday, July 6, 2009 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
Try this:

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

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


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #747628
Posted Monday, July 6, 2009 5:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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
Post #747635
Posted Monday, July 6, 2009 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 6,917, Visits: 6,994
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.

Post #747668
Posted Monday, July 6, 2009 6:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 5, 2014 5:16 AM
Points: 64, Visits: 153
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
Post #747673
Posted Monday, July 6, 2009 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 6,917, Visits: 6,994
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.

Post #747731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse