RMO Replication problems with Pull Subscriptions

Hello,

I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is:
Syncronize -> success -> do nothing
-> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)

When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.

The result is:

> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.

or:

> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted.
Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.

The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.Snapshot need not be run for every subscription. Eliminate that step from your list. Run it once, you should be good.|||Thank you for reply.

To be honest I thought something similar. In my first attempts I generated snapshot through SQL Server Manager once. But every time I ran synchronization after I received error that I must rerun Snapshot Generation. I can post the exact error tomorrow. The facts I can post now for this case are that publication properties: SnapshotAgentExists returns true, but SnapshotAvailable returns false.

Here is the exact error:

You must rerun snapshot because current snapshot files are obsolete.
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Initialize()
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize()|||Thank you a lot once again.
I did your way and now It seems it's working. I'm waiting for results of the extended testing tomorrow. I found the reason why I needed to rerun snapshot every time (Leaked code from first implementations where I needed to setup publisher and publication. The code modifies publication). So now snapshot generation is removed from code and my task looks complete.

Risks of single MSSQL domain account for mult servers?

Greetings:
I am trying to conceive what risks might be created by running
multiple SQL servers within a domain under a single domain account, as
opposed to 1) running under the local service account or 2) multiple
domain service accounts.
In this case, all the SQL servers are SQL2000 running on Win2003. The
service account is assigned only to the "Domain Users" group.
We do use linked server calls, and I have played and suceeded getting
Kereberos up to avoid double hop issues when using Windows Auth. In
fact, this is one of the reasons that sparked the question in my mind
-- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
a unique service account.
As an aside, most of the servers are "line of business" servers, but
HR runs under a unique server with more sensitive information. I don't
really think that merits a seperate service account, but again, I
could well be missing something.
I mostly looking for food for thought, but concrete examples of
gotchas would be appreciated.
Thanks all.
d.
D (or should I call you d?),
One drawback of using a single service account is that a breach of security
on that account means a breach on all of your SQL Servers.
(Yes, it is easier to only have one account to manage. Also, once upon a
time (a long time ago) it made replication easier.)
Russell Fields
"D Barry" <google@.dcbarry.com> wrote in message
news:6d9b9a07.0405201046.548244c2@.posting.google.c om...
> Greetings:
> I am trying to conceive what risks might be created by running
> multiple SQL servers within a domain under a single domain account, as
> opposed to 1) running under the local service account or 2) multiple
> domain service accounts.
> In this case, all the SQL servers are SQL2000 running on Win2003. The
> service account is assigned only to the "Domain Users" group.
> We do use linked server calls, and I have played and suceeded getting
> Kereberos up to avoid double hop issues when using Windows Auth. In
> fact, this is one of the reasons that sparked the question in my mind
> -- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
> a unique service account.
>
> As an aside, most of the servers are "line of business" servers, but
> HR runs under a unique server with more sensitive information. I don't
> really think that merits a seperate service account, but again, I
> could well be missing something.
>
> I mostly looking for food for thought, but concrete examples of
> gotchas would be appreciated.
> Thanks all.
> d.
|||It can be problematic, especially if you have to restart all of the SQL Servers to change the password for the SQL Server account.
|||Russell:
It's "d.". "D." is just too pompous... ;-)
I should have stated the breach against one is a breach of all
arugument. (We do use nice long complex passwords.) I'm looking for
other
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:<ubmx4TqPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> D (or should I call you d?),
> One drawback of using a single service account is that a breach of security
> on that account means a breach on all of your SQL Servers.
> (Yes, it is easier to only have one account to manage. Also, once upon a
> time (a long time ago) it made replication easier.)
> Russell Fields
> "D Barry" <google@.dcbarry.com> wrote in message
> news:6d9b9a07.0405201046.548244c2@.posting.google.c om...
<snip>
[vbcol=seagreen]

Risks of single MSSQL domain account for mult servers?

Greetings:
I am trying to conceive what risks might be created by running
multiple SQL servers within a domain under a single domain account, as
opposed to 1) running under the local service account or 2) multiple
domain service accounts.
In this case, all the SQL servers are SQL2000 running on Win2003. The
service account is assigned only to the "Domain Users" group.
We do use linked server calls, and I have played and suceeded getting
Kereberos up to avoid double hop issues when using Windows Auth. In
fact, this is one of the reasons that sparked the question in my mind
-- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
a unique service account.
As an aside, most of the servers are "line of business" servers, but
HR runs under a unique server with more sensitive information. I don't
really think that merits a seperate service account, but again, I
could well be missing something.
I mostly looking for food for thought, but concrete examples of
gotchas would be appreciated.
Thanks all.
d.D (or should I call you d?),
One drawback of using a single service account is that a breach of security
on that account means a breach on all of your SQL Servers.
(Yes, it is easier to only have one account to manage. Also, once upon a
time (a long time ago) it made replication easier.)
Russell Fields
"D Barry" <google@.dcbarry.com> wrote in message
news:6d9b9a07.0405201046.548244c2@.posting.google.com...
> Greetings:
> I am trying to conceive what risks might be created by running
> multiple SQL servers within a domain under a single domain account, as
> opposed to 1) running under the local service account or 2) multiple
> domain service accounts.
> In this case, all the SQL servers are SQL2000 running on Win2003. The
> service account is assigned only to the "Domain Users" group.
> We do use linked server calls, and I have played and suceeded getting
> Kereberos up to avoid double hop issues when using Windows Auth. In
> fact, this is one of the reasons that sparked the question in my mind
> -- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
> a unique service account.
>
> As an aside, most of the servers are "line of business" servers, but
> HR runs under a unique server with more sensitive information. I don't
> really think that merits a seperate service account, but again, I
> could well be missing something.
>
> I mostly looking for food for thought, but concrete examples of
> gotchas would be appreciated.
> Thanks all.
> d.|||It can be problematic, especially if you have to restart all of the SQL Ser
vers to change the password for the SQL Server account.|||Russell:
It's "d.". "D." is just too pompous... ;-)
I should have stated the breach against one is a breach of all
arugument. (We do use nice long complex passwords.) I'm looking for
other
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:<ubmx4TqPEHA.2976@.TK
2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> D (or should I call you d?),
> One drawback of using a single service account is that a breach of securit
y
> on that account means a breach on all of your SQL Servers.
> (Yes, it is easier to only have one account to manage. Also, once upon a
> time (a long time ago) it made replication easier.)
> Russell Fields
> "D Barry" <google@.dcbarry.com> wrote in message
> news:6d9b9a07.0405201046.548244c2@.posting.google.com...
<snip>
[vbcol=seagreen]

Risks of single MSSQL domain account for mult servers?

Greetings:
I am trying to conceive what risks might be created by running
multiple SQL servers within a domain under a single domain account, as
opposed to 1) running under the local service account or 2) multiple
domain service accounts.
In this case, all the SQL servers are SQL2000 running on Win2003. The
service account is assigned only to the "Domain Users" group.
We do use linked server calls, and I have played and suceeded getting
Kereberos up to avoid double hop issues when using Windows Auth. In
fact, this is one of the reasons that sparked the question in my mind
-- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
a unique service account.
As an aside, most of the servers are "line of business" servers, but
HR runs under a unique server with more sensitive information. I don't
really think that merits a seperate service account, but again, I
could well be missing something.
I mostly looking for food for thought, but concrete examples of
gotchas would be appreciated.
Thanks all.
d.D (or should I call you d?),
One drawback of using a single service account is that a breach of security
on that account means a breach on all of your SQL Servers.
(Yes, it is easier to only have one account to manage. Also, once upon a
time (a long time ago) it made replication easier.)
Russell Fields
"D Barry" <google@.dcbarry.com> wrote in message
news:6d9b9a07.0405201046.548244c2@.posting.google.com...
> Greetings:
> I am trying to conceive what risks might be created by running
> multiple SQL servers within a domain under a single domain account, as
> opposed to 1) running under the local service account or 2) multiple
> domain service accounts.
> In this case, all the SQL servers are SQL2000 running on Win2003. The
> service account is assigned only to the "Domain Users" group.
> We do use linked server calls, and I have played and suceeded getting
> Kereberos up to avoid double hop issues when using Windows Auth. In
> fact, this is one of the reasons that sparked the question in my mind
> -- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
> a unique service account.
>
> As an aside, most of the servers are "line of business" servers, but
> HR runs under a unique server with more sensitive information. I don't
> really think that merits a seperate service account, but again, I
> could well be missing something.
>
> I mostly looking for food for thought, but concrete examples of
> gotchas would be appreciated.
> Thanks all.
> d.|||Russell:
It's "d.". "D." is just too pompous... ;-)
I should have stated the breach against one is a breach of all
arugument. (We do use nice long complex passwords.) I'm looking for
other
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:<ubmx4TqPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> D (or should I call you d?),
> One drawback of using a single service account is that a breach of security
> on that account means a breach on all of your SQL Servers.
> (Yes, it is easier to only have one account to manage. Also, once upon a
> time (a long time ago) it made replication easier.)
> Russell Fields
> "D Barry" <google@.dcbarry.com> wrote in message
> news:6d9b9a07.0405201046.548244c2@.posting.google.com...
> > Greetings:
> >
> > I am trying to conceive what risks might be created by running
> > multiple SQL servers within a domain under a single domain account, as
> > opposed to 1) running under the local service account or 2) multiple
> > domain service accounts.
> >
<snip>
> > Thanks all.
> >
> > d.sql

Risks of single MSSQL domain account for mult servers?

Greetings:

I am trying to conceive what risks might be created by running
multiple SQL servers within a domain under a single domain account, as
opposed to 1) running under the local service account or 2) multiple
domain service accounts.

In this case, all the SQL servers are SQL2000 running on Win2003. The
service account is assigned only to the "Domain Users" group.

We do use linked server calls, and I have played and suceeded getting
Kereberos up to avoid double hop issues when using Windows Auth. In
fact, this is one of the reasons that sparked the question in my mind
-- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
a unique service account.

As an aside, most of the servers are "line of business" servers, but
HR runs under a unique server with more sensitive information. I don't
really think that merits a seperate service account, but again, I
could well be missing something.

I mostly looking for food for thought, but concrete examples of
gotchas would be appreciated.

Thanks all.

d.D (or should I call you d?),

One drawback of using a single service account is that a breach of security
on that account means a breach on all of your SQL Servers.

(Yes, it is easier to only have one account to manage. Also, once upon a
time (a long time ago) it made replication easier.)

Russell Fields
"D Barry" <google@.dcbarry.com> wrote in message
news:6d9b9a07.0405201046.548244c2@.posting.google.c om...
> Greetings:
> I am trying to conceive what risks might be created by running
> multiple SQL servers within a domain under a single domain account, as
> opposed to 1) running under the local service account or 2) multiple
> domain service accounts.
> In this case, all the SQL servers are SQL2000 running on Win2003. The
> service account is assigned only to the "Domain Users" group.
> We do use linked server calls, and I have played and suceeded getting
> Kereberos up to avoid double hop issues when using Windows Auth. In
> fact, this is one of the reasons that sparked the question in my mind
> -- in all the MS Kerebos SQL<->SQL examples, the SQL servers run under
> a unique service account.
>
> As an aside, most of the servers are "line of business" servers, but
> HR runs under a unique server with more sensitive information. I don't
> really think that merits a seperate service account, but again, I
> could well be missing something.
>
> I mostly looking for food for thought, but concrete examples of
> gotchas would be appreciated.
> Thanks all.
> d.|||Russell:

It's "d.". "D." is just too pompous... ;-)

I should have stated the breach against one is a breach of all
arugument. (We do use nice long complex passwords.) I'm looking for
other

"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:<ubmx4TqPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> D (or should I call you d?),
> One drawback of using a single service account is that a breach of security
> on that account means a breach on all of your SQL Servers.
> (Yes, it is easier to only have one account to manage. Also, once upon a
> time (a long time ago) it made replication easier.)
> Russell Fields
> "D Barry" <google@.dcbarry.com> wrote in message
> news:6d9b9a07.0405201046.548244c2@.posting.google.c om...
> > Greetings:
> > I am trying to conceive what risks might be created by running
> > multiple SQL servers within a domain under a single domain account, as
> > opposed to 1) running under the local service account or 2) multiple
> > domain service accounts.
<snip
> > Thanks all.
> > d.

Right single quotation mark errors

Hi,
I've created a table in SQL Server 2000 and I'm now trying to search
through the data and return specific rows. I'm using this command:

select * from Export where libelle_court='Recherche d'investisseurs'

The problem is this: The search fails whenever there is a curly
single quotation mark within the table field ( ' as opposed to ' ).

For example, if the field entry in my table is this:
Recherche d'investisseurs

then both of the following commands retun no fields:
select * from Export where libelle_court='Recherche d'investisseurs'
select * from Export where libelle_court='Recherche d''investisseurs'

However, if the field entry in my table is this:
Recherche d'investisseurs

then both of the commands quoted above succeed.

How can I get SQL Server to treat the curly quotation mark correctly
and return the right results? I've tried changing the collation but
with no success.

Thanks,

RobHi

In general if there is a quotation mark in the field it can be escaped with
another quotation mark, therefore I am not sure why the second example does
not work.

> select * from Export where libelle_court='Recherche d''investisseurs'

What does

select * from Export where libelle_court like 'Recherche d%'

return?

Also check out:

http://msdn.microsoft.com/library/d...con_03_7mch.asp

http://msdn.microsoft.com/library/d...earchvalues.asp

Collation will not effect this.

John

"Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> Hi,
> I've created a table in SQL Server 2000 and I'm now trying to search
> through the data and return specific rows. I'm using this command:
> select * from Export where libelle_court='Recherche d'investisseurs'
> The problem is this: The search fails whenever there is a curly
> single quotation mark within the table field ( ' as opposed to ' ).
> For example, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the following commands retun no fields:
> select * from Export where libelle_court='Recherche d'investisseurs'
> select * from Export where libelle_court='Recherche d''investisseurs'
> However, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the commands quoted above succeed.
> How can I get SQL Server to treat the curly quotation mark correctly
> and return the right results? I've tried changing the collation but
> with no success.
> Thanks,
> Rob|||Just another thought, depending on what the datatypes and options are, check
out trailing spaces. (Also see ANSI_PADDING in BOL)

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:bo5fp2$6q8$1@.sparta.btinternet.com...
> Hi
> In general if there is a quotation mark in the field it can be escaped
with
> another quotation mark, therefore I am not sure why the second example
does
> not work.
> > select * from Export where libelle_court='Recherche d''investisseurs'
> What does
> select * from Export where libelle_court like 'Recherche d%'
> return?
> Also check out:
>
http://msdn.microsoft.com/library/d...con_03_7mch.asp
>
http://msdn.microsoft.com/library/d...earchvalues.asp
> Collation will not effect this.
> John
> "Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
> news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> > Hi,
> > I've created a table in SQL Server 2000 and I'm now trying to search
> > through the data and return specific rows. I'm using this command:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > The problem is this: The search fails whenever there is a curly
> > single quotation mark within the table field ( ' as opposed to ' ).
> > For example, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the following commands retun no fields:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > select * from Export where libelle_court='Recherche d''investisseurs'
> > However, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the commands quoted above succeed.
> > How can I get SQL Server to treat the curly quotation mark correctly
> > and return the right results? I've tried changing the collation but
> > with no success.
> > Thanks,
> > Rob|||Thanks for the help.

I'm not sure exactly what the problem was but it has gone now. I was
working on a number of things so I don't know quite what it was that
fixed the problem. I thought it might be because I was changing the
collation, but I cannot use this to repeat the fault. The web sites
were useful, though, so thanks again for the help.

Robsql

RE-Using passed parameters

Hi All

I link to a report passing parm a,b,c (3 parms) a (Hidden), B(Multi Selection), C(Single selection), the report display correctly and the parm selection boxes display with available options.

I would like to be able to choose from the selection boxes and replace the initial (passed parameters) B and C would have a new value, and display the same report using the selected criteria.

Thank you

Trentino

I can't understand what you are asking. You want to link to the same report with new parameter values? Or you want to just select different values? Maybe another example would help.|||

Hi Brian

I like to select different values.

report A link to report B using parm1 and parm2.

Parm1 passes value 'A', parm2 is spaces (space is interpreted as select all records

matching the other criteria, ex. if there are 5 records all of them will be retieved), the new report storedProc retrieve the data that I would like to use to initialize parm2.

I cannot initialize parm2 with the result value from the SP (5 records value), I get forward dependencies are not valid.

Currently I created a new parm3, initialize it with the SP values, I can select 1 or more of the 5 displayed records (multi value box) and that work ok.

Is there a way to re-use parm2 without adding parm3 so no changes need to be done to the SP?

Hope this help you understand, thank you for your time.

Trentino

|||You probably have based the Valid Values list query used for parm 2 dependent on parm2. This is a circular reference. You need to have the 3rd report parameter and you use the second one as a seed value.|||

Hello Brian,

I've read your previous reply about forward dependencies and I was hoping you'd hear my plee.

I am using Visual Studio 2005 and I'm passing a single parameter (a unique string called AuditGUID) to a report and this single parameter is part of a multivalue list (select box) of many AuditGUID's. This returns a lot of information related to that unique AuditGUID.

When I set the parameter to use a select box, the error says the following:

[rsInvalidReportParameterDependency] The report parameter ‘Audit_GUID_par’ has a DefaultValue or a ValidValue that depends on the report parameter “Audit_GUID_par”. Forward dependencies are not valid.

But, when I change the select box to a textbox, where the user would have to enter a Audit_GUID (which is a very long and complicated string), then the report compiles successfully and works...but I can't allow users to have to go through this, I'd rather let them select it from a list (selectbox). Could you help me with this please?

Thank you for your time and I look forward to hearing from you.

Jean

reusing a single conversation handle

Hi

I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks

DECLARE @.dialog_handle uniqueidentifier

select @.dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'

IF @.dialog_handle is NULL

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE [initiator]

TO SERVICE 'target'

ON CONTRACT [portcontract];

SEND ON CONVERSATION @.dialog_handle

MESSAGE TYPE [Port] (@.msg)

Hi

Reusing conversations is generally a good idea and one good way to achieve this is to store a mapping of spid to conversations handle in a lookup table. http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx goes into the details and helps answer your questions.

Thanks

Ketan

Returning single value from multiple recordsets

Hi- I am sending several queries simultaneously to SS; I declare a variable,
and do several conditional selects to set the value of the variable. At the
end, I select the value of the variable as the return value of the complete
transaction. But, the query ends up returning multiple recordsets, because I
am doing multiple selects (although these selects only set the value of my
declared variable). After I get the recordsets back, I have to iterate
through all just to find my return value. Is there an easier way to isolate
the return value, or return just the return value in one recordset?
ThanksTry SET NOCOUNT ON in the beginning of your proc code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"boney" <boney@.discussions.microsoft.com> wrote in message
news:7148B087-86E9-4838-A985-774418731886@.microsoft.com...
> Hi- I am sending several queries simultaneously to SS; I declare a variabl
e,
> and do several conditional selects to set the value of the variable. At th
e
> end, I select the value of the variable as the return value of the complet
e
> transaction. But, the query ends up returning multiple recordsets, because
I
> am doing multiple selects (although these selects only set the value of my
> declared variable). After I get the recordsets back, I have to iterate
> through all just to find my return value. Is there an easier way to isolat
e
> the return value, or return just the return value in one recordset?
> Thanks|||boney
Can you call a SP with an OUTPUT parameter?
For more details pls refer to the BOL.
"boney" <boney@.discussions.microsoft.com> wrote in message
news:7148B087-86E9-4838-A985-774418731886@.microsoft.com...
> Hi- I am sending several queries simultaneously to SS; I declare a
> variable,
> and do several conditional selects to set the value of the variable. At
> the
> end, I select the value of the variable as the return value of the
> complete
> transaction. But, the query ends up returning multiple recordsets, because
> I
> am doing multiple selects (although these selects only set the value of my
> declared variable). After I get the recordsets back, I have to iterate
> through all just to find my return value. Is there an easier way to
> isolate
> the return value, or return just the return value in one recordset?
> Thanks

returning one single record rather than multiples

Hi

Is it possible to return the results of a query so that instead of
having say 10 rows its concatenated, eg

My query returns 'M' 10 times, can this be returned as 'M M M M M M M
M M M'?

Thanks
LeeLee (lee@.digital-interactive.com) writes:

Quote:

Originally Posted by

Is it possible to return the results of a query so that instead of
having say 10 rows its concatenated, eg
>
My query returns 'M' 10 times, can this be returned as 'M M M M M M M
M M M'?

In SQL 2005:

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

This makes use of the XML functionality, and has the drawback that some
characters will be encoded.

In SQL 2000, you are unfortunately best of with a cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi

I've just tried that (in SQL 2005)

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT [company size] + ' ' AS [text()]
FROM results
FOR XML PATH('')) AS T(Ms)

and its returns errors, is that a working example?

Thanks
Lee|||The example from Erland is good, he was just sketching quickly to give you
an idea of how to solve your problem (and something to work on, since there
was no DDL posted). Here is a copy and paste extension to that:

CREATE TABLE tbl (col char(1))

INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')

SELECT substring(Ms, 1, datalength(Ms) / 2 - 1)
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

DROP TABLE tbl

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Returning from single user mode

I'm using a modified backup plan to perform nightly backups. In the
integrity check I've checked the Attempt to repair (I know some don't
recommend it). To prepare for that stage of the backup I kill the user
processes that are still active when the backup begins. However, when the
backup finishes the databases are being left in single user mode. How can I
return the databases to multi user mode? I've tried a procedure like so:
create procedure setmultiuser
as
declare @.dbname varchar(128),
@.cmd varchar(128)
declare dbname_crsr cursor for
select name from sysdatabases
where sid <> 0x01
open dbname_crsr
fetch dbname_crsr into @.dbname
while (@.@.fetch_status <> -1)
begin
set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
EXEC ('sp_dboption ' + @.cmd)
fetch dbname_crsr into @.dbname
end
close dbname_crsr
deallocate dbname_crsr
But, the accursed EXEC command refuses to allow me to understand it's
syntax.
Any help would be greatly appreciated.I get:
Line 16: Incorrect syntax near 'single'.
The actual line reads:
set @.cmd = @.dbname + ', 'single user', 'FALSE''
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
Throw a print in there to see where the wheels may be coming off...
PRINT ('sp_dboption ' + @.cmd)
How many databases exist on your server? Would it be easier to simply
hardcode the appropriate call within a separate step of the job?
--
Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I'm using a modified backup plan to perform nightly backups. In the
> integrity check I've checked the Attempt to repair (I know some don't
> recommend it). To prepare for that stage of the backup I kill the user
> processes that are still active when the backup begins. However, when the
> backup finishes the databases are being left in single user mode. How can
I
> return the databases to multi user mode? I've tried a procedure like so:
> create procedure setmultiuser
> as
> declare @.dbname varchar(128),
> @.cmd varchar(128)
> declare dbname_crsr cursor for
> select name from sysdatabases
> where sid <> 0x01
> open dbname_crsr
> fetch dbname_crsr into @.dbname
> while (@.@.fetch_status <> -1)
> begin
> set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
> EXEC ('sp_dboption ' + @.cmd)
> fetch dbname_crsr into @.dbname
> end
> close dbname_crsr
> deallocate dbname_crsr
> But, the accursed EXEC command refuses to allow me to understand it's
> syntax.
> Any help would be greatly appreciated.
>|||Does this help?
declare @.cmd varchar(100), @.dbname char(5)
set @.cmd =3D 'thecmd ' set @.dbname =3D 'thedb' set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
print @.cmd
-- Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message =news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> I get:
> > Line 16: Incorrect syntax near 'single'.
> > The actual line reads:
> set @.cmd =3D @.dbname + ', 'single user', 'FALSE''
> > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote =in
> message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Throw a print in there to see where the wheels may be coming off...
> > PRINT ('sp_dboption ' + @.cmd)
> > How many databases exist on your server? Would it be easier to simply
> hardcode the appropriate call within a separate step of the job?
> > -- > Keith
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I'm using a modified backup plan to perform nightly backups. In the
> > integrity check I've checked the Attempt to repair (I know some =don't
> > recommend it). To prepare for that stage of the backup I kill the =user
> > processes that are still active when the backup begins. However, =when the
> > backup finishes the databases are being left in single user mode. =How can
> I
> > return the databases to multi user mode? I've tried a procedure =like so:
> >
> > create procedure setmultiuser
> > as
> >
> > declare @.dbname varchar(128),
> > @.cmd varchar(128)
> >
> > declare dbname_crsr cursor for
> > select name from sysdatabases
> > where sid <> 0x01
> >
> > open dbname_crsr
> > fetch dbname_crsr into @.dbname
> >
> > while (@.@.fetch_status <> -1)
> > begin
> > set @.cmd =3D @.dbname + ', ' + ''single user', 'FALSE''
> > EXEC ('sp_dboption ' + @.cmd)
> >
> > fetch dbname_crsr into @.dbname
> > end
> >
> > close dbname_crsr
> > deallocate dbname_crsr
> >
> > But, the accursed EXEC command refuses to allow me to understand =it's
> > syntax.
> >
> > Any help would be greatly appreciated.
> >
> >
> >|||That was real close. This is what finally worked:
set @.cmd = @.dbname + ''', ''single user'', ''FALSE'''
exec ('sp_dboption ''' + @.cmd)
But you put me on the right track. Thanks a billion!!!
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:ug4IM1bZDHA.2236@.TK2MSFTNGP10.phx.gbl...
Does this help?
declare @.cmd varchar(100), @.dbname char(5)
set @.cmd = 'thecmd '
set @.dbname = 'thedb'
set @.cmd = @.dbname + ''', ''single user'', ''FALSE'''
print @.cmd
--
Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> I get:
> Line 16: Incorrect syntax near 'single'.
> The actual line reads:
> set @.cmd = @.dbname + ', 'single user', 'FALSE''
> "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
> message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Throw a print in there to see where the wheels may be coming off...
> PRINT ('sp_dboption ' + @.cmd)
> How many databases exist on your server? Would it be easier to simply
> hardcode the appropriate call within a separate step of the job?
> --
> Keith
> "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I'm using a modified backup plan to perform nightly backups. In the
> > integrity check I've checked the Attempt to repair (I know some don't
> > recommend it). To prepare for that stage of the backup I kill the user
> > processes that are still active when the backup begins. However, when
the
> > backup finishes the databases are being left in single user mode. How
can
> I
> > return the databases to multi user mode? I've tried a procedure like
so:
> >
> > create procedure setmultiuser
> > as
> >
> > declare @.dbname varchar(128),
> > @.cmd varchar(128)
> >
> > declare dbname_crsr cursor for
> > select name from sysdatabases
> > where sid <> 0x01
> >
> > open dbname_crsr
> > fetch dbname_crsr into @.dbname
> >
> > while (@.@.fetch_status <> -1)
> > begin
> > set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
> > EXEC ('sp_dboption ' + @.cmd)
> >
> > fetch dbname_crsr into @.dbname
> > end
> >
> > close dbname_crsr
> > deallocate dbname_crsr
> >
> > But, the accursed EXEC command refuses to allow me to understand it's
> > syntax.
> >
> > Any help would be greatly appreciated.
> >
> >
>|||Learning is half the fun. Applying what you learn is the other half.
-- Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message =news:Opv#VOcZDHA.2020@.TK2MSFTNGP10.phx.gbl...
> That was real close. This is what finally worked:
> > set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
> exec ('sp_dboption ''' + @.cmd)
> > But you put me on the right track. Thanks a billion!!!
> > > > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote =in
> message news:ug4IM1bZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Does this help?
> > > declare @.cmd varchar(100), @.dbname char(5)
> set @.cmd =3D 'thecmd '
> set @.dbname =3D 'thedb'
> set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
> print @.cmd
> > -- > Keith
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> > I get:
> >
> > Line 16: Incorrect syntax near 'single'.
> >
> > The actual line reads:
> > set @.cmd =3D @.dbname + ', 'single user', 'FALSE''
> >
> > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> =wrote in
> > message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> > Throw a print in there to see where the wheels may be coming off...
> >
> > PRINT ('sp_dboption ' + @.cmd)
> >
> > How many databases exist on your server? Would it be easier to =simply
> > hardcode the appropriate call within a separate step of the job?
> >
> > -- > > Keith
> >
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> > news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > > I'm using a modified backup plan to perform nightly backups. In =the
> > > integrity check I've checked the Attempt to repair (I know some =don't
> > > recommend it). To prepare for that stage of the backup I kill the =user
> > > processes that are still active when the backup begins. However, =when
> the
> > > backup finishes the databases are being left in single user mode. =How
> can
> > I
> > > return the databases to multi user mode? I've tried a procedure =like
> so:
> > >
> > > create procedure setmultiuser
> > > as
> > >
> > > declare @.dbname varchar(128),
> > > @.cmd varchar(128)
> > >
> > > declare dbname_crsr cursor for
> > > select name from sysdatabases
> > > where sid <> 0x01
> > >
> > > open dbname_crsr
> > > fetch dbname_crsr into @.dbname
> > >
> > > while (@.@.fetch_status <> -1)
> > > begin
> > > set @.cmd =3D @.dbname + ', ' + ''single user', 'FALSE''
> > > EXEC ('sp_dboption ' + @.cmd)
> > >
> > > fetch dbname_crsr into @.dbname
> > > end
> > >
> > > close dbname_crsr
> > > deallocate dbname_crsr
> > >
> > > But, the accursed EXEC command refuses to allow me to understand =it's
> > > syntax.
> > >
> > > Any help would be greatly appreciated.
> > >
> > >
> >
> >
> >