Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regards
sp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regardssp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>

Check Roles assign to user

Hello,
Can you tell me how can i verify to what roles a user is assigned? Is there
any system table or stored procedure to check this information?
Best regardssp_helplogins,sp_helpusers
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:A1DF2C34-92DF-4525-871C-FBA160B0F705@.microsoft.com...
> Hello,
> Can you tell me how can i verify to what roles a user is assigned? Is
there
> any system table or stored procedure to check this information?
> Best regards
>sql

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanks
The IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>
|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>
|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
--
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> > How can I check one of my db backups result is vaild in vb or transact
> > sql?
> >
> > For example
> >
> > if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> > 'restore it
> > else
> > ' send a message
> > End if
> >
> > thanks
> >
> >
> >
>

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>

check restore verifyonly result

How can I check one of my db backups result is vaild in vb or transact sql?
For example
if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
'restore it
else
' send a message
End if
thanksThe IF/ELSE method might not work since some errors will terminate the
batch. However, you can check @.@.ERROR (or catch in you VB app):
RESTORE VERIFYONLY
FROM DISK='C:\1.bak'
GO
IF @.@.ERROR = 0
BEGIN
RESTORE DATABASE MyDatabase
FROM DISK='C:\1.bak'
END
ELSE
BEGIN
PRINT 'Cannot restore from backup'
END
GO
Note that RESTORE VERIFYONLY does only a cursory check to see if the backup
is valid. The best way to make sure is with an actual restore, perhaps to a
different database name.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||You should use SQLDMO
This library has an object RESTORE which has a method SQLVerify that can be
used to check the health of a backup media.
Bien cordialement
Med Bouchenafa
"Tolgay" <tgul@.tgul.com> wrote in message
news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> How can I check one of my db backups result is vaild in vb or transact
> sql?
> For example
> if (RESTORE VERIFYONLY FROM disk=c:\1.bak) = isvaid then
> 'restore it
> else
> ' send a message
> End if
> thanks
>
>|||thank you Dan,
it works good.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6y$4bz$FHA.4036@.TK2MSFTNGP10.phx.gbl...
> The IF/ELSE method might not work since some errors will terminate the
> batch. However, you can check @.@.ERROR (or catch in you VB app):
> RESTORE VERIFYONLY
> FROM DISK='C:\1.bak'
> GO
> IF @.@.ERROR = 0
> BEGIN
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\1.bak'
> END
> ELSE
> BEGIN
> PRINT 'Cannot restore from backup'
> END
> GO
> Note that RESTORE VERIFYONLY does only a cursory check to see if the
backup
> is valid. The best way to make sure is with an actual restore, perhaps to
a
> different database name.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tolgay" <tgul@.tgul.com> wrote in message
> news:uGDJ6Jz$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>

check replication conflicts programmatically

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amit
mitsql,
yes the conflict_ tables contain the necessary information. In detail
they contain the column values of the losing replication partner plus
the reason for the conflict and the guid. If you want to be notified
when a conflict occurs you can create an insert trigger on the conflict
tables which either sends a mail or runs some sript/store procedure.
Markus
sql

check replication conflicts programmatically

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amitmitsql,
yes the conflict_ tables contain the necessary information. In detail
they contain the column values of the losing replication partner plus
the reason for the conflict and the guid. If you want to be notified
when a conflict occurs you can create an insert trigger on the conflict
tables which either sends a mail or runs some sript/store procedure.
Markus

check replication conflicts programmatically

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amitmitsql,
yes the conflict_ tables contain the necessary information. In detail
they contain the column values of the losing replication partner plus
the reason for the conflict and the guid. If you want to be notified
when a conflict occurs you can create an insert trigger on the conflict
tables which either sends a mail or runs some sript/store procedure.
Markus

Check remotely if Reporting Services exist

is there a way to check the reporting services remotely.. using the reporting services url?

What do you actually Mean by checking reporting Services remotely? What is your requirement?|||

i want to check if a remote server has an existing reporting service or runs a reporting service...

|||You can check using

http://servername/reportserver

or

http://servername/reports

|||

yeah... but how can i check it programatically?|||Can you specify the scenario where you need this checking.May be I can help you|||

im trying to make an application that can connect to multiple report server... i need to get all the available report server within the network then connect to that report service...

|||

hii

Is this the thing you mean to say i mean you want to see your reporting serverices ?

if this the thing u wanted then you can go for the following path.

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServe

cheers

regards

Mahasweta

|||May be this will help you

Check the link

http://msdn2.microsoft.com/en-us/library/aa226200(SQL.80).aspx

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Management;

using System.IO;

[assembly: CLSCompliant(true)]

namespace TGest

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

const string WmiNamespace = @."\\localhost\root\Microsoft\SqlServer\ReportServer\v9\Admin";

const string WmiRSClass =

@."\\localhost\root\Microsoft\SqlServer\ReportServer\v9\admin:MSReportServer_ConfigurationSetting";

ManagementClass serverClass;

ManagementScope scope;

scope = new ManagementScope(WmiNamespace);

// Connect to the Reporting Services namespace.

scope.Connect();

// Create the server class.

serverClass = new ManagementClass(WmiRSClass);

// Connect to the management object.

serverClass.Get();

if (serverClass == null)

throw new Exception("No class found");

// Loop through the instances of the server class.

ManagementObjectCollection instances = serverClass.GetInstances();

foreach (ManagementObject instance in instances)

{

MessageBox.Show("Instance Detected");

}

}

}

}

|||

thanks man! i got all the reporting services instance in my local my machine... but im trying to access a remote workstation and get all the report server instance in there... but im getting an error "{"Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"}". i've already use a default authentication level(Windows authentication). here's my code..

Code Snippet

try

{

const string WmiNamespace = @."\\bserver\root\Microsoft\SqlServer\ReportServer\v9\Admin";

const string WmiRSClass =

@."\\bserver\root\Microsoft\SqlServer\ReportServer\v9\admin:MSReportServer_ConfigurationSetting";

ManagementClass serverClass;

ManagementScope scope;

ConnectionOptions options;

options = new ConnectionOptions();

options.Authentication = AuthenticationLevel.Default;

scope = new ManagementScope(WmiNamespace,options);

// Connect to the Reporting Services namespace.

scope.Connect();

// Create the server class.

serverClass = new ManagementClass(WmiRSClass);

// Connect to the management object.

serverClass.Get();

if (serverClass == null)

throw new Exception("No class found");

// Loop through the instances of the server class.

ManagementObjectCollection instances = serverClass.GetInstances();

foreach (ManagementObject instance in instances)

{

Console.Out.WriteLine("Instance Detected");

PropertyDataCollection instProps = instance.Properties;

foreach (PropertyData prop in instProps)

{

string name = prop.Name;

object val = prop.Value;

Console.Out.Write("Property Name: " + name);

if (val != null)

Console.Out.WriteLine(" Value: " + val.ToString());

else

Console.Out.WriteLine(" Value: <null>");

}

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

thanks!!

Check Referential Ingerity or Catch Error

Hi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

CyherusHi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

Cyherus

Any error above 16 won't be caught. You need to catch those at the client or app level. If you're having these types of RI issues though, you have an app design issue. The PK should already be known by the app when you go to insert foreign key records. On the insert of a new PK, it should either be auto or a true natural key. In this case, there is no chance of error.|||Many thanks.. now things are much clearer..

I'm reading these data from a text file and foreign keys on these records are not checked against the primary tables in my DB, thus the need to handle this.

I dun quite get you when you mention about inserting new PKs, what do you mean by auto or true natural key??

Check Recovery Model

Hello,
How can i get information about the recovery model of one
database without using the Enterprise Manager?
What table should i query to get this information?
Best RegardsYou can get the recovery model for a database with:
SELECT DATABASEPROPERTYEX('<database name>','Recovery')
for example:
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards|||To add to Jacco's response, the recovery model is also available via
sp_helpdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regardssql

Check Recovery Model

Hello,
How can i get information about the recovery model of one
database without using the Enterprise Manager?
What table should i query to get this information?
Best Regards
You can get the recovery model for a database with:
SELECT DATABASEPROPERTYEX('<database name>','Recovery')
for example:
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards
|||To add to Jacco's response, the recovery model is also available via
sp_helpdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards

Check Recovery Model

Hello,
How can i get information about the recovery model of one
database without using the Enterprise Manager?
What table should i query to get this information?
Best RegardsYou can get the recovery model for a database with:
SELECT DATABASEPROPERTYEX('<database name>','Recovery')
for example:
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards|||To add to Jacco's response, the recovery model is also available via
sp_helpdb.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:449b01c42b91$3600c950$a501280a@.phx.gbl...
> Hello,
> How can i get information about the recovery model of one
> database without using the Enterprise Manager?
> What table should i query to get this information?
> Best Regards

check query status

Is it possible to create an SSIS package that checks for a running Query on my SQL db?

I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?

There is nothing specific in SSIS that can give you that info; but perhaps you can put a query that gives you that inside of an execute sql task...|||

I want to use SSIS to create the package and use the SQL Task in the package, But how can I 'ping' the sql server to verify the sql query is still running or not? That's the portion I'm stuck on.

What would that query look like? Can this even be done?

|||Your question is one of a Transact-SQL nature and as such should probably be asked over in that forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Come back here if you need help implementing the resulting query inside SSIS.|||You can use sp_who or sys.sysprocesses to get lists of running processes. sys.sysprocesses would be easier to work with, if you are on 2005.

Check Privs

Hi all,

I have an webapplication and I want to let the app check the privileges of the user logged in. with that info the app must show some functions of the system and hide others.

I want to know if somenthing like this is possible and how I can do It

PS.
MySQL has a table with users, tables and the privileges those users had on that table and you could just use a select statement to shwo them.
(Is something like this also possible in SQL server 2k)

thnx in advanceEY guys, a big part of my app is this security issue.
If someone knows if this is possible and how to do this or any kind of information, plz tell me.

if you think I want to do something impossible tell me also then I can resign from this assignment ;)|||Hi,
I need some help with logical thinking.

I can retrieve the permissions allowed to a user or role. but if I grant a role an action let's say delete and grant an user to that role I can't retrieve the permission to delete for that user.

What I want to do is to use an own Sproc and sp_helprotect and sp_helpuser to put the retrieved permissions in a table and check the members of a particilar role. then I want to let the aplication get the retrieved information from that table.
because I can't get the output value of a sproc in my application (asp.net), I think this is the best option.

But I don't know if this is wise or not to do like this.
So I ask if somebody knows a better way.

PS.The only thing I can get from the sproc to my app is the Rows affected and the result of a select query

check previous row in the table

Hi...

Is there any way to check previous row in SQL Query?

I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes

I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.

Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD

In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.

Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.You can use a Cursor in a Stored Procedure. It would be very complex though...|||Check out the EXISTS keyword (assuming SQL server)...sql

Check periodically if SQL server is running

Hi,

I have my SQL server installed and running on a windows 2003 server. Sometimes, the server stops unexpectedly. I want to know if there is any mechanism to alert as and when the SQL server or SQL server agent goes down.

I guess everything is a windows service. Can we have a mechanism to test this service from a script and send an alert mail when the server goes down? How can this be done from a script?

Thanks in advance

-SudhakarIf you have two servers, you could have each server send periodic commands to the other to see if they are awake.
Or you could schedule a job to e-mail you at regular intervals and when you don't get a notification you'd know something was wrong.

...or you could fix the f'ing server...|||set the services to autostart and you can use sp_procoption to send you an email everytime sql server restarts.

you ought to figure out why your server is stalling out though.|||You can also use EXECUTE xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'|||I think you should kick the server, that would fix it

Check Performance for queries

Hi,
I have two queries which will yield me the same result.
Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
check the performance of both these queries and select one.
I have heard of performance plans... all those things. I am not
familiar to it. But I would like to learn.
pls guide me
Thanks
kiranAs a starter you might use the SET STATISTICS option (see BOL). Further
on you can view execution plans by selecting the option in the query
menu of Query Analyzer.
M|||Hi
You may want to look at using SQL profiler to do this see duration, reads,
writes and CPU used. You may want to see Tony's blog casts on
http://www.sqlserverfaq.com/ for an introduction to SQL Profiler. Also check
out the topic "Query Tuning", "Analyzing Queries" and "Analyzing a Query" in
books online on who to display query plans and statistics.
John
"kiran" wrote:

> Hi,
> I have two queries which will yield me the same result.
> Is there a way in SQL Server(Query analyzer or Enterprise Manager) to
> check the performance of both these queries and select one.
> I have heard of performance plans... all those things. I am not
> familiar to it. But I would like to learn.
> pls guide me
>
> Thanks
> kiran
>|||Mark he query you wnat to examine and hit CTRL-L in QA.
HTH, jens Suessmeyer.|||Jens wrote:
> Mark he query you wnat to examine and hit CTRL-L in QA.
> HTH, jens Suessmeyer.
>
Thanks a lot Guys, I will give it a try.|||Hi
Also look at running DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear
out the buffer and procedure cache.
http://www.transactsql.com/html/DBC...EANBUFFERS.html
http://www.transactsql.com/html/DBC...EPROCCACHE.html
John
"kiran" wrote:

> Jens wrote:
> Thanks a lot Guys, I will give it a try.
>