|
|
Dan,
My DBA doesn't have the experience to help with a VIEW. Did I mention that
these are 2 separate databases on different servers? This project is over a
year old now and it really needs to get finished so I thought the import would
be the easiest way to go. Thanks to your help, it is almost working.
I added some additional code to check for a changed email address and update
the NetNews database. It runs without error, but I don't have a way to test it
right now. Can you please look at the code and see if it looks OK?
I am also still getting an error on line 10 after the routine runs. The line
that has this code: "and membernumber not in (<cfqueryparam list="yes"
value="#valuelist(newsmember.membernumber)# cfsqltype="cf_sql_integer">)" even
with the cfif that Phil suggested.
-------------
<cfquery datasource="#application.ds#" name="newsMember">
SELECT distinct MemberNumber, Email_Address
FROM NetNewsTest
</cfquery>
<cfquery datasource="#application.dsrepl#" name="qryMember">
SELECT distinct Email,FirstName,LastName,MemberNumber
FROM members
WHERE memberstanding <= 2 AND email IS NOT NULL AND email <> ' '
and membernumber not in (<cfqueryparam list="yes"
value="#valuelist(newsmember.membernumber)#" cfsqltype="cf_sql_integer">)
</cfquery>
<CFIF qryMember.recordcount NEQ 0>
<cfloop query ="qryMember">
<cfquery datasource="#application.ds#" name="newsMember">
insert into NetNewsTest (Email_address, First_Name, Last_Name, MemberNumber)
values ('#trim(qryMember.Email)#', '#trim(qryMember.FirstName)#',
'#trim(qryMember.LastName)#', '# trim(qryMember.MemberNumber)#')
</cfquery>
</cfloop>
</cfif>
<cfquery datasource="#application.dsrepl#" name="qryEmail">
SELECT distinct Email
FROM members
WHERE memberstanding <= 2 AND email IS NOT NULL AND email <> ' '
and qryMember.email NEQ newsMember.email
</cfquery>
<CFIF qryEmail.recordcount NEQ 0>
<cfloop query ="qryEmail">
<cfquery datasource="#application.ds#" name="newsMember">
update NetNewsTest (Email_address)
values ('#trim(qryMember.Email)#')
where email_address = #qryEmail.email#
</cfquery>
</cfloop>
</cfif>
-----------------------------------------------------
Thank you again for the help.
|
|