Data Driven Query Task - question about Insert and Update queries

Does anyone know if, in an Update or Insert query within a DDQ task,
it's possible to have more than one statement in the query, and have
them both execute?
e.g.
update tablex set field1=1 where id=?
update tablex set field2 = 2 where id=?
Also, on the same subject, can you mix the statements, so let's say
even if it's the Update query you're using, could you tag an insert on
the end as well?
e.g.
update tablex set field1=1 where id=?
insert into tablex (field2) values (?)
Thanks for any help.Just as an afterthought, I suppose if this isn't possible it may be
useful to know whether the other two types of query can be 'hijacked'
and used as an extra insert (or whatever), but I'll wait to see if
anyone can confirm the original question or not before investigating
this one!!|||Quick note for anyone else looking at the same question: I've just
found the following on msdn:
"In order to refer to your SQL statements, you assign each statement a
name, called a query type. A query type, returned by your Microsoft=AE
ActiveX=AE script code, is used to select one of your SQL statements to
execute. Data Transformation Services (DTS) provides the following four
names:
Insert
Update
Delete
User
These query types should be viewed only as unique identifiers assigned
to each statement. It is in fact possible to perform any SQL operation
supported by the connection. It would be possible for example, to
perform four different updates, four different inserts or any mix of
these or stored procedures."
So I could do it by having an extra update as the 'delete' query, for
example. However, I'd feel it would be better 'practice' to have two
update statements within the Update query...I'd be grateful if anyone
with more experience than me could possibly provide an answer as to
whether you can have the two statements in the one query, as I've
trawled the internet and come up with nothing?
Many, many thanks indeed.,sql

Data Conversion (String to DateTime)

I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates.

Below is the code that I am using:

<asp:SqlDataSource

id="SqlDataSource1"

runat="server"

connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>"

selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq"

insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@.RepName, @.BranchName, @.Date, @.DueDate, @.ByEmail, @.ByFax, @.Fax, @.ProjExp, @.PrintQty, @.Disc, @.Email, @.RepEmail)">

<InsertParameters>

<asp:FormParameterName="RepName"FormField="LoanRepNameBox"/>

<asp:FormParameterName="BranchName"FormField="BranchList"/>

<asp:FormParameterName="Date"FormField="CurrentDateBox"Type="DateTime"/>

<asp:FormParameterName="DueDate"FormField="ReqDueDateBox"Type="DateTime"/>

<asp:FormParameterName="ByEmail"FormField="ProofByEmailCheckbox"Type="boolean"/>

<asp:FormParameterName="ByFax"FormField="ProofByFaxCheckbox"Type="boolean"/>

<asp:FormParameterName="Fax"FormField="FaxNumberBox"/>

<asp:FormParameterName="ProjExp"FormField="ProjectExplanationBox"/>

<asp:FormParameterName="PrintQty"FormField="PrintQuantityBox"/>

<asp:FormParameterName="Disc"FormField="PDFByDiscCheckbox"Type="boolean"/>

<asp:FormParameterName="Email"FormField="PDFByFaxCheckbox"Type="boolean"/>

<asp:FormParameterName="RepEmail"FormField="LoanRepEmailBox"/>

</InsertParameters>

</asp:SqlDataSource>

protectedvoid Button1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

}

I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance.

Thank you,

Paul

What about remove (cross out in the following) the Type="boolean" for these formfields:

<asp:FormParameterName="ByEmail"FormField="ProofByEmailCheckbox"Type="boolean" />

<asp:FormParameterName="ByFax"FormField="ProofByFaxCheckbox" Type="boolean" />

<asp:FormParameterName="Disc"FormField="PDFByDiscCheckbox" Type="boolean" />

<asp:FormParameterName="Email"FormField="PDFByFaxCheckbox" Type="boolean" />

|||

Thank you, that ended the exception errors.

Paul

Data Archiving

Hi ,

I need to archive my production database to a new Server....

Is it possible to move data using INSERT INTO ServerName.DBName.dbo.TableName from the current Database Server!!!

Do I need to create a linked server to do this...or shoud I go for DTS..

Thanks
Cheriyan.You have too many options :
1.- Backup and restore in the new server.
2.- Create a linked server and generate a script to the all the insert..into your other tables.
3.- Use DTS.

If this is a one-time move, because a hardware upgrade, not a task that should be somewhat performed periodically, then take option 1.

Reagrds,

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob
=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:

> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob
|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:

> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000 :)
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bangås" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> > I have around 7 tables that get new data or updates in the production
> > envt and I would like to insert/update these tables on a daily basis
> > in the testing environment. Can you suggest a script to do this.
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000 :)
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bobexamnotes <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:

> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists> ) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is tha
t
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:

> examnotes <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>