correct way to update a db from a dataset using sqlxml

I have a dataset populated correctly from a mapping XSD (which maps a many to
many rel. from the db).
but on using the SQLXMLDataAdapter.update() method gives me Invalid column
errors. Even with a MS produced northwind schema. Is there a standard code
example of how to do an update like this?
thankyou,
Gordon Doherty
Hi Gordy:
Here is a kb article which has several links to address what you're looking
for: http://support.microsoft.com/default...b;en-us;313483
Regards,
John Cross
This posting is provided "AS IS" with no warranties, and confers no rights.

Correct syntax for an update stored procedure

This is probably a very simple question but i would appreciate some helpwith the correct syntax for andupdate stored procedure

I have created user form that allows the user toupdate thenameandaddress fields in adatatable called customers based on the input valuecustomer ID = ( datatable/Customers)customerID
I have got this far and then got lost:
Create SP_UpdateCustomer
(@.customerID, @.name, @.address)
As
Update customers ( name, address)
Where customerID = @.customerID

GO
Could anyone tell me what the correct sntax should be.
many thanks
MartinHi Martin,
You'll need to specify the data types in the create clause, and add a set clause to change the fields you want to update:
create proc sp_updatecustomer (@.customerid varchar(50), @.name varchar(50), @.address varchar(50))
as
update customers
setname=@.name,address=@.address
where customerID= @.customerID
Note - this might be a bit dangerous from a security standpoint, and you might also want to introduce some validation on the Customer ID field, to avoid anyone maliciously changing all the records by entering a customer ID of "a or 1=1"
|||

rJonas
Many thanks for your reply
I note the securtiy points you made
Thank you
martin

|||

rjonas wrote:

Note - this might be a bit dangerous from asecurity standpoint, and you might also want to introduce somevalidation on the Customer ID field, to avoid anyone maliciouslychanging all the records by entering a customer ID of "a or 1=1"

?? That is not physcially possible with the stored procedure the posteris using. The stored procedure is corerctly parameterized and thedanger you pointed out does not exist here.
Here are some articles on SQL injection and parameterized queries:

Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

copying databases from live to backup SQL Server

Hello All,
I have two installations of SQL Server, one is the live SQL Server with
all live databases. What I want to do is create and update all databases on
this second database server which is to be used as a backup, is there a way
to copy over these 30 odd databases to this backup server, and then also set
it up to do a daily update of these databases with the live data ? or is
that to be done manually taking each database at a time. I know this can be
done automatically in SQL Server 2005, but we have got SQL Server 2000 for
now.
Imran.
Take a look at replication or log shipping
http://sqlservercode.blogspot.com/
|||thanks a lot for the response, looking into replication now.
Imran.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135172121.968297.208350@.g44g2000cwa.googlegr oups.com...
> Take a look at replication or log shipping
>
> http://sqlservercode.blogspot.com/
>

copying databases from live to backup SQL Server

Hello All,
I have two installations of SQL Server, one is the live SQL Server with
all live databases. What I want to do is create and update all databases on
this second database server which is to be used as a backup, is there a way
to copy over these 30 odd databases to this backup server, and then also set
it up to do a daily update of these databases with the live data ? or is
that to be done manually taking each database at a time. I know this can be
done automatically in SQL Server 2005, but we have got SQL Server 2000 for
now.
Imran.Take a look at replication or log shipping
http://sqlservercode.blogspot.com/|||thanks a lot for the response, looking into replication now.
Imran.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135172121.968297.208350@.g44g2000cwa.googlegroups.com...
> Take a look at replication or log shipping
>
> http://sqlservercode.blogspot.com/
>

copying databases from live to backup SQL Server

Hello All,
I have two installations of SQL Server, one is the live SQL Server with
all live databases. What I want to do is create and update all databases on
this second database server which is to be used as a backup, is there a way
to copy over these 30 odd databases to this backup server, and then also set
it up to do a daily update of these databases with the live data ? or is
that to be done manually taking each database at a time. I know this can be
done automatically in SQL Server 2005, but we have got SQL Server 2000 for
now.
Imran.Take a look at replication or log shipping
http://sqlservercode.blogspot.com/|||thanks a lot for the response, looking into replication now.
Imran.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135172121.968297.208350@.g44g2000cwa.googlegroups.com...
> Take a look at replication or log shipping
>
> http://sqlservercode.blogspot.com/
>

Copying column into another

Hi,
We already have "Data Entry Time" column with hundreds of records in it, and
realized later we also need "Data Update Time." The "Data Update Time"
should not be NULL. So as a starting point, we will copy everything in the
"Data Entry Time" column into the "Data Update Time" column for the already
existing records, and then each will go its own way. So my question is how
do I copy the "Data Entry Time" column with records into the "Data Update
Time" column?
YCAsp Psa wrote:
> Hi,
> We already have "Data Entry Time" column with hundreds of records in it, a
nd
> realized later we also need "Data Update Time." The "Data Update Time"
> should not be NULL. So as a starting point, we will copy everything in th
e
> "Data Entry Time" column into the "Data Update Time" column for the alread
y
> existing records, and then each will go its own way. So my question is ho
w
> do I copy the "Data Entry Time" column with records into the "Data Update
> Time" column?
> YC
UPDATE your_table
SET data_update_time = data_entry_time
WHERE data_update_time IS NULL ;
ALTER TABLE your_table
ALTER COLUMN data_update_time DATETIME NOT NULL;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you!
YC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141860909.277930.39530@.z34g2000cwc.googlegroups.com...
> Asp Psa wrote:
> UPDATE your_table
> SET data_update_time = data_entry_time
> WHERE data_update_time IS NULL ;
> ALTER TABLE your_table
> ALTER COLUMN data_update_time DATETIME NOT NULL;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Copying an ntext cell from one cell into another (destination row already exists

Hi!

What I'd like to do is:

UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @.precomputed_id_1)
WHERE table1_ID = @.precomputed_id_2

Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.

I'd like to do something like (PSEUDOcode):

WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@.textptr_initialised_to_point_at_target_c ell))

But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...

Any hints or pointers HUGELY appreciated... THANXIs this a standard *problem*? Or an unsolved one (just an sqlserver limitation that must be dealt with in a totally different way??)

I saw 10 people read the thread but nobody answered, maybe the problem is a generally troublesome one with no good solutions...

Any ideas at all?

Thanx,

wOAst

'Copy Website' function in VS 2005 updates or overwrites Database?

I have a sql 2005 express database uploaded to my website with important information in it.

Now, I had to make some table change and need to update the online database.

I am not sure if the 'Copy Website' function in Visual Studio 2005 will update the database structure and data or will simply overwrite it.

Does anybody know the answer? If it overwrites it, would you please point me to information on how can I update the database structure and data without ruining it?

Thanks.

The system will over right as the moderfications will have been done in the local copy of the database... ie in the APP_Data.. (I am thinking that this is your case). If you only wanted to change the database schema and such one way would have been to create a database project and script out the changes as you were making them. Then you could have run the script against the old database.

Going forward you might have to copy down the database from the production system... Copy your web site project to the production system then import the old data into the new database structure on the server. Or you might be able to find some scripts to compare the databases and generate the scripts required to make the change in the production system.

|||Thanks Glenn!

Copy row in trigger instead of update?

I think this is a tough one, at least with my limited knowlege. Here
is what I am trying to do.
Background:
I want to create an audit trail for edits made to a database. I want
to do this by catching the update in a SQL trigger and turning it into
an insert with a time stamp and cancel the update.
So any row you change really does not change, it is copied to a new
row in the same table with a time stamp.
Copying the row is normally easy as in:
Insert into mytable select * from mytable where id= '17'
This will not work though, The problem with this is that id is an
Identity field and you cannot specify the value of an identity. So the
first trick is to basically do a select * (except id). I figured this
out here is the code:
_________________________________________________________________
declare @.tb sysname, @.col sysname, @.identid sysname
select @.tb='testupdate',
@.col='id'
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
id=17'
print @.sql
exec(@.sql)
The problem with this is that it copies every row in the table. I
need to copy just the changed row. The above code does not include the
timestamp part here is that part, and separate section of code that
will be in the same trigger.
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.id
This little bit of code effectively creates a time stamp on every
edited row and obviously get the id of the changed row. Now if I could
capture that id and use it in the first bit of code I think I would
have it.
I tried to do something like this
CREATE TRIGGER testtrig
ON testupdate
FOR UPDATE
AS
declare @.tb sysname, @.col sysname, @.myid sysname
select @.tb='testupdate',
@.col='id',
@.myid = @.@.identity
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
+ @.myid
print @.sql
exec(@.sql)
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.idThere are several things you might consider.
If you can, add a column to the table with a default value of getdate, so
all inserts are covered.
Then read about instead of triggers... They are new to SQL 2000, the normal
trigger fires AFTER the action, which is causing you a problem. An instead
of trigger fires INSTEAD OF the update ( for instance). You still get the
inserted ,deleted tables. The ACTUAL update only occurs IF you include an
update in your trigger... you could write something like
create trigger mytrig on mytable instead of update
as
insert into mytable select * from inserted
The original update would NOT occur, and instead you would have inserted the
NEW value of the row. The NEW row would have a new ID of course... you'd
have to mess with that...perhaps have a second integer column which
contains the original ID..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"OrlandoRocks" <parkJunkie@.orlandorocks.com> wrote in message
news:55879c92.0409120419.8418a63@.posting.google.com...
> I think this is a tough one, at least with my limited knowlege. Here
> is what I am trying to do.
> Background:
> I want to create an audit trail for edits made to a database. I want
> to do this by catching the update in a SQL trigger and turning it into
> an insert with a time stamp and cancel the update.
> So any row you change really does not change, it is copied to a new
> row in the same table with a time stamp.
> Copying the row is normally easy as in:
> Insert into mytable select * from mytable where id= '17'
> This will not work though, The problem with this is that id is an
> Identity field and you cannot specify the value of an identity. So the
> first trick is to basically do a select * (except id). I figured this
> out here is the code:
> _________________________________________________________________
> declare @.tb sysname, @.col sysname, @.identid sysname
> select @.tb='testupdate',
> @.col='id'
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
> id=17'
> print @.sql
> exec(@.sql)
>
> The problem with this is that it copies every row in the table. I
> need to copy just the changed row. The above code does not include the
> timestamp part here is that part, and separate section of code that
> will be in the same trigger.
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id
>
> This little bit of code effectively creates a time stamp on every
> edited row and obviously get the id of the changed row. Now if I could
> capture that id and use it in the first bit of code I think I would
> have it.
> I tried to do something like this
>
> CREATE TRIGGER testtrig
> ON testupdate
> FOR UPDATE
> AS
> declare @.tb sysname, @.col sysname, @.myid sysname
> select @.tb='testupdate',
> @.col='id',
> @.myid = @.@.identity
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
> + @.myid
> print @.sql
> exec(@.sql)
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id