I have the following little import application, basically it...
- connects to a hosted SQL server,
- pulls down a sign-up list,
- inserts to staging table,
- checks for duplicates,
- filters non-dupes into main table.
Every Windows 7 box it ran on had no problem. With Windows XP, it gets to the first bit of LINQ and throws all kinds of crazy LINQ errors. I came to this conclusion by testing my program on approx. 10 computers. I replicated the problem by running the code on an XP virtual machine at home... same place (LINQ insert, see code below) where it crashed previously.
The error:
Error Occurred Getting Record From Remote Server: Details =>
System.Data.SqlClient.SqlException(0x80131904):
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges() at AfocImport.Program.Main(String[] args)
The code:
(Criticism is welcome, I'm still learning)
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Threading;
//using System.Threading.Tasks;
namespace AfocImport
{
internal class Program
{
private static void Main(string[] args)
{
var LNQ = new LNQDataContext();
var sel = from a in LNQ.Respondents
where a.dateenter > Convert.ToDateTime(DateTime.Today.ToShortDateString())
select new
{
a.recordid,a.phone,a.wphone,a.wphone_ext,a.fax,a.cellphone,a.email,a.area_code,a.area_code_w,a.lname,a.fname,a.address,a.city,a.state,a.zip,a.county,a.address_w,a.city_w,a.state_w,
a.zip_w,a.county_w,a.employer,a.Occ_title,a.jobcode,a.hours,a.datebirth,a.gender,a.maritalstatus,a.income,a.education,a.race,a.Party,a.typehome,a.spousejobcode,a.spousehours,a.childgen1,
a.childbd1,a.childgen2,a.childbd2,a.childgen3,a.childbd3,a.childgen4,a.childbd4,a.childgen5,a.childbd5,a.childgen6,a.childbd6,a.resp_type,a.dateenter,a.dateupdated,a.referred_by_resp,
a.Pro_resp,a.Caution_Flag,a.last_selection,a.last_participation,a.number_parts,a.calling_note,a.notes,a.CC1,a.CC2,a.CC3,a.CC4,a.CC5,a.CC6,a.CC7,a.CC8,a.CC9,a.CC10,a.CC11,a.CC12,a.CC13,
a.CC14,a.CC15,a.CC16,a.CC17,a.CC18,a.CC19,a.CC20,a.CC21,a.CC22,a.CC23,a.CC24,a.CC25,a.CC26,a.CC27,a.CC28,a.CC29,a.CC30,a.CC31,a.CC32,a.CC33,a.CC34,a.CC35,a.CC36,a.CC37,a.CC38,a.CC39,
a.CC40,a.CC41,a.CC42,a.CC43,a.CC44,a.CC45,a.CC46,a.CC47,a.CC48,a.CC49,a.CC50,a.c_brand,a.c_flavor,a.c_length,a.c_type,a.c_past_purchases,a.c_other_brands,a.car_make_1,a.car_model_1,
a.car_year_1,a.car_options_1,a.car_make_2,a.car_model_2,a.car_year_2,a.car_options_2,
};
var LNQH = new LNQHDataContext();
foreach (var a in sel)
{
Console.WriteLine("\tInserting to Staging: " + a.fname + " " + a.lname);
var r = new RespondentStaging
{
recordid = a.recordid,phone = a.phone,wphone = a.wphone,wphone_ext = a.wphone_ext,fax = a.fax,cellphone = a.cellphone,email = a.email,area_code = a.area_code,area_code_w = a.area_code_w,
lname = a.lname,fname = a.fname,address = a.address,city = a.city,state = a.state,zip = a.zip,county = a.county,address_w = a.address_w,city_w = a.city_w,state_w = a.state_w,zip_w = a.zip_w,
county_w = a.county_w,employer = a.employer,Occ_title = a.Occ_title,jobcode = a.jobcode,hours = a.hours,datebirth = a.datebirth,gender = a.gender,maritalstatus = a.maritalstatus,income = a.income,
education = a.education,race = a.race,Party = a.Party,typehome = a.typehome,spousejobcode = a.spousejobcode,spousehours = a.spousehours,childgen1 = a.childgen1,childbd1 = a.childbd1,childgen2 = a.childgen2,
childbd2 = a.childbd2,childgen3 = a.childgen3,childbd3 = a.childbd3,childgen4 = a.childgen4,childbd4 = a.childbd4,childgen5 = a.childgen5,childbd5 = a.childbd5,childgen6 = a.childgen6,childbd6 = a.childbd6,
resp_type = a.resp_type,dateenter = a.dateenter,dateupdated = a.dateupdated,referred_by_resp = a.referred_by_resp,Pro_resp = a.Pro_resp,Caution_Flag = a.Caution_Flag,last_selection = a.last_selection,
last_participation = a.last_participation,number_parts = a.number_parts,calling_note = a.calling_note,notes = a.notes,CC1 = a.CC1,CC2 = a.CC2,CC3 = a.CC3,CC4 = a.CC4,CC5 = a.CC5,CC6 = a.CC6,CC7 = a.CC7,
CC8 = a.CC8,CC9 = a.CC9,CC10 = a.CC10,CC11 = a.CC11,CC12 = a.CC12,CC13 = a.CC13,CC14 = a.CC14,CC15 = a.CC15,CC16 = a.CC16,CC17 = a.CC17,CC18 = a.CC18,CC19 = a.CC19,CC20 = a.CC20,CC21 = a.CC21,CC22 = a.CC22,
CC23 = a.CC23,CC24 = a.CC24,CC25 = a.CC25,CC26 = a.CC26,CC27 = a.CC27,CC28 = a.CC28,CC29 = a.CC29,CC30 = a.CC30,CC31 = a.CC31,CC32 = a.CC32,CC33 = a.CC33,CC34 = a.CC34,CC35 = a.CC35,CC36 = a.CC36,CC37 = a.CC37,
CC38 = a.CC38,CC39 = a.CC39,CC40 = a.CC40,CC41 = a.CC41,CC42 = a.CC42,CC43 = a.CC43,CC44 = a.CC44,CC45 = a.CC45,CC46 = a.CC46,CC47 = a.CC47,CC48 = a.CC48,CC49 = a.CC49,CC50 = a.CC50,c_brand = a.c_brand,c_flavor = a.c_flavor,
c_length = a.c_length,c_type = a.c_type,c_past_purchases = a.c_past_purchases,c_other_brands = a.c_other_brands,car_make_1 = a.car_make_1,car_model_1 = a.car_model_1,car_year_1 = a.car_year_1,car_options_1 = a.car_options_1,
car_make_2 = a.car_make_2,car_model_2 = a.car_model_2,car_year_2 = a.car_year_2,car_options_2 = a.car_options_2
};
LNQH.RespondentStagings.InsertOnSubmit(r);
try
{
LNQH.SubmitChanges();
}
catch (Exception ex)
{
Console.WriteLine("\tError Occurred Getting Record From Remote Server: Details => " + ex);
Console.ReadLine();
}
}
var cnt = Convert.ToInt32(LNQH.RespondentStagings.Count());
if (cnt != 0)
{
List<string> nam;
List<string> phn;
List<string> wph;
List<string> cph;
List<string> good = new List<string>();
List<string> bad = new List<string>();
GetLists(out nam, out phn, out wph, out cph);
var fln = from a in LNQH.RespondentStagings
select new { a.recordid, a.fname, a.lname, a.phone, a.wphone, a.cellphone };
foreach(var a in fln) //was in parrallel for loop but scraped that
{
if (nam.Contains(a.fname + a.lname) && (phn.Contains(a.phone) || wph.Contains(a.wphone) || cph.Contains(a.cellphone)))
{
//Add To Filter Table
InsFilter(a.recordid);
bad.Add("\tAdded to Filter Table: " + a.fname + " " + a.lname);
}
else
{
//Add To Respondent Table
InsRespondent(a.recordid);
good.Add("\tAdded to Respondent Table: " + a.fname + " " + a.lname);
nam.Add(a.fname + a.lname);
phn.Add(a.phone);
wph.Add(a.wphone);
cph.Add(a.cellphone);
}
}
Console.WriteLine("The Following Records Have Been Added to the Main Table... ");
foreach (var a in good) { Console.WriteLine(a); }
Console.WriteLine("The Following Records Are Potential Duplicates and Have Been Added to Quarantine ... ");
foreach (var a in bad) { Console.WriteLine(a); }
}
else
{
Console.WriteLine("Zero Records Inserted ... ");
}
using (var con = new SqlConnection())
{
con.ConnectionString = @"Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxxx";
try
{
con.Open();
var del = new SqlCommand("DELETE FROM dbo.Respondent", con) {CommandType = CommandType.Text};
del.ExecuteNonQuery();
}
finally
{
con.Close();
}
}
//Console.ReadLine();
}
public static void InsFilter(int recid)
{
var LNQH = new LNQHDataContext();
var sel = from a in LNQH.RespondentStagings
where a.recordid == recid
select new
{ a.recordid,a.phone,a.wphone,a.wphone_ext,a.fax,a.cellphone,a.email,a.area_code,a.area_code_w,a.lname,a.fname,a.address,a.city,a.state,a.zip,a.county,a.address_w,a.city_w,a.state_w,a.zip_w,a.county_w,
a.employer,a.Occ_title,a.jobcode,a.hours,a.datebirth,a.gender,a.maritalstatus,a.income,a.education,a.race,a.Party,a.typehome,a.spousejobcode,a.spousehours,a.childgen1,a.childbd1,a.childgen2,a.childbd2,
a.childgen3,a.childbd3,a.childgen4,a.childbd4,a.childgen5,a.childbd5,a.childgen6,a.childbd6,a.resp_type,a.dateenter,a.dateupdated,a.referred_by_resp,a.Pro_resp,a.Caution_Flag,a.last_selection,a.last_participation,
a.number_parts,a.calling_note,a.notes,a.CC1,a.CC2,a.CC3,a.CC4,a.CC5,a.CC6,a.CC7,a.CC8,a.CC9,a.CC10,a.CC11,a.CC12,a.CC13,a.CC14,a.CC15,a.CC16,a.CC17,a.CC18,a.CC19,a.CC20,a.CC21,a.CC22,a.CC23,a.CC24,a.CC25,a.CC26,a.CC27,
a.CC28,a.CC29,a.CC30,a.CC31,a.CC32,a.CC33,a.CC34,a.CC35,a.CC36,a.CC37,a.CC38,a.CC39,a.CC40,a.CC41,a.CC42,a.CC43,a.CC44,a.CC45,a.CC46,a.CC47,a.CC48,a.CC49,a.CC50,a.c_brand,a.c_flavor,a.c_length,a.c_type,a.c_past_purchases,
a.c_other_brands,a.car_make_1,a.car_model_1,a.car_year_1,a.car_options_1,a.car_make_2,a.car_model_2,a.car_year_2,a.car_options_2,
};
foreach (var a in sel)
{
var f = new RespondentFiltered
{
phone_Y = a.phone,wphone_Y = a.wphone,wphone_ext_Y = a.wphone_ext,fax_Y = a.fax,cellphone_Y = a.cellphone,email_Y = a.email,area_code_Y = a.area_code,area_code_w_Y = a.area_code_w,lname_Y = a.lname,fname_Y = a.fname,
address_Y = a.address,city_Y = a.city,state_Y = a.state,zip_Y = a.zip,county_Y = a.county,address_w_Y = a.address_w,city_w_Y = a.city_w,state_w_Y = a.state_w,zip_w_Y = a.zip_w,county_w_Y = a.county_w,employer_Y = a.employer,
Occ_title_Y = a.Occ_title,jobcode_Y = a.jobcode,hours_Y = a.hours,datebirth_Y = a.datebirth,gender_Y = a.gender,maritalstatus_Y = a.maritalstatus,income_Y = a.income,education_Y = a.education,race_Y = a.race,Party_Y = a.Party,
typehome_Y = a.typehome,spousejobcode_Y = a.spousejobcode,spousehours_Y = a.spousehours,childgen1_Y = a.childgen1,childbd1_Y = a.childbd1,childgen2_Y = a.childgen2,childbd2_Y = a.childbd2,childgen3_Y = a.childgen3,childbd3_Y = a.childbd3,
childgen4_Y = a.childgen4,childbd4_Y = a.childbd4,childgen5_Y = a.childgen5,childbd5_Y = a.childbd5,childgen6_Y = a.childgen6,childbd6_Y = a.childbd6,resp_type_Y = a.resp_type,dateenter_Y = a.dateenter,dateupdated_Y = a.dateupdated,
referred_by_resp_Y = a.referred_by_resp,Pro_resp_Y = a.Pro_resp,Caution_Flag_Y = a.Caution_Flag,last_selection_Y = a.last_selection,last_participation_Y = a.last_participation,number_parts_Y = a.number_parts,calling_note_Y = a.calling_note,
notes_Y = a.notes,CC1_Y = a.CC1,CC2_Y = a.CC2,CC3_Y = a.CC3,CC4_Y = a.CC4,CC5_Y = a.CC5,CC6_Y = a.CC6,CC7_Y = a.CC7,CC8_Y = a.CC8,CC9_Y = a.CC9,CC10_Y = a.CC10,CC11_Y = a.CC11,CC12_Y = a.CC12,CC13_Y = a.CC13,CC14_Y = a.CC14,CC15_Y = a.CC15,
CC16_Y = a.CC16,CC17_Y = a.CC17,CC18_Y = a.CC18,CC19_Y = a.CC19,CC20_Y = a.CC20,CC21_Y = a.CC21,CC22_Y = a.CC22,CC23_Y = a.CC23,CC24_Y = a.CC24,CC25_Y = a.CC25,CC26_Y = a.CC26,CC27_Y = a.CC27,CC28_Y = a.CC28,CC29_Y = a.CC29,CC30_Y = a.CC30,
CC31_Y = a.CC31,CC32_Y = a.CC32,CC33_Y = a.CC33,CC34_Y = a.CC34,CC35_Y = a.CC35,CC36_Y = a.CC36,CC37_Y = a.CC37,CC38_Y = a.CC38,CC39_Y = a.CC39,CC40_Y = a.CC40,CC41_Y = a.CC41,CC42_Y = a.CC42,CC43_Y = a.CC43,CC44_Y = a.CC44,CC45_Y = a.CC45,
CC46_Y = a.CC46,CC47_Y = a.CC47,CC48_Y = a.CC48,CC49_Y = a.CC49,CC50_Y = a.CC50,c_brand_Y = a.c_brand,c_flavor_Y = a.c_flavor,c_length_Y = a.c_length,c_type_Y = a.c_type,c_past_purchases_Y = a.c_past_purchases,c_other_brands_Y = a.c_other_brands,
car_make_1_Y = a.car_make_1,car_model_1_Y = a.car_model_1,car_year_1_Y = a.car_year_1,car_options_1_Y = a.car_options_1,car_make_2_Y = a.car_make_2,car_model_2_Y = a.car_model_2,car_year_2_Y = a.car_year_2,car_options_2_Y = a.car_options_2
};
LNQH.RespondentFiltereds.InsertOnSubmit(f);
try { LNQH.SubmitChanges(); }
catch (Exception ex)
{
Console.WriteLine("\tError Occurred Moving Record To Staging Database: Details => " + ex);
Console.ReadLine();
}
}
var del = from b in LNQH.RespondentStagings
where b.recordid == recid
select b;
foreach (var b in del)
{
LNQH.RespondentStagings.DeleteOnSubmit(b);
}
try { LNQH.SubmitChanges(); }
catch (Exception ex)
{
Console.WriteLine("\tError Occurred Deleting Record From Staging Database: Details => " + ex);
Console.ReadLine();
}
}
public static void InsRespondent(int recid)
{
var LNQH = new LNQHDataContext();
var sel = from a in LNQH.RespondentStagings
where a.recordid == recid
select new
{ a.recordid,a.phone,a.wphone,a.wphone_ext,a.fax,a.cellphone,a.email,a.area_code,a.area_code_w,a.lname,a.fname,a.address,a.city,a.state,a.zip,a.county,a.address_w,a.city_w,a.state_w,a.zip_w,
a.county_w,a.employer,a.Occ_title,a.jobcode,a.hours,a.datebirth,a.gender,a.maritalstatus,a.income,a.education,a.race,a.Party,a.typehome,a.spousejobcode,a.spousehours,a.childgen1,a.childbd1,a.childgen2,
a.childbd2,a.childgen3,a.childbd3,a.childgen4,a.childbd4,a.childgen5,a.childbd5,a.childgen6,a.childbd6,a.resp_type,a.dateenter,a.dateupdated,a.referred_by_resp,a.Pro_resp,a.Caution_Flag,a.last_selection,
a.last_participation,a.number_parts,a.calling_note,a.notes,a.CC1,a.CC2,a.CC3,a.CC4,a.CC5,a.CC6,a.CC7,a.CC8,a.CC9,a.CC10,a.CC11,a.CC12,a.CC13,a.CC14,a.CC15,a.CC16,a.CC17,a.CC18,a.CC19,a.CC20,a.CC21,a.CC22,
a.CC23,a.CC24,a.CC25,a.CC26,a.CC27,a.CC28,a.CC29,a.CC30,a.CC31,a.CC32,a.CC33,a.CC34,a.CC35,a.CC36,a.CC37,a.CC38,a.CC39,a.CC40,a.CC41,a.CC42,a.CC43,a.CC44,a.CC45,a.CC46,a.CC47,a.CC48,a.CC49,a.CC50,a.c_brand,
a.c_flavor,a.c_length,a.c_type,a.c_past_purchases,a.c_other_brands,a.car_make_1,a.car_model_1,a.car_year_1,a.car_options_1,a.car_make_2,a.car_model_2,a.car_year_2,a.car_options_2,
};
foreach (var a in sel)
{
#region SQL_INSERT
using (var con = new SqlConnection())
{
con.ConnectionString = @"Data Source=xxxx;Initial Catalog=xxxx;User ID=sa;Password=xxxx";
try
{
con.Open();
var cmd = new SqlCommand("INS_Respond_Local", con) {CommandType = CommandType.StoredProcedure};
var del = new SqlCommand("DELETE FROM dbo.RespondentStaging WHERE recordid = '" + recid + "'", con) {CommandType = CommandType.Text};
#region DeclareSQL
//Bunchof Markup
#endregion
cmd.ExecuteNonQuery();
del.ExecuteNonQuery();
}
finally
{
con.Close();
}
#endregion
}
}
}
private static void GetLists(out List<string> nam, out List<string> phn, out List<string> wph, out List<string> cph)
{
var nam_x = new List<String>();
var phn_x = new List<String>();
var wph_x = new List<String>();
var cph_x = new List<String>();
using (var con = new SqlConnection())
{
con.ConnectionString = @"Data Source=xxxx;Initial Catalog=xxxx;User ID=sa;Password=xxxx";
con.Open();
var cmd = new SqlCommand("SELECT fname + lname as 'N', phone as 'P', wphone as 'W', cellphone as 'C' FROM dbo.Respondent WHERE fname is not null and lname is not null order by fname + lname", con) { CommandType = CommandType.Text };
try
{
var red = cmd.ExecuteReader();
while (red.Read())
{
nam_x.Add(red["N"].ToString());
phn_x.Add(red["P"].ToString());
wph_x.Add(red["W"].ToString());
cph_x.Add(red["C"].ToString());
}
}
catch { }
finally { con.Close(); }
}
nam = nam_x;
phn = phn_x;
wph = wph_x;
cph = cph_x;
}
}
}
