Create field from variable

[nsm1234567]

Hi There, I'm having an issue running the below script.  I have identical tables to retrieve data from which lie on two different servers.  To make the scripting more manageable, I've created a "for" loop which, in theory, would connect to "Company1" the first time round and then "Company2" the second time round.

The problem I have is that when I start loading the data, using "$(zServer)" I get "Field not found - <Company1>" as an error.  If instead  I do the load as:

LOAD recno() as [ID INLINE ],

'Company1' as [Server],

I don't get any problems.

Can anyone see where I'm going wrong here?  Any help would be appreciated

For each CNTR in  20, 40

if $(CNTR) = 20 then

set zServer = 'Company1';

ODBC CONNECT TO COMPANY1 (XUserId is DdBFdAFJSKZCGSdJODbSHSC, XPassword is eBVBYAFGJSbKTaJOBTbGWSZN);

ENDIF     

if $(CNTR) = 40 then

set zServer = 'Company2';

ODBC CONNECT TO COMPANY2 (XUserId is DdBFdAFJSKZCGSdJODbSHSC, XPassword is eBVBYAFGJSbKTaJOBTbGWSZN);

ENDIF                                     

SMSTABLE:

LOAD recno() as [ID INLINE ],

          $(zServer) as [Server],

    "Comm_Crt_Date",

    "Comm_Report_Cde",

    "Comm_Template",

     "In_Message";

SQL SELECT *

FROM Softsure.dbo."View_SMSInBox"

where CONVERT(date,Comm_Crt_Date) >= '2013-01-10'

AND Comm_Report_Cde = '17'

and (SOUNDEX(In_Message) = SOUNDEX('Excellent')

or SOUNDEX(In_Message) = SOUNDEX('Good')  

or SOUNDEX(In_Message) = SOUNDEX('Bad'))  ;

//-------- End Multiple Select Statements ------

NEXT

[Gysbert_Wassenaar · Accepted solution]

Try adding single quotes around the variable:

SMSTABLE:

LOAD recno() as [ID INLINE ],

     '$(zServer)' as [Server],

    "Comm_Crt_Date",

    "Comm_Report_Cde",

    "Comm_Template",

     "In_Message";

[nsm1234567]

That worked!  Thanks for the help.