Enumerating Xml elements and inserting

I have an untyped XML variable that for example holds the following data:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Using SQL DML and\or XQuery I would like to enumerate the XML elements and add an <ExternalId> element to any <Address> element if one doesn't exist with a value of NewId().

So the end result would be:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12334343A</ExternalId>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-333333</ExternalId>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12312312</ExternalId>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Any help would be greatly appreciated.

Regards

Why are you using a unique identifier. Are you familiar with the performance hit that comes with using a unique identifier? I admit there are reasons to want to use a unique identifier and yours may be one of them, but frequently I see unique identifiers used without an understanding of the consequences to performance. Here are some previous threads related to this issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430995&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1544519&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304764&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1493312&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525445&SiteID=1

|||

Thanks for the reply.

Perfomance is not an issue in this instance as it will be run once in a blue moon. But thanks for the heads-up on the performance issues. It doesn't have to be NewID() it can be any globally unique number.

Regards

sql

Enumerating Stored Procedure dependencies using SQL-DMO

I have the following (VB.NET) code:
For some reason, I can't get this code to return anything but a ResultSet
with 0 rows. Any ideas?
Public Function GetDependencies(ByVal db As SQLDMO.Database2) As String
Dim objSP As SQLDMO.StoredProcedure2 = db.StoredProcedures.Item(Me.Text)
Dim objQueryResults As SQLDMO.QueryResults = _
objSP.EnumDependencies(SQLDMO.SQLDMO_DEPENDENCY_TYPE.SQLDMODep_Valid)
Dim sb As New System.Text.StringBuilder(4096)
Dim writer As New System.IO.StringWriter(sb)
For i As Integer = 1 To objQueryResults.ResultSets
objQueryResults.CurrentResultSet = i
For j As Integer = 1 To objQueryResults.Rows
For k As Integer = 1 To objQueryResults.Columns
writer.Write(objQueryResults.ColumnName(k) & ": ")
writer.WriteLine(objQueryResults.GetColumnString(j, k))
Next
Next
Next
writer.Flush()
writer.Close()
Return sb.ToString()
End Function
Developer ExtraordinaireHi
Don't forget, in SQL 7.0 and 2000, dependency information is not guaranteed
to be correct due the Deferred Name resolution.
Have you looked in sysdepends if there is information there.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<developerExtraordinaire@.spamMeAndDie.com> wrote in message
news:ewd2zkeGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I have the following (VB.NET) code:
> For some reason, I can't get this code to return anything but a ResultSet
> with 0 rows. Any ideas?
> Public Function GetDependencies(ByVal db As SQLDMO.Database2) As String
> Dim objSP As SQLDMO.StoredProcedure2 =
db.StoredProcedures.Item(Me.Text)
> Dim objQueryResults As SQLDMO.QueryResults = _
> objSP.EnumDependencies(SQLDMO.SQLDMO_DEPENDENCY_TYPE.SQLDMODep_Valid)
> Dim sb As New System.Text.StringBuilder(4096)
> Dim writer As New System.IO.StringWriter(sb)
> For i As Integer = 1 To objQueryResults.ResultSets
> objQueryResults.CurrentResultSet = i
> For j As Integer = 1 To objQueryResults.Rows
> For k As Integer = 1 To objQueryResults.Columns
> writer.Write(objQueryResults.ColumnName(k) & ": ")
> writer.WriteLine(objQueryResults.GetColumnString(j, k))
> Next
> Next
> Next
> writer.Flush()
> writer.Close()
> Return sb.ToString()
> End Function
>
> Developer Extraordinaire
>

enumerating sql servers in sql server 2005

Is there anyway new in SQL Server 2005 to enumerate all the servers on the
network without sqldmo? I'm working in .NET and want to get the list, im
doing it now in sqldmo and it works fine, but would like to do it with
managed code instead of using a com object...smo. It is the new dmo. :-)
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Brian Henry" wrote:

> Is there anyway new in SQL Server 2005 to enumerate all the servers on the
> network without sqldmo? I'm working in .NET and want to get the list, im
> doing it now in sqldmo and it works fine, but would like to do it with
> managed code instead of using a com object...
>
>|||Or use new ado.net class SqlDataSourceEnumerator, for example:
foreach (DataRow row in
SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
{
Console.WriteLine(row["ServerName"].ToString());
}
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%23ttiTlzBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> Is there anyway new in SQL Server 2005 to enumerate all the servers on the
> network without sqldmo? I'm working in .NET and want to get the list, im
> doing it now in sqldmo and it works fine, but would like to do it with
> managed code instead of using a com object...
>|||thanks a lot!
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23DEvJk0BGHA.2840@.TK2MSFTNGP12.phx.gbl...
> Or use new ado.net class SqlDataSourceEnumerator, for example:
> foreach (DataRow row in
> SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
> {
> Console.WriteLine(row["ServerName"].ToString());
> }
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%23ttiTlzBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>|||do you happen to know a way to list all the databases on a selected server
also with out having to log into it? SQLDMO let you do this before
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23DEvJk0BGHA.2840@.TK2MSFTNGP12.phx.gbl...
> Or use new ado.net class SqlDataSourceEnumerator, for example:
> foreach (DataRow row in
> SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
> {
> Console.WriteLine(row["ServerName"].ToString());
> }
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%23ttiTlzBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>|||whoops i made a mistake i didnt mean not logging in... in SQLDMO i'd do this
Dim oSQLServer As New SQLDMO.SQLServer
oSQLServer.LoginSecure = True
oSQLServer.Connect(s_Server)
Me.cboDatabaseList.Items.Clear()
For Each db As SQLDMO.Database In oSQLServer.Databases
If Not db.SystemObject Then
Dim dbName As String = db.name
Me.cboDatabaseList.Items.Add(dbName)
End If
End If
End If
End If
Next
how would you do that now without sqldmo?|||Here is how - http://blogs.msdn.com/sushilc/archi.../14/242395.aspx
- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.m...5/13/63199.aspx
________________________________________
__________________
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%23ttiTlzBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> Is there anyway new in SQL Server 2005 to enumerate all the servers on the
> network without sqldmo? I'm working in .NET and want to get the list, im
> doing it now in sqldmo and it works fine, but would like to do it with
> managed code instead of using a com object...
>

Enumerating Sql Server Databases

Hi. I would like to enumerate all the databases in a given Sql Server instance.

I've searched and found no information. Would somebody post a piece of VB code to do that?

Thanks in advance

Rafael

You can do it with an SQL string:

use masterselect *from sys.databases

|||

SELECT

*

FROM

master.sys.databases

Should work, but there is probably a more standard way.

|||There is also a stored procedure called:sp_databases

Enumerating SQL Server database tables rows and sprocs using MFC

Hi,
I've been looking around for a while now after an efficient way to enumerate
tables, rows and sprocs of a MsSQL database. The best I could come up with
was SQL DMO, but one cannot have it installed without installing SQL Server
itself or MSDE, so I'm looking for alternative.
After a table name was fetched, I will need to know the rows it contains,
what type they are, and what are the indexes and identity defined for this
table. Effectively, if I could somehow get the CREATE SQL statement from the
DB itself that would be ideal. Also, I will need a way to get the sprocs
code from the DB, and all that without having any prequisites on either
sides - client and server.
I'm using VC++ with MFC. No managed C++ or .NET, unless I can use the
algorythm in an unmanaged enviroment.
Any links, articles, replies and sample code would be highly appreciated.
Thanks in advance,
Stilgar.Stilgar wrote:
> Hi,
> I've been looking around for a while now after an efficient way to enumera
te
> tables, rows and sprocs of a MsSQL database. The best I could come up with
> was SQL DMO, but one cannot have it installed without installing SQL Serve
r
> itself or MSDE, so I'm looking for alternative.
> After a table name was fetched, I will need to know the rows it contains,
> what type they are, and what are the indexes and identity defined for this
> table. Effectively, if I could somehow get the CREATE SQL statement from t
he
> DB itself that would be ideal. Also, I will need a way to get the sprocs
> code from the DB, and all that without having any prequisites on either
> sides - client and server.
> I'm using VC++ with MFC. No managed C++ or .NET, unless I can use the
> algorythm in an unmanaged enviroment.
> Any links, articles, replies and sample code would be highly appreciated.
> Thanks in advance,
> Stilgar.
To connect to the database you'll normally need an ODBC or OLEDB driver
and the usual way to get that is to install the client connectivity
from the SQL Server disc or install MDAC from microsoft.com (unless you
plan to write your own ODBC/OLEDB client).
The metadata is available in the ADO object model or through TSQL
commands that return a recordset. SQL Server provides the Information
Schema views for that purpose and you can read about them in Books
Online.
David Portas
SQL Server MVP
--|||Will DESCRIBE <tablename> work on MsSql?
For each row simply do SELECT * FROM <table>
- MR
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:%23RANlXE7FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've been looking around for a while now after an efficient way to
> enumerate tables, rows and sprocs of a MsSQL database.|||DESCRIBE <tablename> doesn't seem to work with MsSQL. I've executed it and
it errored.
How can I get the field types using SELECT *? Not to mention indexes and
determine what is the identity field?
Stilgar.
"Mark Randall" <markyr@.gmail.com> wrote in message
news:%23oLYohE7FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Will DESCRIBE <tablename> work on MsSql?
> For each row simply do SELECT * FROM <table>
> - MR
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:%23RANlXE7FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I'm using ODBC. Can you shortlist the main TSQL commands? I couldn't find
them myself, as I don't know what to look for exactly here.
How can MDAC help here?
Stilgar.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132322428.645339.183190@.g44g2000cwa.googlegroups.com...
> Stilgar wrote:
> To connect to the database you'll normally need an ODBC or OLEDB driver
> and the usual way to get that is to install the client connectivity
> from the SQL Server disc or install MDAC from microsoft.com (unless you
> plan to write your own ODBC/OLEDB client).
> The metadata is available in the ADO object model or through TSQL
> commands that return a recordset. SQL Server provides the Information
> Schema views for that purpose and you can read about them in Books
> Online.
> --
> David Portas
> SQL Server MVP
> --
>|||http://msdn.microsoft.com/library/d...r />
_9sfo.asp
Click 'Up One Level' and there you go ... I tried to give you that direct
link but ...
-Mark
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:%231s$CpE7FHA.476@.TK2MSFTNGP15.phx.gbl...
> I'm using ODBC. Can you shortlist the main TSQL commands? I couldn't find
> them myself, as I don't know what to look for exactly here.
> How can MDAC help here?
> Stilgar.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1132322428.645339.183190@.g44g2000cwa.googlegroups.com...
>|||Hello Stilgar,
Take a look at the GetSchema() method using the most recent SqlClient bit
drops. BobB has good article about them at [0], but that's framed in ADO.NET
terms. I believe you can use the same patterns in C++/MFC, however. Just
harder :)
[0] http://msdn.microsoft.com/library/d...net2schemas.asp
Long live the fighters!
Kent Tegels
DevelopMentor|||I got an even better one - EXEC sp_tables, sp_columns <table>,
sp_stored_procedures.
Thanks for your help.
Stilgar.
"Mark Nijhof" <Mark@.Nijhof.com> wrote in message
news:urPdcHF7FHA.1184@.TK2MSFTNGP12.phx.gbl...
> http://msdn.microsoft.com/library/d.../>
es_9sfo.asp
> Click 'Up One Level' and there you go ... I tried to give you that direct
> link but ...
> -Mark
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:%231s$CpE7FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||...And I have an even better Idea;
go to
http://dev.mysql.com/downloads/
and download the MySQL++, this makes connecting from VC++ to a MySQL
database really easy, no ODBC, or any other connectors, straight to the
MySQL database.
John
The_Code_Master@.hotmail.com
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:eg$xYBU7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> I got an even better one - EXEC sp_tables, sp_columns <table>,
> sp_stored_procedures.
> Thanks for your help.
> Stilgar.
> "Mark Nijhof" <Mark@.Nijhof.com> wrote in message
> news:urPdcHF7FHA.1184@.TK2MSFTNGP12.phx.gbl...
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sa-ses_9sfo.asp
direct
find
message
either
driver
you
>|||Sure, only problem is I asked about MS SQL Server, not MySQL. Thanks, but
I'm already familiar with it.
Stilgar.
"The Code Master" <The_Code_Master@.hotmail.com> wrote in message
news:BPOff.21604$tV6.12458@.newssvr27.news.prodigy.net...
> ...And I have an even better Idea;
> go to
> http://dev.mysql.com/downloads/
> and download the MySQL++, this makes connecting from VC++ to a MySQL
> database really easy, no ODBC, or any other connectors, straight to the
> MySQL database.
> John
> The_Code_Master@.hotmail.com
>
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:eg$xYBU7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_[/ur
l]
> sa-ses_9sfo.asp
> direct
> find
> message
> either
> driver
> you
>

Enumerating Role Permissions with SMO

Hi,

I am attempting to enumerate role permissions using SMO. I can enumerate the permissions on a atbel, view, or store procedure no problem. When I try to enumerate the permissions that a role has I get the following error:

'Operation not supported on SQL Server 2000'

Can anyone help me in sorting this out. I had the permissions enumerated in DMO but I just can't seem to get it to work in SMO.

Thanks,

James

Just to make sure that I answer this correctly: are you connecting to SQL Server 2000 or SQL Servert 2005?|||

Hi,

I am connecting to SQL2K. I have not problem getting the permissions on a table, view, stored procedure, or function. Its only when I try to get the permissions for a role or user.

James

|||

SMO has aligned the meaning of User.EnumObjectPermissions with all other objects (such as Table). This means that you would enumerate the permissions that have granted or denied to a principal for a User object; not the permissions that have been granted to the User object (which represents a database user). SQL Server 2005 has implemented this (it was not possible for SQL Server 2000) so that clarifies the message you get.

You should be able to call Database.EnumObjectPermissions(User.Name) to obtain the results that you expect.

|||

Thanks,

that worked a treat.

James

|||

Hi,

I am also working on the table permissions.

ObjectPermissionInfo[] objPer = database.EnumObjectPermissions(database.UserName);

But, from this point how could I display all permissions for a table? Could you please send me a sample code?

Thanks in advance.

sql

Enumerating Role Permissions with SMO

Hi,

I am attempting to enumerate role permissions using SMO. I can enumerate the permissions on a atbel, view, or store procedure no problem. When I try to enumerate the permissions that a role has I get the following error:

'Operation not supported on SQL Server 2000'

Can anyone help me in sorting this out. I had the permissions enumerated in DMO but I just can't seem to get it to work in SMO.

Thanks,

James

Just to make sure that I answer this correctly: are you connecting to SQL Server 2000 or SQL Servert 2005?|||

Hi,

I am connecting to SQL2K. I have not problem getting the permissions on a table, view, stored procedure, or function. Its only when I try to get the permissions for a role or user.

James

|||

SMO has aligned the meaning of User.EnumObjectPermissions with all other objects (such as Table). This means that you would enumerate the permissions that have granted or denied to a principal for a User object; not the permissions that have been granted to the User object (which represents a database user). SQL Server 2005 has implemented this (it was not possible for SQL Server 2000) so that clarifies the message you get.

You should be able to call Database.EnumObjectPermissions(User.Name) to obtain the results that you expect.

|||

Thanks,

that worked a treat.

James

|||

Hi,

I am also working on the table permissions.

ObjectPermissionInfo[] objPer = database.EnumObjectPermissions(database.UserName);

But, from this point how could I display all permissions for a table? Could you please send me a sample code?

Thanks in advance.