Data Insertion Too Too Slow...
Nov 27, 2007
Hi,
Env is ms sql server 2000.
ddl:
create table srchPool(tid int primary key, taid int, s tynyint, uid
tynyint);
-- and sql server automatically creates a clustered index for the pk
dml:
insert into srchPool(taid,s,uid)
select article_id, 99, 1484
from targetTBL
where article_content LIKE '% presentation %';
insert into srchPool(taid,s,uid)
select article_id, 55, 1484
from targetTBL
where article_content LIKE '% demonstration %';
-- a few more similar queries ...
The above insertion query TOOK about 2000ms to execute, too too slow,
would be much faster if I insert the data sets into a temp tbl like
select article_id, 99, 1484 into #srchPool(taid,s,uid)
from targetTBL
where article_content LIKE '% presentation %';
-- once its use is finished and drop it
?
Many thanks.
View 7 Replies
ADVERTISEMENT
Jun 19, 2007
Hello,
I have noticed that for one of my data-flows, the process is really long during the phase "the final commit data insertion has started".
To be accurate, the process is fast until it reaches this phase. It happens often when I load millions of lines.
The extraction is done from a database SQL Server 2005 to a database SQL Server 2005, on the same server (with the SQL Server native provider).
I used a SQL Server destination but I have tried with an OLE DB destination and it is the same situation.
Why the process could be so long during this phase?
There is a way to optimised my package to avoid that?
Any idea is welcome.
Thanks.
Guillaume
View 6 Replies
View Related
Mar 23, 2007
I am using vs2005 and sqlserver2000
My problem is i have 5 checkboxes and some textboxes.In this user selects the checkbox and textboxes dynamically .In this user selects one or two or three or when user selects header checkbox then all checkboxes are selected.And in my database i mentioned a single column for group of checkboes.So how should i insert the data into database
dim con as new sqlconnection("userid=sa;pwd=; initial catalog=test")
dim cmd as sql command
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
cmd.CommandText = "insert into check values(" what should i write here ....'" & text.text &"','" @ text2.text "'... ")
con.open()
cmd.executenonquery()
con.close()
End Sub
View 2 Replies
View Related
Jun 19, 2008
Hi Friends,I have 3 labels Steet,City,Pincode and 3 textboxes related to the labels and one button as nae 'Address'I gave the data for Street:abc,City:xyz,Pincode:123 and have to insert into the table.I created one table in the database with table name Adreess and column address varchar(100)but after giving the values in the textboxes and clicked on the button its throwing the exception i.e System.Data.SqlClient.SqlException: The name "abcxyz123" is not permitted in
this context. Valid expressions are constants, constant expressions, and (in
some contexts) variables. Column names are not permitted.I wrote the code like following protected void Button1_Click(object sender, EventArgs e) { string street = txtStreetNo.Text; string city = txtCity.Text; string pincode = txtPincode.Text; string com = street + city+pincode; conn.Open(); SqlDataAdapter daInsert = new SqlDataAdapter("insert into Address values(" + com.ToString() + ")", conn); daInsert.SelectCommand.ExecuteNonQuery();--->here its giving the exception conn.Close(); Response.Write("the values are inserted"); }Please any one tell me am I did the code write or not if its not please give any suggetionsthanksGeeta
View 3 Replies
View Related
Sep 9, 2013
I want XML data to be inserted int SQL table but could not figure out. #Currency is my table with assocaite columns and @XMLCurrency is a variable which holds XML string. How can I insert this XML data to my table.
Create table #Currency (CurrencyId int ,ISOCode nvarchar(10),ISONumbricCOde int,ISOName nvarchar(50), IsEnabledForMPV int default 0)
Declare @XMLCurrency nvarchar(max)
Set @XMLCurrency='<R><T><A>0</A><B>USD</B><C>840</C><D>US Dollar</D></T></R>'
Value 840 should insert into column ISONumbricCOde .
value USD should be insert into ISOCode column.
value 0 should insert into column CurrencyId.
values US Dollar should insert into column ISOName .
View 2 Replies
View Related
Jun 12, 2006
Hi,
i have 4 tables, each consist of app. 10000000 rows.They have same columns (fTime[datetime] and bid[money]).What i wanna do is to collect all of datas into one of the tables, in ascending order by fTime.
PS i wanna do it as fast as possible as well
View 1 Replies
View Related
Dec 25, 2007
HI There,
I am using SQL Server 2005 and C#.Net 2005 as a front end.
I have written stored procedure to insert record into table. In that table i have field named 'TARIFF_CODE' with type 'Varchar' and size 15.
I have also written front end code with Sqlcommand object to call this sp and passed all proper required parameter values.
ICDPara = new SqlParameter();
ICDPara.ParameterName = "@Tariff_code";
ICDPara.Direction = ParameterDirection.Input;
ICDPara.SqlDbType = SqlDbType.VarChar;
ICDPara.Value = Tariff_code_act; --------------------> [e.g TR000012].
ICDPara.Size = 15;
ICDCmd.Parameters.Add(ICDPara);
But when i execute it and see it in Database i got only first 5 chars for pass value for field 'TARIFF_CODE'.
Insterestingly, it wont happens when i execute that SP through SSMS.
Please help me out.
Regards And Thanks.
View 1 Replies
View Related
Apr 25, 2007
Hi I have 5 tables. 1). Student 2).Student Info 3). Academics 4).Parents 5).Address. Each table has its own primary key & student Id is taken as the primary key for student Table and is used as foreign key for other tables. Now whenever i try to insert data into these tables nothing happens. I urgently require help. My project is a student database and I am not able to insert data into these tables. My Code is:- protected void Button1_Click(object sender, EventArgs e) { SqlConnection conn; SqlCommand comm,comm1,comm2,comm3,comm4; string connectionString = ConfigurationManager.ConnectionStrings["XMG.MDFConnectionString"].ConnectionString; conn = new SqlConnection(connectionString); comm = new SqlCommand( "INSERT INTO Student (form_no,programe,date_admission,name_student,gender,caste,nationality,date_birth,place_birth,student_email,martial_status ) " + "VALUES (@form_no,@programe,@date_admission,@name_student,@gender,@caste,@nationality,@date_birth,@place_birth,@photograph,@student_email,@martial_status)", conn); comm.Parameters.Add("@form_no", System.Data.SqlDbType.Int); comm.Parameters["@form_no"].Value = formnotextbox.Text; comm.Parameters.Add("@programe", System.Data.SqlDbType.VarChar); comm.Parameters["@programe"].Value = BcaDropDownList.SelectedValue; comm.Parameters.Add("@date_admission", System.Data.SqlDbType.DateTime); comm.Parameters["@date_admission"].Value = doatextbox.Text; comm.Parameters.Add("@name_student", System.Data.SqlDbType.VarChar); comm.Parameters["@name_student"].Value = Nametextbox.Text; comm.Parameters.Add("@gender", System.Data.SqlDbType.Char); comm.Parameters["@gender"].Value = GenderDropDownList.SelectedValue; comm.Parameters.Add("@caste", System.Data.SqlDbType.VarChar); comm.Parameters["@caste"].Value = casttextbox.Text; comm.Parameters.Add("@nationality", System.Data.SqlDbType.VarChar); comm.Parameters["@nationality"].Value = nationalityTextBox.Text; comm.Parameters.Add("@date_birth", System.Data.SqlDbType.DateTime); comm.Parameters["@date_birth"].Value = sdobTextBox.Text; comm.Parameters.Add("@place_birth", System.Data.SqlDbType.VarChar); comm.Parameters["@place_birth"].Value = pobTextBox.Text; comm.Parameters.Add("@student_email", System.Data.SqlDbType.VarChar); comm.Parameters["@student_email"].Value = emailTextBox.Text; comm.Parameters.Add("@martial_status", System.Data.SqlDbType.Bit); comm.Parameters["@martial_status"].Value = maritalstatusDropDownList.SelectedValue; comm1 = new SqlCommand("INSERT INTO Student info(spouse_name,spouse_dob,marriage_anniversary,student_emp_status,name_organ,designation,nature_of_job,office_address,date_of_joining,joined_as,salary,parent_marriage_anniversary,Category,Physically_Challenged,Blood_Group,donate_blood,Height,Weight,complextion,Anthropometric_Details,Membership_in_club,club_designation,relative_mats,name_of_relative,relative_programme,relative_designation,relative_institutions,relative_class ) " + "VALUES (@spouse_name,@spouse_dob,@marriage_anniversary,@student_emp_status,@name_organ,@designation,@nature_of_job,@office_address,@date_of_joining,@joined_as,@salary,@parent_marriage_anniversary,@Category,@Physically_Challenged,@Blood_Group,@donate_blood,@Height,@Weight,@complextion,@Anthropometric_Details,@Membership_in_club,@club_designation,@relative_mats,@name_of_relative,@relative_programme,@relative_designation,@relative_institutions,@relative_class)", conn); comm1.Parameters.Add("@spouse_name", System.Data.SqlDbType.VarChar); comm1.Parameters["@spouse_name"].Value = SpousenameTextBox.Text; comm1.Parameters.Add("@spouse_dob", System.Data.SqlDbType.DateTime); comm1.Parameters["@spouse_dob"].Value = dobTextBox.Text; comm1.Parameters.Add("@marriage_anniversary", System.Data.SqlDbType.DateTime); comm1.Parameters["@marriage_anniversary"].Value = anniversaryTextBox.Text; comm1.Parameters.Add("@student_emp_status", System.Data.SqlDbType.Bit); comm1.Parameters["@student_emp_status"].Value = employeestatesTextBox.Text; comm1.Parameters.Add("@name_organ", System.Data.SqlDbType.VarChar); comm1.Parameters["@name_organ"].Value = organisationnameTextBox.Text; comm1.Parameters.Add("@designation", System.Data.SqlDbType.VarChar); comm1.Parameters["@designation"].Value = designationTextBox.Text; comm1.Parameters.Add("@nature_of_job", System.Data.SqlDbType.VarChar); comm1.Parameters["@nature_of_job"].Value = snatureofjobTextBox.Text; comm1.Parameters.Add("@date_of_joining", System.Data.SqlDbType.DateTime); comm1.Parameters["@date_of_joining"].Value = sdateofjoiningTextBox.Text; comm1.Parameters.Add("@office_address", System.Data.SqlDbType.VarChar); comm1.Parameters["@office_address"].Value = sofficeaddressTextBox.Text; comm1.Parameters.Add("@joined_as", System.Data.SqlDbType.VarChar); comm1.Parameters["@joined_as"].Value = sjoinedasTextBox.Text; comm1.Parameters.Add("@salary", System.Data.SqlDbType.Int); comm1.Parameters["@salary"].Value = ssalaryTextBox.Text; comm1.Parameters.Add("@parent_marriage_anniversary", System.Data.SqlDbType.DateTime); comm1.Parameters["@parent_marriage_anniversary"].Value = parentmarriageanniversaryTextBox.Text; comm1.Parameters.Add("@Category", System.Data.SqlDbType.Char); comm1.Parameters["@Category"].Value = categoryTextBox.Text; comm1.Parameters.Add("@Physically_Challenged", System.Data.SqlDbType.Bit); comm1.Parameters["@Physically_Challenged"].Value = physicallychallengedTextBox.Text; comm1.Parameters.Add("@Blood_Group", System.Data.SqlDbType.Char); comm1.Parameters["@Blood_Group"].Value = sbloodgroupTextBox.Text; comm1.Parameters.Add("@donate_blood", System.Data.SqlDbType.Bit); comm1.Parameters["@donate_blood"].Value = donateDropDown.SelectedValue; comm1.Parameters.Add("@Height", System.Data.SqlDbType.Decimal); comm1.Parameters["@Height"].Value = heightTextBox.Text; comm1.Parameters.Add("@Weight", System.Data.SqlDbType.Int); comm1.Parameters["@Weight"].Value = weightTextBox.Text; comm1.Parameters.Add("@complextion", System.Data.SqlDbType.VarChar); comm1.Parameters["@complextion"].Value = complextionTextBox.Text; comm1.Parameters.Add("@Anthropometric_Details", System.Data.SqlDbType.Char); comm1.Parameters["@Anthropometric_Details"].Value = AnthroprometicDetailsTextBox.Text; comm1.Parameters.Add("@Membership_in_club", System.Data.SqlDbType.Bit); comm1.Parameters["@Membership_in_club"].Value = MembersshipinclubTextBox.Text; comm1.Parameters.Add("@club_name", System.Data.SqlDbType.VarChar); comm1.Parameters["@club_name"].Value = ClubNameTextBox.Text; comm1.Parameters.Add("@club_designation", System.Data.SqlDbType.Char); comm1.Parameters["@club_designation"].Value = ClubDesignationTextBox.Text; comm1.Parameters.Add("@relative_mats", System.Data.SqlDbType.Bit); comm1.Parameters["@relative_mats"].Value = RelativeinMatsTextBox.Text; comm1.Parameters.Add("@name_of_relative", System.Data.SqlDbType.VarChar); comm1.Parameters["@name_of_relative"].Value = NameofRelativeTextBox.Text; comm1.Parameters.Add("@relative_programme", System.Data.SqlDbType.Char); comm1.Parameters["@relative_programme"].Value = RelativeProgrammeTextBox.Text; comm1.Parameters.Add("@relative_designation", System.Data.SqlDbType.VarChar); comm1.Parameters["@relative_designation"].Value = RelativeDesignationTextBox.Text; comm1.Parameters.Add("@relative_institutions", System.Data.SqlDbType.VarChar); comm1.Parameters["@relative_institutions"].Value = RelativeInstitutiosTextBox.Text; comm1.Parameters.Add("@relative_class", System.Data.SqlDbType.Char); comm1.Parameters["@relative_class"].Value = RelativeClassTextBox.Text; comm2 = new SqlCommand("INSERT INTO Academics (High_School_Board,High_School_Institute,High_School_Percentage,High_School_Year,Higher_Secon_Board,Higher_Secon_Institute,Higher_Secon_Percentage,Higher_Secon_Year,Graduation_Board,Graduation_Institute,Graduation_Percentage,Graduation_Year,Other_Certificates,Gap,Gap_Certificate,Migration_Certificate,Character_Certificate,Other_fields,Level_of_fields ) " + "VALUES (@High_School_Board,@High_School_Institute,@High_School_Percentage,@High_School_Year,@Higher_Secon_Board,@Higher_Secon_Institute,@Higher_Secon_Percentage,@Higher_Secon_Year,@Graduation_Board,@Graduation_Institute,@Graduation_Percentage,@Graduation_Year,@Other_Certificates,@Gap,@Gap_Certificate,@Migration_Certificate,@Character_Certificate,@Other_fields,@Level_of_fields)", conn); comm2.Parameters.Add("@High_School_Board", System.Data.SqlDbType.VarChar); comm2.Parameters["@High_School_Board"].Value = HighSchoolBoardtextbox.Text; comm2.Parameters.Add("@High_School_Institute", System.Data.SqlDbType.VarChar); comm2.Parameters["@High_School_Institute"].Value = HighSchoolInstitutionTextBox.Text; comm2.Parameters.Add("@High_School_Percentage", System.Data.SqlDbType.Decimal); comm2.Parameters["@High_School_Percentage"].Value = HighSchoolpercentageTextBox.Text; comm2.Parameters.Add("@High_School_Year", System.Data.SqlDbType.Int); comm2.Parameters["@High_School_Year"].Value = HighSchoolYearTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Board", System.Data.SqlDbType.VarChar); comm2.Parameters["@Higher_Secon_Board"].Value = HigherSecondaryBordTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Institute", System.Data.SqlDbType.VarChar); comm2.Parameters["@Higher_Secon_Institute"].Value = HigherSecondaryinstituteTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Percentage", System.Data.SqlDbType.Decimal); comm2.Parameters["@Higher_Secon_Percentage"].Value = HigherSecondarypercentageTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Year", System.Data.SqlDbType.Int); comm2.Parameters["@Higher_Secon_Year"].Value = HigherSecondaryYearTextBox.Text; comm2.Parameters.Add("@Graduation_Board", System.Data.SqlDbType.Text); comm2.Parameters["@Graduation_Board"].Value = GraduationBoardTextBox.Text; comm2.Parameters.Add("@Graduation_Institute", System.Data.SqlDbType.VarChar); comm2.Parameters["@Graduation_Institute"].Value = GraduationInstituteTextBox.Text; comm2.Parameters.Add("@Graduation_Percentage", System.Data.SqlDbType.Decimal); comm2.Parameters["@Graduation_Percentage"].Value = GraduationPercentageTextBox.Text; comm2.Parameters.Add("@Graduation_Year", System.Data.SqlDbType.Int); comm2.Parameters["@Graduation_Year"].Value = GraduationYearTextBox.Text; comm2.Parameters.Add("@Gap", System.Data.SqlDbType.Bit); comm2.Parameters["@Gap"].Value = gapeDropDownList.Text; comm2.Parameters.Add("@Gap_Certificate", System.Data.SqlDbType.Bit); comm2.Parameters["@Gap_Certificate"].Value = GapCertificateTextBox.Text; comm2.Parameters.Add("@Migration_Certificate", System.Data.SqlDbType.Bit); comm2.Parameters["@Migration_Certificate"].Value = MigrationCertificateTextBox.Text; comm2.Parameters.Add("@Character_Certificate", System.Data.SqlDbType.Bit); comm2.Parameters["@Character_Certificate"].Value = CharacterCertificateTextBox.Text; comm2.Parameters.Add("@Other_fields", System.Data.SqlDbType.Text); comm2.Parameters["@Other_fields"].Value = OtherFildTextBox.Text; comm2.Parameters.Add("@Level_of_fields", System.Data.SqlDbType.Text); comm2.Parameters["@Level_of_fields"].Value = LevelOfFildsTextBox.Text; comm2.Parameters.Add("@Other_Certificates", System.Data.SqlDbType.Text); comm2.Parameters["@Other_Certificates"].Value = OthercertificateTextBox.Text; comm3 = new SqlCommand("INSERT INTO Parents (father_name,father_dob,father_photograph,father_blood_group,father_job_status,Fname_organization,Fdesignation,Fnature_of_job,Foff_address,Fdate_joining,Fjoined_as,Fsalary,Fname_firm,Ffirm_established,Fdeals_in,Fannual_turn_over,Fincome,Fbusness_off_address,Fcontact_no,Fmobile_no,F_Email,Mother_Name,Mother_dob,Mother_Photograph,Mother_Blood_Group,Mother_Work_Status,Name_Organization,MDesignation,Moffice_address,Mcontact_no,Mdate_joining,Joined_As,Salary_Drawn,M_annualincome,M_Email,M_Mobileno ) " + "VALUES (@father_name,@father_dob,@father_photograph,@father_blood_group,@father_job_status,@Fname_organization,@Fdesignation,@Fnature_of_job,@Foff_address,@Fdate_joining,@Fjoined_as,@Fsalary,@Fname_firm,@Ffirm_established,@Fdeals_in,@Fannual_turn_over,@Fincome,@Fbusness_off_address,@Fcontact_no,@Fmobile_no,@F_Email,@Mother_Name,@Mother_dob,@Mother_Photograph,@Mother_Blood_Group,@Mother_Work_Status,@Name_Organization,@MDesignation,@Moffice_address,@Mcontact_no,@Mdate_joining,@Joined_As,@Salary_Drawn,@M_annualincome,@M_Email,@M_Mobileno)", conn); comm3.Parameters.Add("@father_name", System.Data.SqlDbType.VarChar); comm3.Parameters["@father_name"].Value = Fnametextbox.Text; comm3.Parameters.Add("@father_dob", System.Data.SqlDbType.DateTime); comm3.Parameters["@father_dob"].Value = fDateOfBirthTextBox.Text; comm3.Parameters.Add("@father_photograph", System.Data.SqlDbType.Image); comm3.Parameters["@father_photograph"].Value = PhotographTextBox.Text; comm3.Parameters.Add("@father_blood_group", System.Data.SqlDbType.Char); comm3.Parameters["@father_blood_group"].Value = BloodGroupTextBox.Text; comm3.Parameters.Add("@father_job_status", System.Data.SqlDbType.VarChar); comm3.Parameters["@father_job_status"].Value = JobStatusTextBox.Text; comm3.Parameters.Add("@Fname_organization", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fname_organization"].Value = NameofOrgationTextBox.Text; comm3.Parameters.Add("@Fdesignation", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fdesignation"].Value = fDesignationTextBox.Text; comm3.Parameters.Add("@Fnature_of_job", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fnature_of_job"].Value = NatureofJobTextBox.Text; comm3.Parameters.Add("@Foff_address", System.Data.SqlDbType.VarChar); comm3.Parameters["@Foff_address"].Value = OfficeAddressTextBox.Text; comm3.Parameters.Add("@Fdate_joining", System.Data.SqlDbType.DateTime); comm3.Parameters["@Fdate_joining"].Value = DateofJoiningTextBox.Text; comm3.Parameters.Add("@Fjoined_as", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fjoined_as"].Value = JoinedasTextBox.Text; comm3.Parameters.Add("@Fsalary", System.Data.SqlDbType.Int); comm3.Parameters["@Fsalary"].Value = SalaryTextBox.Text; comm3.Parameters.Add("@Fname_firm", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fname_firm"].Value = NameofFirmTextBox.Text; comm3.Parameters.Add("@Ffirm_established", System.Data.SqlDbType.Int); comm3.Parameters["@Ffirm_established"].Value = firmEstablishedTextBox.Text; comm3.Parameters.Add("@Fdeals_in", System.Data.SqlDbType.Int); comm3.Parameters["@Fdeals_in"].Value = dealsinTextBox.Text; comm3.Parameters.Add("@Fannual_turn_over", System.Data.SqlDbType.Int); comm3.Parameters["@Fannual_turn_over"].Value = AnnualTurnOverTextBox.Text; comm3.Parameters.Add("@Fincome", System.Data.SqlDbType.Int); comm3.Parameters["@Fincome"].Value = IncomeTextBox.Text; comm3.Parameters.Add("@Fbusness_off_address", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fbusness_off_address"].Value = BoAddressTextBox.Text; comm3.Parameters.Add("@Fcontact_no", System.Data.SqlDbType.BigInt); comm3.Parameters["@Fcontact_no"].Value = ContactNumberTextBox.Text; comm3.Parameters.Add("@Fmobile_no", System.Data.SqlDbType.BigInt); comm3.Parameters["@Fmobile_no"].Value = MobileNumberTextBox.Text; comm3.Parameters.Add("@F_Email", System.Data.SqlDbType.Text); comm3.Parameters["@F_Email"].Value = EmailIdTextBox.Text; comm3.Parameters.Add("@Mother_Name", System.Data.SqlDbType.Text); comm3.Parameters["@Mother_Name"].Value = MnameTextBox.Text; comm3.Parameters.Add("@Mother_dob", System.Data.SqlDbType.DateTime); comm3.Parameters["@Mother_dob"].Value = MdobTextBox.Text; comm3.Parameters.Add("@Mother_Photograph", System.Data.SqlDbType.Image); comm3.Parameters["@Mother_Photograph"].Value = HighSchoolInstitutionTextBox.Text; comm3.Parameters.Add("@Mother_Blood_Group", System.Data.SqlDbType.Text); comm3.Parameters["@Mother_Blood_Group"].Value = MbloodgroupTextBox.Text; comm3.Parameters.Add("@Mother_Work_Status", System.Data.SqlDbType.Char); comm3.Parameters["@Mother_Work_Status"].Value = MworkstatusTextBox.Text; comm3.Parameters.Add("@Name_Organization", System.Data.SqlDbType.VarChar); comm3.Parameters["@Name_Organization"].Value = MnameofOrgationTextBox.Text; comm3.Parameters.Add("@MDesignation", System.Data.SqlDbType.Text); comm3.Parameters["@MDesignation"].Value = MdesignationTextBox.Text; comm3.Parameters.Add("@Moffice_address", System.Data.SqlDbType.VarChar); comm3.Parameters["@Moffice_address"].Value = MofficeAddressTextBox.Text; comm3.Parameters.Add("@Mcontact_no", System.Data.SqlDbType.BigInt); comm3.Parameters["@Mcontact_no"].Value = McontactnoTextBox.Text; comm3.Parameters.Add("@Mdate_joining", System.Data.SqlDbType.DateTime); comm3.Parameters["@Mdate_joining"].Value = MdateofjoiningTextBox.Text; comm3.Parameters.Add("@Joined_As", System.Data.SqlDbType.Char); comm3.Parameters["@Joined_As"].Value = MjoiningasTextBox.Text; comm3.Parameters.Add("@Salary_Drawn", System.Data.SqlDbType.Float); comm3.Parameters["@Salary_Drawn"].Value = MSalarydrwnTextBox.Text; comm3.Parameters.Add("@M_annualincome", System.Data.SqlDbType.Float); comm3.Parameters["@M_annualincome"].Value = MAnnualincomeTextBox.Text; comm3.Parameters.Add("@M_Email", System.Data.SqlDbType.Text); comm3.Parameters["@M_Email"].Value = MemailidTextBox.Text; comm3.Parameters.Add("@M_Mobileno", System.Data.SqlDbType.BigInt); comm3.Parameters["@M_Mobileno"].Value = mmnoTextBox.Text; comm4 = new SqlCommand("INSERT INTO Address (Residential_Address,City,State,Country,Phone_No,Fax_No,Permanent_Address,Permanent_City,Permanent_State,Permanent_Country,Name_L_Guardian,Guardian_Address,Contact_Number,Relation_L_Guardian,Photograph_L_Guardian,Hostel_Details ) " + "VALUES (@Residential_Address,@City,State,@Country,@Phone_No,@Fax_No,@Permanent_Address,@Permanent_City,@Permanent_State,@Permanent_Country,@Name_L_Guardian,@Guardian_Address,@Contact_Number,@Relation_L_Guardian,@Photograph_L_Guardian,@Hostel_Details)", conn); comm4.Parameters.Add("@Residential_Address", System.Data.SqlDbType.VarChar); comm4.Parameters["@Residential_Address"].Value = ResidentialAddressTextBox.Text; comm4.Parameters.Add("@City", System.Data.SqlDbType.VarChar); comm4.Parameters["@City"].Value = cityTextBox.Text; comm4.Parameters.Add("@State", System.Data.SqlDbType.VarChar); comm4.Parameters["@State"].Value = stateTextBox.Text; comm4.Parameters.Add("@Country", System.Data.SqlDbType.VarChar); comm4.Parameters["@Country"].Value = CountryTextBox.Text; comm4.Parameters.Add("@Phone_No", System.Data.SqlDbType.BigInt); comm4.Parameters["@Phone_No"].Value = PhonenumberTextBox.Text; comm4.Parameters.Add("@Fax_No", System.Data.SqlDbType.BigInt); comm4.Parameters["@Fax_No"].Value = FaxnumberTextBox.Text; comm4.Parameters.Add("@Permanent_Address", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_Address"].Value = PermanentAdressTextBox.Text; comm4.Parameters.Add("@Permanent_City", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_City"].Value = PermanentcityTextBox.Text; comm4.Parameters.Add("@Permanent_State", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_State"].Value = PermanentStateTextBox.Text; comm4.Parameters.Add("@Permanent_Country", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_Country"].Value = PermanentCountryTextBox.Text; comm4.Parameters.Add("@Name_L_Guardian", System.Data.SqlDbType.VarChar); comm4.Parameters["@Name_L_Guardian"].Value = NameofLocalGuardianTextBox.Text; comm4.Parameters.Add("@Guardian_Address", System.Data.SqlDbType.VarChar); comm4.Parameters["@Guardian_Address"].Value = GuardianAddressTextBox.Text; comm4.Parameters.Add("@Contact_Number", System.Data.SqlDbType.BigInt); comm4.Parameters["@Contact_Number"].Value = GContactTextBox.Text; comm4.Parameters.Add("@Relation_L_Guardian", System.Data.SqlDbType.VarChar); comm4.Parameters["@Relation_L_Guardian"].Value = relationloTextBox.Text; comm4.Parameters.Add("@Photograph_L_Guardian", System.Data.SqlDbType.Image); comm4.Parameters["@Photograph_L_Guardian"].Value = SpousenameTextBox.Text; comm4.Parameters.Add("@Hostel_Details", System.Data.SqlDbType.VarChar); comm4.Parameters["@Hostel_Details"].Value = HostalDetailTextBox.Text; try { conn.Open(); comm.ExecuteNonQuery(); comm1.ExecuteNonQuery(); comm2.ExecuteNonQuery(); comm3.ExecuteNonQuery(); comm4.ExecuteNonQuery(); Response.Redirect("HelpDesk.aspx"); } catch { } finally { conn.Close(); } }} PLEASE HELP ME. I AM IN TROUBLE.
View 3 Replies
View Related
Jan 3, 2008
Hi everybody.I'm having difficulties with a button handler I put together from a few examples around the internet.Here's my code: <script runat="server"> Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs) Dim FVArtikel_id As Label = FormViewDisplay.FindControl("Artikel_idLabel") Dim FVArtikel_naam As Label = FormViewDisplay.FindControl("Artikel_naamLabel") Dim FVArtikel_prijs As Label = FormViewDisplay.FindControl("Artikel_prijsLabel") Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim SQLString As String Dim SQLAddString As String If Not Session("sid") Is Nothing Then DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:Documents and SettingsAdministratorBureaublad2ehandslego.nldatadb.mdb") DBConnection.Open() SQLString = "SELECT Count(*) FROM Orders WHERE sid = '" & CType(Session("sid"), String) & "' AND Artikel_id = '" & FVArtikel_id.Text & "'" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@sid", Session("sid")) 'string? DBCommand.Parameters.AddWithValue("@Artikel_id", FVArtikel_id.Text) 'string? If DBCommand.ExecuteScalar() = 0 Then SQLAddString = "INSERT INTO Orders (sid, Order_datum, " & _ "Artikel_id, Order_artikel, Order_prijs, Order_hoeveelheid) VALUES (" & _ "'" & Session("sid") & "', " & _ "'" & Today & "', " & _ "'" & FVArtikel_id.Text & "', " & _ "'" & FVArtikel_naam.Text & "', " & _ "'" & FVArtikel_prijs.Text & "', 1)" DBCommand = New OleDbCommand(SQLAddString, DBConnection) DBCommand.Parameters.AddWithValue("@sid", Session("sid")) 'string? DBCommand.Parameters.AddWithValue("@Artikel_id", FVArtikel_id.Text) 'string? DBCommand.Parameters.AddWithValue("@Artikel_naam", FVArtikel_naam.Text) DBCommand.Parameters.AddWithValue("@Artikel_prijs", FVArtikel_prijs.Text) 'string? DBCommand.ExecuteNonQuery() End If DBConnection.Close() Src.Text = "Item Added" Src.ForeColor = Color.FromName("#990000") Src.BackColor = Color.FromName("#E0E0E0") Src.Font.Bold = True End If End Sub</script> I'm not getting any errors, it seems to me that i'm not getting a 'sid' value passed along but i don't know what to do about it.I've also already tried step debugging. This is my last resort. I hope you can help me.
View 3 Replies
View Related
Oct 15, 2004
Hi all;
Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??
Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??
Example:
------------
I have two tables "Customer" and "CustomerDetails"..
SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).
SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.
FYI:
----
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)
Thanks in advanced!
View 5 Replies
View Related
Feb 1, 2006
how to restrict data insertion upto 50 MB in a table?
View 1 Replies
View Related
Oct 12, 2007
Hi,
I ran my package and it was successfu. I tried running it again, but this time it throws me this error:
Dim_T_Account [56575]: Unable to prepare the SSIS bulk insert for data insertion.
Error: 0xC004701A at CallerType, CallerChannel, Dealer, DODealer, HotlineType, Model, Reg'l Signal Code, Account, Contact, DTS.Pipeline: component "Dim_T_Account" (56575) failed the pre-execute phase and returned error code 0xC0202071.
Information: 0x40043008 at CallerType, CallerChannel, Dealer, DODealer, HotlineType, Model, Reg'l Signal Code, Account, Contact, DTS.Pipeline: Post Execute phase is beginning.
Why suddenly without changing anything, i encountered this error? What does it mean it cannot prepare the SSIS bulk insert. My connection to server is working ok.
cherriesh
View 9 Replies
View Related
Oct 24, 2007
Hi,
I am using SQL Server Destinations in my data flow tasks. I'm running this package in the server until i encountered this error:
OnError,,,LOAD AND UPDATE Dimension Tables,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1071636367,0x,Unable to prepare the SSIS bulk insert for data insertion.
OnError,,,Load Dimensions,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1071636367,0x,Unable to prepare the SSIS bulk insert for data insertion.
OnError,,,Discount Reason, ISIS Condition, ISIS Defect, ISIS Repair, ISIS Section, ISIS Symptom, Job Status, Parts, Purchase SubOrder Type, Service Contract, Service Reason, Service Type, TechServiceGrp, WarrantyType, Branch, Wastage Reason,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Dim_T_ISISDefect" (56280) failed with error code 0xC0202071. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
What could be the reason for this? I don't usually have an error.
cherriesh
View 6 Replies
View Related
Sep 12, 2006
I am attempting to insert information from Visual Web Developer 2005 using either the Gridview or Datalist controls into a SQL Server 2005 database and get stuck when defining the custom statement.When I enter the text within the insert tab, the <next> button remains greyed out, preventing me from continuing to the next page.If I copy the same text into the select tab, then I can continue with the wizard, however this raises other problems which may or may not be related (multiple insertions of the data into the SQL Server database table - possibly due to postback functions). I would rather use insert to confirm that my second problem is not because I am using the wrong option.My question is:Should I be able to use the insert function within VWD express or is this only available within the standard/pro editions?
View 2 Replies
View Related
Jan 15, 2008
Having searched the forum, this one clearly has form... However beyond assisting those who have fallen at the first hurdle (i.e. forgetting/not knowing that they cannot execute the package remotely to the instance of SQL Server into which they are inserting), the issues raised by others have not been addressed. Thus I am bringing nothing new to the table here - just providing an executive summary of problems which others have run into, written about, but not received answers for.
First the complete error:
Description: Unable to prepare the SSIS bulk insert for data insertion. End Error Error: 2008-01-15 04:55:27.58 Code: 0xC004701A Source: <xxx> DTS.Pipeline Description: component "<xxx> failed the pre-execute phase and returned error code 0xC0202071. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:53:34 AM Finished: 5:00:00 AM Elapsed: 385.384 seconds. The package execution failed. The step failed.
Important points
It mostly works - It produces no error more than 9 times out of 10.
It fails on random dataflows - My package has several dataflows, (mostly) executing concurrently. Where the error occurs it does not do so on the same dataflow each time: on one run it'll fail on dataflow A whilst B,C,D and E succeed, then A-E will all succeed (and continue doing so for the next ten runs thereafter), and then the error recurs for dataflow D, with A,B,C and E all succeeding.
Hope someone has something interesting to say,
Tamim.
View 10 Replies
View Related
Apr 11, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 1 Replies
View Related
Jan 3, 2008
Hi All SSIS experts,
Happy 2008!!!!
I am inserting data into a tab delimted text file using SSIS package.
After data insetion some extra tabs get added between columns in some rows in the text file.
Can we programmatically delete the extra tabs from the text file, if so how to use/implement the code inside the SSIS package?
Any pointer/suggestions are welcome.
Thanks & Regards,
View 9 Replies
View Related
Apr 5, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 5 Replies
View Related
Jul 27, 2006
Hi,
I'm using ASP.NET 1.1, SQL Server 2000 Server:
I followed the ASP.NET 1.1 Starter Kit's Commerce application and applied the same principles it had written the code to retrieve data to my web application I created. For example I've written this Function in a class to return a sqldatareader:
Public Function GetAdvanceSearch(ByVal s As String, ByVal Ext As Integer, ByVal fdate As DateTime, ByVal tdate As DateTime) As SqlDataReader
Dim oDrAdSearch As SqlDataReaderDim oCmdGetSearch As New SqlCommand("spAdvanceSearch", oComConn)
With oCmdGetSearch .CommandType = CommandType.StoredProcedure .Parameters.Add(New SqlParameter("@DialNo", SqlDbType.VarChar)).Value = s .Parameters.Add(New SqlParameter("@FDate", SqlDbType.DateTime)).Value = fdate .Parameters.Add(New SqlParameter("@TDate", SqlDbType.DateTime)).Value = tdate .Parameters.Add(New SqlParameter("@Ext", SqlDbType.Int)).Value = ExtEnd With
oComConn.Open()oDrAdSearch = oCmdGetSearch.ExecuteReader(CommandBehavior.CloseConnection)
If oDrAdSearch.HasRows Then Return oDrAdSearchElse Return NothingEnd If
End Function
And When I'm calling this function I do write in this way (assuming that this function is in a class called "Calls"):
Dim objCalls as New Calls
DataGrid1.DataSource = objCalls.GetAdvanceSearch(<PARAMS.......>)DataGrid1.Databind
My application is a Telephone Call Recording System and could expect vast amount of data. Averagely, a month may produce approximately 50,000 records or more. So while querying through my web application for a month, the application itself either gets stuck or the retrieval speed gets drastically slow. However I'm using Datareaders for every querying scenario. My Web application is hosted in a Windows 2000 Server and accessed via Local Network or IntraNet.
What are the ways I could make this retrieval more speedier and efficient? I would like to hear from anyone who have come across this problem and anyone who could help me on this.
Thanks in Advance. Looking forward for a reply from some one.
View 3 Replies
View Related
Jan 9, 2006
Hi,
I have migrated my app from VB to VB.Net. A 3-tier app with remoting and COM+.
I am experiencing a long wait time of about 3 times higher than what it would take in the VB App.
I am using DataAdapter.FiLL method to fill the datatable.
I have tried.
Using DataReader ( Made the things worse )
Using BeginLoadData and EndLoadData
Creating a Dataset and calling fill with the dataset so that the round trip to the middletier is saved to bring the SQL.
But i feel now that whatever is done. the problem is with the fill method only?
Is there any alternative?
Please suggest. It is one of the most important thing which if not possible may lead to scrapping up idea of upgrading to .Net.
Shri
View 10 Replies
View Related
Apr 28, 2008
Hi,
I am using SQL2K for a HMS application database. And I have several database in it.
After a long periode of time (about 6 month) the the application is slowing down when accessing the data.
I use full log for the database.
I keep only the last 3 days transaction data and every day I delete transaction data older then 4 day, so the number of records in the transaction table is just about 3000 to 5000 records only.
Please help me. Thanks.
please it is very urgent.
View 4 Replies
View Related
Feb 2, 2008
Slow OLEDB Source in Data Flow
Hi All,
I have a simple data flow task, composed of only an OLEDB Source, a Conditional Split, and two Execute SQL statements (both insert statements, one after the other). When I run my package in Visual Studio for debugging, I noticed that after executing around ~9800 in the first and another ~9800 records in the second insert statements, the OLEDB Source will take around 3 or 4 minutes to fetch another set of ~9800 records. I have set the DefaultBufferMaxRows property of the Data Flow to 10000. My query to retrieve those 700,000 records runs for about 2-3 mins to finish (which I think should be decent enough). Is this an expected behavior of SSIS? The expected number of records to be retrieved is 700,000, and it takes forever to finish the transfer of these records. Please help
View 4 Replies
View Related
Nov 16, 2006
HI,
I am query event log information for a single day using WMI Data reader task and it is taking for ever. I am querying event log for remote servers but the server exectuing the process and queried servers both are in the same domain.
Kindly advise.
Thanks
Shafiq
View 2 Replies
View Related
Jul 5, 2006
Hi
Here is the brief to my problemWe had our database on SQL Server 2000 and Windows 2000.This machine
had 2gb of RAM and dual Penitum 3 processors and about 25-30 users were
connected all the time. The size of database is around 2 gb. Even on this setup
rate of data retrival was good, never had any issues. We moved to SQL Server 2005 and Windows 2003. This machines has
2 Pentium Xeon 3.4 processors and 2 stick of KINGSTON 1024 MB 333
MHZ DDR DIMM ECC CL2.5 DUAL RANK X4 INTEL. The rate of data
retrival is awful and its very slow. It using about 1.7 to 1.9 gb of RAM all the
time. Page File usage is about 2.07 gb and Virtual Usage is about 1.7gb.I dont quiet understand why is it so slow to get data. We use bespoke software,
so nothing has changed there. Hardware specification of our server is far more better then the recommended
system requirement for SQL Server 2005.Am i missing something out or i havent set up the SQL Server properly? Any help would really be appreciated.Mits
View 3 Replies
View Related
Jun 11, 2007
Hello,
I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.
I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:
- OLE DB source, reading +- 800,000 rows from a SQL server database
- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.
- An aggregation
- OLEDB target, fact table in a SQL server database.
It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.
Any comments and advice will be greatly appreciated.
Thanks.
(PS some machine details:
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:WINDOWS
System Directory C:WINDOWSsystem32
Boot Device DeviceHarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:pagefile.sys)
View 6 Replies
View Related
Mar 10, 2006
I am trying to export a table with ~ 10 Million rows to a flat file and it is taking for ever with SQL2005 export functionality. I have tried creating an SSIS package with a flat-file destination and the results are the same. In each case it does the operation in chunks of about 9900+ rows, and each chunk takes ~1-2 minutes which sounds unreasonable.
I tried bcp, and it fails after a few thousand rows. I tried moving the data to SQL2000 first then to flat file from SQL2K, but the move from SQL2005->SQL2000 was going at the same rate as above.
So, the bottleneck seems to be data going out of SQL2005 no matter what the destination is. I'm wondering if there is some setting that Iam missing that would make this run in a reasonable amount of time?
View 1 Replies
View Related
Mar 18, 2008
We have an information retrieval application in which there is a single connection to a database followed by multiple table open, read, and close commands. Response time is consistantly less than 1 second on a LAN.
When Internet connected (not VPN), the first table read is typically fast, but the response time becomes slower and slower after multiple table open, read, and close commands. There seems to be a considerable amount of handshaking based on monitoring of the router's status lights.
View 7 Replies
View Related
Sep 6, 2006
I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?
View 2 Replies
View Related
Jun 1, 2007
we had some slow down complaints lately and this query seems to be the culprit almost every single time. The estimated execution plan is a clustered index seek as there is a clustered index on the uidcustomerid column. setting profile statistics on shows that every time it executes it does an index seek.
profiler session showed a huge number of reads for these queries depending on the value being looked up. 1500 through 50000. i set up profile io on and the culprit is lob logical reads. everything else is 0 or very low. in this case lob logical reads is over 1700.
3 of the columns in the select statement are text columns. when i take them out of the query the lob logical reads drops to 0 and goes up incrementally as i add each column back in.
is there anyway to improve the performance without changing data types to varchar(max)?
select SID,Last_name,Name_2,First_name,Middle_initial,Descriptives,Telephone_number,mainline,Residence,ADL,
DID_number,Svce_street,Svce_town,Svce_state,Svce_appt,Mailing_street,Mailing_town,Mailing_state,Mailing_appt,
Mailing_zip,Listing,Addl_listing,Published,Listed,Gold_number,PIN,status,SSnumber,tax_jurisdiction,
Bill_date,Past_balance,Service_start_date,Service_end_date,LOA,FCC_type,Line_type,I_W,Jacks,Voice_messaging,
vms_ring_cycles,CCS,phonesmarts,ringmate,voice_dialing,Bill_detail,Contact_Number,Contact_extension,
Best_Time,suspend,suspend_start,suspend_end,credits_allowed,credits_granted,home_region,Calling_Plan,Local_Plan,
Local_Plan_Rate,Flat_Rate,Sales_agent,Community,Building_Mgmt,How_Heard,Incentive_1,Incentive_1a,Incentive_1b,
Incentive_1c,Incentive_2,Incentive_2a,Incentive_2b,Incentive_2c,Incentive_3,Incentive_3a,Incentive_3b,
Incentive_3c,block_operator,block_collect,block_group,block_adult,block_call_return,block_repeat_dialing,
block_call_trace,block_caller_id,block_anonymous,block_all_high_toll,block_regional_and_ld,block_DA_Call_Completion,
block_DA,block_3rd_party,bank,prepayment,dial_around_number,custid,waive_interest,Financial_Treatment,
Other_Feature_1_code,Other_Feature_1_rate,Other_Feature_2_code,Other_Feature_2_rate,Other_Feature_3_code,
Other_Feature_3_rate,Other_Feature_4_code,Other_Feature_4_rate,Partial_Account,mail_date,snp_1_date,snp_2_date,
terminate_date,snp1notified,snp1peak,snp1offpeak,snp2notified,snp2peak,snp2offpeak,avg_days_paid,Pulled_Ld,SNP1,
SNP2,Treatment,Collections,Installment,Nynex_BTN,LD_rate,local_discount,to_month,rounds_up,full_package_made,
local_made,PIC,LPIC,tax_exempt_local,tax_exempt_federal,CommissionedAgent,LDRateID,UidCustomerId,
accVchLineClassUSOC,block_Inter_Reg_LD,block_international,block_DA_3rd_Collect,block_DH2,block_ISP_2,block_ISP_3,
block_ISP4_3_GBAS,block_ISP3_3_GBAS,block_collect_only,block_LD_Reg_DA,block_usage_based,block_ISP5_3_GBAS,
block_ISP5_2_GBAS,block_group_adult,csr_PIC,csr_LPIC,csr_SA,csr_exception,cutover_status,cutover_datetime,
OutsideAgent,prfVchAttributes,uidResellerID,Category,uidDealID
from profiles where UidCustomerID in (352199267)
View 3 Replies
View Related
Sep 7, 2006
To extract data from an ODBC source, try the following:
Add an ADO.Net Connection Manager.
Edit the Connection Manager editor and select the ODBC Data Provider
Configure the Connection Manager to use your DSN or connection string
Add a Data Flow Task to your package.
Add a Data Reader Source adapter to your data flow
Edit the Data Reader source adapter to use the ADO.Net connection manager that you added.
Edit the Data Reader source to query for the data you wish to extract.
hth
Donald
Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.
When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.
Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?
Thanks!
View 3 Replies
View Related
Jan 4, 2007
Hello,
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Thank you for your help!
cdun2
View 32 Replies
View Related
Apr 9, 2008
Hello,
I have developed some packages to load data into "Fact" tables in the data warehouse.
Some packages are OK, other ones not. What is the problem?: some packages load fact tables with lots of "Lookup - Data Flow Transformation" into the "data flow task" (lookup against dimension tables) but they are very very slow, too much slow to be choosen as a solution.
Do you have any other solutions to avoid using "Lookup - Data Flow Transformation"? Any other solution (SSIS, TSQL and so on....) is welcome to speed up the Fact table loading process.
Thank in advance
View 7 Replies
View Related
Apr 28, 2008
Please someone help to get out this situation
How to perform bulk insertion in a table for different records
View 4 Replies
View Related