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
