Get all databases in a SQL db

Is it possible to programmatically get a list of all the databases in a SQL
server db ?
TIA,
-FatemaSELECT name FROM master..sysdatabases
Jacco Schalkwijk
SQL Server MVP
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
> Is it possible to programmatically get a list of all the databases in a
> SQL
> server db ?
> TIA,
> -Fatema|||Thanks for the prompt reply.
I also need answers for the following:
- Is there a way to find out all the instances of the SQL server running ?
- Read the alerts generated by SQL server.
- Read the logs generated by SQL server.
Thanks,
-Fatema
"Jacco Schalkwijk" wrote:

> SELECT name FROM master..sysdatabases
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
>
>|||Jacco
I can't believe that Jacco has suggested to query a system table :-)
select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u$l6Y7nPFHA.2604@.TK2MSFTNGP10.phx.gbl...
> SELECT name FROM master..sysdatabases
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
>|||:-)
Jacco Schalkwijk
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Olf6sSoPFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Jacco
> I can't believe that Jacco has suggested to query a system table :-)
> select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:u$l6Y7nPFHA.2604@.TK2MSFTNGP10.phx.gbl...
>

Gerating Excel Reports from SSIS

I want to be able to create an Excel report in SSIS after querying the data from a SQLSERVER table.

I have a IS package where I'm loading all the data required in the report and the final step of the IS package I would like to build the reports. I think it makes sense to take this approach instead of setting up a RSS package.

AS anyone seen any Blogs which explains such a flow?

You can use an Excel destination in your data flow, but beyond that, you won't be able to apply unique formatting rules, grid lines, images, charts, etc...

Not without programming a script component, anyway.

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

sql

Generating Scripts to Populate Tables

I would like to find a utility that can scan through an existing Sql
Server 2000 database and create scripts to re-insert/re-populate the
data into another DB with the same table structures. Like a back-up
utility.
Can anyone recommend one (or tell me which ones to avoid)? I
would like one that takes dependencies between tables into
consideration.

ThanksTry this stored procedure:
http://vyaskn.tripod.com/code.htm#inserts
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Chris Jones" <sarophes@.yahoo.com> wrote in message
news:ad3e5184.0408110953.4a2c4c20@.posting.google.c om...
> I would like to find a utility that can scan through an existing Sql
> Server 2000 database and create scripts to re-insert/re-populate the
> data into another DB with the same table structures. Like a back-up
> utility.
> Can anyone recommend one (or tell me which ones to avoid)? I
> would like one that takes dependencies between tables into
> consideration.
> Thanks

generating scripts in SqlServer 2005?

Okay, in Sql 2000, when I wanted to generate scripts, I could easily have
both the create and drop commands scripted into the same document with one
step. How do I do that with Sql 2005. I can generate the script for
creating the table and for dropping the table, but I cannot see the flag
where I can have both scripted at the same time.> step. How do I do that with Sql 2005.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1sql

Generating scripts in Management Studio

I'm using Management Studio to manage and interface with our Sql
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.

> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP
|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>
|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
>

Generating scripts in Management Studio

I'm using Management Studio to manage and interface with our Sql
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.

> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
>

Generating Scripts for Table Alterations in SQLServer

Hi,

I am using SQLServer and I would like to track changes or generate scripts for every change or alteration made to the table.. Please help me with any ideas..

Thank You,

Lesego

Two ways are common.

1. Profiler. Kind of clunky, but more or less guaranteed to see every change made, and cannot be easily overridden

2. In 2005, use a DDL trigger/Event Notification. I use this all of the time to monitor changes to my databases by other people who have dbo rights in cases where things are too political to give more specific rights.

Generate database script does not include database role members

I have read in the connect website (https://connect.microsoft.com/
SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125021)
That this issue was resolved in SP2. I have SP2 installed and still
have the same problem.
Am I missing something ?
-Noel
I looked under Tools | Options | Scripting and expected to see a new option
there, but did not find one. :-(
FWIW, I voted "5" on both 265852 and 125021.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1175102798.822976.257790@.n76g2000hsh.googlegr oups.com...
>I have read in the connect website (https://connect.microsoft.com/
> SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125021)
> That this issue was resolved in SP2. I have SP2 installed and still
> have the same problem.
> Am I missing something ?
> -Noel
>
|||(isyourfriend@.gmail.com) writes:
> I have read in the connect website (https://connect.microsoft.com/
> SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125021)
> That this issue was resolved in SP2. I have SP2 installed and still
> have the same problem.
> Am I missing something ?
I researched the issue and found that if you select "Script object permissions" it will script membership in user-defined roles, but not
in predefined roles like db_datawriter. I don't recall how SP1 worked;
maybe it did not script any membership at all. But in that case they
only fixed half the problem.
(And it's not intuitive that you need to select "Script object permissions".
I submitted
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=266078
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Mar 29, 6:18 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> (isyourfri...@.gmail.com) writes:
>
> I researched the issue and found that if you select "Script object permissions" it will script membership in user-defined roles, but not
> in predefined roles like db_datawriter. I don't recall how SP1 worked;
> maybe it did not script any membership at all. But in that case they
> only fixed half the problem.
> (And it's not intuitive that you need to select "Script object permissions".
> I submittedhttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
I am totally disappointed about the way M$ is handling BUGs that were
100% non existent on previous releases.
Thanks for the feedback.
-Noel

generate attributelist from tables

hi there!
I've been working on a project in ASP.NET and SQLserver 2000.
Since this is actually something for school, I must give a report on my work done here.
What I want to do is make an attributelist of all the tables that are in a database, or one at the time would already be a great help!
So for each table I would need in text format all its column names, types en 'allow null' property, but I just can't find out how or with what to do this.

thanks for your help!
Tombaselect * from INFORMATION_SCHEMA.COLUMNS

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver Garraux
There is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegr oups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>
|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver GarrauxThere is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegroups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP

Generarting XML based on Schema in SQLSERVER 2005

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help

Generarting XML based on Schema in SQLSERVER 2005

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help