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.

enumerating registered sqlservers does not fill datatable complete

I've tried to enumerate sqlservers using two different methods and have the
same results... only the server name is returned, I am unable to get the
instance name so user will be unable to connect to selected server if it is
a
named instance (i.e. "Server"\SQLEXPRESS)...
Dim dt As DataTable
dt = SmoApplication.EnumAvailableSqlServers(False)
OR
Dim dt As DataTable
Dim instance As Sql.SqlDataSourceEnumerator =
Sql.SqlDataSourceEnumerator.Instance
dt = instance.GetDataSources
However I can go into SQL Server Management Studio, browse for servers and
every instance for every server (local and network) is listed... I assume
(foolishly) that MS Apps utilize thier own sdk's, if so why does it work
there but not for meOK I think I've found the culprit... the "Windows Firewall/Internet
Connection Sharing (ICS)" service
When I stop this service the enumerations return the Instance name and
Version of the SQL Servers... Hooray but what if Users have this service
running during my application installation? I'm suprised this isn't more of
a
known issue... I'm able to duplicate the issue on 2 machines both running XP
sp2 where one has SQL Server Express 2005 then other has SQL Server Express
2005 SP1, same results. Has anyone heard or exprienced this?

> I've tried to enumerate sqlservers using two different methods and have th
e
> same results... only the server name is returned, I am unable to get the
> instance name so user will be unable to connect to selected server if it i
s a
> named instance (i.e. "Server"\SQLEXPRESS)...
> Dim dt As DataTable
> dt = SmoApplication.EnumAvailableSqlServers(False)
> OR
> Dim dt As DataTable
> Dim instance As Sql.SqlDataSourceEnumerator =
> Sql.SqlDataSourceEnumerator.Instance
> dt = instance.GetDataSources
> However I can go into SQL Server Management Studio, browse for servers and
> every instance for every server (local and network) is listed... I assume
> (foolishly) that MS Apps utilize thier own sdk's, if so why does it work
> there but not for me|||On Tue, 13 Jun 2006 14:17:02 -0700, rqcoder
<rqcoder@.discussions.microsoft.com> wrote:
in <EF3759A0-3FE7-4839-B10E-E892752C02AB@.microsoft.com>

>OK I think I've found the culprit... the "Windows Firewall/Internet
>Connection Sharing (ICS)" service
>When I stop this service the enumerations return the Instance name and
>Version of the SQL Servers... Hooray but what if Users have this service
>running during my application installation? I'm suprised this isn't more of
a
>known issue... I'm able to duplicate the issue on 2 machines both running X
P
>sp2 where one has SQL Server Express 2005 then other has SQL Server Express
>2005 SP1, same results. Has anyone heard or exprienced this?
It happens on any machine that's running the firewall. It looks like
the tolerance window for the enumerations is extremely small - less than
a second. If anything hampers that initial query in the slightest then
the instance names don't show up. And I'm describing SQLDMO behavior
using VB6 but the enumerations code is most likely the same or extremely
similar.
What works well for me is Kerio Personal Firewall with specific rules
but that doesn't always wash well in the general user community.
Stefan Berglund

Enumerating publications on a server via RMO

Hello everyone,
I'm looking to get a list of publications on a given SQL Server
instance via RMO. I can't seem to find any information on how to do
this. What I need is, given a server name or existing connection, find
out if the machine is a publisher and if so, get a list of its
publications. Currently I am able to query the publication and
articles, but I have to specify the publication/article name in
advance. I need the ability to discover them at runtime.
Any help or nudge in the right direction would be appreciated,
Regards,
Anthony
Hello everyone,
I was able to find something in the Microsoft.SqlServer.Replication
namespace that does exactly what I want; however, it comes with the
following disclaimer from Microsoft : "This class supports the SQL
Server 2005 infrastructure and is not intended to be used directly
from your code." This leads me to believe that there's another way of
enumerating publications on a SQL Server 2005 instance via RMO, but
for now I'll post what I found because... well, it works for me. The
following is sample code to iterate through all the publications on a
given SQL Server (2005?) instance and enumerate through the articles
within, outputing to the richtextbox rtbFilter :
**referencing :
[C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
\Microsoft.SqlServer.ConnectionInfo.dllusing
Microsoft.SqlServer.Management.Common]
[C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
\Microsoft.SqlServer.Rmo.dll]
using Microsoft.SqlServer.Replication;
...
ServerConnection conn = new ServerConnection("sql server name", "user
name", "user password");
try
{
conn.Connect();
ReplicationServer rs = new ReplicationServer(conn);
ArrayList al = rs.EnumLightPublications("name of the database being
replicated", 3, true, true); // 1 = trans, 2 = merge, 3 = all. I
*presume* these are correct (tested through quick trial and error,
didn't find the proper enum equivalent)
foreach (LightPublication lp in al)
{
rtbFilter.AppendText("Name:" + lp.Name + "\r\n");
rtbFilter.AppendText("Description:" + lp.Description + "\r\n");
rtbFilter.AppendText("Type:" + lp.Type.ToString() + "\r\n");
rtbFilter.AppendText("PublisherName:" + lp.PublisherName + "\r\n");
rtbFilter.AppendText("PublisherType:" + lp.PublisherType + "\r\n");
rtbFilter.AppendText("PublicationDBName:" + lp.PublicationDBName +
"\r\n");
rtbFilter.AppendText("\r\n");
TransPublication tp = new TransPublication(lp.Name,
lp.PublicationDBName, conn);
foreach (TransArticle ta in tp.EnumArticles())
{
rtbFilter.AppendText("\tName:" + ta.Name + "\r\n");
rtbFilter.AppendText("\tDescription:" + ta.Description + "\r\n");
rtbFilter.AppendText("\tType:" + ta.Type.ToString() + "\r\n");
rtbFilter.AppendText("\tFilterProcOwner:" + ta.FilterProcOwner + "\r
\n");
rtbFilter.AppendText("\tFilterProcName:" + ta.FilterProcName + "\r
\n");
rtbFilter.AppendText("\tFilterClause:" + ta.FilterClause + "\r\n");
}
}
}
catch(Exception ex)
{
Console.Out.WriteLine(ex.Message);
}
finally
{
conn.Disconnect();
}
Hope this helps,
Anthony

Enumerating jobs on a SQL server using SMO - in C#

Hi all, I'm trying to connect to SQL and enumerate all Agent jobs to find a
particular and call job.envoke on it.
Looked every where but can't find a code snippet on it or a sample.
Anyone can assist? CheersYou can use this to list all the jobs.
USE msdb
EXEC sp_help_job
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Oops... Just read the subject line about SMO.. My fault..
You can try this link..
http://msdn2.microsoft.com/en-us/li...nt.
aspx
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thanks omnibuzz, I've tried that uri but since I'm new to SQL prog I haven't
figured out how to use for instance jobbasecollection classes to find out if
the job I'm interested in exist on the server.
Any help there with the code\implementation? Thanks.
"Omnibuzz" wrote:

> Oops... Just read the subject line about SMO.. My fault..
> You can try this link..
> http://msdn2.microsoft.com/en-us/li...n
t.aspx
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>

Enumerating instances without DMO

Can anyone suggest a method of enumerating instances of MSDE 2000 without
using DMO ?

Using C++, MFC, and ADO.

ThanksSee questions 12 and 13:

http://support.microsoft.com/defaul...6&Product=sql2k

If you need to enumerate instances externally (ie without access to the
server's registry), then SQLDMO may still be the best option - see the
ListAvailableSQLServers method. Also see here:

http://www.sqldev.net/misc/ListSQLSvr.htm

Simon|||Simon,

Many thanks. I feel a bit foolish as I trawled through that document,
although it was a few years ago...

I have not got satisfactory results from 'ListAvailableSQLServers'. Function
returns OK but the resulting loop yields only 1 server, I have two
instances. Never mind, the registry is fine for my needs and a lot simpler.

Cheers

Jon.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1118213082.991451.13250@.f14g2000cwb.googlegro ups.com...
> See questions 12 and 13:
> http://support.microsoft.com/defaul...6&Product=sql2k
> If you need to enumerate instances externally (ie without access to the
> server's registry), then SQLDMO may still be the best option - see the
> ListAvailableSQLServers method. Also see here:
> http://www.sqldev.net/misc/ListSQLSvr.htm
> Simonsql

enumerating Foreach Loop from rowset

Please let me know if I am on the right track here.

I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.

I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.

Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection > Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).

Thanks - Dana Reed

It sounds spot on to me Dana!

-Jamie

enumerating all tables for size

Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
ArunTry this:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
Arun|||Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun

quote:

---

>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>

---

|||Run the following and see what it says:
EXEC sp_spaceused 'SoftwareFile'
If that reports the same size, then your sysindexes table is not accurate,
and you will have to run DBCC UPDATEUSAGE to fix that.
Look up SQL Server Books Online for more information on DBCC UPDATEUSAGE.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:0af201c3d9eb$284ef130$a501280a@.phx.gbl...
Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun

quote:

---

>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>

---

enumerating all tables for size

Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
ArunTry this:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
Is there any SP or a function to enumerate all the tables
for their size for a given database... ?
Thanks
Arun|||Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun
>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in
message
>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>|||Run the following and see what it says:
EXEC sp_spaceused 'SoftwareFile'
If that reports the same size, then your sysindexes table is not accurate,
and you will have to run DBCC UPDATEUSAGE to fix that.
Look up SQL Server Books Online for more information on DBCC UPDATEUSAGE.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Arun" <anonymous@.discussions.microsoft.com> wrote in message
news:0af201c3d9eb$284ef130$a501280a@.phx.gbl...
Vyas,
thanks and it works as you say.. Since I am a novice in
SQL, i do not the size it displays.. will you please
explain about the size , for instance line below is one of
the largest table in my database
[dbo].[SoftwareFile] 6175326 355529.66
If the last number is in MB then its size is 355 Gig which
would be incorrect as my whole harddisk size itself is
300Gig and the whole database holding it is 70 Gig
Arun
>--Original Message--
>Try this:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>
>"Arun" <anonymous@.discussions.microsoft.com> wrote in
message
>news:066901c3d959$d9e77fd0$a301280a@.phx.gbl...
>Is there any SP or a function to enumerate all the tables
>for their size for a given database... ?
>Thanks
>Arun
>
>.
>

Enumerate SQL Server Logins and Permissions

Haven't been able to find the answer to this after sever searches. So decided to open the thread myself.

I am trying to write a report enumerating logins in SQL Server and all database-level and object-level permissions granted to each login.

Can someone tell me what system objects I can query to fetch this information?

TIA.

Hi,

You can get the results by querying the following system views(cata log views):

Database-Level Views:

sys.database_permissions
sys.database_principals

Server-Level Views:

sys.server_permissions
sys.sql_logins
sys.server_principals

Thanks & Regards,

Kiran.Y

|||

That's just what I was looking for. Thanks much.

sql