replacing Apostrophe

I’m trying to key the syntax for replacing the Apostrophe with double
Apostrophe and update table.
Example: Programmer’s with Programmer’’s
Here code for finding occurrences:
SELECT DEALNAME
FROM DLWKTABLE20060609
where dealname like '%['']%'
BUT how do I update:
Update dlwktable20060609
Set dealname = replace(dealname, ?,?)
where dealname like '%['']%'declare @.c varchar(50)
select @.c ='O''Brian'
select replace(@.c, '''',''') ,@.c
Then the update will be
Update dlwktable20060609
Set dealname = replace(dealname, '''',''')
where dealname like '%['']%'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Logger wrote:
> I'm trying to key the syntax for replacing the Apostrophe with double
> Apostrophe and update table.
> Example: Programmer's with Programmer''s
> Here code for finding occurrences:
> SELECT DEALNAME
> FROM DLWKTABLE20060609
> where dealname like '%['']%'
> BUT how do I update:
> Update dlwktable20060609
> Set dealname = replace(dealname, ?,?)
> where dealname like '%['']%'

Replacing a view in merge

We have SQL 2005 and I need to update a view used in a publication. The
views are in a separate pub so it should be pretty easy. My thought is that
I run sp_dropmergearticle, update the view and then run sp_addmergearticle.
Am I correct? Also, will the subscribers get the new publication/snapshot
when synching? Thanks.
David
I'd use sp_addscriptexec. Simply because it avoids the need for a new
snapshot of all the articles.
Rgds,
Paul Ibison
|||But the users have limited rights. Isn't this a problem in this solution?
Or is that requirement only to run the sp on the publication? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3C9223C0-069E-48A5-9CD6-C0A3B7743276@.microsoft.com...
> I'd use sp_addscriptexec. Simply because it avoids the need for a new
> snapshot of all the articles.
> Rgds,
> Paul Ibison
>
|||Yes - just run it on the publisher and it'll go down to the subscribers on
synchronization.
HTH,
Paul Ibison
|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7BBAE4B2-76CF-48E5-AE87-36A77D6F4DCE@.microsoft.com...
> Yes - just run it on the publisher and it'll go down to the subscribers on
> synchronization.
> HTH,
> Paul Ibison
>

Replacing a portion of text string in column

I need to replace a portion of a url in a column as a result of
changing servers. Is there a SELECT/REPLACE/UPDATE combination query
that can do this. The table has close to a thousand entries and would
be nice if a query can be set to do this. Tried the REPLACE example
in the BOOKS ONLINE but it creates syntax error, apparently because it
does not like the characters in the url and/or wildcards. I don't need
to replace the entire url, only the portion before ".com". Thanks in
anticipation of your help.

Pradip SagdeoPradip,
This statement:
select replace('http://www.technicalvideos.net','s.net','s14.net')
seems to work OK, so, perhaps you could give your exact update and the exact
error along with some sample data.
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Pradip Sagdeo" <pradip.m.sagdeo@.pfizer.com> wrote in message
news:8dbc5a0f.0401280726.708a1c6@.posting.google.co m...
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.
> Pradip Sagdeo|||Thanks. I will try again. Must have made a typing mistake.

Pradip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pradip Sagdeo (pradip.m.sagdeo@.pfizer.com) writes:
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.

Unfortunately, the replace() function does not support wildcards, so
if you need to use that, you have to be creative. Or descend to use
some client code to handle it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Replace syntax error

Hello

In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:

UPDATE CAT_Products

SET Description = replace (Description, '</h3>', '</h2>')
WHERE Description <> '</h2>'

UPDATE CAT_Products
SET Description = replace (Description, '<h3>', '<h2>')
WHERE Description <> '<h2>'

However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

What do I need to adjust?

Thanks

can you explain what you mean by not working? also pls provide some sample data you have that you are expecting the REPLACE to happen on.

|||

Hello ndinaker

When I put in this code:

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

I dose not execute and I get this error message instead:

[Error] Script lines: 1-5 --------
Argument data type ntext is invalid for argument 1 of replace function.

[Executed: 12/04/07 19:17:21 BST ] [Execution: 0/ms]

As in the text Description column, I am trying to update the <h3> and </h3> to <h2> and </h2> for example: <h3> Some Text </h3> to be replaced by <h2> Some Text </h2>

However, as it is written in html format in the DescriptionHTML database column it looks like:

<h3> Some Text </h 3> to be replaced by <h 2> Some Text </h2>

Thanks

|||

I think the pattern you are matching is only for <h3> and not </h3>. So you probably need to a double replace.

declare

@.str varchar(100)

set

@.str='<h3> Some Text </h3>'

--final value = <h 2> Some Text </h2>

select

@.str,replace(@.str,'<h3>','<h2>'),replace(replace(@.str,'<h3>','<h2>'),'</h3>','</h2>')

|||

Hi ndinakar

Thanks for the code, but I needed to change:

@.str varchar(100)

To

@.str ntext

H

owever this change cause the code to fail:

UPDATE CAT_Products

@.str ntext
set

@.str = '<h3> * </h3>'
--final value = <h 2> * </h2>

select

@.str, replace(@.str,'<h3>', '<h2>'), replace( replace(@.str,'<h3>', '<h2>'), '</h3>', '</h2>')

I got this error message:

[Error] Script lines: 1-13 --------
Line 4: Incorrect syntax near'@.str'.

More exceptions ... Must declare the variable'@.str'.

[Executed: 12/04/07 21:49:22 BST ] [Execution: 0/ms]

|||

Try if this works:

UPDATE

CAT_Products

set

DescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>')

WHERE

Description<>'<h2>'

|||

Before you try the update try a SELECT first to see what records are getting affected and how.

SELECT

NewDescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

As suggested, I tried this first:

SELECT

NewDescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>'),
DescriptionHTML

FROM

CAT_Products
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:40:42 BST ] [Execution: 0/ms]

Then I tried this:

UPDATE

CAT_Products
set

DescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>')
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:43:54 BST ] [Execution: 0/ms]

It looks like I will have to update this column manually, I had best roll up my sleeves.

Thanks

|||Ahhhh...its the NTEXT. Its going to be a pain to retrieve those values/update them through the query analyzer. Perhaps you can write a little tool..a VB tool with a form...that retrieves the values into a textbox and a button to update it..that will be easier..|||

Try this:

SELECT

NewDescriptionHTML

=replace(replace(convert(varchar(4000),DescriptionHTML),'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

Hi ndinakar

Thanks again for your help and support

Yes you are right the Ntext is a pain, cos when I tried your latest code suggestion I got this error:

[Error] Script lines: 1-15 --------
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 13/04/07 09:17:43 BST ] [Execution: 0/ms]

It certainly dose not like the NTEXT.

A cup of coffee and rolling up of the sleeves coming up.

Thanks

sql

REPLACE statement

I am running the following statement but its not updating anything. Whats
wrong.
update baandb.dbo.ttiitm012500
set t_item = REPLACE(t_item,'- get fro','')
where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
ok I found my stupid error. a space in the wrong place.
ghunter wrote:
>I am running the following statement but its not updating anything. Whats
>wrong.
>update baandb.dbo.ttiitm012500
>set t_item = REPLACE(t_item,'- get fro','')
>where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1

REPLACE statement

I am running the following statement but its not updating anything. Whats
wrong.
update baandb.dbo.ttiitm012500
set t_item = REPLACE(t_item,'- get fro','')
where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1ok I found my stupid error. a space in the wrong place.
ghunter wrote:
>I am running the following statement but its not updating anything. Whats
>wrong.
>update baandb.dbo.ttiitm012500
>set t_item = REPLACE(t_item,'- get fro','')
>where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1

REPLACE statement

I am running the following statement but its not updating anything. Whats
wrong.
update baandb.dbo.ttiitm012500
set t_item = REPLACE(t_item,'- get fro','')
where t_item like '%get fro%'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1ok I found my stupid error. a space in the wrong place.
ghunter wrote:
>I am running the following statement but its not updating anything. Whats
>wrong.
>update baandb.dbo.ttiitm012500
>set t_item = REPLACE(t_item,'- get fro','')
>where t_item like '%get fro%'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1

Replace SQL view on merge

I have a merge publication that I need to update a published view. I have
tried the following but does not work:
sp_droparticle
drop view
create view
I need to know if I can do this process without being in EM. Thanks.
David
IIRC you can use sp_addscriptexec to do this for subscribers deployed via
UNCs.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u7HXevuFGHA.3684@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to update a published view. I have
>tried the following but does not work:
> sp_droparticle
> drop view
> create view
> I need to know if I can do this process without being in EM. Thanks.
> David
>
|||I found documentation for sp_dropmergearticle and sp_addmergearticle. I
tried them as follows in EM and it worked. Can I do this in a single
script file also? Thanks.
exec sp_dropmergearticle ......
drop view ...
create view ...
exec sp_addmergearticle ......
I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
operations above. Then I issued an "exec sp_start_job ...." to run the
snapshot agent.
Does this seem like the correct way to do what I want to do?
David
*** Sent via Developersdex http://www.codecomments.com ***
|||After I tried the script sequence (and it worked on the Publisher) I tried
to synch with a subscriber and got the following error:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPub\20060111144516\vw_BillingDetail_176 7.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
(Source: LIFETIMEANTEC (Data source); Error number: 3724)
------
Any ideas why this is occurring? Thanks.
David
"David" <daman@.lifetime.com> wrote in message
news:OwkGNLvFGHA.3684@.TK2MSFTNGP14.phx.gbl...
> I found documentation for sp_dropmergearticle and sp_addmergearticle. I
> tried them as follows in EM and it worked. Can I do this in a single
> script file also? Thanks.
> exec sp_dropmergearticle ......
> drop view ...
> create view ...
> exec sp_addmergearticle ......
> I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
> operations above. Then I issued an "exec sp_start_job ...." to run the
> snapshot agent.
> Does this seem like the correct way to do what I want to do?
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||David,
I'd use a separate snapshot publication for these views, as you are
currently reinitializing the whole set of articles inc data when a view
changes which is a bit of an overkill. Also, when you change a view, it's
best to use alter view rather than drop and create - that way you'll be able
to keep the permissions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I wasn't aware you could have separate publications for the same database.
Would I then remove the views and stored procs from the current publication
and then create a 2nd one with just the views and stored procs? That sounds
really slick.
Since the subscribers are laptops, I assume I would need to create new
publication synchs on them also? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23wAvlvFGHA.1760@.TK2MSFTNGP10.phx.gbl...
> David,
> I'd use a separate snapshot publication for these views, as you are
> currently reinitializing the whole set of articles inc data when a view
> changes which is a bit of an overkill. Also, when you change a view, it's
> best to use alter view rather than drop and create - that way you'll be
> able to keep the permissions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
the setup you describe is exactly how I do it. You'll need separate
subscriptions it's true, but the versatility is worth it. Actually I have a
separate publication for each programming object type - sps, views and udfs.
Another advantage is that a problem in one publication doesn't affect the
others (use independant distribution agents).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||And by "independant distribution agents" do you mean creating separate
distributors? Currently the distributor is on the same server.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23f25l0vFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> David,
> the setup you describe is exactly how I do it. You'll need separate
> subscriptions it's true, but the versatility is worth it. Actually I have
> a separate publication for each programming object type - sps, views and
> udfs. Another advantage is that a problem in one publication doesn't
> affect the others (use independant distribution agents).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
not a different distributor - in fact this is not possible to another
publication from the same publisher. What I mean is the option on the
subscription options tab - to 'Use a distribution agent that is
independant....'. This'll isolate the jobs entirely.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, but I cannot find this in the Subscription Options tab. I went into
Publisher properties and found the tab but there is no checkbox with that
name on it. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e4dewY1FGHA.3984@.TK2MSFTNGP14.phx.gbl...
> David,
> not a different distributor - in fact this is not possible to another
> publication from the same publisher. What I mean is the option on the
> subscription options tab - to 'Use a distribution agent that is
> independant....'. This'll isolate the jobs entirely.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Replace or update data?

Hi,

I have products in a database each of which have varying amounts of data describing them.

Some products have variations e.g. color size, and some have price bands e.g. quantity 1-10 = $5 : 11+ = $6. This extra data is stored in seperate tables to the main products table. The rows in these other tables reference a product ID in the main table.

The question is, if an administrator were to update the product data but only change, say, the name or cost of a single product variation or the price of a single price band is it worth keeping track of exactly which item of data was changed and update that piece of data in the database or would it be better to just scrub all the data in the extra tables for the current product and re-insert all of it fresh??

Cheers,

I.From an efficiency point of view you should just update the value(s) that have changed. Inserts can be an expensive operation, particularly if there are triggers or indices to deal with.

If you're using DataSets along with a DataAdapter this will keep track of which row(s) changed, which were deleted/inserted and so call the appropriate SQL to refresh the database.|||Would you recomend cacheing the dataset in session state or viewstate?|||By caching do you mean saving the dataset for use during a postback? If so then it depends. Session state will lead to quicker response times as the cached data doesn't need to make a roundtrip to the client. But using session state will eat up your server's RAM and could make the site less responsive.

If you don't think the use of server side memory wil be an issue go with session state. Be sure to clear out the DataSet once you don't need it anymore. The .Net Framework will do this eventually but it would help to clear it out as soon as its not needed. (I assume you're not using a clustered web server which would make using session state a little trickier for complex objects like DataSets.)

If by caching you mean allowing the DataSet to be used by multiple users then you should place it into the Cache object. The Cache object is visible to all users and they share the same data.|||Yeah, for use during a postback.

Another concern I have is the cost of using a dataset update.

Upon update, a sql query is executed for each row in the dataset that's been modified/added/deleted. Would it not be better to wrap all the data in XML and send it direct to the database all in one go? Of course, doing that would mean manually checking to determine the operation required for each row.

Thanks,

WT.|||I think the DataSet update with a DataAdapter will be about as efficient as you can get, at least without doing some more coding. I haven't tried the XML route but it seems like you'd be adding the overhead of serializing the data to XML and then de-serializing it back to get it into the database.|||Thanks for your help.

I think a good reason for using viewstate for saving data inbetween postbacks is that viewstate doesn't time out.

Also, although I'll be trying this approach for product variations, I think that my original example of price bands might be better suited to deleting all the current data and replacing it.

This is because the price bands need to be consistent with one another and they also need to be complete. Imagine that whilst someone is editing a set of price bands, another user deletes all of them and gives the product a single price. If the first user then adds a new price band, upon update of the dataset, the new price band will be added and no other updates will take place. This would result in the situation of there being a single price band for the product and no price band indicating the price of 1 item. (this was deleted by the second user.)

Similarly, imagine that two different users add a new price band to a product's current set. The bands have the same lower bound but a different price. The first band is inserted but the second can't be because a unique contraint at the database forbids two bands having the same lower bound. So in this case, extra code would be needed tp recognise that an update needs to be used instead of an insert.

So in this case, I think that a 'last one in wins' approach is best to updating these price bands rather than trying to merge different sets together.

What do you think?

WT.

Replace

I know there is a function in MySQL where you can use "Replace" to update a row if one already exists and if the row does not exist it will insert a new row. Can this be done in MSSQL also? Thanksif exists (select * from tbl where ...)
update tbl set ...
else
insert tbl ...

update tbl set ...
select @.error = @.@.error, @.rowcount = @.@.rowcount
if @.@.error = 0 and @.rowcount = 0
insert tbl ...

I include the @.@.error in the second method to show that if you check one then you will lose the other so have to save them both in one statement.

repeat a statement in a loop

hello,
i need to repeat the update statement for all days in the actual month
example:if i run today (19.09.2005) the job - it must run 19 times for
the value of
GETDATE() to GetDate() -19. How can be done this in a loop.
In the update statement is GetDate() used in the where condition and it must
be replaced with the values
in the first loop with GetDate() -1
in the second loop with GetDate() - 2
...
in the last GetDate()-19
The update statement looks like :
UPDATE [table1]
SET [OrderDate]=(
SELECT DISTINCT Top 1 OrderCreationDate
FROM table2
WHERE (SalesDocNr = table1.OrderNr)
)
WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
FROM table1 INNER JOIN
table2 ON table1.OrderNr = table2.SalesDocNr
WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
CONVERT(VarChar(10), GETDATE() , 104))
)
thanks
XavierWhile can use a WHILE loop or a cursor to get this done as you want, a
better approach is to use a single UPDATE statement which can update all the
rows.
Based on the sample code you posted, it is hard to work out such a solution,
so please refer to www.aspfaq.com/5006 and post relevant information for
others to reproduce your problem.
Anith|||For a looped approach try this for your basic loop. @.date should be used in
place of GETDATE() in your update query. Replace the PRINT statement with
your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
ONLY. You may want a better method than what I did.
Mike
DECLARE @.day int
DECLARE @.date datetime
SET @.day = DAY(GETDATE())
SET @.date = GETDATE()
SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
WHILE @.day > 0
BEGIN
PRINT @.date
SET @.day = @.day - 1
SET @.date = DATEADD(d, -1, @.date)
END
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it
> must
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||Please post DDL (CREATE TABLE), sample data (INSERTs) and show your required
results.
I'm certain it's possible to do what you want in a single UPDATE statement
without a loop. However, the UPDATE you have posted may not give reliable
results because you've used TOP without ORDER BY. For that reason it's trick
y
to guess what you intended by it (even though it may not always work as you
wanted).
I expect the solution will look like
:
UPDATE Table1
SET orderdate =
(
..
)
WHERE insertdate >= DATEADD(DAY,-19,CURRENT_TIMESTAMP)
AND insertdate <= CURRENT_TIMESTAMP ;
David Portas
SQL Server MVP
--
"Xavier" wrote:

> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it mu
st
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||it works,
thanks Mike
"Mike Jansen" wrote:

> For a looped approach try this for your basic loop. @.date should be used
in
> place of GETDATE() in your update query. Replace the PRINT statement with
> your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
> ONLY. You may want a better method than what I did.
> Mike
>
> DECLARE @.day int
> DECLARE @.date datetime
>
> SET @.day = DAY(GETDATE())
> SET @.date = GETDATE()
> SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
> WHILE @.day > 0
> BEGIN
> PRINT @.date
> SET @.day = @.day - 1
> SET @.date = DATEADD(d, -1, @.date)
> END
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
>
>|||> While can use a WHILE loop or a cursor to get this done as you want, a
> better approach is to use a single UPDATE statement which can update all
> the rows.
I don't think that's really possible without SQL Server 2005 (or perhaps a
_really_ messed up query in SQL 2000), in which case it would be something
like this as a base:
WITH DAYS(DayValue, Remaining) AS
(
SELECT
CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime) AS DayValue,
DAY(GETDATE()) - 1
UNION ALL
SELECT
DATEADD(d, -1, r.DayValue) AS DayValue,
r.Remaining - 1
FROM
DAYS r
WHERE
r.Remaining > 0
)
SELECT * FROM DAYS;
Instead of SELECT * FROM DAYS you'd do an UPDATE and JOIN to DAYS.
Mike

reorg PK clustered index in VLDB

My data warehouse is now 4 years old and the size is about
600 GB od data.
We had only data insert no update in this duration. PKs
are all clustered indexes and in chronological order.
Some of tables are as large as 100 GB.
I wanted to check if the PKs are in good figure and ran
DBCC showcontig against many of large tables and 80 % had
bad rate for scan density , such as 50 %, 30 %.
I may need to reorganise PK.
I BOL it says comparing the values of Extent Switches and
Extents Scanned is a way to know how much fragmented. But
it says this method does not work if the index spans
multiple files. I presume all VLDB exploit multiple files
for one table in order to gain physical disk I/O.
My question: how can I check fragmentation rate of my
large tables which span multiple files (up to 4 to 6
files)?
What is the best way to reorganise clustered index which
are PK ? I have to drop all FK in order to reorganise PK,
don't I !
I hope to hear your idea!!!When you say you presume your db spans multiple files, does the database use
more than one file other than the MDF? DBCC DBreindex on the clustered key
should reindex your data tables and automatically reindex your other
nonclustered indexes.
Some links:
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://www.microsoft.com/technet/co...ql/sql1014.mspx
http://www.sqlservercentral.com/scr...butions/721.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
> My data warehouse is now 4 years old and the size is about
> 600 GB od data.
> We had only data insert no update in this duration. PKs
> are all clustered indexes and in chronological order.
> Some of tables are as large as 100 GB.
> I wanted to check if the PKs are in good figure and ran
> DBCC showcontig against many of large tables and 80 % had
> bad rate for scan density , such as 50 %, 30 %.
> I may need to reorganise PK.
> I BOL it says comparing the values of Extent Switches and
> Extents Scanned is a way to know how much fragmented. But
> it says this method does not work if the index spans
> multiple files. I presume all VLDB exploit multiple files
> for one table in order to gain physical disk I/O.
> My question: how can I check fragmentation rate of my
> large tables which span multiple files (up to 4 to 6
> files)?
> What is the best way to reorganise clustered index which
> are PK ? I have to drop all FK in order to reorganise PK,
> don't I !
> I hope to hear your idea!!!|||MDF file is used only for system table in all of my
databases. (especially when dealing with VLDB).
The database is over 600GB, and each table could be nearly
100GB,
Would DBreindex a good solution ?
This will copy the whole table into different location
without asking !

>--Original Message--
>When you say you presume your db spans multiple files,
does the database use
>more than one file other than the MDF? DBCC DBreindex on
the clustered key
>should reindex your data tables and automatically reindex
your other
>nonclustered indexes.
>Some links:
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql0326.mspx
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql1014.mspx
>http://www.sqlservercentral.com/scr...tributions/721.
asp
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
about
had
and
But
files
PK,
>
>.
>|||Did those links help?
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
> MDF file is used only for system table in all of my
> databases. (especially when dealing with VLDB).
> The database is over 600GB, and each table could be nearly
> 100GB,
> Would DBreindex a good solution ?
> This will copy the whole table into different location
> without asking !
>
> does the database use
> the clustered key
> your other
> /sql0326.mspx
> /sql1014.mspx
> asp
> message
> about
> had
> and
> But
> files
> PK,|||Links were very good! Thank you very much!
Especially Index Defrag Best Practices.
So, according to the article I should use fragmentation
level by logical scan fragmentation.
Still I am not very sure about using DBCC INDEXDEFRAG.
Because when a table is 100GB, and do this operation, how
large the log should be allocated ? 200 GB, 300 GB ?
Usually for copying data it takes about 2.5 times of data
size consumed in log before the data is inserted into.
Would DBCC INDEXDEFRAG be a best way in VLDB environment ?

>--Original Message--
>Did those links help?
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
nearly
on
reindex
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.sqlservercentral.com/scr...tributions/721.
PKs
ran
which
>
>.
>|||Depends on the needed uptime of your DB, you can write scripts to defrag in
chunks. Here is an example of using dbreindex (you can alter to use index
defrag) and backing up the log when needed, think I got this from MVP Andrew
Kelly but not 100% sure:
-- Reindexing the tables --
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:159401c40c21$2fc43b60$3a01280a@.phx.gbl...
> Links were very good! Thank you very much!
> Especially Index Defrag Best Practices.
> So, according to the article I should use fragmentation
> level by logical scan fragmentation.
> Still I am not very sure about using DBCC INDEXDEFRAG.
> Because when a table is 100GB, and do this operation, how
> large the log should be allocated ? 200 GB, 300 GB ?
> Usually for copying data it takes about 2.5 times of data
> size consumed in log before the data is inserted into.
> Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>
> message
> nearly
> on
> reindex
> PKs
> ran
> which

Rendering reports to Word format

Currently my company inserts graphs from Excel into a Word document. They
then update the Word document with explanations about their analysis.
The requirement is for the graphs in this Word document to come from
Reporting Services 2000. Analysts then need to be able to update the text in
the document.
The SoftArtisans product appears to provide the required functionality,
however, my company has very strict guidelines about introducing additional
software into the organisation.
Do you have any ideas about how to render the Word document, including
graphs and sub-reports using Reporting Services 2000?
My thanks in advanceI understand your dilemma, but I strongly suggest you attempt to leverage
commercial software that already does the job before trying to roll your
own.
If you have no other choice, get your hands on Office 2003 Interop
Assemblies. You can then render the report(s) through web service calls
(take a look at the Render() method). There are other methods that allow
you to get a list of the image streams for the report. You'll need to get
to the image streams and pull the chart image from the web service. Then
you can write the image(s) to the word document. This is one way to do it.
-Tim
"SuzyM" <SuzyM@.discussions.microsoft.com> wrote in message
news:5FA762D2-BAE6-4471-A28A-F01B3D0FA44E@.microsoft.com...
> Currently my company inserts graphs from Excel into a Word document. They
> then update the Word document with explanations about their analysis.
> The requirement is for the graphs in this Word document to come from
> Reporting Services 2000. Analysts then need to be able to update the text
> in
> the document.
> The SoftArtisans product appears to provide the required functionality,
> however, my company has very strict guidelines about introducing
> additional
> software into the organisation.
> Do you have any ideas about how to render the Word document, including
> graphs and sub-reports using Reporting Services 2000?
> My thanks in advance