Another record locking issue...

Hi all,

I'm working on a Planning Databse for a factory that allows jobs to be allocated to machines and to be given a priority. This info. is displayed on an Access 2000 form e.g.

MACHINE ONE MACHINE TWO
Priority Job Number Priority Job Number
1 J111 1 J654
2 J562 2 J865
3 j231 3 J821

Both the Production Manager and the Factory Manager have this form open at all times. The problem is this, if for example the Factory Manager changes the priority of a job on his PC, the change is not shown on the Production Managers PC (cos' his screen has not been refreshed with update) The Production Manager therefore has old data on his screen and could mistakenly think that the job priority had not changed at all...
I have managed to write some VBA code to get round this but I was wondering if there was any way of 'pushing ' changes to the front-end when they are made or of using record-locking etc. to achieve the same affect...

Thanks
MThat situation shouldn't happen. However, review optimistic and pessimistic locking. Pessimisitic should definitely work if you're having problems with optimistic.|||Give them a refresh button?|||I have already implemented a refresh button but every now and again the users forget to press it and this causes problems. Also tried using the Access Timer Interval to refersh every n seconds but that caused other problems that I won't go into here.

Sorry I didn't reply sooner as I was off ill yesterday :-(|||The only way that I can see that would resolve your problem is implementing some kind of refresh interval on the front end, as the refresh button aproach apparently didn't work!

The problem with "forcing new entries" is basically that you actually retrieve data from the db, and thus it's kind of difficult to force a re-fetch from the db side...|||Listen, I can send you some monkeys that can click the refresh button for you...|||Hey guys,

I've just realised that i never mentioned the back-end is a SQL Server 2000 database!!! Basically, I just don't understand record locking well enough and will have to go and do some research...thanks for all your inpute.

Another Backup Question

I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.
Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:

>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>

Another Backup Question

I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:

>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hour
s
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>

Another Backup Question

I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>

Another " Error: 4813 Expected the text length in data stream - "

After working for over a year, one of our snapshot replication
distributions now fails with the following error:
The process could not bulk copy into table '"texttab"'.
Expected the text length in data stream for bulk copy of text, ntext,
or image data.
(Source: DESTINATION_SERVER (Data source); Error number: 4813)
Function sequence error
(Source: ODBC Driver Manager (ODBC); Error number: S1010)
The last column of the table in error is type "text". If we lower the
number of the "BCPBatchSize" in the distribution agent profile, more
rows get copied before the error occurs - but there are too many other
very large tables in the snapshot to use that as a go-around.
As suggested in a previous post, we cannot change the table structure
because it is a third-party developed product.
When we use the -UseInProcLoader option in the dist agent, it creates
another error.
We're running SQL Server 2000 8.00.760
Thanks for any ideas.
remove the text column and replicate the remaining table. Use another method
to synchronize the text column, such as using a trigger to write to an audit
table. Have DTS read this audit table at regular intervals and then update
the text column between the publisher and subscriber. In many topologies you
will find that the text/image column is highly static.
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
"Dean" <dpichotto@.cse-insurance.com> wrote in message
news:1120071435.407646.302670@.g49g2000cwa.googlegr oups.com...
> After working for over a year, one of our snapshot replication
> distributions now fails with the following error:
> The process could not bulk copy into table '"texttab"'.
> Expected the text length in data stream for bulk copy of text, ntext,
> or image data.
> (Source: DESTINATION_SERVER (Data source); Error number: 4813)
> Function sequence error
> (Source: ODBC Driver Manager (ODBC); Error number: S1010)
> The last column of the table in error is type "text". If we lower the
> number of the "BCPBatchSize" in the distribution agent profile, more
> rows get copied before the error occurs - but there are too many other
> very large tables in the snapshot to use that as a go-around.
> As suggested in a previous post, we cannot change the table structure
> because it is a third-party developed product.
> When we use the -UseInProcLoader option in the dist agent, it creates
> another error.
> We're running SQL Server 2000 8.00.760
> Thanks for any ideas.
>
|||Thanks very much for your reply.
Wow - how twisted! I wonder if using ftp for the distribution would get
around this...

Annotated schema with views?

My database I am working with is very normalized so I ended up creating
views for certain tables which I then use in my XSD schema to select XML
from the DB.
The problem is the generated query does unecessary existence checks because
it doesn't read the underlying table column schema info.. the underlying
column is definately marked "NOT NULL".
...
(((((_Q6.A32 IS NOT NULL AND (_Q6.A32 =
N'11111145-110b-11c4-d8b3-c8c5154c131e') OR _Q6.A32 IS NOT NULL AND (_Q6.A32
= N'11111145-110b-11c4-d8b3-c8c5154c131d')) OR _Q6.A32 IS NOT NULL
...
How do I get rid of this unecessary checks. Surely it slows down performance
of the query.
Hi Joe
This should not be a problem. Since we do not look at the relational schema
when we generate the queries, we do not know whether the column can be null
or not. However, the query optimizier will know about it and this optimize
these expressions away. Thus, there should be no slow down in performance
due to these IS NOT NULL checks...
Best regards
Michael
"Joe" <morbidcamel@.msn.com> wrote in message
news:%23A08G49dFHA.2776@.TK2MSFTNGP10.phx.gbl...
> My database I am working with is very normalized so I ended up creating
> views for certain tables which I then use in my XSD schema to select XML
> from the DB.
> The problem is the generated query does unecessary existence checks
> because
> it doesn't read the underlying table column schema info.. the underlying
> column is definately marked "NOT NULL".
> ...
> (((((_Q6.A32 IS NOT NULL AND (_Q6.A32 =
> N'11111145-110b-11c4-d8b3-c8c5154c131e') OR _Q6.A32 IS NOT NULL AND
> (_Q6.A32
> = N'11111145-110b-11c4-d8b3-c8c5154c131d')) OR _Q6.A32 IS NOT NULL
> ...
> How do I get rid of this unecessary checks. Surely it slows down
> performance
> of the query.
>
>
|||OK, thank you for the advice. I got this information of existence checks
from the following article
http://support.microsoft.com/default...b;en-us;813955
I have another couple of questions though.
Question 1:
Will SQL 2005 support SQLXML natively and will the generated queries use FOR
XML PATH instead of the bulky FOR XML EXPLICIT. I know of the XML columns,
but I don't want to redo my tables necessarily.
Question 2:
I'm running into limitations in terms of performace with my XSD Schema
because there is simply a lot of elements.
I changed my XML format to be attribute centric and also added a lot of
clustered indexes on the keys and selected elements. This speeded up
tremendously. The problem is I have to add more elements in future and the
containment hierarchy is becoming huge. I ended up writting some logic to
query elements in fragments and add children manually. Will this be better
in SQL 2005 (if question 1 is "yes" ofcourse)?
Question 3:
UpdateGrams doesn't work well with identity columns. I ended using GUIDs
instead. This is fine in my current project, but I have another existing
project I want to convert to use SQLXML with .NET serialization which
depends hugely on identity values... will this be improved in future. I
suppose you can always write XSLT to generate clever SQL script or something
as a work-around, but I like the convenience of updategrams. Let MS do the
query generation work .
Question 4:
I know AS in 2005 uses XML/A. What is the chances in future of mapping XSD
to cubes instead of using XSLT to get the same effect? I don't know if this
is the right group to pose this question though.
Question 5:
SQL queries FOR XML doesn't support XML Attribute Groups, will this be
supported in future? Or is a XSLT the only way to do this as well?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok6LcyDeFHA.640@.tk2msftngp13.phx.gbl...
> Hi Joe
> This should not be a problem. Since we do not look at the relational
> schema when we generate the queries, we do not know whether the column can
> be null or not. However, the query optimizier will know about it and this
> optimize these expressions away. Thus, there should be no slow down in
> performance due to these IS NOT NULL checks...
> Best regards
> Michael
> "Joe" <morbidcamel@.msn.com> wrote in message
> news:%23A08G49dFHA.2776@.TK2MSFTNGP10.phx.gbl...
>
|||See below for the answers that I know (I am not the owner of the SQLXML
component for a couple of years now).
Best regards
Michael
"Joe" <morbidcamel@.msn.com> wrote in message
news:uc9DJzMeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> OK, thank you for the advice. I got this information of existence checks
> from the following article
> http://support.microsoft.com/default...b;en-us;813955
> I have another couple of questions though.
> Question 1:
> Will SQL 2005 support SQLXML natively and will the generated queries use
> FOR XML PATH instead of the bulky FOR XML EXPLICIT. I know of the XML
> columns, but I don't want to redo my tables necessarily.
>
SQL 2005 will ship with SQLXML 4.0 which is SQLXML 3.0 SP2 with support for
the new datatypes and minus the IIS ISAPI for exposing templates and queries
through IIS (use ASP.Net instead is the recommendation).
I don't know whether they will start using FOR XML PATH, but given the cost
of rewriting, I would assume not.

> Question 2:
> I'm running into limitations in terms of performace with my XSD Schema
> because there is simply a lot of elements.
> I changed my XML format to be attribute centric and also added a lot of
> clustered indexes on the keys and selected elements. This speeded up
> tremendously. The problem is I have to add more elements in future and the
> containment hierarchy is becoming huge. I ended up writting some logic to
> query elements in fragments and add children manually. Will this be better
> in SQL 2005 (if question 1 is "yes" ofcourse)?
I am not sure that I have enough information about your scenario to provide
you good feedback. But I would assume that the SQLXML team is interested in
understanding your scenario and pain points.

> Question 3:
> UpdateGrams doesn't work well with identity columns. I ended using GUIDs
> instead. This is fine in my current project, but I have another existing
> project I want to convert to use SQLXML with .NET serialization which
> depends hugely on identity values... will this be improved in future. I
> suppose you can always write XSLT to generate clever SQL script or
> something as a work-around, but I like the convenience of updategrams. Let
> MS do the query generation work .
I will pass this along.

> Question 4:
> I know AS in 2005 uses XML/A. What is the chances in future of mapping XSD
> to cubes instead of using XSLT to get the same effect? I don't know if
> this is the right group to pose this question though.
You better suggest that in the AS newsgroup (although I will forward this
request/question).

> Question 5:
> SQL queries FOR XML doesn't support XML Attribute Groups, will this be
> supported in future? Or is a XSLT the only way to do this as well?
I don't quite understand this question. Attribute Groups are an XML schema
concept.
Could you please provide an example?
Thanks
Michael

> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok6LcyDeFHA.640@.tk2msftngp13.phx.gbl...
>
|||Question 1:
There are no plans for using FOR XML PATH for SQL 2005. SQLXML will use FOR
XML EXPLICIT as it did in earlier releases..
Question 2:
In Sql 2005, there are no changes in the way SQLXML generate queries.
For the complexity brought by the elements, if you use simple type elements
in an xsd:sequence block, we will treat them as complex type elements and
generate a select statement for it. This is necessary to preserve the order
of the elements. If you specify the simple type elements in xsd:all group,
it will be treated like an attribute. On the other hand, there are some
other cases, a simple type element might be interpreted as complex type
elements so I would recommend using attributes vs elements.
If your schema is deep and consists of several complex type elements, the
generate FOR XML query will be also big. I couldn't understand how you can
partially generate hierarchies and combine them. Don't you need to find the
relevant parent Xml element to insert the Xml fragments? We would love to
hear more about your solution.
Question 3:
Updategrams have support for identity-columns. You may either use
updg:at-identity :
http://msdn.microsoft.com/library/de...egram_375f.asp
or use the sql:identity annoations in schema to specify the identity
columns.
http://msdn.microsoft.com/library/de...tions_7j03.asp
Let us know if these solutions don't work for you.
Question 4:
Question 5:
Ditto Michael here.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KzUBbTeFHA.2984@.TK2MSFTNGP15.phx.gbl...
> See below for the answers that I know (I am not the owner of the SQLXML
> component for a couple of years now).
> Best regards
> Michael
> "Joe" <morbidcamel@.msn.com> wrote in message
> news:uc9DJzMeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> SQL 2005 will ship with SQLXML 4.0 which is SQLXML 3.0 SP2 with support
> for the new datatypes and minus the IIS ISAPI for exposing templates and
> queries through IIS (use ASP.Net instead is the recommendation).
> I don't know whether they will start using FOR XML PATH, but given the
> cost of rewriting, I would assume not.
>
> I am not sure that I have enough information about your scenario to
> provide you good feedback. But I would assume that the SQLXML team is
> interested in understanding your scenario and pain points.
>
> I will pass this along.
>
> You better suggest that in the AS newsgroup (although I will forward this
> request/question).
>
> I don't quite understand this question. Attribute Groups are an XML schema
> concept.
> Could you please provide an example?
> Thanks
> Michael
>
>
|||I have attached some XML sample. I cannot give you the schema because there
is some traid secrets in there It is based on the OMG MOF model and is
represented with interfaces and classes. Note that XSLT is used to transform
XML to XMI etc.
When requestig the fragments, they always have a PID (Parent Id) an so doing
I can add it to the parent element's contents. As you can see, the XML can
nest quite deeply. All XML are serialized into classes. The XML can also be
serialized to a DataSet for generating Diffgrams and/or SqlAdaptors... The
problem is, I have to add more model elements to my schema, and when
requesting a <Pkg> (package) element there can be up to 12 different
complex types retrieved.
In my case a typical X-Path query looks like
/Nms[@.ID='f5fde8f9-b359-5eca-71b6-8012ce027c32'] and for multiple elements
/Nms[@.ID='000000ea-00e0-00fa-b0ca-d0bad000eae0' or
@.ID='00000000-00fa-00b0-eae0-cad0bad00001' or
@.ID='e4ecf9eb-a252-4f0b-8a47-5bf305e36d27' or
@.ID='f5fde8f9-b359-5eca-71b6-8012ce027c33']. When the fragment is selected,
an additional query is done to get a collection of light-weight
"descriptors" of parent elements, which in turn is used to construct the
parent elements if they weren't already constructed. SQLXML and .NET
Serializer reduces the ETL time significantly. All I want though is super
fast query times, which up until now was quite impressive. The second
version of my framework is only due in another 6 months so there is still
some time to find a solution and all my hopes is currently on SQL2005.
As far as the attribute groups are involved I am refering to something like
:
<xs:attributeGroup name="XMI.element.att">
<xs:annotation>
<xs:documentation>
XMI.element.att defines the attributes that each XML element
that corresponds to a metamodel class must have to conform to
the XMI specification.
</xs:documentation>
</xs:annotation>
<xs:attribute name="xmi.id" type="xs:ID"/>
<xs:attribute name="xmi.label" type="xs:string"/>
<xs:attribute name="xmi.uuid" type="xs:string"/>
</xs:attributeGroup>
Can SQLXML annotations added to this attribute group be interpreted by the
SQLXML engine.
Here is sample XML requested from SQL.
....
<Nms N="Synap-c" ID="00000000-00fa-00b0-eae0-cad0bad00001"
PID="000000ea-00e0-00fa-b0ca-d0bad000eae0">
<An>SiloFx Synap-c suite of products</An>
<Tag>[Tank].[Synap-c]</Tag>
<NmsC>
<Nms N="Administrator" ID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27"
PID="00000000-00fa-00b0-eae0-cad0bad00001">
<An>{1}:{2}</An>
<Tag>[Tank].[Synap-c].[Administrator]</Tag>
<NmsC>
<Nms N="EAE" ID="f5fde8f9-b359-5eca-71b6-8012ce027c33"
PID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27">
<An>SiloFx Synap-c Enterprise Architect Edition (EAE) at
Administrator</An>
<Tag>[Tank].[Synap-c].[Administrator].[EAE]</Tag>
<NmsC>
<Pkg N="Types" ID="f5fde8f9-b359-5eca-71b6-8012ce027f30"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c33" V="Internal" A="false" R="true"
L="false" B="false">
<An>Administrator: Synap-c EAE types used in projects
and ontologies</An>
<PkgC />
</Pkg>
<Nms N="Projects"
ID="f5fde8f9-b359-5eca-71b6-8012ce027c31"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c33">
<An>Administrator: Synap-c EAE types used in projects
and ontologies</An>
<Tag>[Tank].[Synap-c].[Administrator].[EAE].[Projects]</Tag>
<NmsC>
<Pkg N="Application Architecture"
ID="54968e3e-8330-46e2-b2df-4c15e6e30c2f"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Application Architecture"
ID="fce438aa-25e4-4f88-bd26-0a92ac82627c"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="As Is"
ID="9cfe0641-8e53-4586-bd72-d397fd890b28"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="SCM Organisational Structure"
ID="57deba4b-a86e-4a7c-96b7-4d9a0e0deb09"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="true">
<PkgC />
</Pkg>
<Pkg N="Application Architecture"
ID="aaf77499-5968-46e3-ab32-887545cfaedd"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Application Architecture"
ID="68cd9da0-765d-4958-ba12-95c5ae9e2860"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Technical Architecture"
ID="a6e957b1-f5ef-457d-bf68-ec95c9281899"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Created on 2005/06/27 08:48:05 AM 872"
ID="555da19a-aa21-4ed4-9694-915f134d48f3"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC>
<Asc N="Links to"
ID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
L="true" AT="DependsOn">
<Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
<AscC>
<AsE N="(Shop - Instance 1*)"
ID="c5529649-0672-cae5-7a71-691db0d70c90"
PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
TID="b75471b1-e5f6-455a-9857-4776c6820dff" C="true" M="1|1|1|0|0" A="None"
Nv="false" OID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a" />
<AsE N="P(Shop - Instance 1*)"
ID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a"
PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
TID="445a1f81-ccf0-44f3-9a05-e4b5a722ca55" C="true" M="1|1|1|0|0" A="None"
Nv="true" OID="c5529649-0672-cae5-7a71-691db0d70c90" />
</AscC>
</Asc>
<Asc N="Buys goods - Instance"
ID="920109d7-82b3-48ea-900e-a0a0b44a7768"
PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
L="true" AT="DependsOn">
<Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
<AscC>
<AsE N="(Buys goods - Instance*)"
ID="361f87ab-4c73-c4f0-6ad3-176260208799"
PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
TID="64de4e3c-8e80-4cda-ba9d-7782942ab0b1" C="true" M="1|1|1|0|0" A="None"
Nv="false" OID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc" />
<AsE N="P(Buys goods - Instance*)"
ID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc"
PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
TID="1a8e048a-d72c-45e4-ac85-64e61dcd5594" C="true" M="1|1|1|0|0" A="None"
Nv="true" OID="361f87ab-4c73-c4f0-6ad3-176260208799" />
</AscC>
</Asc>
<Cls N="Shop - Instance 1"
ID="0dae96ef-3e78-48b3-ba27-60d735345725"
PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Public" A="false" R="false"
L="true" S="false" B="false">
<ClsC />
</Cls>
...
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:Okj2O53eFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Question 1:
> There are no plans for using FOR XML PATH for SQL 2005. SQLXML will use
> FOR XML EXPLICIT as it did in earlier releases..
> Question 2:
> In Sql 2005, there are no changes in the way SQLXML generate queries.
> For the complexity brought by the elements, if you use simple type
> elements in an xsd:sequence block, we will treat them as complex type
> elements and generate a select statement for it. This is necessary to
> preserve the order of the elements. If you specify the simple type
> elements in xsd:all group, it will be treated like an attribute. On the
> other hand, there are some other cases, a simple type element might be
> interpreted as complex type elements so I would recommend using attributes
> vs elements.
> If your schema is deep and consists of several complex type elements, the
> generate FOR XML query will be also big. I couldn't understand how you can
> partially generate hierarchies and combine them. Don't you need to find
> the relevant parent Xml element to insert the Xml fragments? We would love
> to hear more about your solution.
> Question 3:
> Updategrams have support for identity-columns. You may either use
> updg:at-identity :
> http://msdn.microsoft.com/library/de...egram_375f.asp
> or use the sql:identity annoations in schema to specify the identity
> columns.
> http://msdn.microsoft.com/library/de...tions_7j03.asp
> Let us know if these solutions don't work for you.
> Question 4:
> Question 5:
> Ditto Michael here.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KzUBbTeFHA.2984@.TK2MSFTNGP15.phx.gbl...
>
|||Yes, we do support attribute groups in SqlXml. You can annotate them as
normal attributes and the annotations will be resolved based on the context
of attributegroup ref..
As far as I undertand your technique, you are executing multiple queries to
construct one Xml document.instead of executing one complex query. That
might be useful since the complexity of our FOR XML explicit queries
increases with the square of complex type elements.
Michael may talk about if SQL 2005 will offer solutions that will perform
better in deep hierarchies.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <morbidcamel@.msn.com> wrote in message
news:eKARm$6eFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I have attached some XML sample. I cannot give you the schema because there
>is some traid secrets in there It is based on the OMG MOF model and is
>represented with interfaces and classes. Note that XSLT is used to
>transform XML to XMI etc.
> When requestig the fragments, they always have a PID (Parent Id) an so
> doing I can add it to the parent element's contents. As you can see, the
> XML can nest quite deeply. All XML are serialized into classes. The XML
> can also be serialized to a DataSet for generating Diffgrams and/or
> SqlAdaptors... The problem is, I have to add more model elements to my
> schema, and when requesting a <Pkg> (package) element there can be up to
> 12 different complex types retrieved.
> In my case a typical X-Path query looks like
> /Nms[@.ID='f5fde8f9-b359-5eca-71b6-8012ce027c32'] and for multiple elements
> /Nms[@.ID='000000ea-00e0-00fa-b0ca-d0bad000eae0' or
> @.ID='00000000-00fa-00b0-eae0-cad0bad00001' or
> @.ID='e4ecf9eb-a252-4f0b-8a47-5bf305e36d27' or
> @.ID='f5fde8f9-b359-5eca-71b6-8012ce027c33']. When the fragment is
> selected, an additional query is done to get a collection of light-weight
> "descriptors" of parent elements, which in turn is used to construct the
> parent elements if they weren't already constructed. SQLXML and .NET
> Serializer reduces the ETL time significantly. All I want though is super
> fast query times, which up until now was quite impressive. The second
> version of my framework is only due in another 6 months so there is still
> some time to find a solution and all my hopes is currently on SQL2005.
> As far as the attribute groups are involved I am refering to something
> like :
> <xs:attributeGroup name="XMI.element.att">
> <xs:annotation>
> <xs:documentation>
> XMI.element.att defines the attributes that each XML element
> that corresponds to a metamodel class must have to conform to
> the XMI specification.
> </xs:documentation>
> </xs:annotation>
> <xs:attribute name="xmi.id" type="xs:ID"/>
> <xs:attribute name="xmi.label" type="xs:string"/>
> <xs:attribute name="xmi.uuid" type="xs:string"/>
> </xs:attributeGroup>
> Can SQLXML annotations added to this attribute group be interpreted by the
> SQLXML engine.
>
> Here is sample XML requested from SQL.
> ...
> <Nms N="Synap-c" ID="00000000-00fa-00b0-eae0-cad0bad00001"
> PID="000000ea-00e0-00fa-b0ca-d0bad000eae0">
> <An>SiloFx Synap-c suite of products</An>
> <Tag>[Tank].[Synap-c]</Tag>
> <NmsC>
> <Nms N="Administrator" ID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27"
> PID="00000000-00fa-00b0-eae0-cad0bad00001">
> <An>{1}:{2}</An>
> <Tag>[Tank].[Synap-c].[Administrator]</Tag>
> <NmsC>
> <Nms N="EAE" ID="f5fde8f9-b359-5eca-71b6-8012ce027c33"
> PID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27">
> <An>SiloFx Synap-c Enterprise Architect Edition (EAE) at
> Administrator</An>
> <Tag>[Tank].[Synap-c].[Administrator].[EAE]</Tag>
> <NmsC>
> <Pkg N="Types" ID="f5fde8f9-b359-5eca-71b6-8012ce027f30"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c33" V="Internal" A="false" R="true"
> L="false" B="false">
> <An>Administrator: Synap-c EAE types used in projects
> and ontologies</An>
> <PkgC />
> </Pkg>
> <Nms N="Projects"
> ID="f5fde8f9-b359-5eca-71b6-8012ce027c31"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c33">
> <An>Administrator: Synap-c EAE types used in projects
> and ontologies</An>
> <Tag>[Tank].[Synap-c].[Administrator].[EAE].[Projects]</Tag>
> <NmsC>
> <Pkg N="Application Architecture"
> ID="54968e3e-8330-46e2-b2df-4c15e6e30c2f"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="fce438aa-25e4-4f88-bd26-0a92ac82627c"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="As Is"
> ID="9cfe0641-8e53-4586-bd72-d397fd890b28"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="SCM Organisational Structure"
> ID="57deba4b-a86e-4a7c-96b7-4d9a0e0deb09"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="true">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="aaf77499-5968-46e3-ab32-887545cfaedd"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="68cd9da0-765d-4958-ba12-95c5ae9e2860"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Technical Architecture"
> ID="a6e957b1-f5ef-457d-bf68-ec95c9281899"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Created on 2005/06/27 08:48:05 AM 872"
> ID="555da19a-aa21-4ed4-9694-915f134d48f3"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC>
> <Asc N="Links to"
> ID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
> L="true" AT="DependsOn">
> <Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
> <AscC>
> <AsE N="(Shop - Instance 1*)"
> ID="c5529649-0672-cae5-7a71-691db0d70c90"
> PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> TID="b75471b1-e5f6-455a-9857-4776c6820dff" C="true" M="1|1|1|0|0" A="None"
> Nv="false" OID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a" />
> <AsE N="P(Shop - Instance 1*)"
> ID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a"
> PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> TID="445a1f81-ccf0-44f3-9a05-e4b5a722ca55" C="true" M="1|1|1|0|0" A="None"
> Nv="true" OID="c5529649-0672-cae5-7a71-691db0d70c90" />
> </AscC>
> </Asc>
> <Asc N="Buys goods - Instance"
> ID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
> L="true" AT="DependsOn">
> <Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
> <AscC>
> <AsE N="(Buys goods - Instance*)"
> ID="361f87ab-4c73-c4f0-6ad3-176260208799"
> PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> TID="64de4e3c-8e80-4cda-ba9d-7782942ab0b1" C="true" M="1|1|1|0|0" A="None"
> Nv="false" OID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc" />
> <AsE N="P(Buys goods - Instance*)"
> ID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc"
> PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> TID="1a8e048a-d72c-45e4-ac85-64e61dcd5594" C="true" M="1|1|1|0|0" A="None"
> Nv="true" OID="361f87ab-4c73-c4f0-6ad3-176260208799" />
> </AscC>
> </Asc>
> <Cls N="Shop - Instance 1"
> ID="0dae96ef-3e78-48b3-ba27-60d735345725"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Public" A="false" R="false"
> L="true" S="false" B="false">
> <ClsC />
> </Cls>
> ...
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:Okj2O53eFHA.2556@.TK2MSFTNGP10.phx.gbl...
>
|||In SQL Server 2005 you can compose FOR XML queries and we introduced the new
FOR XML PATH mode (see
http://msdn.microsoft.com/library/en.../forxml2k5.asp) that
should make it much easier to author complex hierarchies. Note however, that
while EXPLICIT mode is more complex to write and maintain, in some cases, it
may perform faster since it "XMLifies" the tree only once at the end. The
processes that create intermediate results that then are composed together
may often perform slower.
HTH
Michael
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:ONUxR$GfFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Yes, we do support attribute groups in SqlXml. You can annotate them as
> normal attributes and the annotations will be resolved based on the
> context of attributegroup ref..
> As far as I undertand your technique, you are executing multiple queries
> to construct one Xml document.instead of executing one complex query. That
> might be useful since the complexity of our FOR XML explicit queries
> increases with the square of complex type elements.
> Michael may talk about if SQL 2005 will offer solutions that will perform
> better in deep hierarchies.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Joe" <morbidcamel@.msn.com> wrote in message
> news:eKARm$6eFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
|||I thank you for the input. I am busy porting some of the logic to SQLServer
2005. I think using the XML PATH is going to do the trick for me.
This will mean I don't have to have views to get it into the right format,
some of the views uses the same tables, thus I can use reuse one table in
my query,
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O7AmR3NfFHA.3780@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2005 you can compose FOR XML queries and we introduced the
> new FOR XML PATH mode (see
> http://msdn.microsoft.com/library/en.../forxml2k5.asp) that
> should make it much easier to author complex hierarchies. Note however,
> that while EXPLICIT mode is more complex to write and maintain, in some
> cases, it may perform faster since it "XMLifies" the tree only once at the
> end. The processes that create intermediate results that then are composed
> together may often perform slower.
> HTH
> Michael
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:ONUxR$GfFHA.2156@.TK2MSFTNGP14.phx.gbl...
>
|||Cool, thanks. Feel free to share your feedback on how it went and how the
FOR XML PATH works.
Best regards
Michael
"Joe" <morbidcamel@.msn.com> wrote in message
news:eZxGtU4hFHA.576@.tk2msftngp13.phx.gbl...
>I thank you for the input. I am busy porting some of the logic to SQLServer
>2005. I think using the XML PATH is going to do the trick for me.
> This will mean I don't have to have views to get it into the right format,
> some of the views uses the same tables, thus I can use reuse one table in
> my query,
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:O7AmR3NfFHA.3780@.TK2MSFTNGP10.phx.gbl...
>

Annotated Schema Not Working

When I attempt to use Annotated Schemas, either the
Northwind examples or one's I have written myself they
will not run. If I run templates that don't use the
Annotated Schemas or URL queries everything works fine,
but, the Annotated Schemas don't. The errors that come
back are page cannot be found when I add an XML Path to
the URL query to the annotated schema. Can anybody help,
I have tried to get this working for a couple of weeks?
I am about ready to give up.
A couple of things to check (assuming you're talking about using a schema
through an IIS virtual root):
Have you enabled XPath Queries in the virtual root?
Have you put the schema in a virtual name of type "schema"?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike" <mojodallas@.msn.com> wrote in message
news:1c5f01c4465e$5a415e00$7d02280a@.phx.gbl...
> When I attempt to use Annotated Schemas, either the
> Northwind examples or one's I have written myself they
> will not run. If I run templates that don't use the
> Annotated Schemas or URL queries everything works fine,
> but, the Annotated Schemas don't. The errors that come
> back are page cannot be found when I add an XML Path to
> the URL query to the annotated schema. Can anybody help,
> I have tried to get this working for a couple of weeks?
> I am about ready to give up.

Annotated Schema Not Working

When I attempt to use Annotated Schemas, either the
Northwind examples or one's I have written myself they
will not run. If I run templates that don't use the
Annotated Schemas or URL queries everything works fine,
but, the Annotated Schemas don't. The errors that come
back are page cannot be found when I add an XML Path to
the URL query to the annotated schema. Can anybody help,
I have tried to get this working for a couple of weeks?
I am about ready to give up.
A couple of things to check (assuming you're talking about using a schema
through an IIS virtual root):
Have you enabled XPath Queries in the virtual root?
Have you put the schema in a virtual name of type "schema"?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike" <mojodallas@.msn.com> wrote in message
news:1c5f01c4465e$5a415e00$7d02280a@.phx.gbl...
> When I attempt to use Annotated Schemas, either the
> Northwind examples or one's I have written myself they
> will not run. If I run templates that don't use the
> Annotated Schemas or URL queries everything works fine,
> but, the Annotated Schemas don't. The errors that come
> back are page cannot be found when I add an XML Path to
> the URL query to the annotated schema. Can anybody help,
> I have tried to get this working for a couple of weeks?
> I am about ready to give up.

Annon Merge over VPN: WSM Insanity

I have my replication "working", though I would like to script as much
of the client installation as possible, and running into mega
headaches.
Here's the setup:
Client - MSDE
connects to Publisher/Distributor via VPN
annonymous pull subscription
I can install my MSDE, create the db, logins, etc.
1) Through EM, I can create the subscription through wizard. (selecting
"Available to Synch Manager", "Start immediately"). It works.
Syncronization Manager (type in passwords) synch works.
2) I script the subscription created in 1). Delete subscription and run
script with osql (doesn't run Merge Agent job automatically. Why not? I
even put in an extra line: EXEC sp_start_job @.job_name =
'MERGE_AGENT_JOBNAME'). Go back to EM, right click on subsrciption -
Start Synchronizing. It works. WSM (type in passwords) synch works.
3). Delete subcription and recreate with same script as above. Go
straight to WSM. The subscription is in the list. Synch (type in
passwords). DOES NOT WORK!!! I get the error "the schema script <blah
blah blah> could not be propagated to the subscriber."
Why should I have to run the subscription once through EM before it
will work with WSM? I guess it has something to do with that Merge
Agent job and it needs to be fired off first?
Obviously EM will not be distributed to my end users in far off lands.
I want them to be able to run a script and then synch through WSM. What
is the best way to achieve this?
Much Thanks
answers in line.
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
<richerwin@.ubs-europe.org> wrote in message
news:1109180376.620053.87490@.o13g2000cwo.googlegro ups.com...
> I have my replication "working", though I would like to script as much
> of the client installation as possible, and running into mega
> headaches.
> Here's the setup:
> Client - MSDE
> connects to Publisher/Distributor via VPN
> annonymous pull subscription
> I can install my MSDE, create the db, logins, etc.
> 1) Through EM, I can create the subscription through wizard. (selecting
> "Available to Synch Manager", "Start immediately"). It works.
> Syncronization Manager (type in passwords) synch works.
I take it you are using SQL Authentication in WSM. If so your user will have
to enter the password each time WSM runs. You might want to use intergrated
authentication and enter the user account in the pal on the publisher.

> 2) I script the subscription created in 1). Delete subscription and run
> script with osql (doesn't run Merge Agent job automatically. Why not? I
> even put in an extra line: EXEC sp_start_job @.job_name =
> 'MERGE_AGENT_JOBNAME'). Go back to EM, right click on subsrciption -
> Start Synchronizing. It works. WSM (type in passwords) synch works.
The merge agent doesn't start automatically because you have defined it to
start on demand.

> 3). Delete subcription and recreate with same script as above. Go
> straight to WSM. The subscription is in the list. Synch (type in
> passwords). DOES NOT WORK!!! I get the error "the schema script <blah
> blah blah> could not be propagated to the subscriber."
This means that the subscriber does not have rights to read the snapshot
share. You will have to craft a new snapshot share which does not use the
administrative share (ie \\servername\c$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\UNC). Make sure this new snapshot share has read
permission for the accounts or a group which the merge users are in, and
that this group or user has rights to read the underlying files and folders.
> Why should I have to run the subscription once through EM before it
> will work with WSM? I guess it has something to do with that Merge
> Agent job and it needs to be fired off first?
This does sound odd. Perhaps it is a rights issue.
> Obviously EM will not be distributed to my end users in far off lands.
> I want them to be able to run a script and then synch through WSM. What
> is the best way to achieve this?
> Much Thanks
>
|||
> This does sound odd. Perhaps it is a rights issue.
I create my subscription. Local machine is RICH-VAIO,
Publisher/Distributor is WSCLAN08; database is TMS. At both ends, the
SQL Server is the default instance.
1) I run the following Win2000 DOS BATCH command:
"C:\Program Files\Microsoft SQL Server\80\COM\replmerg.exe" -Publisher
[WSCLAN08] -PublisherDB [TMS] -Publication [pubUser100] -PublisherLogin
[tmsuser] -PublisherPassword [tmsuser] -Distributor [WSCLAN08]
-DistributorLogin [tmsuser] -DistributorPassword [tmsuser] -Subscriber
[RICH-VAIO] -SubscriberDB [TMS] -SubscriptionType 2
-SubscriberSecurityMode 0 -SubscriberLogin [tmsuser]
-SubscriberPassword []
and get the following log:
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'RICH-VAIO'
Connecting to Distributor 'WSCLAN08'
Initializing
Connecting to Publisher 'WSCLAN08'
Retrieving publication information
Retrieving subscription information
The subscription has been marked for reinitialization. Reinitialization
will occur the next time you synchronize this subscription.
The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_pubUser100\2 0050224042747\snapshot.pre'
could not be propagated to the subscriber.
Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_pubUser100\2 0050224042747\snapshot.pre'
could not be propagated to the subscriber.
Category:AGENT
Source: RICH-VAIO
Number: 0
Message: The process could not read file
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_pubUser100\2 0050224042747\snapshot.pre'
due to OS error 1326.
Category:OS
Source:
Number: 1326
Message: Logon failure: unknown user name or bad password.
2) Immediately, I go to EM, right click on my subscription, and select
Start Synchronizing. The last entry in the log history is:
The job succeeded. The Job was invoked by User tmsuser. The last step
to run was step 1 (Run agent.).
3) Immediately, I run the same batch file as above. I get the following
log:
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'RICH-VAIO'
Connecting to Distributor 'WSCLAN08'
Initializing
Connecting to Publisher 'WSCLAN08'
Retrieving publication information
Retrieving subscription information
The merge process is cleaning up meta data in database 'TMS'.
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s)
in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
The merge process is cleaning up meta data in database 'TMS'.
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s)
in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Uploading data changes to the Publisher
No data needed to be merged.
Downloading data changes to the Subscriber
4) Successive changes to the data at the subscriber are successfully
uploaded as in 3).
WHAT THE FREAK IS GOING ON WITH Merge Agent?
Please, please, please help. My kindom for a solution.
It must be noted I am not logged into Win2000 as tmsuser, though
tmsuser is a domain user under which SQLSERVER AGENT runs at the
subscriber, and tmsuser has full rights on the snapshot share at the
Publisher (as evident by the successful replications above).
|||just a wild thought here - why are you running the merge binary from the
command line?
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
<richerwin@.ubs-europe.org> wrote in message
news:1109221256.951320.175930@.f14g2000cwb.googlegr oups.com...
>
> I create my subscription. Local machine is RICH-VAIO,
> Publisher/Distributor is WSCLAN08; database is TMS. At both ends, the
> SQL Server is the default instance.
>
> 1) I run the following Win2000 DOS BATCH command:
> "C:\Program Files\Microsoft SQL Server\80\COM\replmerg.exe" -Publisher
> [WSCLAN08] -PublisherDB [TMS] -Publication [pubUser100] -PublisherLogin
> [tmsuser] -PublisherPassword [tmsuser] -Distributor [WSCLAN08]
> -DistributorLogin [tmsuser] -DistributorPassword [tmsuser] -Subscriber
> [RICH-VAIO] -SubscriberDB [TMS] -SubscriptionType 2
> -SubscriberSecurityMode 0 -SubscriberLogin [tmsuser]
> -SubscriberPassword []
> and get the following log:
> Microsoft SQL Server Merge Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Connecting to Subscriber 'RICH-VAIO'
> Connecting to Distributor 'WSCLAN08'
> Initializing
> Connecting to Publisher 'WSCLAN08'
> Retrieving publication information
> Retrieving subscription information
> The subscription has been marked for reinitialization. Reinitialization
> will occur the next time you synchronize this subscription.
> The schema script
>
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_pubUser100\2 0050224042747\snapshot.pre
'
> could not be propagated to the subscriber.
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147201001
> Message: The schema script
>
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_pubUser100\2 0050224042747\snapshot.pre
'
> could not be propagated to the subscriber.
> Category:AGENT
> Source: RICH-VAIO
> Number: 0
> Message: The process could not read file
>
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_pubUser100\2 0050224042747\snapshot.pre
'
> due to OS error 1326.
> Category:OS
> Source:
> Number: 1326
> Message: Logon failure: unknown user name or bad password.
> 2) Immediately, I go to EM, right click on my subscription, and select
> Start Synchronizing. The last entry in the log history is:
> The job succeeded. The Job was invoked by User tmsuser. The last step
> to run was step 1 (Run agent.).
> 3) Immediately, I run the same batch file as above. I get the following
> log:
> Microsoft SQL Server Merge Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Connecting to Subscriber 'RICH-VAIO'
> Connecting to Distributor 'WSCLAN08'
> Initializing
> Connecting to Publisher 'WSCLAN08'
> Retrieving publication information
> Retrieving subscription information
> The merge process is cleaning up meta data in database 'TMS'.
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s)
> in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
> The merge process is cleaning up meta data in database 'TMS'.
> The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s)
> in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
> Uploading data changes to the Publisher
> No data needed to be merged.
> Downloading data changes to the Subscriber
> 4) Successive changes to the data at the subscriber are successfully
> uploaded as in 3).
>
> WHAT THE FREAK IS GOING ON WITH Merge Agent?
> Please, please, please help. My kindom for a solution.
> It must be noted I am not logged into Win2000 as tmsuser, though
> tmsuser is a domain user under which SQLSERVER AGENT runs at the
> subscriber, and tmsuser has full rights on the snapshot share at the
> Publisher (as evident by the successful replications above).
>
|||
> why are you running the merge binary from the command line?
1) To show I'm getting the same results as from WSM.
2) So I can write the results to a log file with the >> redirect
command (so I can post them here) and show the group what I am doing.
3) End users won't have EM, so they need a way to start replication
(which isn't working from WSM, which is why I started this thread)
4) Proof of concept - it SHOULD work, shouldn't it?
thanks
|||[vbcol=seagreen]
This must have to do with accounts / permissions.
I have a couple of theories.
1) Why would executing the Merge agent job from within EM work, while
neither command-line nor WSM work? After all, I generated the
command-line args from the job from EM.
Is EM firing off the merge job under the same userID that I am when I
fire off the command line? (In the example above, both should be
'tmsuser'). How can I tell?
2) When I execute synchronize from WSM, do I have to be logged in to
Windows with the same domain ID that SQLSERVER AGENT (Windows service)
runs under?
Any help appreciated.
Thanks

AND not working in my SQL statement...

Any ideas why this line is bringing up just titles rather than titles and slogan:

dbsreq = "SELECT data_contents, data_style FROM content WHERE data_language = '" & siaith & "' AND data_type = 'title' AND 'slogan';"

(obviously it's all on one line in my code)

I don't understand why it brings up just 'title', not 'title' and 'slogan'?

Any ideas/fizes/suggestion would be greatfully appreciated.

Thanks,
DeianHi ,

You may try to use:
dbsreq = "SELECT ... AND data_type = 'title' AND data_type = 'slogan'";

Regards,|||Actually, you would want something like:

dbsreq = "SELECT ... AND (data_type = 'title' OR data_type = 'slogan')";

Terri|||i've tried everything including changing it to OR, but OR does't work as it brings in wrong data. I think I may need nested statements?|||Make sure you use parentheses to logically separate your conditions when an OR is used.

If you are still having trouble, response.write your dbsreq and let us know what it contains when it is about to be executed.

Terri|||what i've got ok... is a db structure like this:

table content has data_language (has welsh, english or both), data_contents (well the contents, i.e what the user reads), data_style (style to apply to the data), and data_type (type of data, i.e title, slogan, body paragraphs, smallprint, ext.links, int.link etc,etc)

Now what i want to do is (in pure english):

select data_contents and data_style from the content tables where the data_language is (in the first case english and both (i.e both is a value that descirbes neither english nor welsh, but a common word like dotcodotuk in other words it is a made up word that i just put in for fun) and also the data_type which in the first case would be titles and slogans. Now when the page gets regenerated the language will change to welsh hence the variable. And thats all that will change for the title ascx file. So I don't see why an OR statement is needed.

What happens is i bind the data using response.write(span blah blah blah...) It DOES work.

Problem is when i want (titles AND slogan) and (english AND Both) or in the case of the regenerated page (welsh AND both).

I'm planning to have my whole site working on variables to write sql statements based on ascx files that get/set their values from parent page or fellow ascx files. Therefore eliminating pages.

All contents and styles will be DB based.

Thanks for any help. If anyone is on msn (deian9@.hotmail.com), i'll be on-line until about midnight(gmt) to show my site online or swap files/code.

Thanks, this is really bugging me!

Deian|||What you are saying does not make any sense to me.

Think about it.

SELECT * FROM content WHERE data_language = 'English' AND data_language = 'Both'

This statement willnever return any records. That WHERE condition is evaluated for each row independently. You are not going to have any rows where the data_language contains both English and Both.

This statement would be more correct:

SELECT * FROM content WHERE (data_language = 'English' OR data_language = 'Both')

Now, when you combined that with the need to return titles and slogans, again, you would have a statement like this:

SELECT * FROM content WHERE (data_language = 'English' OR data_language = 'Both') AND (data_type = 'titles' OR data_type = 'slogan')

Terri|||yes you are very right there...

how can i get english and both when i can only have one value in a field

so when u lot say i need english or both to return either u are right and i'm wrong, this logic stuff is like one big puzzle to me.

reason i thot u lot was wrong is because i got lots more data than intended, which means i need more filters or sorting.

thanks for pointing that out, again... my apologies for being stupid!

Analysis Services stops working

We are sporadically seeing analysis services cubes stop responding for no apparant reason. This has happened 3 times in the last 5 days. There are no errors in the events logs, and no dump files to be found anywhere on the server.

Once this happens, If I go into SQL Server Management Studio and try to browse any of the cubes, I get the following error message:

"Unexpected Error: Server: The operation has been cancelled". This message appears under the "Dimension" column heading in the browser pane.

In the detail fields portion of the browser pane is the message:

"The query could not be processed".

We are running Enterprise edition, 64-bit platform, Service Pack 2. It works great most of the time...just don't have any idea how to troubleshoot this further.

Anyone have any suggestions?

This sounds a lot like the automated query cancellation behavior added in SP2 for the case where allocations are failing and the server is unable to shrink memory. Still, we should only hit that case once unless something else has gone wrong in the server. What does the msmdsrv process's memory usage look like when it's failing? I'm interested in process private bytes, and also the MSAS cleaner memory shrinkable and nonshrinkable numbers.

- Brad

|||Thanks for respondibng. I will take a look at the memory usage next time it fails. Most likely in the next day or two.|||

Here's what I found under MSAS memory counters in Performance Monitor. (FYI, total memory on the server is 8GB)

Total Memory:

Avg: 8602630

Max: 8605659

NonShrinkable:

Avg: 8601991

Max: 8604350

Shrinkable:

Avg: 1347

Max: 1445

|||

OK, so that means that the service is in fact out of memory, and for some reason, it's unable to reclaim the memory. This explains the behavior you're seeing, but it's not at all clear why killing sessions isn't making some of that nonshrinkable memory into shrinkable memory.

It might be useful to look at the output of DISCOVER_MEMORYUSAGE to try to characterize the memory in use, but it's likely that I would need more information to diagnose the issue more accurately. It's probably time to open a support ticket on this one, as I would most likely need to see a memory dump to determine where and why the memory is tied up.

- Brad

Analysis Services Plugin Debug

Hi!

I am working on an AS Algorithm Plugin . I want to debug it in the AS Server, but I cant figure out how it works. Now I every time install and deploy the plugin, but if I catch an exception, it just shows me the message, not the stack trace. Anyone, something friendlier method?

Sorry for my poor English!

Bye,
Laci

To debug, just attach the debugger to the msmdsrv.exe process.

Do you use managed or native code to implement your plug-in? Also, are you using one of the samples or developing from scratch? If you provide more details, there could be some easier ways to catch errors

|||Thank You. It fully solved my problem!

Analysis Services Plugin Debug

Hi!

I am working on an AS Algorithm Plugin . I want to debug it in the AS Server, but I cant figure out how it works. Now I every time install and deploy the plugin, but if I catch an exception, it just shows me the message, not the stack trace. Anyone, something friendlier method?

Sorry for my poor English!

Bye,
Laci

To debug, just attach the debugger to the msmdsrv.exe process.

Do you use managed or native code to implement your plug-in? Also, are you using one of the samples or developing from scratch? If you provide more details, there could be some easier ways to catch errors

|||Thank You. It fully solved my problem!

Analysis Services Perfomance Issues (when open a cube definition)

Hi

we are dealing whit a perfomance issue in Analysis Server 2005 working with olap cubes. This cubes are part of a solution with a view (91 tables) and every time we try to access some meta data of the cube in the Visual Studio 2005 (i.e.: Calculations, KPIs, etc) it takes a lot of time to read the metadata of the cube. The cubes are not so big, but every time we try to get the cube metadata we have problems.In Example; it takes more than 3 minutes to get the structure of the cube in the browse section; but after this we can easily drop fields to the sections of the cube designer and watch the cube data and calculations.

We have the same problem in Excel 2007 when we try to connect to the Analysis Services database and read some information; when we get the definition of the cubes, then the data takes a normal time to be filled.

Does anybody has some ideas or suggestions ?

Regards

Bruno

Do you have any ROLAP dimensions or a lot of security roles?|||

Hi Jeffrey

no, we currently have 1 one that call an advanced function and uses a .Net Assembly in order to check the security for some objects. I don't know if this may affect the "browser" perfomance.

Regards

Bruno

|||

Do you have dimensions with ragged hierarchies? Are you on SQL Server 2005 RTM, SP1 or SP2?

You can attach the Profiler to the AS Server to identify which queries and/or commands take a long time to complete.

Analysis Services Deployment Security Issue

We have been working on moving our database from SQL2000 to 2003 the last couple of months. We are now working on our cube migration and have attempted a few deployments. The problem is, every time we try to process the deployed cube it gives us the following error:

The following system error occurred: Logon failure: unknown user name or bad password.

We only have one connection in this deployment and have tried with various cubes all the way down to one dimension.

Our connection always tests successfully on the dialog box itself, we have attempted both, windows and SQL authentication, and both always yield the same error.

Up to this point we have not able to process any cube we have attempted.

As far as I can tell our SQL and Analysis services both are set to the same instance name, but I’m thinking this may be a configuration issue, since it seems that internally Analysis services just cant contact SQL services.

Anyone has an idea of what may be wrong with the configuration?

Have you tried setting the Impersonation Information to Default, and including the user name and password in the connect string?|||Hmm, I had not tried that. Attempted it now and it worked.

This solves my problem but now I question why would the other settings not work?

Analysis services connection

Hi!!
1. I am working with Analysis services 2005 and I would want to know
if, Is it recommendable to separate the DB server from of server AS?,
Since when I consult the cubes diminishes the performance the server of
database.
2. How I can to create news administrators OLAP in SQL 2005?
Is there one book what you can recommend on how connecting to me of
remote form?
Thanks,
Cecilia
Chile
1. Yes, you can run SQL and AS on separate machines, just be aware that
each machine would require its own SQL license.
2. To create add a user to the administration group at the server level
for AS you need to open up SQL Server Management Studio, connect to your
server, right click and select "properties" and then go to the security
settings and add the user to the list there.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***
|||The connection of server work, thanks Darren.
Now I have other doubts:
1. if I have SQL server and AS on separate machines and I execute a MDX
query for a rolap cube, consume this query too many resources on the BD
server?
2. it really improves the performance of my server, if i separate it in
the 2 mentioned machines?
3. What percent of the load of a query takes the BD machine, (aprox)?
Thanks,
Cecilia

Analysis services connection

Hi!!
1. I am working with Analysis services 2005 and I would want to know
if, Is it recommendable to separate the DB server from of server AS?,
Since when I consult the cubes diminishes the performance the server of
database.
2. How I can to create news administrators OLAP in SQL 2005?
Is there one book what you can recommend on how connecting to me of
remote form?
Thanks,
Cecilia
Chile1. Yes, you can run SQL and AS on separate machines, just be aware that
each machine would require its own SQL license.
2. To create add a user to the administration group at the server level
for AS you need to open up SQL Server Management Studio, connect to your
server, right click and select "properties" and then go to the security
settings and add the user to the list there.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***|||The connection of server work, thanks Darren.
Now I have other doubts:
1. if I have SQL server and AS on separate machines and I execute a MDX
query for a rolap cube, consume this query too many resources on the BD
server?
2. it really improves the performance of my server, if i separate it in
the 2 mentioned machines?
3. What percent of the load of a query takes the BD machine, (aprox)?
Thanks,
Cecilia|||In article <1163085164.731268.160000@.e3g2000cwe.googlegroups.com>,
Hoody.cu@.gmail.com says...
> The connection of server work, thanks Darren.
> Now I have other doubts:
> 1. if I have SQL server and AS on separate machines and I execute a MDX
> query for a rolap cube, consume this query too many resources on the BD
> server?
>
Sorry, but ROLAP cubes are the one exception. In the case of ROLAP, the
AS server is really just a thin semantic layer that translates MDX into
SQL and runs some pretty large queries against the DB server

> 2. it really improves the performance of my server, if i separate it in
> the 2 mentioned machines?
>
This really only makes a difference when AS is using MOLAP storage as
then it has its own copy of the data and may not need to query the DB
server at all after it has gone through it's processing stage

> 3. What percent of the load of a query takes the BD machine, (aprox)?
>
For ROLAP probably 99%
For MOLAP 0%
(after the data has been read once during the processing stage)
I assume that you are using ROLAP because you need "real-time" data. I
don't know what your exact requirements are, or if you have looked into
any of this before, but AS has a number of ways of implementing "near
real-time" systems. So that if it works with your data and your users
can accept some degree of "lag" in the update of their reports you
should be able to get an increase in the performance of your MDX queries
and reduce the load on your DB server.
If you are interested in this there are a couple of different features
you could read up on.
1) proactive caching - can automatically keep your MOLAP storage up to
date, based either on notifications or polling the source system.
2) You can use incremental processing and schedule a job to run on a
periodic basis to refresh the MOLAP store.
These options do not fit all circumstances and sometimes you may need to
use ROLAP storage, but if you can avoid ROLAP you will probably have a
much better experience.
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

Analysis Services and Reporting Services

Hello !
I am currently evaluating ability of Reporting Services (RS) to work
with output of MDX queries. RS looks very appealing when working with
relational output. But working with multidimensional output is somewhat
cumbersome. I haven't found any examples so far running against Analysis
Services.
Any thoughts\sources of information on this subject would be greatly
appreciated. We have to decide whether go with RS as UI to display output
from the cube .
Thank you in advance,
Igor.There's no real integration for AS currently in RS - just the OLAP provider
for OLE DB. This means you need to re-format the data you retrieve from cube
s and there's no query builder type implementation. What you could do is wri
te a reporting tool in .NET
to create the rdl (examples in the RS books online) and supply the MDX via a
client tool - such as a web page or desktop app. That way you can handle th
e formatting etc in the reporting tool, rather than doing every time in the
Report Designer.
HTH
Phil.|||Thanks a lot,
Igor.
"Phil Austin" <anonymous@.discussions.microsoft.com> wrote in message
news:E99A5AFD-CB71-4878-BD98-4380C528CBFC@.microsoft.com...
> There's no real integration for AS currently in RS - just the OLAP
provider for OLE DB. This means you need to re-format the data you retrieve
from cubes and there's no query builder type implementation. What you could
do is write a reporting tool in .NET to create the rdl (examples in the RS
books online) and supply the MDX via a client tool - such as a web page or
desktop app. That way you can handle the formatting etc in the reporting
tool, rather than doing every time in the Report Designer.
> HTH
> Phil.|||There are some examples in the samples folder of Reportin service to
fetch from OLAP. Try it
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I would like to suggest u to use the MS Reporting Services addin of
Panorama NovaView to have MS Analysis Services views in MS Reporting
Services with a simple wizard. On our website www.gmsbv.nl you will
find some screenshots.
Marco
"imarchenko" <imarchenko@.hotmail.com> wrote in message news:<#$1yPagBEHA.1600@.tk2msftngp13.
phx.gbl>...
> Hello !
> I am currently evaluating ability of Reporting Services (RS) to work
> with output of MDX queries. RS looks very appealing when working with
> relational output. But working with multidimensional output is somewhat
> cumbersome. I haven't found any examples so far running against Analysis
> Services.
> Any thoughts\sources of information on this subject would be greatly
> appreciated. We have to decide whether go with RS as UI to display output
> from the cube .
>
> Thank you in advance,
>
> Igor.

Analysis Services and Proclarity

We have following environment setup: SQL Server 2005, SSAS 2005 and Proclarity working together to provide some business intelligence.

I have login problems with Proclarity Dashboar server.

What happens is after user logs in into the dashboard server and leaves the browser (IE6) idle for some period of time, the user gets logged out (due to session time out). Once logged out, the user can't log back in as themselves. Now if the user logins with some other userid and logs out properly, then he/she can log back in again as themselves.

Dashboard uses the windows userid/password for logins.

Anybodys help would be greatly appriciated.

Thanks

Giri

Looks like you are using basic authentication for the ProClarity Dashboard server? You have the option to use windows integrated security!

My advice is to contact http://www.proclarity.com/services/support.asp about this issue. You will need a valid account for this. As a customer you will also have access to information on two ProClarity communities. I have seen a lot of discussions and recommendations of how to set up windows integrated security(Active Directory) with ProClarity Dashboard Server 6.2

It is impossible to write all the details you will have to know here.

HTH

Thomas Ivarsson

Analysis Services 2005 Deployment Wizard

I am working with SQL server 2005 analysis and reporting services. I am

instructed to create a cube for a database using analysis services and
then replicate it so as to produce reports online by reporting services

when requested by clients. I am able to create the cube and also deploy

the report made, in HTTP separately. But the following doubts arise
during the Cube deployment:

The Cube was created as per the requirements by my team lead. Then I
used the deployment wizard in the analysis services 2005 to convert it
into XML script. Using the SQL Management Studio I opened it as
Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query and executed it. What has to be done after this in
order to use it as a production server?

Once the production server is setup and the connection is made with the

staging server, How can we set the timings to when the updating of the
production server has to be set in terms of hours, days or weeks?

Hoping these doubts would be clarified as early as possible.

Take a look at the Synchronization functionality in Analysis Services. It allows you to synchronize on database to another using single command.

In your case I can imagine, you having updates done to a single master server, and then running sycnhronization commands against multiple servers telling them to synchronize the changes happend on the master.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.