partially update webpage

How to dynamically and partially Update the webpage? The content of
the part of the page is from the user's requerement and get data from
the server side.
The purpose is to speed up the page update. We have a page that most
of content are static, only small part is changed per user's select
and the replying data need to get from server side database. Thanks
for help. GeneKeep your content in a database. Then have the Web site query the database
to render the content.

Or just use a CMS such as SiteSimplify.

"GeneSZ" <jingshengzhou@.hotmail.com> wrote in message
news:60a82f9c.0308291149.3e754970@.posting.google.c om...
> How to dynamically and partially Update the webpage? The content of
> the part of the page is from the user's requerement and get data from
> the server side.
> The purpose is to speed up the page update. We have a page that most
> of content are static, only small part is changed per user's select
> and the replying data need to get from server side database. Thanks
> for help. Gene

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a database with same file
structure as you want to have for the one you want to attach, stop SQL server, copy the files you
want to attach over file files for the db you just created and start SQL server. Now, the db is
corrupt to some extent, the question is whether the database will be available with problems in it
or suspect when you start SQL server. MS Support is probably the best route in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a da
tabase with same file
structure as you want to have for the one you want to attach, stop SQL serve
r, copy the files you
want to attach over file files for the db you just created and start SQL ser
ver. Now, the db is
corrupt to some extent, the question is whether the database will be availab
le with problems in it
or suspect when you start SQL server. MS Support is probably the best route
in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

Partial database copy - kind of replication

Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary :(.
Thanks:
PeterPeter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>

Partial database copy - kind of replication

Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary .
Thanks:
Peter
Peter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary .
> Thanks:
> Peter
>
|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary .
> Thanks:
> Peter
>
|||Unless the dataflow is in both directionns, and involves
autonomy or conflicts, I would recommend transactional
over merge, as it is significvantly faster and less
resource-intensive.
If you want a non-replication version, then
binary_checksums can be used although there is some
discussion as to whether these can always be relied upon
100%. Redgate do a DataCompare tool to do this type of
thing also.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Parent Table Control

Hi All,
I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
UPDATED, DELETED.
If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
I would like know if have way to I specifique that DELETE can be used only
in 'TPARENT'
If user try use DELETE in CLILD he receive a error, user can use DELETE
only in TPARENT.
I did try use triger, but if I try DELET of TPARENT I also receive the
error, I want receive erro on;y if I try use DELETE on CLILD
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
AND type = 'TR')
DROP TRIGGER BlockDeleteOnDomiciliosBancarios
GO
CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
ON DomiciliosBancarios
FOR DELETE
AS
BEGIN
ROLLBACK TRANSACTION
PRINT ('No possvel apagar de DomiciliosBancarios')
END
can anyone help-me -- Thanks
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPAI VALUES ('Test 01')
INSERT INTO TPAI VALUES ('Test 02')
INSERT INTO TPAI VALUES ('Test 03')
---
CREATE TABLE CLILD
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to delete
rows in TPARENT table. However, I am not sure what's the exact error
message when "If user try use DELETE in CLILD he receive a error, user can
use DELETE only in TPARENT." Would you please help describe it further? If
I have misunderstood your concern, please feel free to point it out.
Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
CLILD table's related rows will also be deleted when you are deleting
TPARENT rows.
Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
while leave the rows in CLILD. The related rows in CLILD will be also
deleted. If you want to delete rows in TPARENT and not delete rows in
CLILD, you must specify your won trigger to accomplish this instead of
using FOREIGN KEY.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Here is a suggestion: Use both a hidden table and a public view for
CLILD. Put the referential integrity on the hidden table, and put
an INSTEAD OF trigger on the view that generates an error and
does not delete anything. If you need to, roll back the transaction
in the trigger also, but since it is an INSTEAD OF trigger, there is
no DELETE to roll back. Here is a repro:
CREATE TABLE TPARENT
(
CONSTRAINT pk_TPARENT
PRIMARY KEY(TPARENT),
TPARENT CHAR(30)
NOT NULL
)
INSERT INTO TPARENT VALUES ('Test 01')
INSERT INTO TPARENT VALUES ('Test 02')
INSERT INTO TPARENT VALUES ('Test 03')
-- Hidden table with foreign key constraint
CREATE TABLE CLILD_hidden
(
CONSTRAINT fk_CLILD
FOREIGN KEY(TPARENT )
References TPARENT (TPARENT )
ON UPDATE CASCADE
ON DELETE CASCADE,
TPARENT CHAR(30)
NOT NULL
)
go
-- view that is used externally as the table
create view CLILD as
select TPARENT from CLILD_hidden
go
insert into CLILD VALUES ('Test 01')
insert into CLILD VALUES ('Test 02')
insert into CLILD VALUES ('Test 03')
go
-- do not allow delete from the view
CREATE TRIGGER BlockDeleteOnCLILD
ON CLILD INSTEAD OF DELETE
AS
-- ROLLBACK TRANSACTION -- if necessary for other reasons
PRINT ('No possvel apagar de CLILD')
go
select * from CLILD
go
delete from TPARENT
where TPARENT = 'Test 01'
go
select * from CLILD
go
delete from CLILD
where TPARENT = 'Test 02'
go
delete from TPARENT
where TPARENT = 'Test 03'
go
select * from CLILD
go
-- drop view CLILD
-- drop table CLILD_hidden, TPARENT
-- Steve Kass
-- Drew University
ReTF wrote:

>Hi All,
>I Have 2 tables, and when I DELETE or UPDATE 'TPARENT' the 'CLILD' also
>UPDATED, DELETED.
>If I DELETE in CLILD, the row in CLILD is DELETED, but in 'TPARENT' no.
>I would like know if have way to I specifique that DELETE can be used only
>in 'TPARENT'
>If user try use DELETE in CLILD he receive a error, user can use DELETE
>only in TPARENT.
>I did try use triger, but if I try DELET of TPARENT I also receive the
>error, I want receive erro on;y if I try use DELETE on CLILD
>IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = 'BlockDeleteOnDomiciliosBancarios'
> AND type = 'TR')
> DROP TRIGGER BlockDeleteOnDomiciliosBancarios
>GO
>CREATE TRIGGER BlockDeleteOnDomiciliosBancarios
>ON DomiciliosBancarios
>FOR DELETE
>AS
>BEGIN
> ROLLBACK TRANSACTION
> PRINT ('No possvel apagar de DomiciliosBancarios')
>END
>can anyone help-me -- Thanks
>CREATE TABLE TPARENT
>(
> CONSTRAINT pk_TPARENT
> PRIMARY KEY(TPARENT),
> TPARENT CHAR(30)
> NOT NULL
> )
>INSERT INTO TPAI VALUES ('Test 01')
>INSERT INTO TPAI VALUES ('Test 02')
>INSERT INTO TPAI VALUES ('Test 03')
>---
>CREATE TABLE CLILD
>(
> CONSTRAINT fk_CLILD
> FOREIGN KEY(TPARENT )
> References TPARENT (TPARENT )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> TPARENT CHAR(30)
> NOT NULL
> )
>
>|||Hi,
I want block if the user try DELETE of CLILD, the user can DELETE only of
TPARENT .
Because CLILD has ON DELETE/UPDATE CASCADE, when TPARENT (row) is deleted in
CLILD this also deleted.
But if user try use DELETE direct in CLILD the user must receive a error,
the user can only use DELETE in TPARENT no in childs.
sorry about my english, this is not my native language, if you don't
understand let-me know and I will explain again. Thanks
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> escreveu na mensagem
news:azlw3bxlFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know how to delete
> rows in TPARENT table. However, I am not sure what's the exact error
> message when "If user try use DELETE in CLILD he receive a error, user
> can
> use DELETE only in TPARENT." Would you please help describe it further? If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowlegde, when you are specifing ON DELETE/UPDATE CASCADE,
> CLILD table's related rows will also be deleted when you are deleting
> TPARENT rows.
> Since you have specificed FOREIGN KEY, you cannot delete rows in TPARENT
> while leave the rows in CLILD. The related rows in CLILD will be also
> deleted. If you want to delete rows in TPARENT and not delete rows in
> CLILD, you must specify your won trigger to accomplish this instead of
> using FOREIGN KEY.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi,
Thanks for your reply.
I understood you request as below
1) User is not able to delete from CLILD table
2) User could delete from TPARENT table
3) When rows in TPARENT table is deleted, remain the related rows in CLILD
table.
If I have misunderstood your concern, please feel free to point it out.
To accomplish this, you cannot use Foreign Key in your tables. As I have
said before, you'd better create your own triggers to do so.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!
Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en...es_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!
|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/e...des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
--
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Parameters in Stored Procedures problem

I have 2 stored procedures, both are listed below. The
first Stored Procedure takes 8 parameters and runs and
update query using those parameters. This procedure
takes about 3 minutes to run. The second procedure
doesn't take any parameters, but declares the same 8
variables at the start of the procedure and sets them to
same valuse that I used when I ran the first procedure
and this one takes less than a second to run.
Just a little more information here is the size of the
tables:
aggQuotaAtt 135194
txoQuota: 135194
mstrsCustomer: 19741
So my question is why does the same procedure take
radically different when I use parameters.
Here is the first procedure which takes 3 minutes:
ALTER PROCEDURE [dbo].[Paul'sJunk]
@.PrdID varchar(18),
@.ShipToID int,
@.PoolYearPeriodID int,
@.AorID int,
@.Aor0ID int,
@.Aor1ID int,
@.Aor2ID int,
@.Aor3ID int
as
print getDate()
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
and here is the second procedure. This is the one that
takes less than a second:
ALTER PROCEDURE [dbo].[Paul'sJunk2]
as
DECLARE @.PrdID varchar(18)
DECLARE @.ShipToID int
DECLARE @.PoolYearPeriodID int
DECLARE @.AorID int
DECLARE @.Aor0ID int
DECLARE @.Aor1ID int
DECLARE @.Aor2ID int
DECLARE @.Aor3ID int
SET @.PrdID = '20027-933'
SET @.ShipToID = 0
SET @.PoolYearPeriodID = 73
SET @.AorID = 1
SET @.Aor0ID = NULL
SET @.Aor1ID = NULL
SET @.Aor2ID = NULL
SET @.Aor3ID = NULL
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))Jory asked effectively the same question yesterday in the .programming
group - the same responses apply.
"Paul Cavacas" <pcavacas@.oceanspray.com> wrote in message
news:08f301c36327$a1e66090$a601280a@.phx.gbl...
> I have 2 stored procedures, both are listed below. The
> first Stored Procedure takes 8 parameters and runs and
> update query using those parameters. This procedure
> takes about 3 minutes to run. The second procedure
> doesn't take any parameters, but declares the same 8
> variables at the start of the procedure and sets them to
> same valuse that I used when I ran the first procedure
> and this one takes less than a second to run.
> Just a little more information here is the size of the
> tables:
> aggQuotaAtt 135194
> txoQuota: 135194
> mstrsCustomer: 19741
> So my question is why does the same procedure take
> radically different when I use parameters.
> Here is the first procedure which takes 3 minutes:
> ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
> as
> print getDate()
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
> and here is the second procedure. This is the one that
> takes less than a second:
> ALTER PROCEDURE [dbo].[Paul'sJunk2]
> as
> DECLARE @.PrdID varchar(18)
> DECLARE @.ShipToID int
> DECLARE @.PoolYearPeriodID int
> DECLARE @.AorID int
> DECLARE @.Aor0ID int
> DECLARE @.Aor1ID int
> DECLARE @.Aor2ID int
> DECLARE @.Aor3ID int
> SET @.PrdID = '20027-933'
> SET @.ShipToID = 0
> SET @.PoolYearPeriodID = 73
> SET @.AorID = 1
> SET @.Aor0ID = NULL
> SET @.Aor1ID = NULL
> SET @.Aor2ID = NULL
> SET @.Aor3ID = NULL
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))|||can't say about the whole thing, but
what are the differences in the execution plan?
i have concern the select queries:
SELECT ShipToID FROM WaveAgg.dbo.mstrsCustomer WHERE
Aor0ID = @.Aor0ID
etc, where @.AorxID value is NULL,
this query should be a simple index lookup
but i have situations where the NULL value in the where
clause resulting in a table scan
>--Original Message--
> I have 2 stored procedures, both are listed below. The
>first Stored Procedure takes 8 parameters and runs and
>update query using those parameters. This procedure
>takes about 3 minutes to run. The second procedure
>doesn't take any parameters, but declares the same 8
>variables at the start of the procedure and sets them to
>same valuse that I used when I ran the first procedure
>and this one takes less than a second to run.
> Just a little more information here is the size of the
>tables:
>aggQuotaAtt 135194
>txoQuota: 135194
>mstrsCustomer: 19741
> So my question is why does the same procedure take
>radically different when I use parameters.
>Here is the first procedure which takes 3 minutes:
>ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
>as
>print getDate()
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
>and here is the second procedure. This is the one that
>takes less than a second:
>ALTER PROCEDURE [dbo].[Paul'sJunk2]
>as
>DECLARE @.PrdID varchar(18)
>DECLARE @.ShipToID int
>DECLARE @.PoolYearPeriodID int
>DECLARE @.AorID int
>DECLARE @.Aor0ID int
>DECLARE @.Aor1ID int
>DECLARE @.Aor2ID int
>DECLARE @.Aor3ID int
>SET @.PrdID = '20027-933'
>SET @.ShipToID = 0
>SET @.PoolYearPeriodID = 73
>SET @.AorID = 1
>SET @.Aor0ID = NULL
>SET @.Aor1ID = NULL
>SET @.Aor2ID = NULL
>SET @.Aor3ID = NULL
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>.
>

Parameterizing the XPath for a modify()

Hi all,

I'm trying to write a generic stored procedure which will parameterize the XPath in my XML file so that I can update a value by giving just its path and the new value I wish to store:

Here is the code:
CREATE PROCEDURE ModifyLoanXML
@.LoanNumber char(60),
@.XPathQuery varchar(300),
@.Value varchar(300)
AS
UPDATE Loans SET LoanXML.modify('replace value of (sql:variable("@.XPathQuery"))[1] with xs:string(sql:variable("@.Value"))')
WHERE Loans.LoanNumber = @.LoanNumber
GO

Unfortunately I am getting the following error:
Msg 2337, Level 16, State 1, Procedure ModifyLoanXML, Line 6
XQuery [Loans.LoanXML.modify()]: The target of 'replace' must be at most one node, found 'xs:string ?'

I've tried a number of things to try to get this to go through but I'm having no luck. Is it possible to completely parameterize the XPath you wish to change when calling XQuery.modify() ?

Is there any way to specify that my path will always point to an attribute (not a node) so that this proc can be created? Thanks!

-Karthik Hariharan

The path specified in modify() must be a string literal, so the approach you are taking will not work out. The sql:variable("@.XPathQuery") is not interpreted as a path, but as a string value.

You can use dynamic sql to achieve what you are looking for. SInce you will be generating dynamic SQL, you will need to take steps to ensure that you dont end up with SQL injection from accepting untrusted XPaths.

|||Thanks Todd. I was trying to avoid the dynamic SQL method precisely to avoid any SQL injection vulnerabilities. Is there no way to achieve this dynamix XQuery using a stored procedure? If anyone else has a suggestion please let me know. Thanks.
|||I resolved the issue by using an Exec() T-SQL command. Here is my code below:

CREATE PROCEDURE [dbo].[ModifyLoanXML]
@.LoanID UNIQUEIDENTIFIER,
@.XPathQuery varchar(max),
@.Value varchar(max)
AS
DECLARE @.query varchar(max)
DECLARE @.LoanIDstr varchar(max)
SET @.LoanIDStr = CONVERT (varchar(max),@.LoanID)

SET @.query='UPDATE Loans SET LoanXML.modify(''declare namespace MISMO="http://mrgdev.local/mismo/";replace value of ' + @.XPathQuery + ' with "' +@.Value +'"'') WHERE Loans.InternalID = ''' + @.LoanIDstr + ''''
exec(@.query)

To avoid a possible SQL injection, I parameterized the LoanID and convert it to a varchar within the stored procedure. Just wanted to share this with you all.

Regards,
Karthik Hariharan
|||

Hi Karthik,

Your code still appears to be subject to SQL injection attacks with respect to the XPathQuery variable and the Value variable, if either is untrusted. Since you are concatenating them with the SQL string, if an untrusted user was able to specify the XPathQuery or Value variable, then they may be able to embed quotes and comment characters to change the behavior of your query. You can change your query to parameterize both Value (using sql:variable) and LoadIDstr (using a parameter) but accepting untrusted XPathQuery will be difficult without fully validating that it is safe.

parameterized update sp_executesql

I am trying to limit network traffic by only sending fields that have actually changed to an update stored procedure. I am obviously not doing this correctly as I am rather new to this. This is a small version of my sproc that I am using to test the workings. I am getting 'Error converting data type nvarchar to int'. If you can help me get this easy query working, I would very much like your help to use the EXEC (sql) where sql is an sp_executesql statement because some of my update fields are much larger than 4000 nvarchar. The following statement was working when I was using the second version. If I am trying to update an integer foreign key field, shouldn't I try to use the parameters to send an integer value for that field, rather than an nvarchar? As you can see, I really need to get some not so basic info on this. I have done hours of research on the net and can't find some of these simple background principals. I also question whether I am saving enough network traffic to validate the cost in processing for building this query and executing it. Any help you can provide will be greatly appreciated.

Non working

CREATE PROCEDURE tspJobs_Updatetest
(
@.JobID int=0,
@.AddressID int=0,
@.AddressChg int=0,
@.CustomerID int=0,
@.CustomerChg int=0,
@.ContactID int=0,
@.ContactChg int=0,
@.PlanID int=0,
@.PlanChg int=0,

)
AS

DECLARE @.updateClause nvarchar(1000);
DECLARE @.whereClause nvarchar(500);
DECLARE @.sqlStatement nvarchar(4000);
DECLARE @.paramDefinition nvarchar(1000);
DECLARE @.valuesDefinition nvarchar(1500);

--Set Where Clause and parameter statement

IF @.JobID > 0
BEGIN
SET @.whereClause = ' WHERE job_id = @.update_job_id '
SET @.paramDefinition = N'@.update_job_id int '
SET @.valuesDefinition = N'@.update_job_id=@.JobID '

--Build Update Statement

SET @.updateClause = null

IF @.AddressChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_address_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_address_id = @.AddressID'
IF @.updateClause IS NULL
SET @.updateClause = N'address_id = @.update_address_id'
ELSE
SET @.updateClause = @.updateClause + N', address_id = @.update_address_id'
END

IF @.CustomerChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_customer_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_customer_id = @.CustomerID'
IF @.updateClause IS NULL
SET @.updateClause = N'customer_id = @.update_customer_id'
ELSE
SET @.updateClause = @.updateClause + N', customer_id = @.update_customer_id'
END

IF @.ContactChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_contact_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_contact_id = @.ContactID'
IF @.updateClause IS NULL
BEGIN
IF @.ContactID = 0
SET @.updateClause = N'contact_id = NULL'
ELSE
SET @.updateClause = N'contact_id = @.update_contact_id'
END
ELSE
BEGIN
IF @.ContactID = 0
SET @.updateClause = @.updateClause + N', contact_id = NULL'
ELSE
SET @.updateClause = @.updateClause + N', contact_id = @.update_contact_id'
END
END

IF @.PlanChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_plan_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_plan_id = @.PlanID'
IF @.updateClause IS NULL
BEGIN
IF @.PlanID = 0
SET @.updateClause = N'plan_id = NULL'
ELSE
SET @.updateClause = N'plan_id = @.update_plan_id'
END
ELSE
BEGIN
IF @.PlanID = 0
SET @.updateClause = @.updateClause + N', plan_id = NULL'
ELSE
SET @.updateClause = @.updateClause + N', plan_id = @.update_plan_id'
END
END

-- Complete SQL statement

IF NOT @.updateClause IS NULL
BEGIN
SET @.sqlStatement = 'UPDATE [dbo].[Jobs] SET ' + @.updateClause + @.whereClause
EXEC sp_executesql @.sqlStatement,@.paramDefinition, @.valuesDefinition
END
END
GO

Working, but using nvarchar for integer foreign key field updates

CREATE PROCEDURE tspJobs_Update
(
@.JobID int=0,
@.AddressID int=0,
@.AddressChg int=0,
@.CustomerID int=0,
@.CustomerChg int=0,
@.ContactID int=0,
@.ContactChg int=0,
@.PlanID int=0,
@.PlanChg int=0

)
AS

DECLARE @.updateClause varchar(3000);
DECLARE @.whereClause varchar(1000);
DECLARE @.sqlStatement nvarchar(4000);
DECLARE @.paramDefinition nvarchar(500)

--Set Where Clause and parameter statement

IF @.JobID > 0
BEGIN
SET @.whereClause = ' WHERE job_id = @.update_job_id'
SET @.paramDefinition = N'@.update_job_id int'

--Build Update Statement

SET @.updateClause = null

IF @.AddressChg > 0
BEGIN
DECLARE @.update_address_id varchar(15)
SET @.update_address_id = @.AddressID
IF @.updateClause IS NULL
SET @.updateClause = 'address_id = ' + @.update_address_id
ELSE
SET @.updateClause = @.updateClause + ', address_id = ' + @.update_address_id
END

IF @.CustomerChg > 0
BEGIN
DECLARE @.update_customer_id varchar(15)
SET @.update_customer_id = @.CustomerID
IF @.updateClause IS NULL
SET @.updateClause = 'customer_id = ' + @.update_customer_id
ELSE
SET @.updateClause = @.updateClause + ', customer_id = ' + @.update_customer_id
END

IF @.ContactChg > 0
BEGIN
DECLARE @.update_contact_id varchar(15)
SET @.update_contact_id = @.ContactID
IF @.updateClause IS NULL
BEGIN
IF @.update_contact_id IS NULL
SET @.updateClause = 'contact_id = NULL'
ELSE
SET @.updateClause = 'contact_id = ' + @.update_contact_id
END
ELSE
BEGIN
IF @.update_contact_id IS NULL
SET @.updateClause = @.updateClause + ', contact_id = NULL'
ELSE
SET @.updateClause = @.updateClause + ', contact_id = ' + @.update_contact_id
END
END

IF @.PlanChg > 0
BEGIN
DECLARE @.update_plan_id varchar(15)
SET @.update_plan_id = @.PlanID
IF @.updateClause IS NULL
BEGIN
IF @.update_plan_id IS NULL
SET @.updateClause = 'plan_id = NULL'
ELSE
SET @.updateClause = 'plan_id = ' + @.update_plan_id
END
ELSE
BEGIN
IF @.update_plan_id IS NULL
SET @.updateClause = @.updateClause + ', plan_id = NULL'
ELSE
SET @.updateClause = @.updateClause + ', plan_id = ' + @.update_plan_id
END
END-- Complete SQL statement

IF @.updateClause <> null
BEGIN
SET @.sqlStatement = 'UPDATE [dbo].[Jobs] SET ' + @.updateClause + @.whereClause
EXEC sp_executesql @.sqlStatement,@.paramDefinition, @.update_job_id=@.JobID
END
END
GO

I know that this is a huge post, but I have pretty much exhausted my resources.

Thanks,

I didn't analyze the logic of the SP. But you are making the SP more complicated than necessary. Here are the reasons:

1. You are using dynamic SQL which can hurt performance

2. Your dynamic SQL code doesn't protect against SQL injection. So you can compromise your database/server if you haave more SPs like this that take string parameters

3. Since the UPDATE statement is executed dynamically you need to grant UPDATE permissions to all callers of the SP. This increases the attack surface of the database and grants more permissions to users than necessary. And it kind of defeats the purpose of having a SP. You might as well form the UPDATE statement on the client-side and execute it directly. You will get the same or probably better performance

4. Debugging code using dynamic SQL can be hard and difficult to maintain also as you have encountered

5. Lastly, you don't really gain that much by updating only the necessary columns. SQL Server has to do lot of work to locate the row to update and after you have located the row for update it doesn't matter in most cases if you update one or all of the columns. The only case where it matters is if you have lot of variable length columns that can overflow or increase the row size which will require more work. Otherwise you might as well update the entire row. You don't save anything in terms of performance or efficiency.

If you are not passing the old values (i.e., columns that were not changed) then you can use alternate approach below which doesn't require dynamic SQL:

UPDATE dbo.Jobs

SET address_id = CASE WHEN AddressChg > 0 THEN @.AddressID ELSE address_id END

, ...

WHERE job_id = @.update_job_id

The main point to note is that keeping transactions/calls light-weight gives the best performance and improves overall utilization of the server.

|||

Thank you very much for your reply. I agree that the dynamic SQL use opened us up to some danger, although this is a windows form application. I would love to get rid of it, however, I don't understand how the statement that you have shown works. The case I understand of course, it's the 'ELSE address_id END' that I don't understand. I don't have this value (address_id) from my application, only the 'THEN AddressID'. Is this address_id coming from the table update mechanism? If so, this is definitely the answer to most of my problems.

|||

The address_id in the ELSE clause comes from the row you are updating. It refers to the column in the table. So it is a way to use the current value of address_id if there is no change. SQL is a set-based language so statements like UPDATE has logically a before and after image for each row & the operations happen in one shot. This allows you to swap values in two columns using an UPDATE statement without any intermediate storage:

update t

set a = b, b = a

The database engine handles all the dirty work for you.

|||It works beautifully. Thank you so much for this elegant, simple solution.