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
ADVERTISEMENT
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
Feb 1, 2006
how to restrict data insertion upto 50 MB in a table?
View 1 Replies
View Related
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
Apr 8, 2008
hello
i want to ask if the insertion of a record into a partion is slower than insertion it into a non partitioned table or not?
cuz sql has to decide to wich partion the record has to insert according to the partitioning key and is this decesion process is making insertion slower ?
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
Nov 27, 2007
Hi,Env is ms sql server 2000.ddl:create table srchPool(tid int primary key, taid int, s tynyint, uidtynyint);-- and sql server automatically creates a clustered index for the pkdml:insert into srchPool(taid,s,uid)select article_id, 99, 1484from targetTBLwhere article_content LIKE '% presentation %';insert into srchPool(taid,s,uid)select article_id, 55, 1484from targetTBLwhere 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 likeselect article_id, 99, 1484 into #srchPool(taid,s,uid)from targetTBLwhere article_content LIKE '% presentation %';-- once its use is finished and drop it?Many thanks.
View 7 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
Jun 23, 2006
Hi,I have a table with 20.000.000 of tuples.I have been monitoring the performance of the insertion and updates,but not convince me at all.The table have 30 columns, what and 12 of it, are calcultated column.The test that i do was this:1 Insertion with all the columns and calculing the calcultated columnsin the insertion sentence.1 insertion and all the columns calculated in @vars..1 insertion with the basic fields, and 10 updates.And the result was that the last test was the most performant.What is your opinion?
View 4 Replies
View Related
Sep 20, 2007
Hi Experts,
I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain
Insertion in Master Table..................................... A
Insertion in Detail Table........................................B
Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.
So please can any one tell me a good approach for this.
View 6 Replies
View Related
May 5, 2008
Hi there
I have a relatively noobish query and I am hoping to get a solution to it.
Heres the query in a nutshell.
I have a 'Type' table which has a 'TypeName' varchar attribute. So when I do a row insert into this Type table, I want a new table created with the value I insert into the 'TypeName' column as the table name.
For example If i insert 'xyz' into the 'Type' table for the 'TypeName' column. I wish for a trigger to fire which will create a table 'xyz' with some set attributes. I am really new to SQL Server and my preliminary googling left me disheartened with the results. So here I am.
I hope I was clear in the way I expressed my doubt and also that the people here might be able to help me out in this quest.
View 2 Replies
View Related
Oct 11, 2007
I want to insert data into Header and Details table simultaneously using SQLBulkCopy. Header table contains an Identity columns and Details table contains Foreign Key to this identity column in Header Table.I want to use DataTable as datasource in SQLBulkCopy.Can any body help on this
thanks
View 2 Replies
View Related
Oct 1, 2007
This is my code through which I insert the data into herm_Gen_Liab_Pre table.set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[sample]
--@policyno varchar(9),-- FOR MOLD COVERAGE
--@formno varchar(6) -- FOR MOLD COVERAGE
as
TRUNCATE TABLE herm_Gen_Liab_Pre
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
DECLARE @genliabpre TABLE
( accmnth varchar(2),
accyear varchar (4),
treffmnth varchar (2),
treffyr varchar (4),
trexmnth varchar (2),
trexyr varchar (4),
statecode varchar (2),
typeofpolicy varchar(2),
exposure varchar(7),
annualstatementlineofbusinesscode varchar(3),
PremiumRecordId varchar (14),
Sublinecode varchar (3),
classcode varchar (5),
incemnth varchar (2),
inceyr varchar (4),
territorycode varchar(3)
--policyno varchar(9),-- for mold coverage
--formno varchar(6), -- for mold coverage
--moldcoverage varchar(1) -- mold coverage value
);
insert into @genliabpre (accmnth,accyear,treffmnth,treffyr,trexmnth,trexyr,
statecode,typeofpolicy,exposure,annualstatementlineofbusinesscode,PremiumRecordId,
Sublinecode,classcode,incemnth,inceyr,territorycode)
select datepart(m,p.EntryDate),
datepart(yy,p.EntryDate),
datepart(m,p.PremiumEffectiveDate),
datepart(yy,p.PremiumEffectiveDate),
datepart(m,p.PolicyExpirationDate),
datepart(yy,p.PolicyExpirationDate),
p.statecode,p.policytypecode,p.ExposureLimitAmt,s.lineofbusinesscode,
s.invoiceno,s.sublinecode,s.ClassCode,
datepart(m,p.PolicyOriginalEffectiveDate),
datepart(yy,p.PolicyOriginalEffectiveDate),s.TerritoryCode
from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s
on p.invoiceno = s.invoiceno
where
s.lineofbusinesscode in ('052','152','170','171','175','180')--,'192','194') -- general liability
AND P.STATECODE IN ('NY','NJ','PA','RI','GA')
and p.entrydate between '01/01/2004' and '12/31/2007'
order by p.entrydate
----------------------------INSERT MOLD COVERAGE--------------------------------
/*
SET @policyno = (select cast(pd.policyno as varchar(9))
from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf
on pd.policyno = pf.policyno
where pd.entrydate between '01/01/2004' and '12/31/2007'
and pf.formno in ('CG2167','CG3131')
and exists
( select policyno from HERMITAGE.DBO.premiumdirect p join
HERMITAGE.DBO.premiumstatdirect ps
on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)
where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')
and ps.sublinecode in ('334','335','336','337','338','339'))
)
SET @FORMNO = (select PF.FORMNO
from HERMITAGE.DBO.premiumdirect pd join HERMITAGE.DBO.policyforms pf
on pd.policyno = pf.policyno
where pd.entrydate between '01/01/2004' and '12/31/2007'
and pf.formno in ('CG2167','CG3131')
and exists
( select policyno from HERMITAGE.DBO.premiumdirect p join
HERMITAGE.DBO.premiumstatdirect ps
on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)
where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')
and ps.sublinecode in ('334','335','336','337','338','339'))
)
*/
---------------------------------------------------------------------------------
insert into herm_Gen_Liab_Pre (
AccountingDate,
TransactionEffectiveDate,
TransactionExpirationDate,
StateCode,
typeofpolicy,
exposure,
AnnualStatementLineOfBusinessCode,
PremiumRecordId,
SublineCode,
ClassificationCode,
InceptionDate,
territorycode
)
select accmnth= case accmnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end + + SUBSTRING(accyear,4,1),
treffmnth=case treffmnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end + + substring(treffyr,3,2),
trexmnth = case trexmnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end+ + substring(trexyr,3,3),statecode,
typeofpolicy,exposure,AnnualStatementLineOfBusinessCode,premiumrecordid,Sublinecode,
classcode,incemnth = case incemnth
when '1' then '1'
when '2' then '2'
when '3' then '3'
when '4' then '4'
when '5' then '5'
when '6' then '6'
when '7' then '7'
when '8' then '8'
when '9' then '9'
when '10' then '0'
when '11' then '-'
when '12' then '&' end+ + substring(inceyr,3,2),TerritoryCode
from @genliabpre
exec genliab_state
update herm_Gen_Liab_Pre
set territorycode = '999' where statecode = '31'
update herm_Gen_Liab_Pre
set TransactionTypecode ='1'
----------------------STATE EXCEPTION INDICATOR CODE-----------------------------
update herm_Gen_Liab_Pre
set StateExceptionIndicator = '1' where sublinecode = '325' and classificationcode in ('99935','99936') and statecode = '20'
update herm_Gen_Liab_Pre
set StateExceptionIndicator = '1' where sublinecode = '334'
and classificationcode in ('01412','01418','05113','05114','05118','05123','05124'
,'05125','05213','05223','05224','60010','60011','62003','63010','63011'
,'63012','63013','64500') and statecode = '20'
update herm_Gen_Liab_Pre
set StateExceptionIndicator = '1' where sublinecode in ('345','347') and classificationcode = '80050' and statecode = '20'
select * from herm_Gen_Liab_Pre
The problem is there is a column in this table called riskidCoPaymentPercentageMoldDamageCoverageCode and I have to insert value '1' into that column based on this select query
select cast(pd.policyno as varchar(9)),pf.formno
from premiumdirect pd join policyforms pf on pd.policyno = pf.policyno
where pd.entrydate between '01/01/2004' and '12/31/2007'
and pf.formno in ('CG2167','CG3131')
and exists
( select policyno from premiumdirect p join premiumstatdirect ps
on substring(pd.policyno,1,9) = substring(pf.policyno,1,9)
where ps.LineOfBusinessCode in ( '170', '052' , '152' , '171', '175', '180')
and ps.sublinecode in ('334','335','336','337','338','339'))
order by pd.entrydate
How can i insert implement this. I tried temp variable, was successful partially, so need a better approach to it.
Chintan
View 6 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 6, 2006
Hi,
This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.
This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.
My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?
How can i insert the value stored in a variable in a table from previous SQL Task.
Can anyone suggest some solution,links to try a workaround?
Thanks in advance.
Regards,
Aman
View 4 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
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
May 25, 2006
HI,
How to create package in SSIS by applying the business Logic like if the record already exist it should be and update else it should be an insert in the destination table. how to achive this funcality in SQL SERVER 2005 (Business Intelligence studion).
Thanks & Regards
Nagaraj.S
View 4 Replies
View Related
Nov 17, 2015
Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .
View 8 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
May 2, 2008
Hi,
I found out that executing the procedure SP_INDEXOPTION and setting 'AllowRowLocks' to false i can prevent the sql server from locking rows in a table and 'AllowPageLocks' prevents from pages being locked. I need to preform same operation
in case of tables. I need to perform insertion operations concurrently and acquire required locks manually. Is there a way to stop sqlserver from acquiring locks on the table. I need to disable all the locks (row, page and table).
Thank you in advance.
View 9 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
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
Jun 10, 2008
How to bold a value while inserting into the table in SQL 2005?
View 1 Replies
View Related
Apr 3, 2006
How to get information , if there is no insertion of a row in a table within particular span of time.
View 2 Replies
View Related
Sep 20, 2007
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
View 6 Replies
View Related
Nov 26, 2007
Hi,
I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.
View 2 Replies
View Related
