Credentials used to run this report are not stored

Hi all,

When I create a new report subscription in report manager, I got this error

Credentials used to run this report are not stored

any idea?

Thanks,

Jone

Can you go to report properties -> Data Sources and make sure that "Connect Using Credentials stored securely in the report server" is selected and valid user name and password provided?

Thanks,
Sharmila

|||

Thanks for your response.

By the way, which name and password should I input. I tried to using ASPNET as the username (or 'sa' and the password to access sql aerver) but it failed, and gave error message bad user name or password.

Thanks,

jone

|||

For example, if the below is your connection string, you need to enter a user name and password to access the data source.

data source=<ServerName>; initial catalog=<SampleDB>

Thanks,
Sharmila

|||

Thanks, Sharmila.

I use user name = 'sa' and password = 'access' to access my datasource, and I tried to use them in 'Credentials stored securely in the report server', and checked both or first ckeckbox under this selection, and I got

Logon failed. (rsLogonFailed)

Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)|||

Jone,

If you select the 'Credentials stored securely in the report server', you should not check the boxes for 'Use as Windows credentials when connecting to the data source' or 'Impersonate the authenticated user after a connection has been made to the data source'. After entering user name = 'sa' and password = 'access' , press Apply at the bottom and try to view your report.

If this still doesn't work, you should try to login to the database using user name = 'sa' and password = 'access' through Management Studio to see if that works.

Hope this helps.

Jarret

|||

Thanks, Jarret.

According to your suggestion, I configured my datasource and ran my report, and I got error

Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integrated Security unless connecting via http or https.|||

Could you use a Windows domain account and it's password? Then, you will need to check the box for 'Use as Windows credentials when connecting to the data source' also.

Jarret

|||

Thank you, Jarret.

I used my user name an dpassword that I use to login my machine, and checked the 'use as windows credentials when connectingto the data source', and the subscription has been created.

But I can't find the e-mail extension on the report. How can the e-mail extension or icon be availale on the report?

BTW, in sql server-->configuration tools--.report service configuration -->email settings,

I input my e-mail address as the sender's address, and the name of our exchange server name as the 'smpt server' name, or should I input "default virtual smtp server' as the smtp server name?

Thanks,

Jone

|||

Hi, more question:

since the application will be used by others, what should I handle the credential issues since I can't use my username and password there?

Thanks,

Jone

|||

I don't think there is an email extension for Reporting Services, you might be able to find something out there that will add this functionality though. I haven't heard of any, but I haven't looked either.

I think you will need to leave your exchange server in the SMTP Server name.

Jarret

|||

You have you have the credentials stored in order to create a subscription.

I just did a test and it seems that the stored credentials are not used when a user runs the report from the browser. So, when your users run the report, you will still see their name in the ExecutionLog table as having executed the report. Also, you can restrict access to the report for specific users as well, just as normal.

My test was: I set the stored credentials as my account, then had a co-worker run the report. Then, I looked in the ExecutionLog, it showed my co-workers account.

Hope this helps.

Jarret

|||

Thank you so much, Jarret. You save me days of time

Jone

|||

Glad I could help Jone, can you mark this one as answered so others can view the solution?

Jarret

|||No problem!

Creatng Charts Using scripts in sql server 2005 express

Hi all,

I want to Create charts for database table entries in sql server 2005 express edition, by using the scripts only not through wizards...

Plz help me for the above topic

Thanks & Regards,
Srinivaswhat scripts are you refering to? TSQL?|||Hi,
I just want to prepare the charts with out using wizards..., by using some stored proceduers we can get the data from database and then create the charts with some code( script).., that scripts r in visual studio or any thing ,

give me some idea about that one (or) give me some links related to this topic..

thanking you very much..

regards,
Srinivas|||

Hi,

I really did not understand what you are talking about, but perhaps you are referencing the ReportViewer controls..http://msdn2.microsoft.com/en-us/library/ms251671.aspx

regards

|||

T-SQL only returns data, you will need to use some other program to generate the charts from the data. You could use the Reporting controls in VS but I believe the report designer for VS is only available in VS Pro and as a download for VWD Express. (The other Express products don't have report design as a feature.) You can also consider Report Server, which is part of SQL Express Advanced. You will additionally need the SQL Express Toolkit to get the report design projects as part of BIDS.

Office also provides charting capabilities in both Excel and Access. Just retrieve the data and create your chart. In Office 2003 I found Access to have slightly better charting capabilities because of the use of the Office Web Components, but Excel has better analytical tools if you want to manipulate the data prior to creating your charts.

There are also a number of third-party tools and controls that you should be able to find by searching the web.

Regards,
Mike Wachal

Creation of two tables in a stored procedure

Hi,

I was wondering if there was a way to create two temp tables within the same stored procedure.

Can we have two create statements one after the other?

Thanks

Crazy:

I have done so many, many times.

Dave

|||

what exactly error you are getting?

i too did so many number of tmes.

Gurpreet S. Gill

creation of table that type of format........

hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
-------------
empid ph
-- ----------
office Mobile home
--- --- ---
100 9380768532 98455555 98822213

--------------

i want above type of format and then how to insert into values that
phone table . please help me.surya (suryaitha@.gmail.com) writes:
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:
> Phone table
> -------------
> empid ph
> -- ----------
> office Mobile home
> --- --- ---
> 100 9380768532 98455555 98822213
> --------------
> i want above type of format and then how to insert into values that
> phone table . please help me.

The INSERT statement would be:

INSERT phonetable (empid, office, mobile, home)
VALUES (100, '9380768532', '98455555', '98822213')

If you are using some application environment, it is not unlikely that
the Client API offers some interface that constructs the INSERT statement
under the covers, but gives you a "nicer" interface.

The table design as such is not unquestionable. You have indicated
that office, modbile and home are infact subfields of ph, but there
is no such thing in a database table.

Depending on your requirements, it may be better to do:

CREATE TABLE phonetypes
(phonetype char(3) NOT NULL,
phonetypename varchar(20) NOT NULL,
CONSTRAINT pk_phonetypes PRIMARY KEY(phonetypeid))

INSERT phonetypes (phonetypes, phonetypename)
VALUES ('OFC', 'Office')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('MOB', 'Mobile')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('HOME', 'Home')

CREATE TABLE phonenumbers
(empid int NOT NULL,
phoneno varchar(200) NOT NULL,
phonetype char(3) NOT NULL,
isdefault bit NOT NULL,
CONSTRAINT pk_phonenumbers PRIMARY KEY(empid, phoneno),
CONSTRAINT fk_phonetype FORIEGN KEY (phonetype)
REFERENCES phonetypes (phonetype),
CONSTRAINT fk_employees (empid)
REFERENCES employees(empid))

There would be a trigger on phonenumbers, that enforces that isdefault may
be 1 for at most one combination of (empid, phoneno).

This design buys you more flexibility. Some people have more than one mobile
phone. It also permits you to add other telephone types such as FAX or
IP telephone like Skype. Since on services like Skype you don't have
traditional telephone numbers, but, as I understand it, something that looks
more like an email address, I've made phoneno varchar(200).

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"surya" <suryaitha@.gmail.com> wrote in news:1144477944.400250.34550
@.z34g2000cwc.googlegroups.com:

> hello all
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:

Aren't you going to fail your class if you don't
do your own homework?

Creation of Jobs...

I wanted to created a few jobs in my database.
How do i create it using the Batch files???
Thanks,
Sandu_BangaloreLook up sp_add_job in BOL...

Or better yet...create one manually in EM and then script it ( I always like the easy way out)

Creation of Instance.

I have been doing research on how to create a new instance with MSDE. Does anyone know how to create one in an automated fashion using one of the standard tools that are installed with MSDE 2000A? I suspect that is accomplished with cnfgsvr.exe but have
not found any examples on the Internet. Thanks.
Message posted via http://www.sqlmonster.com
hi Thomas,
Thomas Vollmer via SQLMonster.com wrote:
> I have been doing research on how to create a new instance with MSDE.
> Does anyone know how to create one in an automated fashion using one
> of the standard tools that are installed with MSDE 2000A? I suspect
> that is accomplished with cnfgsvr.exe but have not found any examples
> on the Internet. Thanks.
in order to install a brand new MSDE instalnce you only have to run it's
setup.exe bootstrapp installer ..
MSDE does not provide support for response files nor for for unattended
installations...
http://msdn.microsoft.com/library/de...stsql_84xl.asp
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

creation of IDTSOutput90 and associated objects at run-time

is there a way to both create and then update IDTSOutput90 instances and associated objects at run-time from metadata?
i can create an IDTSOutput90 instance successfully at runtime via either IDTSOutputCollection90.New() or CManagedComponentWrapper.InsertOutput(), but the IDTSOutput90 instance returned will not allow me to set any properties (i.e., HRESULT == 0xC004801D)

allen
You can not change the package at runtime only at design time. That even the ouptut can be created at runtime is a bug that you should file.

Thanks,
Matt

Creation of Database from dump

Hello,
I have a SQL database dump with me. I would like to create a database using the dump. Could somebody help me with the steps?
Thanks :)Restore database <dbname> from disk = <filename> with
move <dbfilename> to <physicalfilename>
,move ...
,stats
,replace

or refer to Books OnLine for more information.

Creation of credentials and certificates to protect a DB

Hi, i want to know if its posible to create credentials or certificates in order to protect a SQL 2005 data base.

Because if someone Buckups one of my DBs from my server, and try to restore it in orther server i dont want they to see my DB information because he dont have the correct credentials or certificates for it.

This is posible?. if is, How i do it ?

Best Regards.

You can use encryption, so that if a database is stolen, the thief cannot retrieve the original data. For more information, please consult the resources mentioned in the second post of the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

Also, note that if you are trying to protect against a machine administrator or a sysadmin, then encryption may not be sufficient. You can check the following thread for this aspect: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

Thanks
Laurentiu

|||

I dont know if i can encrypt al the DB, because the information would not be retrived correct to the front end, and Encrypting/decrypting all the time the DB would take lots of time.

Regards.

|||

You don't need to encrypt the entire database, you just need to encrypt the information in it that you are trying to protect.

If you don't want to rely on encryption, then the only secure solution is to control access to the database files by limiting access to them and by controlling the access that you grant in SQL Server.

Thanks
Laurentiu

|||Unfortunately, that particular feature doesn't currently exist. You still need to apply physical security to your backups. It's a feature that I'd like to see in the next version of SQL Server though. Right now, the best that you can do is to encrypt data which can not be decrypted, even after restoring on another instance of SQL Server unless they have the correct database key loaded and then the correct certificate (if you're using certificate based encryption).

Creation of credentials and certificates to protect a DB

Hi, i want to know if its posible to create credentials or certificates in order to protect a SQL 2005 data base.

Because if someone Buckups one of my DBs from my server, and try to restore it in orther server i dont want they to see my DB information because he dont have the correct credentials or certificates for it.

This is posible?. if is, How i do it ?

Best Regards.

You can use encryption, so that if a database is stolen, the thief cannot retrieve the original data. For more information, please consult the resources mentioned in the second post of the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

Also, note that if you are trying to protect against a machine administrator or a sysadmin, then encryption may not be sufficient. You can check the following thread for this aspect: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

Thanks
Laurentiu

|||

I dont know if i can encrypt al the DB, because the information would not be retrived correct to the front end, and Encrypting/decrypting all the time the DB would take lots of time.

Regards.

|||

You don't need to encrypt the entire database, you just need to encrypt the information in it that you are trying to protect.

If you don't want to rely on encryption, then the only secure solution is to control access to the database files by limiting access to them and by controlling the access that you grant in SQL Server.

Thanks
Laurentiu

|||Unfortunately, that particular feature doesn't currently exist. You still need to apply physical security to your backups. It's a feature that I'd like to see in the next version of SQL Server though. Right now, the best that you can do is to encrypt data which can not be decrypted, even after restoring on another instance of SQL Server unless they have the correct database key loaded and then the correct certificate (if you're using certificate based encryption).

Creation of a Setup Project

Is possible to create a Setup Project of a Reporting Services Project to install it in another Server ?
You may be better asking this Q in the reporting services newsgroup as this
group deals with Replication.
"Asphart" <anonymous@.discussions.microsoft.com> wrote in message
news:EA5A329A-9D6D-4E75-9721-4691A19E8A68@.microsoft.com...
> Is possible to create a Setup Project of a Reporting Services Project to
install it in another Server ?

Creation of "global" variables in runtime.

Is it possible to create "global" (package scope) variables in runtime in SSIS packages? If yes, please give some examples.

Hi Yuriy:

Not sure I understand your question. Are you asking how to create variables that are consumed at runtime, or to have on-the-fly variables created as a package runs?

The first type is easy -- go to the Control Flow tab, make sure nothing is selected, and right-click anywhere in the white space outside of an object, and select "Variables" from the shortcut menu. Alternatively, select "Variables" from the SSIS menu at the top of the main window.

The dialog you see is pretty self-explanatory. Click on the blue "x" button in the toolbar to add a new variable. Give it a name, set its data type and initial value. Delete a variable by selecting it in the list and clicking the X button with the red "x" over it.

Once you create a variable it can be referenced through code in a Script Task or expressions in other tasks. Most often you must reference the variable as @.[User::<VariableNameHere>]. Notice the two colons (::) between "User" and the variable name, and the @. sign in front of the expression.

If an object in the Control Flow tab is selected when you invoke the Variables command, the variable is local to that object. I do this now and then on containers when I need a variable that is used to share data among the tasks within a container.

I don't believe it's possible to create entirely new variables at runtime.

|||No object can be created at runtime inside the packge.

Creating/Running DTS Packages with MSDE

Hi,
Is it possible to create DTS packages with MSDE? If so, how?
Would you execute them via the "dtsrun" command-prompt utility?
Thanks very much,
Mark Holahan
MSDE does not come with the tools that are necessary to create DTS packages.
For that you will need to user Enterprise Manager, which come with the full
versions of SQL Server, including the Developers version. MSDE instances can
store DTS packages in their repository and MSDE 2000 comes with the
dtsrun.exe utility, allowing you to execute packages.
Jim
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
news:e93IFVWBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to create DTS packages with MSDE? If so, how?
> Would you execute them via the "dtsrun" command-prompt utility?
> Thanks very much,
> Mark Holahan
>

Creating XML using For XML Explicit

Hi,
I am stuck and I thought somebody can help me here. I am trying to
construct a query from a a table to create an XML result. My table has
the following columns
| Book | Author | Chapter | Page | URL | Description |
I am trying to get it to return
Any ideas on how to construct the query?
Thanks,
Mo
<?xml version="1.0" encoding="utf-8" ?>
<siteMap>
<siteMapNode title="Home" url="~/home.aspx" description="Go To
Home">
<siteMapNode title="Book 1" url="~/book1/book1.aspx"
description="Go To Book 1">
<siteMapNode title="Chapter 1" url="~/book1/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book1/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book1/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book1/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page1/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page1/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page1/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page2/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page2/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page2/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book1/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book1/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Book 2" url="~/book2/book2.aspx"
description="Go To Book 2">
<siteMapNode title="Chapter 1" url="~/book2/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book2/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book2/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book2/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book2/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book2/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMap>
> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
You could try something like this
Select a.Title as "@.Title" , a.Url as "@.Url", a.description as "@.Description
",
cast((Select b.Title as "@.Title" , b.Url as "@.Url" , b.description as "@.De
scription" from sitemap b
where b.parent = a.id
for xml Path('siteMapNode')) as xml)
from sitemap a
where a.parent is null order by a.id
for Xml path ('siteMapNode'), root('siteMap')
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities|||Hi Mo
Did Gary's answer using the easier to use FOR XML PATH mode in SQL Server
2005 help or do you need the explicit mode solution?
Thanks
Michael
"Mo" <le_mo_mo@.yahoo.com> wrote in message
news:1172481947.758227.123760@.k78g2000cwa.googlegroups.com...
> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
>

Creating XML using For XML Explicit

Hi,
I am stuck and I thought somebody can help me here. I am trying to
construct a query from a a table to create an XML result. My table has
the following columns
| Book | Author | Chapter | Page | URL | Description |
I am trying to get it to return
Any ideas on how to construct the query?
Thanks,
Mo
<?xml version="1.0" encoding="utf-8" ?>
<siteMap>
<siteMapNode title="Home" url="~/home.aspx" description="Go To
Home">
<siteMapNode title="Book 1" url="~/book1/book1.aspx"
description="Go To Book 1">
<siteMapNode title="Chapter 1" url="~/book1/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book1/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book1/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book1/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page1/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page1/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page1/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter2/
page2/paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter2/
page2/paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter2/
page2/paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book1/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book1/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book1/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Book 2" url="~/book2/book2.aspx"
description="Go To Book 2">
<siteMapNode title="Chapter 1" url="~/book2/chapter1/
chapter1.aspx" description="Go To Chapter 1">
<siteMapNode title="Page 1" url="~/book2/chapter1/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter1/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 2" url="~/book2/chapter2/
chapter2.aspx" description="Go To Chapter 2">
<siteMapNode title="Page 1" url="~/book2/chapter2/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter2/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
<siteMapNode title="Chapter 3" url="~/book2/chapter3/
chapter3.aspx" description="Go To Chapter 3">
<siteMapNode title="Page 1" url="~/book2/chapter3/page1/
page1.aspx" description="Go To Page 1">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
<siteMapNode title="Page 2" url="~/book2/chapter3/page2/
page2.aspx" description="Go To Page 2">
<siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
paragraph1.aspx" description="Go To Paragraph 1"/>
<siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
paragraph2.aspx" description="Go Paragraph 2"/>
<siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
paragraph3.aspx" description="Go Paragraph 3"/>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMapNode>
</siteMap>

> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
You could try something like this
Select a.Title as "@.Title" , a.Url as "@.Url", a.description as "@.Description",
cast((Select b.Title as "@.Title" , b.Url as "@.Url" , b.description as "@.Description" from sitemap b
where b.parent = a.id
for xml Path('siteMapNode')) as xml)
from sitemap a
where a.parent is null order by a.id
for Xml path ('siteMapNode'), root('siteMap')
BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
|||Hi Mo
Did Gary's answer using the easier to use FOR XML PATH mode in SQL Server
2005 help or do you need the explicit mode solution?
Thanks
Michael
"Mo" <le_mo_mo@.yahoo.com> wrote in message
news:1172481947.758227.123760@.k78g2000cwa.googlegr oups.com...
> Hi,
> I am stuck and I thought somebody can help me here. I am trying to
> construct a query from a a table to create an XML result. My table has
> the following columns
> | Book | Author | Chapter | Page | URL | Description |
> I am trying to get it to return
> Any ideas on how to construct the query?
> Thanks,
> Mo
>
> <?xml version="1.0" encoding="utf-8" ?>
> <siteMap>
> <siteMapNode title="Home" url="~/home.aspx" description="Go To
> Home">
> <siteMapNode title="Book 1" url="~/book1/book1.aspx"
> description="Go To Book 1">
> <siteMapNode title="Chapter 1" url="~/book1/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book1/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book1/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book1/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page1/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page1/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page1/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter2/
> page2/paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter2/
> page2/paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter2/
> page2/paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book1/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book1/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book1/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book1/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book1/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book1/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Book 2" url="~/book2/book2.aspx"
> description="Go To Book 2">
> <siteMapNode title="Chapter 1" url="~/book2/chapter1/
> chapter1.aspx" description="Go To Chapter 1">
> <siteMapNode title="Page 1" url="~/book2/chapter1/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter1/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter1/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter1/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter1/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 2" url="~/book2/chapter2/
> chapter2.aspx" description="Go To Chapter 2">
> <siteMapNode title="Page 1" url="~/book2/chapter2/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter2/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter2/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter2/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter2/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> <siteMapNode title="Chapter 3" url="~/book2/chapter3/
> chapter3.aspx" description="Go To Chapter 3">
> <siteMapNode title="Page 1" url="~/book2/chapter3/page1/
> page1.aspx" description="Go To Page 1">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page1/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page1/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page1/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> <siteMapNode title="Page 2" url="~/book2/chapter3/page2/
> page2.aspx" description="Go To Page 2">
> <siteMapNode title="Paragraph 1" url="~/book2/chapter3/page2/
> paragraph1.aspx" description="Go To Paragraph 1"/>
> <siteMapNode title="Paragraph 2" url="~/book2/chapter3/page2/
> paragraph2.aspx" description="Go Paragraph 2"/>
> <siteMapNode title="Paragraph 3" url="~/book2/chapter3/page2/
> paragraph3.aspx" description="Go Paragraph 3"/>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMapNode>
> </siteMap>
>

Creating XML Document from SQL Server

Would anyone please help me , how to create XML document
for a specific record from the SQL Server database.
Is there any builtin tool available. If so, how to access
it.
Would I need to create a program to generate XML
document. If so, could you suggest the development
package/code examples...
Appreciate the info.
Would something along the line of
select * from T for xml auto
or
select * from T for xml raw
help?
Best regards
Michael
"Sat" <anonymous@.discussions.microsoft.com> wrote in message
news:442c01c42b8b$f0669640$a001280a@.phx.gbl...
> Would anyone please help me , how to create XML document
> for a specific record from the SQL Server database.
> Is there any builtin tool available. If so, how to access
> it.
> Would I need to create a program to generate XML
> document. If so, could you suggest the development
> package/code examples...
> Appreciate the info.

Creating views under INFORMATION_SCHEMA in SQL 2005

Hello to everyone,

I have a couple of useful views created under INFORMATION_SCHEMA in SQL 2000. Now I am trying to create them in SQL 2005 environment. As an example simply run the following code as "sa" in SQL 2005 (it works in SQL 2000):

CREATE VIEW INFORMATION_SCHEMA.MyTABLES
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

In my environment I received the following error:

Msg 2760, Level 16, State 1, Procedure MyTABLES, Line 3
The specified schema name "INFORMATION_SCHEMA" either does not exist or you do not have permission to use it.

I tried to create the view under other schema and change the owner to INFORMATION_SCHEMA and some other techniques that worked in SQL 2000 (as a direct update of sysobjects). Also I tried (using the system stored procedures as well as the GUI) to change the login/user/schema chain in order to overcome the problem with no success. Please let me know if you can run the above code successfully or if you have any other suggestions.

Peter Petrov

Hi,

The information views are not intended to be extendible. The Information Schema is an ANSI specification for obtaining metadata from your database. Since it is a standard it is ment to be used across multiple types of databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thank you, Geert.

We all know that but sometimes as we also know we need some of the benefits of the system objects for temporary “relief”. So the question is not “To do or not to do that?” but “How we can do that?”. That’s why the “back doors” exist J

creating views in mssql

hey guys,
can you share an example of how I can create views in MSSQL where the table
is stored in oarcle server.
thanks guys.
neil
Hi,
For that you have to create a linked server to the Oracle server.
see this link to get info on creating linked Server to ORACLE
http://support.microsoft.com/default...b;EN-US;280106
After that create the view from sql server .
Sample:
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
Thanks
Hari
MCDBA
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> hey guys,
> can you share an example of how I can create views in MSSQL where the
> table is stored in oarcle server.
> thanks guys.
> neil
>
|||Hi Hari,
Thanks for the link. I'm gonna try it tomorrow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEf6BjYqEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> For that you have to create a linked server to the Oracle server.
> see this link to get info on creating linked Server to ORACLE
> http://support.microsoft.com/default...b;EN-US;280106
> After that create the view from sql server .
> Sample:
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> Thanks
> Hari
> MCDBA
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
>

creating views in mssql

hey guys,
can you share an example of how I can create views in MSSQL where the table
is stored in oarcle server.
thanks guys.
neilHi,
For that you have to create a linked server to the Oracle server.
see this link to get info on creating linked Server to ORACLE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
After that create the view from sql server .
Sample:
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
Thanks
Hari
MCDBA
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> hey guys,
> can you share an example of how I can create views in MSSQL where the
> table is stored in oarcle server.
> thanks guys.
> neil
>|||Hi Hari,
Thanks for the link. I'm gonna try it tomorrow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEf6BjYqEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> For that you have to create a linked server to the Oracle server.
> see this link to get info on creating linked Server to ORACLE
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> After that create the view from sql server .
> Sample:
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> Thanks
> Hari
> MCDBA
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
>> hey guys,
>> can you share an example of how I can create views in MSSQL where the
>> table is stored in oarcle server.
>> thanks guys.
>> neil
>

Creating Views

Hai Guys,
Can i create a view by joining 2 tables that r on two different database.
Good Day.
Thanks.
Shabuyes, it's possible.|||How do i do this.
I have one server with 2 databases.
my DB server is Sqlserver2000.

Please Help.

Thanks .|||This should probably do it, if you have linked the servers ...

create view NameOfTheView as
select someField, someOtherField
from
ServerName1.DBName.dbo.TableName Server1,
ServerName2.DBName.dbo.TableName Server2,
where
Server1.Field1 = Server2.Field1 and
<insert some other suitable conditions here ...>|||Something like:SELECT s.sysadmin, d.owner
FROM msdb.dbo.sysdtspackages AS d
JOIN master.dbo.syslogins AS s
ON (s.name = d.owner)-PatP|||you need to run sp_addlinkedserver for the remote server
then run sp_addlinkedsrvlogin to create login maps
then invoke the four part name in the query

BOOKS ONLINE{sp_addlinkedserver}
BOOKS ONLINE{sp_addlinkedsrvlogin}
BOOKS ONLINE{Distributed Queries}

this can be done easily in the enterprise manager
SECURITY>LINKED SERVERS

this is kind of the way a partitioned view is created check out federated servers in books online|||you need to run sp_addlinkedserver for the remote server
then run sp_addlinkedsrvlogin to create login mapsDid I miss a meeting somewhere? When did a second server join this discussion?

-PatP|||Did I miss a meeting somewhere? When did a second server join this discussion?

-PatP

whoops
my bad

ignore my pre-post-question post that i had just posted