SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
MSP_TASKS.TASK_START_DATE
FROM MSP_PROJECTS LEFT OUTER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
MSP_TASKS.TASK_START_DATE"
Using my fields how would I exclude the weekends from this.If you are using a stored procedure to get your dataset you could add the
following column to your select statement:
(6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
NoOfWorkingDays
You will also need to SET DATEFIRST 1 at the start of your stored procedure
to define your week start date as Monday, otherwise tha bove won't work.
HTH,
Magendo_man
Stirling, Scotland
"Benw" wrote:
> SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> MSP_TASKS.TASK_START_DATE
> FROM MSP_PROJECTS LEFT OUTER JOIN
> MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> MSP_TASKS.TASK_START_DATE"
> Using my fields how would I exclude the weekends from this.|||I dont think I have ever done a stored procedure before. How would I start
and where. Sorry to ask such a dumb question. I just started doing this a
couple months ago.
"magendo_man" wrote:
> If you are using a stored procedure to get your dataset you could add the
> following column to your select statement:
> (6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
> NoOfWorkingDays
> You will also need to SET DATEFIRST 1 at the start of your stored procedure
> to define your week start date as Monday, otherwise tha bove won't work.
> HTH,
> Magendo_man
> Stirling, Scotland
>
> "Benw" wrote:
> > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> > MSP_TASKS.TASK_START_DATE
> > FROM MSP_PROJECTS LEFT OUTER JOIN
> > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> >
> > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > MSP_TASKS.TASK_START_DATE"
> >
> > Using my fields how would I exclude the weekends from this.|||In this instance you don't necessarily need a stored procedure, but it does
execute faster. Check in BOL for CREATE PROCEDURE. Essentially it is a
query in a procedure "wrapper". This allows you to pass parameters in and
optionally out. RS then uses EXEC <procedurename> @.param1, @.param2, ... as
its data source.
"Benw" wrote:
> I dont think I have ever done a stored procedure before. How would I start
> and where. Sorry to ask such a dumb question. I just started doing this a
> couple months ago.
> "magendo_man" wrote:
> > If you are using a stored procedure to get your dataset you could add the
> > following column to your select statement:
> >
> > (6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> > TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
> > NoOfWorkingDays
> >
> > You will also need to SET DATEFIRST 1 at the start of your stored procedure
> > to define your week start date as Monday, otherwise tha bove won't work.
> >
> > HTH,
> > Magendo_man
> >
> > Stirling, Scotland
> >
> >
> > "Benw" wrote:
> >
> > > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> > > MSP_TASKS.TASK_START_DATE
> > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > >
> > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > MSP_TASKS.TASK_START_DATE"
> > >
> > > Using my fields how would I exclude the weekends from this.|||Also note, the datefirst default is 7 (Sunday). If you change Datefirst, you
probably want to read the current value (SELECT @.@.DateFirst) , run your code
and then reset datefirst to its original value.
"Benw" wrote:
> I dont think I have ever done a stored procedure before. How would I start
> and where. Sorry to ask such a dumb question. I just started doing this a
> couple months ago.
> "magendo_man" wrote:
> > If you are using a stored procedure to get your dataset you could add the
> > following column to your select statement:
> >
> > (6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> > TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
> > NoOfWorkingDays
> >
> > You will also need to SET DATEFIRST 1 at the start of your stored procedure
> > to define your week start date as Monday, otherwise tha bove won't work.
> >
> > HTH,
> > Magendo_man
> >
> > Stirling, Scotland
> >
> >
> > "Benw" wrote:
> >
> > > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> > > MSP_TASKS.TASK_START_DATE
> > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > >
> > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > MSP_TASKS.TASK_START_DATE"
> > >
> > > Using my fields how would I exclude the weekends from this.|||You could past my suggested code in to your query just after
MSP_TASKS.TASK_START_DATE and before FROM MSP_PROJECTS LEFT OUTER JOIN.
However, you may have to adjust my code if your system defaults to the SQL
standard of Sunday being the first day of the week. If that is the case then
the code would, I think, have to be:
SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE, MSP_TASKS.TASK_START_DATE,
(7 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -2 ) AS
NoOfWorkingDays
FROM MSP_PROJECTS LEFT OUTER JOIN
MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
This will give you a new column named NoOfWorkingDays in the dataset. Please
test this thoroughly before using it on a production system.
"Benw" wrote:
> I dont think I have ever done a stored procedure before. How would I start
> and where. Sorry to ask such a dumb question. I just started doing this a
> couple months ago.
> "magendo_man" wrote:
> > If you are using a stored procedure to get your dataset you could add the
> > following column to your select statement:
> >
> > (6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> > TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
> > NoOfWorkingDays
> >
> > You will also need to SET DATEFIRST 1 at the start of your stored procedure
> > to define your week start date as Monday, otherwise tha bove won't work.
> >
> > HTH,
> > Magendo_man
> >
> > Stirling, Scotland
> >
> >
> > "Benw" wrote:
> >
> > > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> > > MSP_TASKS.TASK_START_DATE
> > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > >
> > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > MSP_TASKS.TASK_START_DATE"
> > >
> > > Using my fields how would I exclude the weekends from this.|||wow, that worked, also. THat fixed about 4 reports. Now I have one more
request and if you dont have time, I understand. But could you take me thru
the working days formula and explain to me what it is doing. If you dont
have time, I understand. Thanks
"magendo_man" wrote:
> You could past my suggested code in to your query just after
> MSP_TASKS.TASK_START_DATE and before FROM MSP_PROJECTS LEFT OUTER JOIN.
> However, you may have to adjust my code if your system defaults to the SQL
> standard of Sunday being the first day of the week. If that is the case then
> the code would, I think, have to be:
> SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE, MSP_TASKS.TASK_START_DATE,
> (7 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -2 ) AS
> NoOfWorkingDays
> FROM MSP_PROJECTS LEFT OUTER JOIN
> MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
>
> This will give you a new column named NoOfWorkingDays in the dataset. Please
> test this thoroughly before using it on a production system.
>
> "Benw" wrote:
> > I dont think I have ever done a stored procedure before. How would I start
> > and where. Sorry to ask such a dumb question. I just started doing this a
> > couple months ago.
> >
> > "magendo_man" wrote:
> >
> > > If you are using a stored procedure to get your dataset you could add the
> > > following column to your select statement:
> > >
> > > (6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> > > TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
> > > NoOfWorkingDays
> > >
> > > You will also need to SET DATEFIRST 1 at the start of your stored procedure
> > > to define your week start date as Monday, otherwise tha bove won't work.
> > >
> > > HTH,
> > > Magendo_man
> > >
> > > Stirling, Scotland
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > > > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> > > > MSP_TASKS.TASK_START_DATE
> > > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > > >
> > > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > > MSP_TASKS.TASK_START_DATE"
> > > >
> > > > Using my fields how would I exclude the weekends from this.|||There are three parts to the formula:
1) Work out number of working days between start date and the end of the
week it is in
2) Work out number of whole weeks between start and finish date weeks,
multiply by 5 to get number of working days
3) Work out number of working days from beginning of the week up to the
finish date
Then add these all together.
The DATEPART(weekday, date) function gives you the number of the day in the
week between 1 and 7. In this case 1 is Sunday and 7 is Saturday. You can
change this, for example to 1 being Monday and 7 being Sunday by changing the
SQL DATEFIRST parameter in a stored procedure using SET DATEFIRST 2, which
sets Monday as the start of your week.
The DATEDIFF(week, date1, date2) function gives you the number of weeks
between date1 and date2.
HTH,
Magendo_Man
"Benw" wrote:
> wow, that worked, also. THat fixed about 4 reports. Now I have one more
> request and if you dont have time, I understand. But could you take me thru
> the working days formula and explain to me what it is doing. If you dont
> have time, I understand. Thanks
> "magendo_man" wrote:
> > You could past my suggested code in to your query just after
> > MSP_TASKS.TASK_START_DATE and before FROM MSP_PROJECTS LEFT OUTER JOIN.
> > However, you may have to adjust my code if your system defaults to the SQL
> > standard of Sunday being the first day of the week. If that is the case then
> > the code would, I think, have to be:
> >
> > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE, MSP_TASKS.TASK_START_DATE,
> > (7 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> > TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -2 ) AS
> > NoOfWorkingDays
> > FROM MSP_PROJECTS LEFT OUTER JOIN
> > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> >
> >
> > This will give you a new column named NoOfWorkingDays in the dataset. Please
> > test this thoroughly before using it on a production system.
> >
> >
> > "Benw" wrote:
> >
> > > I dont think I have ever done a stored procedure before. How would I start
> > > and where. Sorry to ask such a dumb question. I just started doing this a
> > > couple months ago.
> > >
> > > "magendo_man" wrote:
> > >
> > > > If you are using a stored procedure to get your dataset you could add the
> > > > following column to your select statement:
> > > >
> > > > (6 - DATEPART(weekday,TASK_START_DATE)) + 5*(DATEDIFF(Week, TASK_START_DATE,
> > > > TASK_FINISH_DATE) -1) + (DATEPART(weekday,TASK_FINISH_DATE) -1 ) AS
> > > > NoOfWorkingDays
> > > >
> > > > You will also need to SET DATEFIRST 1 at the start of your stored procedure
> > > > to define your week start date as Monday, otherwise tha bove won't work.
> > > >
> > > > HTH,
> > > > Magendo_man
> > > >
> > > > Stirling, Scotland
> > > >
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME, MSP_TASKS.TASK_ID,
> > > > > MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> > > > > MSP_TASKS.TASK_START_DATE
> > > > > FROM MSP_PROJECTS LEFT OUTER JOIN
> > > > > MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> > > > >
> > > > > From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> > > > > MSP_TASKS.TASK_START_DATE"
> > > > >
> > > > > Using my fields how would I exclude the weekends from this.|||Thanks for the explanation, I have learned alot from this.
"Benw" <Benw@.discussions.microsoft.com> wrote in message
news:2A0A983A-53E9-49CC-8CF5-9FBDB331F518@.microsoft.com...
> SELECT MSP_PROJECTS.PROJ_ID, MSP_PROJECTS.PROJ_NAME,
> MSP_TASKS.TASK_ID,
> MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_FINISH_DATE,
> MSP_TASKS.TASK_START_DATE
> FROM MSP_PROJECTS LEFT OUTER JOIN
> MSP_TASKS ON MSP_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID
> From this I need to count days " MSP_TASKS.TASK_FINISH_DATE -
> MSP_TASKS.TASK_START_DATE"
> Using my fields how would I exclude the weekends from this.
Sunday, February 26, 2012
Get rid of leading 0s of a varchar field
Hi,
I have a field called StreetNo in a table called Prospects, the field is a
varchar(10). The table is populated by someone else, so I have no control
over what kind of data entered into the table. The StreetNo I got is a
complete mess, e.g. 00001, 01234, 01234a, 0000 PO Box. Is there a SQL
statement that I can use to show the StreetNo without the leading 0s?
I tried:
select Case when StreetNo is null then '' else convert(varchar(10),
convert(integer, StreetNo)) + ' ' end
This is not working for 01234a or 0000 PO Box
TIA
TIABetter to use a 3rd party product or a programming language for data
cleanup. With t-SQL, given all the data values are messed up, you can try
something like:
SELECT STUFF( @.s, 1, PATINDEX( '%[^0]%', @.s ) - 1, SPACE(0) )
Anith
I have a field called StreetNo in a table called Prospects, the field is a
varchar(10). The table is populated by someone else, so I have no control
over what kind of data entered into the table. The StreetNo I got is a
complete mess, e.g. 00001, 01234, 01234a, 0000 PO Box. Is there a SQL
statement that I can use to show the StreetNo without the leading 0s?
I tried:
select Case when StreetNo is null then '' else convert(varchar(10),
convert(integer, StreetNo)) + ' ' end
This is not working for 01234a or 0000 PO Box
TIA
TIABetter to use a 3rd party product or a programming language for data
cleanup. With t-SQL, given all the data values are messed up, you can try
something like:
SELECT STUFF( @.s, 1, PATINDEX( '%[^0]%', @.s ) - 1, SPACE(0) )
Anith
Get rid of column name as tag when query for xml
here is my query (Email is of type XML):
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Ohh yes my man! It did work just as expected thou performance was a bit slow
.
I'll test it on another server to see it that's query issue or stress testin
g
they might be doing on the server. thanks!
"Martin Honnen" wrote:
> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>|||I read your post with similiar suggestions you gave someone else but couldn'
t
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:
> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
>|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:
> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Ohh yes my man! It did work just as expected thou performance was a bit slow
.
I'll test it on another server to see it that's query issue or stress testin
g
they might be doing on the server. thanks!
"Martin Honnen" wrote:
> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>|||I read your post with similiar suggestions you gave someone else but couldn'
t
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:
> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
>|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:
> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.
Get rid of column name as tag when query for xml
here is my query (Email is of type XML):
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!
Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Ohh yes my man! It did work just as expected thou performance was a bit slow.
I'll test it on another server to see it that's query issue or stress testing
they might be doing on the server. thanks!
"Martin Honnen" wrote:
> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>
|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>
|||I read your post with similiar suggestions you gave someone else but couldn't
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:
> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>
|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...[vbcol=seagreen]
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:
> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>
|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!
Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Ohh yes my man! It did work just as expected thou performance was a bit slow.
I'll test it on another server to see it that's query issue or stress testing
they might be doing on the server. thanks!
"Martin Honnen" wrote:
> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>
|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>
|||I read your post with similiar suggestions you gave someone else but couldn't
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:
> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>
|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...[vbcol=seagreen]
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:
> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>
|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.
get rid of an anonymous subscription
Hi all
I've got the following situation:
setup up an merge publication (distributor and publisher at the same server)
setup an anonymous subscription
deleted the subscription
In the replication monitor, I still see the deleted subscription.
Why?
And how do I get rid of it?
Thanks a lot for any informations and greetz
Aline
1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and
2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.
3. Restart the replication monitor.
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.
I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...
I'm still thankful for any hint, to get rid of these traces after deleting.
|||Let me make sure
1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.
2. Can you check the subscription by running select * from sysmergesubscriptions on the publication database?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.
The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.
The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.
So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...
Any other ideas?
Thanks for your help so far.
|||Sorry for the late reply.
Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights.
|||sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.
I now realise, I need another approach. The new questions are:
After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?
Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?
Thanks for your help
Aline
|||Hi Aline,
This is a bug.
Currently when you create an anonymous subscription and delete it with:
sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.
You can use the follwoing workaround/trick :)
On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.
get rid of an anonymous subscription
Hi all
I've got the following situation:
setup up an merge publication (distributor and publisher at the same server)
setup an anonymous subscription
deleted the subscription
In the replication monitor, I still see the deleted subscription.
Why?
And how do I get rid of it?
Thanks a lot for any informations and greetz
Aline
1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and
2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.
3. Restart the replication monitor.
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.
I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...
I'm still thankful for any hint, to get rid of these traces after deleting.
|||Let me make sure
1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.
2. Can you check the subscription by running select * from sysmergesubscriptions on the publication database?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.
The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.
The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.
So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...
Any other ideas?
Thanks for your help so far.
|||Sorry for the late reply.
Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights.
|||sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.
I now realise, I need another approach. The new questions are:
After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?
Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?
Thanks for your help
Aline
|||Hi Aline,
This is a bug.
Currently when you create an anonymous subscription and delete it with:
sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.
You can use the follwoing workaround/trick :)
On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.
get rid of an anonymous subscription
Hi all
I've got the following situation:
- setup up an merge publication (distributor and publisher at the same server) setup an anonymous subscription deleted the subscription
In the replication monitor, I still see the deleted subscription.
Why?
And how do I get rid of it?
Thanks a lot for any informations and greetz
Aline
1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and
2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.
3. Restart the replication monitor.
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.
I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...
I'm still thankful for any hint, to get rid of these traces after deleting.
|||Let me make sure
1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.
2. Can you check the subscription by running select*from sysmergesubscriptions on the publication database?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.
The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.
The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.
So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...
Any other ideas?
Thanks for your help so far.
|||Sorry for the late reply.
Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights.
|||sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.
I now realise, I need another approach. The new questions are:
After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?
Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?
Thanks for your help
Aline
|||Hi Aline,
This is a bug.
Currently when you create an anonymous subscription and delete it with:
sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.
You can use the follwoing workaround/trick :)
On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.
Subscribe to:
Posts (Atom)