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

Data Flow Stuck In Phase The Final Commit Data Insertion Has Started

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

Insertion Of Data

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

Insertion Od Data In The Table

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

XML Data Insertion In Table

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

Huge Data Insertion

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

Data Insertion Problem

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

Sql Database Data Insertion Problem.

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

Data Insertion Failure. Help Much Appreciated.

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

Insertion Data Via Stored Procedure [URGENT]!!

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

How To Restrict Data Insertion Upto 50 MB In A Table

Feb 1, 2006

how to restrict data insertion upto 50 MB in a table?

View 1 Replies View Related

SSIS Bulk Insert For Data Insertion.

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

Unable To Prepare The SSIS Bulk Insert For Data Insertion

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

Configure Data Source Insertion Into SQL Server 2005 Database - Express Editions

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

SQL ServerDestination Error - Unable To Prepare The SSIS Bulk Insert For Data Insertion.

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

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

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

To Programmaticaly Delete The Extra Tabs In Text File(after Data Insertion Using SSIS Packages)

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

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

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

Data Retrieval Terribly Slow

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

Data Access Very Slow In .net As Compared To VB

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

Sql Server Data Transfaring Very Slow

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

Slow OLEDB Source In Data Flow

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

WMI Data Reader Task Painfully Slow

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

Very Slow Data Access On SQL Server 2005?

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

Data Flow Task Running Very Slow

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

Export/data Dump To Flat File Is Too Slow

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

Slow SQL Server Data Transfers When Internet Connected

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

Import Data From SQL Server 2000 Incredibly Slow

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

Slow Running Query With A High Lob Logical Reads, Lob Data

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

Data Transfer From Lotus Notes Very Slow Compared To SQL 2000 DTS

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

Slow Running Insert After Changing OLE DB Destination Data Access Mode

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

Load Fact Table, Very Slow With Lookup - Data Flow Transformation

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

Insertion

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







Copyrights 2005-15 www.BigResource.com, All rights reserved