replacing nodes and sub-trees

Hi,

I have an table where I use XML data type to store documents. The following is an example of original document:

<!ORIGINAL DOCUMENT>

<ABC>

<xyz>.....</xyz>

<xyz>.....</xyz>

<gef>.....</gef>

<qew>....</qew>

</ABC>

<!NEW DOCUMENT>

<ABC>

<xyz>.....</xyz>

<gef>.....</gef>

<reb>....</reb>

</ABC>

My question is how to update the original document so that the two previous instances of xyz tag are replaced by the new single xyz tag, the original gef tag is replaced by the new gef tag, and the reb tag is added to the original document as it is not not present in the original document. The qew will not be updated. I can do this using DOM but I am looking to accomplish this using xquery and xml dml. Is this possible ? I appreciate help on this.

Thanks

It sounds like your algorithm is

For each element name in the NEW document, delete all the elements of that name in the OLD document and insert the new element into the OLD document.

You could do this w/ XQuery DML by generating a DML statement based on the NEW document.

Replacing Merge Objects

I have a merge publication that I need to change some views and get the new
views to the subscribers. At first, i tried the following steps and it
failed when trying to synch from subscriber:
exec sp_dropmergearticle ...
ALTER VIEW ....
exec sp_addmergearticle ...
re-created snapshot
Do I only need the ALTER VIEW and then re-create snapshot? I want to keep
the VIEW in the articles. Thanks.
David
Or, do I need to drop and re-create the subscriber snapshot. We are using
SQL 2000 and have laptops as anonymous subscribers. I don't want to lose
the data changes that have been made on the laptops. I prefer to have them
synch first, then recreate their snapshot. However, this is very difficult
as laptops synch at different times. Can anyone give me advice or point me
to a "how-to" article? I'm sure our situation is no different than a lot of
other Merge replications with disconnected subscribers. I am very new to
SQL Server replication so I want to do it right the first time. Thanks.
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:eCgHjbUGGHA.3984@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to change some views and get the new
>views to the subscribers. At first, i tried the following steps and it
>failed when trying to synch from subscriber:
> exec sp_dropmergearticle ...
> ALTER VIEW ....
> exec sp_addmergearticle ...
> re-created snapshot
> Do I only need the ALTER VIEW and then re-create snapshot? I want to keep
> the VIEW in the articles. Thanks.
> David
>
|||David,
have you given up trying to isolate the views into a different publication?
This will be much better than reinitializing to send down view changes,
which is what you'll be obliged to do this way. Add the views to a new
publication and have all your subscribers subscribe to this new publication.
Typically this'll be a snapshot one for the sake of clarity. Each time the
views change, you generate a new snapshot and synchronize. Disable the
snapshot job in this case because typically you won't synchronize often, and
it'll be manually controlled. Pls let me know if any of this is unclear.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No, I haven't given up on that. I plan to do that in near future. But
I still don't understand why I can't synch now when I change a view.
The laptop synch fails with:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPubAll\2006
0114140533\vw_BillingDetail_1737.sch' could not be propagated to the
subscriber.
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
What I did on the publisher was 1. alter view 2. sp_addmergearticle 3.
run new snapshot.
Then, when I tried to synch from a laptop I got the error above.
Wouldn't I get this even with a separate publication? Or would I have to
know that a new snapshot exists at the subscriber (laptop) before
synching.
As you can tell, the light still has not gone on in my brain about this.
I want it to be simple so user of laptop doesn't have to do special
things. What am I missing? Thanks. I really appreciate your help. I
did a lot of replication with Access in the past and this is very
different.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
it'll be fine if you don't drop the view from the publication. If it is left
there, you just alter the view on the publisher then reinitialize. This
process will call a system proc (sp_MSunmarkreplinfo) behind the scenes that
will remove the replication flag and then allow the article to be dropped
during the replication process. The way you're doing it means the
replication engine 'thinks' this is the first time it's seen the view so the
flag is not reset on the subscriber and you get the same error you'd get if
you tried manually to alter the view on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll give that a try. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||When you refer to "reinitialize" do you mean just try the synch again with
laptop subscriber? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23hAUlrhGGHA.2684@.TK2MSFTNGP11.phx.gbl...
> David,
> it'll be fine if you don't drop the view from the publication. If it is
> left there, you just alter the view on the publisher then reinitialize.
> This process will call a system proc (sp_MSunmarkreplinfo) behind the
> scenes that will remove the replication flag and then allow the article to
> be dropped during the replication process. The way you're doing it means
> the replication engine 'thinks' this is the first time it's seen the view
> so the flag is not reset on the subscriber and you get the same error
> you'd get if you tried manually to alter the view on the subscriber.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
to reinitialize, you right-click on the publication and select to
'reinitialize all subscriptions'. This means that you'll have to create a
new snapshot then synchronize.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Wait. What about the laptops that are out in the field that have not yet
updated the Publisher with their changes yet? If I create a new snapshot,
won't that kill their updates?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OoecZ5qGGHA.1628@.TK2MSFTNGP12.phx.gbl...
> David,
> to reinitialize, you right-click on the publication and select to
> 'reinitialize all subscriptions'. This means that you'll have to create a
> new snapshot then synchronize.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||AFAIR, they'll have the option of uploading their changes before receiving
the snapshot.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replacing columnn name programmatically

Hi,

The following script does not return any resultset against a test db
while I know for a fact tables with letter "aaa" has columns that
contains "ccc".
What's wrong? the the inner cursor?

Thanks.

-- get all tbls with letter aaa
declare @.tbl varchar(8000)
declare tblCursor cursor for
SELECT name
FROM sysobjects
WHERE xtype = 'U'
AND name LIKE '%aaa%'

open tblCursor
fetch next from tblCursor
into @.tbl

while (@.@.fetch_status = 0)
begin

-- get all columns with letter ccc and replace it with nothing /
remove it
declare @.tbuffer varchar(4000)
declare @.cbuffer varchar(8000)

declare abnormal_cols cursor for
SELECT o.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
and o.id = object_id('+@.tbl')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer

while (@.@.fetch_status = 0)
begin
-- EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
'COLUMN';
-- test
print @.tbuffer + ', ' + @.cbuffer;
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer
end

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @.tbl

end
close tblCursor
deallocate tblCursor;Hi

I can't see why there are two cursors here, try:

SELECT o.name, Replace(c.name,'ccc','') as NewName, c.name as OldName
FROM sysobjects o JOIN syscolumns c ON o.id = c.id
JOIN syscolumns a ON o.id = a.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
AND a.name LIKE '%aaa%'

John

"Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0310211451.2f59f9c4@.posting.google.c om...
> Hi,
> The following script does not return any resultset against a test db
> while I know for a fact tables with letter "aaa" has columns that
> contains "ccc".
> What's wrong? the the inner cursor?
> Thanks.
>
> -- get all tbls with letter aaa
> declare @.tbl varchar(8000)
> declare tblCursor cursor for
> SELECT name
> FROM sysobjects
> WHERE xtype = 'U'
> AND name LIKE '%aaa%'
> open tblCursor
> fetch next from tblCursor
> into @.tbl
> while (@.@.fetch_status = 0)
> begin
> -- get all columns with letter ccc and replace it with nothing /
> remove it
> declare @.tbuffer varchar(4000)
> declare @.cbuffer varchar(8000)
> declare abnormal_cols cursor for
> SELECT o.name, c.name
> FROM sysobjects o
> JOIN syscolumns c ON o.id = c.id
> WHERE o.xtype = 'U'
> AND c.name LIKE '%ccc%'
> and o.id = object_id('+@.tbl')
> -- ORDER BY c.name
> open abnormal_cols
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> while (@.@.fetch_status = 0)
> begin
> -- EXEC sp_rename
'+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
> 'COLUMN';
> -- test
> print @.tbuffer + ', ' + @.cbuffer;
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> end
> close abnormal_cols
> deallocate abnormal_cols;
> fetch next from tblCursor
> into @.tbl
> end
> close tblCursor
> deallocate tblCursor;

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

sql

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

Replacing / with space

Hi,
I have a column with the following data.
A/B
X Y
Z
X/Y A/D
I want to replace the /with space so that I have the output-
A B
X Y
Z
X Y A D
Can I do that? Please let me know what SQL query will help.
Thanksreplace(column_name, '/', ' ')|||Or SHIFT-F1 will do the trick...|||Thanks a lot. Works.
Originally posted by joejcheng
replace(column_name, '/', ' ')|||No takers for your advice .. blindman

btw .. i tried shift + f1 .. but it didnt replace the / with ' ' ;)sql

replace"***" in image path field.

I am hoping someone will be able to help me with this.
i have a table of accountholders with the following fields
account_no
account_name
image_path
the image_path field has a defaultvalue images/***.jpg
I am looking for a query code to update the value in the image_path field by replacing the *** value with the value of the

account_no fieldupdate accountholders
set image_path = replace(image_path, '***', account_no)

EDIT: safer would be:

update accountholders
set image_path = replace(image_path, '***', account_no)
where image_path='images/***.jpg'

REPLACE with NULL

Hello,
When I use the following:
REPLACE(CategoryType_ID, 'Non Selected', null)
all the values are replaced with NULL even when the value is not "'Non
Selected".
Any help with understanding why this happens would be appreciated.
Thanks, sck10The REPLACE function returns NULL if any one of the arguments is NULL. The
right function to do what you need is NULLIF:
NULLIF(CategoryType_ID, 'Non Selected')
You can also accomplish the same with CASE:
CASE WHEN CategoryType_ID = 'Non Selected'
THEN NULL
ELSE CategoryType_ID
END
I am assuming here the CategoryType_ID column is a character data type.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hi
"sck10" wrote:
> Hello,
> When I use the following:
> REPLACE(CategoryType_ID, 'Non Selected', null)
> all the values are replaced with NULL even when the value is not "'Non
> Selected".
> Any help with understanding why this happens would be appreciated.
> Thanks, sck10
If this is an update you can
UPDATE mYtable
SET CategoryType_ID = NULL
WHERE CategoryType_ID = 'Non Selected'
John|||Thanks Plamen Ratchev,
just what I was looking for...
sck10
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:uWahjljfHHA.4980@.TK2MSFTNGP02.phx.gbl...
> The REPLACE function returns NULL if any one of the arguments is NULL. The
> right function to do what you need is NULLIF:
> NULLIF(CategoryType_ID, 'Non Selected')
> You can also accomplish the same with CASE:
> CASE WHEN CategoryType_ID = 'Non Selected'
> THEN NULL
> ELSE CategoryType_ID
> END
> I am assuming here the CategoryType_ID column is a character data type.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||Thanks John,
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EAA2A779-0D99-4F93-A464-F855323CB61A@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> When I use the following:
>> REPLACE(CategoryType_ID, 'Non Selected', null)
>> all the values are replaced with NULL even when the value is not "'Non
>> Selected".
>> Any help with understanding why this happens would be appreciated.
>> Thanks, sck10
> If this is an update you can
> UPDATE mYtable
> SET CategoryType_ID = NULL
> WHERE CategoryType_ID = 'Non Selected'
> John
>sql

Replace substring in field

Hi
I am trying to figure a way of replacing occurances of the following text: "a good years work" with the (corrected): "a good year's work". The substring exists within a variety of sentences eg "Paul has produced a good years work", "Quite simple a good y
ears work". Basically the users have missed the apostrophe! The field is a column in a sql table - I guess I need some kind of update query method
Thanks
Eddie
Hi,
There are 2 approaches, 1 is just display with "a good year's work"
set quoted_identifier off
select replace(field_name,'a good years work',"a good year's work") from
table_name
2nd approach is to to update the values inside the table
set quoted_identifier off
update table_name
set field_name=replace(field_name,'a good years work',"a good year's work")
select field_name from table_name
Thanks
Hari
MCDBA
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:59B4865C-7C36-433C-865A-85A73CCC2C1A@.microsoft.com...
> Hi
> I am trying to figure a way of replacing occurances of the following text:
"a good years work" with the (corrected): "a good year's work". The
substring exists within a variety of sentences eg "Paul has produced a good
years work", "Quite simple a good years work". Basically the users have
missed the apostrophe! The field is a column in a sql table - I guess I need
some kind of update query method
> Thanks
> Eddie

Replace substring in field

Hi
I am trying to figure a way of replacing occurances of the following text: "
a good years work" with the (corrected): "a good year's work". The substrin
g exists within a variety of sentences eg "Paul has produced a good years wo
rk", "Quite simple a good y
ears work". Basically the users have missed the apostrophe! The field is a
column in a sql table - I guess I need some kind of update query method
Thanks
EddieHi,
There are 2 approaches, 1 is just display with "a good year's work"
set quoted_identifier off
select replace(field_name,'a good years work',"a good year's work") from
table_name
2nd approach is to to update the values inside the table
set quoted_identifier off
update table_name
set field_name=replace(field_name,'a good years work',"a good year's work")
select field_name from table_name
Thanks
Hari
MCDBA
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:59B4865C-7C36-433C-865A-85A73CCC2C1A@.microsoft.com...
> Hi
> I am trying to figure a way of replacing occurances of the following text:
"a good years work" with the (corrected): "a good year's work". The
substring exists within a variety of sentences eg "Paul has produced a good
years work", "Quite simple a good years work". Basically the users have
missed the apostrophe! The field is a column in a sql table - I guess I need
some kind of update query method
> Thanks
> Eddie

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 parameter doesnt work

I have the following in my commandtext but it doesnt seem to replace the LanguageColumnName variable:

Dim cmd As New SqlCommand("SELECT'+@.LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode", MyConnection)
cmd.Parameters.Add(New SqlParameter("@.UserCode", UserCode))
cmd.Parameters.Add(New SqlParameter("@.LanguageColumnName", LanguageColumnName))

I have tried'+@.LanguageColumnName+' and also just @.LanguageColumnName but this variable isnt replaced for some reason.

The value of LanguageColumnName is "de"...the funny thing is that when I just type my command like the following it DOES work..:

SELECTde FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode

What am I doing wrong?

Take a look at your SqlCommand, it looks like:

"SELECT'de' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode"

So you will get a set of 'de' strings. You're on the right direction, but not yet the proper way of using dynamic SQL. You can use EXECUTE command to execute dynamic built SQL statement, for example:

Dim cmd As New SqlCommand("EXEC('SELECT'+@.LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode')", MyConnection)
cmd.Parameters.Add(New SqlParameter("@.UserCode", UserCode))
cmd.Parameters.Add(New SqlParameter("@.LanguageColumnName", LanguageColumnName))

replace maintanance plan optimization

hello i have the following question: i want to replace maintanance plan
with my own scripts and when i examine in profiler sql running during
optimization i see dbcc dbreindex command with sorted_data_reorg clause,
which according to BOL is 6.x feature and is no longer available in SQL
Server 2000. what kind of dbreindex syntax should i use?
example
dbcc dbreindex(N'[dbo].[table]', N'', 85, sorted_data_reorg)
thanks a lot for reply. mojza
--
Posted via http://dbforums.comJust ignore that clause. Use DBCC like BOL has it referenced.
--
Andrew J. Kelly
SQL Server MVP
"mojza" <member12685@.dbforums.com> wrote in message
news:3061295.1057051503@.dbforums.com...
> hello i have the following question: i want to replace maintanance plan
> with my own scripts and when i examine in profiler sql running during
> optimization i see dbcc dbreindex command with sorted_data_reorg clause,
> which according to BOL is 6.x feature and is no longer available in SQL
> Server 2000. what kind of dbreindex syntax should i use?
> example
> dbcc dbreindex(N'[dbo].[table]', N'', 85, sorted_data_reorg)
>
> thanks a lot for reply. mojza
> --
> Posted via http://dbforums.com

REPLACE ISSUE..

Hi All,

I have the following STRING - 'D or Shorty'
Struggling with the syntax required to replace the 'or' with ; without
also replacing the 'or' in 'Shorty' with ;

e.g.

ORIG STRIMG:
D or Shorty

REQUIRED STRING:
D ; Shorty

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I have the following STRING - 'D or Shorty'
> Struggling with the syntax required to replace the 'or' with ; without
> also replacing the 'or' in 'Shorty' with ;
> e.g.
> ORIG STRIMG:
> D or Shorty
> REQUIRED STRING:
> D ; Shorty

SELECT replace('D or Shorty', ' or ', ' ; ')

I would guess that your real problem is somewhat more complex, but knowing
it, it's difficult to suggest a solution.

One should be aware of that SQL Server capabilities for string
manipulation is not extremely powerful, so depending on your task,
it could be an idea to bring it client-side and use something like
Perl to do whatever you want to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Mar 2005 13:30:37 -0800, hharry wrote:

> Hi All,
> I have the following STRING - 'D or Shorty'
> Struggling with the syntax required to replace the 'or' with ; without
> also replacing the 'or' in 'Shorty' with ;
> e.g.
> ORIG STRIMG:
> D or Shorty
> REQUIRED STRING:
> D ; Shorty
> Thanks in advance

DECLARE @.ss varchar(30)
SET @.ss = 'D or Shorty'
SELECT @.ss, REPLACE(@.ss, ' or ', ' ; ')

---------- --------------
D or Shorty D ; Shorty

(1 row(s) affected)|||Thanks all,

Yes, the issue is slightly more complex and I have went with this
option:

SELECT 'D or Shorty', SUBSTRING('D or Shorty', 1, CHARINDEX(' or ', 'D
or Shorty') - 1) + ' ; ' +
SUBSTRING('D or Shorty', (CHARINDEX(' or ', 'D or Shorty') + 4), LEN('D
or Shorty'))

Ross Presser wrote:
> On 15 Mar 2005 13:30:37 -0800, hharry wrote:
> > Hi All,
> > I have the following STRING - 'D or Shorty'
> > Struggling with the syntax required to replace the 'or' with ;
without
> > also replacing the 'or' in 'Shorty' with ;
> > e.g.
> > ORIG STRIMG:
> > D or Shorty
> > REQUIRED STRING:
> > D ; Shorty
> > Thanks in advance
> DECLARE @.ss varchar(30)
> SET @.ss = 'D or Shorty'
> SELECT @.ss, REPLACE(@.ss, ' or ', ' ; ')

> ----------
--------------
> D or Shorty D ; Shorty
> (1 row(s) affected)|||On 15 Mar 2005 14:47:56 -0800, hharry wrote:

> Thanks all,
> Yes, the issue is slightly more complex and I have went with this
> option:
> SELECT 'D or Shorty', SUBSTRING('D or Shorty', 1, CHARINDEX(' or ', 'D
> or Shorty') - 1) + ' ; ' +
> SUBSTRING('D or Shorty', (CHARINDEX(' or ', 'D or Shorty') + 4), LEN('D
> or Shorty'))

If your issue will come up in a similar way in the future, it might be
worth it to encapsulate that "replace first occurence only" into a UDF.

CREATE FUNCTION ReplaceFirstOccurence
( @.src varchar(8000), @.sub varchar(8000), @.repl varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.n int
SET @.n = CHARINDEX(@.sub, @.src)
RETURN SUBSTRING(@.src, 1, @.n-1)
+ @.repl
+ SUBSTRING(@.src, @.n + LEN(@.sub) + 1,8000)
END

A much crazier idea that I just discovered, while googling on this topic,
is to pull in the VBScript regular expression library!

http://www.sqlteam.com/item.asp?ItemID=13947

They only demonstrate using it for testing a string against a regexp, but
it could easily be extended to use replace.

Replace - simple MDX question

I am using the following MDX statement to retrun data to populate a drop dow
n
list allowing the user to select a time period:
WITH
MEMBER [Measures].[DisplayName] AS
'SPACE([Period].CurrentMember.Level.Ordinal * 4) +
[Period].CurrentMember.Name'
MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Unique
Name'
SELECT
{[Measures].[UniqueName], [Measures].[DisplayName]} ON
Columns,
[Period].Members ON Rows
FROM MyCube
The idea of the "SPACE([Period].CurrentMember.Level.Ordinal * 4)" bit in
the
first MEMBER declaration is to provide indentation to give a sense of the
hierarchy in the drop down list display.
I now need to serve a client application that doesn't allow leading spaces
in the drop down list display. Is there a function I can use instead of SPAC
E
to put an alterntive indentation character at the start?
For example, a hyphen. So the returned data would look something like:
ALL
--2004
--Qtr1
--Jan
--Feb
--MarWITH
MEMBER [Measures].[DisplayName] AS
'String([Period].CurrentMember.Level.Ordinal * 4, "-") +
[Period].CurrentMember.Name'
MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Unique
Name'
SELECT
{[Measures].[UniqueName], [Measures].[DisplayName]} ON
Columns,
[Period].Members ON Rows
FROM MyCube
"Dave Morrow" wrote:

> I am using the following MDX statement to retrun data to populate a drop d
own
> list allowing the user to select a time period:
> WITH
> MEMBER [Measures].[DisplayName] AS
> 'SPACE([Period].CurrentMember.Level.Ordinal * 4) +
> [Period].CurrentMember.Name'
> MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Uniq
ueName'
> SELECT
> {[Measures].[UniqueName], [Measures].[DisplayName]} O
N Columns,
> [Period].Members ON Rows
> FROM MyCube
> The idea of the "SPACE([Period].CurrentMember.Level.Ordinal * 4)" bit
in the
> first MEMBER declaration is to provide indentation to give a sense of the
> hierarchy in the drop down list display.
> I now need to serve a client application that doesn't allow leading spaces
> in the drop down list display. Is there a function I can use instead of SP
ACE
> to put an alterntive indentation character at the start?
> For example, a hyphen. So the returned data would look something like:
> ALL
> --2004
> --Qtr1
> --Jan
> --Feb
> --Mar
>

Repeating report

I want to do the following with RS:
I have a invoice that I want to print three times and at the end of the
report I want to print a letter refering the invoices. I could do that making
2 reports, calling the first with three copies for the invoices and the
second report would be the letter.
But that doesn't seems to be a pretty way to do things.
Anybody can help me'
Thanx.You could do this with a report and subreport. Make a report for your
invoice and an report for your letter. Then add to your letter report: two
subreports that both point to the invoice report. It should optimize pretty
well.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TheRSBeginner" <TheRSBeginner@.discussions.microsoft.com> wrote in message
news:9CC3661E-F5D1-4D9E-8CF9-7F918403EB1A@.microsoft.com...
>I want to do the following with RS:
> I have a invoice that I want to print three times and at the end of the
> report I want to print a letter refering the invoices. I could do that
> making
> 2 reports, calling the first with three copies for the invoices and the
> second report would be the letter.
> But that doesn't seems to be a pretty way to do things.
> Anybody can help me'
> Thanx.
>

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
Turn off autoclose for the database. What probably happened is that you have autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren't open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine and
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>
|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,[vbcol=seagreen]
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
state.[vbcol=seagreen]
and
>
|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off the autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> have autoclose, some other
> have it opened, SQL server
> weren't open by other process,
> state.
> and
>