Adding attributes to the root element.

Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
..
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advanceVankayala wrote:

> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Adding attributes to the root element.

Hi,
I am using the following caluse to generate xml ... For XML Path
('Vendor'), root ('vendors'), elements xsinil.
Which is good and working ... the result some what
<Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Vendors ID='... > ...
...
</Vendors>
I would like to add more attributes to the root element 'Vendors'
1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
2. Last query execution time -->
xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
3. custom attributes like Region="California"
Query looks like:
SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
Please help ...
Thanks in advance
Vankayala wrote:

> I am using the following caluse to generate xml ... For XML Path
> ('Vendor'), root ('vendors'), elements xsinil.
> Which is good and working ... the result some what
> <Vendors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <Vendors ID='... > ...
> ...
> </Vendors>
> I would like to add more attributes to the root element 'Vendors'
> 1. xsd file --> xsi:noNamespaceSchemaLocation="VendorDetails.xsd"
> 2. Last query execution time -->
> xml_generation_timestamp="2007-07-25 13:41:00.370" by using getdate()
> 3. custom attributes like Region="California"
>
> Query looks like:
> SELECT TOP 10 PERCENT
> pv.VendorID AS '@.ID',
> pv.AccountNumber AS '@.AccountNumber',
> pv.Name,
> pv.ActiveFlag AS 'Details/@.ActiveFlag',
> pv.CreditRating AS 'Details/CreditRating',
> pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
> FROM
> PurchasingVendor pv
> FOR XML PATH('Vendor'), root('Vendors'), elements xsinil
One way to achieve that is by selecting the first query result into a
variable of type xml, then you can use the modify method to manipulate
the variable as needed, then you can select the variable:
DECLARE @.x xml;
SET @.x = (SELECT TOP 10 PERCENT
pv.VendorID AS '@.ID',
pv.AccountNumber AS '@.AccountNumber',
pv.Name,
pv.ActiveFlag AS 'Details/@.ActiveFlag',
pv.CreditRating AS 'Details/CreditRating',
pv.PreferredVendorStatus AS 'Details/PreferredVendorStatus'
FROM
PurchasingVendor pv
FOR XML PATH('Vendor'), root('Vendors'), elements xsinil, TYPE);
SET @.x.modify('
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
insert attribute xsi:noNamespaceSchemaLocation {"VendorDetails.xsd"}
into (/*)[1]
');
-- add further attributes if needed
SELECT @.x;

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Adding addition indexes on the subscriber tables

We have snapshot tables which are regenerated each night. Is there a way to
create additional indexes on the subscriber side since the vendor won't allow
us to create the indexes on the publisher?
Tom,
you could use a post-snapshot script for this.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||other than the excellent advice Paul has given you, you can use
sp_addscriptexec to create them on all subscribers deployed through a UNC.
Keep in mind that you want to minimize the indexes on your subscriber(s) to
improve performance of your replication solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSx7mh6PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Tom,
> you could use a post-snapshot script for this.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sp_addscriptexec works for transactional and merge, but not snapshot
Rgds,
Paul
|||stop making me look bad, Paul. I am perfectly capable of doing that on my
own.
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

Adding a user to a Read-Only database

Hey guys,

I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.

The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:

exec sp_change_users_login 'Update_One', 'YYY','YYY'

Except for I get the following error:

Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.

Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?Easiest would be to drop the login on your server, and re-create it with the SID of the user in the database. Alternatively, you can try to have the vendor drop the user and login on their end, and recreate it with your login's SID. The new SID will be brought to your server with the appropriate TLog restore.