How do I access sql system stored procs from code

Hello:

Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated!

ThanksOkay i am stupid again and did not do a google search. So after do this I found an excellent resource for this particular problem. I found it at:

http://www.ftponline.com/vsm/2003_01/magazine/columns/databasedesign/default.aspx

Shows how to access Table Metadata programmatically using C# and VB.Net AWESOME! Working on my own code generator and now I am finally getting to a point where I can start to finish it. All I needed was a way to generate stored procs from table metadata. Now I have the table metadata and a way to generate stored procs I am all set. AWESOME.

Thanks for those that read this post hope it helps you out in the future. Remeber a real programmer does not write code but writes code that generates code for him/her.|||Just the same as any other stored procedure...you need permissions to run the proc and the path to the proc, e.g master

How do determine who is the owner of an object?

I want to find objects that are owned by SQL Logins that no longer exist.
Help is appreciated,
ThanksWalter
You cannot drop Logins that have owned objects
"WalterWalt" <,> wrote in message
news:OvVYN3sHHHA.4688@.TK2MSFTNGP04.phx.gbl...
>I want to find objects that are owned by SQL Logins that no longer exist.
> Help is appreciated,
> Thanks
>|||I dropped the builtin\administrators login and have a sneaking suspicion
that that left some objects with an orphaned owner. I would like to confirm
this is not the case by running a query to get the owner of all obects. Can
you answer my question? Thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OSB7k%23sHHHA.420@.TK2MSFTNGP06.phx.gbl...
> Walter
> You cannot drop Logins that have owned objects
>
>
> "WalterWalt" <,> wrote in message
> news:OvVYN3sHHHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||WalterWalt wrote:
> I want to find objects that are owned by SQL Logins that no longer exist.
> Help is appreciated,
> Thanks
I think sysobjects.uid is the user ID in sysusers, in a database.
Linking database users to logins is a further step.
For instance:
create table tempdb.guest.guesttable ( i int)
select * from tempdb.dbo.sysobjects where name = 'guesttable'
select * from tempdb.dbo.sysusers order by uid
HTH|||>I dropped the builtin\administrators login and have a sneaking suspicion
>that that left some objects with an orphaned owner.
Why do you suspect this? Assuming SQL 2000, logins do not own database
objects directly. Database users own database objects and SQL will not let
you drop a database user that owns objects. Also, objects cannot be owned
by Windows groups (builtin\administrators is a Windows group).
Separately, users are mapped to database logins. SQL Server will not let
you drop a login that is mapped to a database user. However, you can end up
with orphaned users (users without logins) if you restore/attach a database.

> I would like to confirm this is not the case by running a query to get the
> owner of all obects. Can you answer my question?
Run the query below in your databases. I would expect that ObjectOwner will
not be NULL. OwnerLogin may be null for system schema or if you have
orphaned database users that also own objects.
SELECT DISTINCT
u.name AS ObjectOwner,
l.name AS OwnerLogin
FROM sysobjects o
LEFT JOIN sysusers u ON
u.uid = o.uid
LEFT JOIN master.dbo.syslogins l ON
l.sid = u.sid
Hope this helps.
Dan Guzman
SQL Server MVP
"WalterWalt" <,> wrote in message
news:egKnrGtHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>I dropped the builtin\administrators login and have a sneaking suspicion
>that that left some objects with an orphaned owner. I would like to
>confirm this is not the case by running a query to get the owner of all
>obects. Can you answer my question? Thanks.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OSB7k%23sHHHA.420@.TK2MSFTNGP06.phx.gbl...
>|||OwnerLogin for sys is null. Does that mean there are orphaned database
users?
Thanks for you help.
If I logon to my local machine that is running SQL Server 2000 as
administrator, and then logon to SQL Server using Windows Authentication I
am a member of the sysadmin server role even though I don't have a SQL
Server login explicitly created for Administrator. I get my permissions
implicitly through the BUILTIN\Administrators group. While logged into SQL
Server in this context I create a database called TEST and a table called
tblTEST. Then I log out of SQL and then back in as sa. I delete the
BUILTIN\Administrators group. So who is the owner of db TEST and tblTest
and how do I query for other objects like that?
Thanks and sorry to Uri is sounded snappy.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3624EDF3-0531-462E-B641-40124E28B6F5@.microsoft.com...
> Why do you suspect this? Assuming SQL 2000, logins do not own database
> objects directly. Database users own database objects and SQL will not
> let you drop a database user that owns objects. Also, objects cannot be
> owned by Windows groups (builtin\administrators is a Windows group).
> Separately, users are mapped to database logins. SQL Server will not let
> you drop a login that is mapped to a database user. However, you can end
> up with orphaned users (users without logins) if you restore/attach a
> database.
>
> Run the query below in your databases. I would expect that ObjectOwner
> will not be NULL. OwnerLogin may be null for system schema or if you have
> orphaned database users that also own objects.
> SELECT DISTINCT
> u.name AS ObjectOwner,
> l.name AS OwnerLogin
> FROM sysobjects o
> LEFT JOIN sysusers u ON
> u.uid = o.uid
> LEFT JOIN master.dbo.syslogins l ON
> l.sid = u.sid
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "WalterWalt" <,> wrote in message
> news:egKnrGtHHHA.1816@.TK2MSFTNGP06.phx.gbl...
>|||> OwnerLogin for sys is null. Does that mean there are orphaned database
> users?
System schema users (e.g. INFORMATION_SCHEMA) are special cases and don't
need to be associated with logins in SQL 2000. However, ths sys schema was
introduced in SQL 2005 - aren't we were talking about SQL 2000 here? There
are major differences between SQL 2000 and 2005 related to user/schema
separation.

> I delete the BUILTIN\Administrators group. So who is the owner of db TEST
> and tblTest
The tblTest table will continue to be owned by user dbo. The TEST database
will still to be owned by your Windows account. Note that the database
owner database owner will be your Windows account because you connected via
Windows group membership (BUILTIN\Administrators) when you created the
database. Databases can't be owned by Windows groups.

> and how do I query for other objects like that?
Database ownership is recorded in sysdatabases and also as the mapping
between the dbo database user and syslogins. The login mapped to the dbo
user and the database owner should be the same but can get out-of-sync after
a restore or attach. You can use sp_changedbowner to change/fix database
ownership. The query below can identify a mismatch.
SELECT
u.name AS DatabaseUser,
l.name AS DboLogin,
l2.name AS DatabaseOwner
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON
l.sid = u.sid
JOIN master.dbo.sysdatabases d on
d.name = DB_NAME()
LEFT JOIN master.dbo.syslogins l2 ON
l2.sid = d.sid
WHERE u.name = 'dbo'
Hope this helps.
Dan Guzman
SQL Server MVP
"WalterWalt" <,> wrote in message
news:Ow6jV%236HHHA.4992@.TK2MSFTNGP04.phx.gbl...
> OwnerLogin for sys is null. Does that mean there are orphaned database
> users?
> Thanks for you help.
> If I logon to my local machine that is running SQL Server 2000 as
> administrator, and then logon to SQL Server using Windows Authentication I
> am a member of the sysadmin server role even though I don't have a SQL
> Server login explicitly created for Administrator. I get my permissions
> implicitly through the BUILTIN\Administrators group. While logged into
> SQL Server in this context I create a database called TEST and a table
> called tblTEST. Then I log out of SQL and then back in as sa. I delete
> the BUILTIN\Administrators group. So who is the owner of db TEST and
> tblTest and how do I query for other objects like that?
> Thanks and sorry to Uri is sounded snappy.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:3624EDF3-0531-462E-B641-40124E28B6F5@.microsoft.com...
>

How determine the number of CALs installed

How can I determine the number of CALs installed on SQL Server 7? I
need to determine the number of CALs that must be purchased to upgrade.
..Bill.
"Bill" <no@.no.com> wrote in message
news:ui6MnwoOGHA.1532@.TK2MSFTNGP12.phx.gbl...
> How can I determine the number of CALs installed on SQL Server 7? I
> need to determine the number of CALs that must be purchased to upgrade.
> --
> .Bill.
CALs are licences only. They are not installed anywhere so your question
doesn't really make sense.
The proper way to determine your licence requirement is to count your users
or installed devices. SQL Server's CAL requirement is measured by those
metrics, not by concurrent usage. There is a licence figure that you can set
using the SQL Server Licensing Setup utility in Control Panel so I expect
you'll want to check that as well.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

How determine how a Context menu was closed?

Hello,
I need to be able to determine the method used to close a Context menu. It
appears that there are three possibilities: 1) a menu selection was clicked,
2) the User clicked outside the menu or 3) the User hit ESC. I need to
determine if the menu closed due to either of the last two possiibilities.
(This may seem like a mysterious requirement but the popup of the Context
Menu is triggered by a specific event in a Frame. If a menu selection is
made then certain actions are taken but if the User "cancels" by items #2 or
#3 I need to take a different action to "clean up").
TrackPopupMenu() blocks and does not return until the menu has been closed,
but this happens before the handler for a selected menu item (if an item was
choosen) is called. That means that upon return from TrackPopupMenu() I
can't tell yet if a menu item was selected (since, if a handler is going to
fire, it hasn't yet fired). WM_EXITMENULOOP is also sent when the menu
closes but I can't depend that this will always been sent/processed *AFTER*
any possible menu selection message so I hacve the same problem with using
that.
So the question boils down to how can I, upon return from WM_EXITMENULOOP(),
detect how the menu was closed? I guess I could "go to sleep" for one
second upon return to give any possible menu selection message time to hit
its handler but that seems like a big kludge and I'm not sure how long I'd
have to wait to be sure.
Thanks.
Al
AlKoch@.MyRealBoxREMOVEALLTHESECHARS.comSorry - please ignore - obviously sent to wrong group!
Al

How determine how a Context menu was closed?

Hello,
I need to be able to determine the method used to close a Context menu. It
appears that there are three possibilities: 1) a menu selection was clicked,
2) the User clicked outside the menu or 3) the User hit ESC. I need to
determine if the menu closed due to either of the last two possiibilities.
(This may seem like a mysterious requirement but the popup of the Context
Menu is triggered by a specific event in a Frame. If a menu selection is
made then certain actions are taken but if the User "cancels" by items #2 or
#3 I need to take a different action to "clean up").
TrackPopupMenu() blocks and does not return until the menu has been closed,
but this happens before the handler for a selected menu item (if an item was
choosen) is called. That means that upon return from TrackPopupMenu() I
can't tell yet if a menu item was selected (since, if a handler is going to
fire, it hasn't yet fired). WM_EXITMENULOOP is also sent when the menu
closes but I can't depend that this will always been sent/processed *AFTER*
any possible menu selection message so I hacve the same problem with using
that.
So the question boils down to how can I, upon return from WM_EXITMENULOOP(),
detect how the menu was closed? I guess I could "go to sleep" for one
second upon return to give any possible menu selection message time to hit
its handler but that seems like a big kludge and I'm not sure how long I'd
have to wait to be sure.
Thanks.
Al
AlKoch@.MyRealBoxREMOVEALLTHESECHARS.com
Sorry - please ignore - obviously sent to wrong group!
Al
sql

how check if Transaction object already rollback or not?

Hi,
i would like to know how to determine a transaction is already rollback so that we dont have to reroll back again and get an exception like below:

This SqlTransaction has completed;
it is no longer usable. at System.Data.SqlClient.SqlTransaction.Rollback()

Please help because when i tried to do this: objTrans.rollback() and i got the above exception.

Regards,If the SqlTransaction's Connection property is nothing, then the transaction is no longer valid (and you should not call Rollback() ). Here's the docs for a code sample:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasstopic.asp|||Thanks alot..
actually i have another issue that is somehow related to this thread, i m using dataAdater to populate my TreeView COntrol but if i refresh 2 browser at the same time it will give me 2 exception. The first exception is

1)There is already an open DataReader associated with this Connection which must be closed first.
2)The SqlCommand is currently busy Open, Fetching.

I m not so sure why can this be? i m doing recursive loop and below is my code

private void LoadNode()
{
//clear the tree Component
TreeView1.Nodes.Clear();
string Tsql;
TreeNode Node;
Tsql="sp_framework_get_imis_function";
DataSet dsTree;
dsTree=ExecuteDbConnection.fillDataset(Tsql,imis.framework.net.ImisUser .getDetails.userID);
int i=0;
TreeNode tempNode=null;
if (dsTree.Tables.Count <= 0) return;
while (i<= dsTree.Tables[0].Rows.Count -1)
{
//create a new node for parent
//get the parent node
Node=new TreeNode();
if (dsTree.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty|| dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (dsTree.Tables[0].Rows[i]["function_desc"].ToString() !=string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty ) Node.Text=dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()==null?dsTree.Tables[0].Rows[i]["function_desc"].ToString() :dsTree.Tables[0].Rows[i]["function_desc"].ToString() + "<br>"+dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsTree.Tables[0].Rows[i]["function_desc"].ToString()==string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty )Node.Text=dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsTree.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty && dsTree.Tables[0].Rows[i]["function_desc_chinese"].ToString()==string.Empty ) Node.Text=dsTree.Tables[0].Rows[i]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
return;
}
Node.SelectedImageUrl=dsTree.Tables[0].Rows[i]["img_selected"].ToString();
Node.ImageUrl =dsTree.Tables[0].Rows[i]["img"].ToString();
if (dsTree.Tables[0].Rows[i]["program"].ToString() !="")
{
Node.NavigateUrl ="index.aspx?pageID=" +dsTree.Tables[0].Rows[i]["function_id"];
Node.Target="WorkFrame";
}
tempNode=Node;
TreeView1.Nodes.Add(Node);
ChildNode(tempNode,Convert.ToInt32(dsTree.Tables[0].Rows[i]["function_id"]));
i=i+1;
}
}//end sub
private void ChildNode(TreeNode node,int functionID)
{
try
{
TreeNode nodeX;
string userID=imis.framework.net.ImisUser.getDetails.userID;
string tSql;
string TempSQL;
if (imis.framework.net.ImisUser.getDetails.userID ==null)
return;
TempSQL="sp_framework_get_imis_function_child_id";
DataSet dsChild;
dsChild=ExecuteDbConnection.fillDataset(TempSQL,functionID,userID);
int i=0;
if (dsChild.Tables[0].Rows.Count >0)
{
while (i <=dsChild.Tables[0].Rows.Count-1)
{
//get all the child of the belong the the first level nodes
nodeX=new TreeNode();
if (dsChild.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty || dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (dsChild.Tables[0].Rows[i]["function_desc"].ToString() !=string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty) nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()==null?dsChild.Tables[0].Rows[i]["function_desc"].ToString() :dsChild.Tables[0].Rows[i]["function_desc"].ToString() + "<br>"+dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsChild.Tables[0].Rows[i]["function_desc"].ToString()==string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()!=string.Empty)nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString();
if(dsChild.Tables[0].Rows[i]["function_desc"].ToString()!=string.Empty && dsChild.Tables[0].Rows[i]["function_desc_chinese"].ToString()==string.Empty) nodeX.Text=dsChild.Tables[0].Rows[i]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
}
//nodeX.Text= dsChild.Tables[0].Rows[i]["function_desc"].ToString();
nodeX.SelectedImageUrl=dsChild.Tables[0].Rows[i]["img_selected"].ToString();;
nodeX.ImageUrl =dsChild.Tables[0].Rows[i]["img"].ToString();
if (dsChild.Tables[0].Rows[i]["program"].ToString() != "")
{
nodeX.NavigateUrl="index.aspx?pageID=" +dsChild.Tables[0].Rows[i]["function_id"];
nodeX.Target="WorkFrame";
}
node.Nodes.Add(nodeX);
tSql="sp_framework_get_imis_function_child_id";
DataSet ds2=ExecuteDbConnection.fillDataset(tSql,Convert.ToInt32(dsChild.Tables[0].Rows[i]["function_id"]),userID);
if (Convert.ToBoolean(ds2.Tables[0].Rows.Count > 0))
{
int j=0;
while (j <=ds2.Tables[0].Rows.Count -1)
{
//recursive way of getting all the child in the first level
TreeNode nodeY=new TreeNode();
if (ds2.Tables[0].Rows[j]["function_desc"].ToString()!=string.Empty || ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString() !=string.Empty)
{
//desc_chinese is not null
if (ds2.Tables[0].Rows[j]["function_desc"].ToString() !=string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()!=string.Empty) nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==null?ds2.Tables[0].Rows[j]["function_desc"].ToString() :ds2.Tables[0].Rows[j]["function_desc"].ToString() + "<br>"+ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString();
if(ds2.Tables[0].Rows[j]["function_desc"].ToString()==string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()!=string.Empty)nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString();
if(ds2.Tables[0].Rows[j]["function_desc"].ToString()!=string.Empty && ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==string.Empty) nodeY.Text=ds2.Tables[0].Rows[j]["function_desc"].ToString();
}
else
{
//throw exception because both desc and desc_chinese is not null.
}
//nodeY.Text=ds2.Tables[0].Rows[j]["function_desc_chinese"].ToString()==null?ds2.Tables[0].Rows[j]["function_desc"].ToString() :ds2.Tables[0].Rows[j]["function_desc"].ToString() + "<br>"+dsChild.Tables[0].Rows[j]["function_desc_chinese"].ToString() ;
nodeY.SelectedImageUrl=ds2.Tables[0].Rows[j]["img_selected"].ToString();
nodeY.ImageUrl =ds2.Tables[0].Rows[j]["img"].ToString();
if (ds2.Tables[0].Rows[j]["program"].ToString() != "")
{
nodeY.NavigateUrl="index.aspx?pageID=" +ds2.Tables[0].Rows[j]["function_id"];
nodeY.Target="WorkFrame";
}
nodeX.Nodes.Add(nodeY);
ChildNode(nodeY,Convert.ToInt32(ds2.Tables[0].Rows[j]["function_id"]));
++j;
}//end while
}//end if
++i;
}//end while
}//end if
}
catch(Exception err)
{
imis.framework.net.ApplicationLog.writeError(err,"TreeView");
}
}//end sub

Please advise, i m using dataAdater purely.|||I'm not sure. I'd advise you to repost the previous message in a new thread so that more people will take a look at it.sql

How can you determine which version of SQL Server is installed on a machine? -

How can you determine which version of SQL Server is installed on a machine?select @.@.version|||Try the below mentioned query to even find Product Version, Product Level as well as the edition of SQL Server ...

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

--Sandu_Bangalore

How can you determine if your server has Raided disks or not.

Good afternoon,
I'd like to know what the command or where you look to determine if the
disks on an array are raided or not. Is there a command we can run to see
what it is. I'm talking specifically about hardware raid arrays only.
Thanks,
LeeRoy
Typically you will have a RAID array utility that you would execute to find
out information about the RAID card(s) and array(s) that are configured on a
server.
Keith
"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
> the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
|||"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
Check in the Device Manager applett. It should show you something.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thank you Rick and Keith...
It did answer very well my question, I finally found the answer.
Thanks,
LeeRoy
"Rick Sawtell" wrote:

> "Pascal" <Pascal@.discussions.microsoft.com> wrote in message
> news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> the
> Check in the Device Manager applett. It should show you something.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

How can you determine if your server has Raided disks or not.

Good afternoon,
I'd like to know what the command or where you look to determine if the
disks on an array are raided or not. Is there a command we can run to see
what it is. I'm talking specifically about hardware raid arrays only.
Thanks,
LeeRoyTypically you will have a RAID array utility that you would execute to find
out information about the RAID card(s) and array(s) that are configured on a
server.
Keith
"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
> the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy|||"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
Check in the Device Manager applett. It should show you something.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick and Keith...
It did answer very well my question, I finally found the answer.
Thanks,
LeeRoy
"Rick Sawtell" wrote:

> "Pascal" <Pascal@.discussions.microsoft.com> wrote in message
> news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> the
> Check in the Device Manager applett. It should show you something.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

How can you determine if your server has Raided disks or not.

Good afternoon,
I'd like to know what the command or where you look to determine if the
disks on an array are raided or not. Is there a command we can run to see
what it is. I'm talking specifically about hardware raid arrays only.
Thanks,
LeeRoyTypically you will have a RAID array utility that you would execute to find
out information about the RAID card(s) and array(s) that are configured on a
server.
--
Keith
"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
> the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy|||"Pascal" <Pascal@.discussions.microsoft.com> wrote in message
news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> Good afternoon,
> I'd like to know what the command or where you look to determine if
the
> disks on an array are raided or not. Is there a command we can run to see
> what it is. I'm talking specifically about hardware raid arrays only.
> Thanks,
> LeeRoy
Check in the Device Manager applett. It should show you something.
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick and Keith...
It did answer very well my question, I finally found the answer.
Thanks,
LeeRoy
"Rick Sawtell" wrote:
> "Pascal" <Pascal@.discussions.microsoft.com> wrote in message
> news:9810B2A1-6028-42C4-BE19-E544EC550930@.microsoft.com...
> > Good afternoon,
> > I'd like to know what the command or where you look to determine if
> the
> > disks on an array are raided or not. Is there a command we can run to see
> > what it is. I'm talking specifically about hardware raid arrays only.
> >
> > Thanks,
> >
> > LeeRoy
> Check in the Device Manager applett. It should show you something.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?