How to diagnose a deadly embrace

I have an application which is running on 40 active workstations. I am
encountering lock outs many times a day. When I run the stored procedure
"sp_who2" I can see the spids that are blocked and by which other spid.
If I "kill xx" where xx is the head of the blocking chain the system frees
up. When doing the "sp_who2" I see a command column which only shows the
start of a command like "insert", 'update", "select" or "AWAITING COMMAND".
Is there a way to see the whole command to isolate which table(s) are
causing the problem?
Are there other tools to help me out?
Desparately in need of help...
Mark
Mark Butler wrote:
> I have an application which is running on 40 active workstations. I
> am encountering lock outs many times a day. When I run the stored
> procedure "sp_who2" I can see the spids that are blocked and by which
> other spid.
> If I "kill xx" where xx is the head of the blocking chain the system
> frees up. When doing the "sp_who2" I see a command column which only
> shows the start of a command like "insert", 'update", "select" or
> "AWAITING COMMAND". Is there a way to see the whole command to
> isolate which table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
You can use Profiler to see what command are starting and not completing
or committing as the case may be. If you look at the starting and
completed events, you can see if something did not complete (missing
completed event). That might mean that data was not rolled back. It
sounds like you might have open transactions. Make sure no one is using
SQL Enterprise Manager (or other tools that do not fetch all data at
once) to edit data in tables as they will leave open locks on data.
If the same user is responsibl, you can filter the Profiler data. The
output may generate a lot of information. I would start looking at
SQL:BatchStarting/Completed and RPC:Starting/Completed. If yo uneed more
detail, you can add SQL:StmtStarting/Completed and
SP:StmtStarting/Completed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You could also use DBCC INPUTBUFFER or fn_get_sql on specific spids to find
out what they are doing at a given point in time. See SQL Server Books
Online for more information. You may find my code useful in this scenario:
http://vyaskn.tripod.com/fn_get_sql.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
> I have an application which is running on 40 active workstations. I am
> encountering lock outs many times a day. When I run the stored procedure
> "sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
COMMAND".
> Is there a way to see the whole command to isolate which table(s) are
> causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
|||You can start SQL Server Profiler to trace which table is causing the lock.
John King
http://www.agileinfollc.com
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
>I have an application which is running on 40 active workstations. I am
>encountering lock outs many times a day. When I run the stored procedure
>"sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
> COMMAND". Is there a way to see the whole command to isolate which
> table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>

How to develop a report to use a global datasource?

Hi all..
By using report manager, I can configure a datasource that can be global for
all reports (stored in "/" path).
When I develop the report in VS.NET, I can set the report to use a shared
datasource or a private datasource. When I use a shared datasource, a new
datasource is created under the report path when I deploy it. That way I
always have to manually change connection information of the report using
report manager.
Is there a way to point to the global datasource at design time?
thanks
JaimeHi,
use <DataSourceReference>DSN path</DataSourceReference>
inside the <DataSource> tag for each report, to use a gloal datasource.
Thanks
Bava
"Jaime Stuardo" wrote:
> Hi all..
> By using report manager, I can configure a datasource that can be global for
> all reports (stored in "/" path).
> When I develop the report in VS.NET, I can set the report to use a shared
> datasource or a private datasource. When I use a shared datasource, a new
> datasource is created under the report path when I deploy it. That way I
> always have to manually change connection information of the report using
> report manager.
> Is there a way to point to the global datasource at design time?
> thanks
> Jaime

How to determine the domain for sp_grantlogin?

I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a
username, but also need the domain - as per the BOL
=====================
Syntax
sp_grantlogin [@.loginame =] 'login'
Arguments
[@.loginame =] 'login'
Is the name of the Windows NT user or group to be added. The Windows NT user
or group must be qualified with a Windows NT domain name in the form
Domain\User, for example London\Joeb. login is sysname, with no default.
=====================
I've been using xp_loginconfig 'default domain', but need an alternative
because I no longer have EXEC permission to xp_loginconfig.
Will SERVERPROPERTY('MachineName') give me the domain? If not, what will?
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.
--
VP Technology
Aralan Solutions Inc.Hi
Possibly depending on if you have been granted a direct login
select loginame from sysprocesses
where spid = @.@.SPID
or parse the output of:
exec master..xp_cmdshell 'SET USERDOMAIN'
John
"Hal Heinrich" wrote:

> I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a
> username, but also need the domain - as per the BOL
> =====================
> Syntax
> sp_grantlogin [@.loginame =] 'login'
> Arguments
> [@.loginame =] 'login'
> Is the name of the Windows NT user or group to be added. The Windows NT us
er
> or group must be qualified with a Windows NT domain name in the form
> Domain\User, for example London\Joeb. login is sysname, with no default.
> =====================
> I've been using xp_loginconfig 'default domain', but need an alternative
> because I no longer have EXEC permission to xp_loginconfig.
> Will SERVERPROPERTY('MachineName') give me the domain? If not, what will?
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
> --
> VP Technology
> Aralan Solutions Inc.|||xp_sqlagent_proxy_account 'GET' would return similar information if your
agent were setup to run under an account on the default domain name you're
after... but that is also an extended stored procedure so your permissions
won't allow for that either.
Assuming you already have accounts added to your SQL server on this default
domain name you're after - you could poll the master..syslogins table for th
e
first / top example with a loginname column value like '%\%' and then use
substring to derive it. (yes, it's a hack).
I take it you are using this as a generic procedure to run on multiple
disparate SQL servers residing in more than one domain? If not, I question
why you'd even need to derive the name since it's typically going to be a
fixed value that you could hard-code (unless your box keeps getting shuffled
from domain to domain during those pesky corporate IT consolidation efforts)
=)
"Hal Heinrich" wrote:

> I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a
> username, but also need the domain - as per the BOL
> =====================
> Syntax
> sp_grantlogin [@.loginame =] 'login'
> Arguments
> [@.loginame =] 'login'
> Is the name of the Windows NT user or group to be added. The Windows NT us
er
> or group must be qualified with a Windows NT domain name in the form
> Domain\User, for example London\Joeb. login is sysname, with no default.
> =====================
> I've been using xp_loginconfig 'default domain', but need an alternative
> because I no longer have EXEC permission to xp_loginconfig.
> Will SERVERPROPERTY('MachineName') give me the domain? If not, what will?
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
> --
> VP Technology
> Aralan Solutions Inc.

how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increas
e
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find th
e
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

How to determine table size?

Is there a stored procedure or command for determining the allocation size of
a table and related info?
thanks
Try,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:

> Is there a stored procedure or command for determining the allocation size of
> a table and related info?
> thanks

How to determine table size?

Is there a stored procedure or command for determining the allocation size o
f
a table and related info?
thanksTry,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:

> Is there a stored procedure or command for determining the allocation size
of
> a table and related info?
> thanks

How to determine table size?

Is there a stored procedure or command for determining the allocation size of
a table and related info?
thanksTry,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:
> Is there a stored procedure or command for determining the allocation size of
> a table and related info?
> thankssql

How to determine sp caller current database?

When executing a stored procedure that is defined in another database, as:

USE db1;

EXEC db2.dbo.sproc;

Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

If nothing else, you can use CONTEXT_INFO to retain that information.

To call the stored procedure you can do something like:

Code Snippet

USE db1;

declare @.binVar varbinary(128)
set @.binVar = convert(varbinary(128), 'db1')
set context_info @.binVar

EXEC db2.dbo.sproc;

and to fetch the information from within the stored procedure you can use something like:

Code Snippet

convert(varchar(128), context_info())

( This is assuming that db_name() is not working for you. )

sql

How to determine objects signed by certificate

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

I'm not sure this posted correctly the first time...

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates. There is sys.certificates which doesn't help as far as determining what it has been applied to and a syscerts that I came across in sys.columns, but can't actually query. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'my_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'your_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

Thanks, that is exactly what I was looking for.

How to determine objects signed by a certificate

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

I'm not sure this posted correctly the first time...

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates. There is sys.certificates which doesn't help as far as determining what it has been applied to and a syscerts that I came across in sys.columns, but can't actually query. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'my_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'your_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

Thanks, that is exactly what I was looking for.

How to determine if a user has a permission programmatically?

Hi,
I need to determine in advance if a user logged on to SQL Server 2005 can
execute certain privileged stored procedures and statements (such as
sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
determine programmatically if a user has specific permissions (ALTER ANY
LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
ALTER ANY ROLE).
Is there a way to perform this check programmatically via T-SQL? If this can
be done programmatically, is there a way to determine if a permission is
implied implicitly if it is not set up explicitly (e.g. via a group the user
belongs to, such as BUILTIN\Administrators)? Is there a better approach to
make the check?
Thanks,
AlekTry sp_helprotect , described here
http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Alek" <alekDOTdavisATintelDOTcom> wrote in message
news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to determine in advance if a user logged on to SQL Server 2005 can
> execute certain privileged stored procedures and statements (such as
> sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
> determine programmatically if a user has specific permissions (ALTER ANY
> LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
> ALTER ANY ROLE).
> Is there a way to perform this check programmatically via T-SQL? If this
> can be done programmatically, is there a way to determine if a permission
> is implied implicitly if it is not set up explicitly (e.g. via a group the
> user belongs to, such as BUILTIN\Administrators)? Is there a better
> approach to make the check?
> Thanks,
> Alek
>|||In SQL Server 2005, you can use the has_perms_by_name
function to check the current users effective permissions on
securable.
http://msdn2.microsoft.com/en-us/library/ms189802.aspx
-Sue
On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
<alekDOTdavisATintelDOTcom> wrote:

>Hi,
>I need to determine in advance if a user logged on to SQL Server 2005 can
>execute certain privileged stored procedures and statements (such as
>sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
>determine programmatically if a user has specific permissions (ALTER ANY
>LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
>ALTER ANY ROLE).
>Is there a way to perform this check programmatically via T-SQL? If this ca
n
>be done programmatically, is there a way to determine if a permission is
>implied implicitly if it is not set up explicitly (e.g. via a group the use
r
>belongs to, such as BUILTIN\Administrators)? Is there a better approach to
>make the check?
>Thanks,
>Alek
>|||Thank you Remus. This is very helpful.
Alek
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:%233xgzrYHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Try sp_helprotect , described here
> http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Alek" <alekDOTdavisATintelDOTcom> wrote in message
> news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
>|||Thanks a lot Sue. This looks exactly like what I'm looking for. :-)
Alek
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ng3sn2pdu6apq505ftk8mhnpp73l2i7mk7@.
4ax.com...
> In SQL Server 2005, you can use the has_perms_by_name
> function to check the current users effective permissions on
> securable.
> http://msdn2.microsoft.com/en-us/library/ms189802.aspx
> -Sue
> On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
> <alekDOTdavisATintelDOTcom> wrote:
>
>sql

how to determine if a stored proc is running

I need to determine whether a stored procedure is executiing and perform
various steps dependent on the state of the procedure in question (i.e.,
determined from sysprocesses.status). I know I can get the listing of
active commands sysprocesses.cmd (from the master table, sysprocesses),
but this doesn't give me the _stored procedure_ name, as it appears in
Enterprise Manager when you click on the ProcessID and it gives the
"Last TSQL Batch..." Where exactly is this information is this stored?
Somewhere, I assume, in MDDB?
TIA!
gms--Greg M. Silverman wrote:
> I need to determine whether a stored procedure is executiing and
> perform various steps dependent on the state of the procedure in
> question (i.e., determined from sysprocesses.status). I know I can get
> the listing of active commands sysprocesses.cmd (from the master
> table, sysprocesses), but this doesn't give me the _stored procedure_
> name, as it appears in Enterprise Manager when you click on the
> ProcessID and it gives the "Last TSQL Batch..." Where exactly is this
> information is this stored? Somewhere, I assume, in MDDB?
> TIA!
> gms--
>
okay, looks like DBCC INPUTBUFFER (pid) gives me what I need.
gms--

How to determine if a SP is running?

I need to to modify a stored procedure so that it can
determine if it is already running. I could create a
table to add/opdate a status record, but I would prefer
to read a system table looking for the proc running on
a connection if possible.
If anyone has done something like this, I would really
like to hear about it.
tia,
BillYou can't do that directly, but you can use an application lock inside your
stored procedure. See sp_getapplock in Books Online for the details.
Jacco Schalkwijk
SQL Server MVP
<bill_sheets@.hotmail.com> wrote in message
news:1107442939.517642.53580@.g14g2000cwa.googlegroups.com...
>I need to to modify a stored procedure so that it can
> determine if it is already running. I could create a
> table to add/opdate a status record, but I would prefer
> to read a system table looking for the proc running on
> a connection if possible.
> If anyone has done something like this, I would really
> like to hear about it.
> tia,
> Bill
>

How to determine EXEC permission to an extended stored procedure?

The following proc indicates whether you have EXEC permission to a proc -
however it fails for extended procs. I'd be grateful for a fix!
A good test is @.SPNM = 'xp_sprintf'. Note that the proc is getting a valid
object id for the extended procs.
PROCEDURE procHasExecutePermission
( @.SPNM sysname,
@.HAS bit OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.OID int
SET @.OID = OBJECT_ID(@.SPNM)
IF @.OID IS NULL
IF SUBSTRING(@.SPNM, 1, 3) = 'sp_' OR SUBSTRING(@.SPNM, 1, 3) = 'xp_'
SET @.OID = OBJECT_ID('master..' + @.SPNM)
IF @.OID IS NULL
SET @.HAS = 0
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
END
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.It doesn't fail (only) for extended procs, it fails for the objects
that begin with 'sp_' or 'xp_', because you are getting the OBJECT_ID
for the object from the master database, but the PERMISSIONS function
accepts only object id-s for objects from the current database. In some
cases, it may look like it's working for some objects from master, but
that's only because the same object id is allocated in the current
database for another object.
If you need to check for objects that may be in master, I would use
another procedure like this:
USE master
GO
CREATE PROCEDURE procHasExecutePermission1
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
USE YourDatabase
GO
CREATE PROCEDURE procHasExecutePermission2
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
CREATE PROCEDURE procHasExecutePermission3
(@.SPNM sysname, @.HAS int OUTPUT) AS
IF LEFT(@.SPNM,3)='sp_'
EXEC master..procHasExecutePermission1 @.SPNM, @.HAS OUTPUT
IF @.HAS IS NOT NULL RETURN
EXEC procHasExecutePermission2 @.SPNM, @.HAS OUTPUT
I have used sysobjects to check the object type, so if you pass a table
name it will return NULL instead of 0.
You may want to improve these procedures using PARSENAME if you need to
allow procedure names prefixed with the database name. If the database
name may also be other than the current database or the master
database, then it gets complicated... there may be a solution by
calling the PERMISSIONS function from Dynamic SQL.
Razvan

How to determine compiled proc size?

How do I determine the amount of server memory a stored procedure (or any
other object) takes up?http://www.sql-server-performance.com/rd_data_cache.asp gives some
information regarding querying the syscacheobjects system table and what
each row in the table means. You should be able to use that to derive
the number of pages of memory used with many kinds of server objects.
Good luck,
Tony Sebion
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:

> How do I determine the amount of server memory a stored procedure (or any
> other object) takes up?|||There's also an undocumented DBCC command: DBCC MEMOBJLIST but it requires
starting the service with the T3654 flag and it doesn't show what's in AWE
memory.
"Tony Sebion" wrote:

> http://www.sql-server-performance.com/rd_data_cache.asp gives some
> information regarding querying the syscacheobjects system table and what
> each row in the table means. You should be able to use that to derive
> the number of pages of memory used with many kinds of server objects.
> Good luck,
> Tony Sebion
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:
>
>

How to detect if SQL Reporting Services is installed ?

From an ASP.Net application I need to detect if on a given node, Repoting
services is installed and if so, which version. Does anyone know a stored
procedure or a Web Services API to do this ?
thanks,RaviHello Ravi,
There are a couple ways to determine RS version:
1) Check the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Reporting Services\Version
2) Use the webservice API like this:
For RS 2000:
ReportingService rs1 = new ReportingService();
rs1.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs1.ServerInfoHeaderValue = new ServerInfoHeader();
rs1.ListChildren("/", false);
Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerVersionNumber);
For RS 2005:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
Note that there is a new property in 2005, "ReportServerVersion". Also be
aware that ReportServerVersionNumber returns differntly formatted values
for 2000 and 2005.
-Chris
> From an ASP.Net application I need to detect if on a given node,
> Repoting services is installed and if so, which version. Does anyone
> know a stored procedure or a Web Services API to do this ?
> thanks,Ravi
>|||Thanks Chris, that was very helpful.
I have started using the #1 to read the registry. But my requirement is that
I need to be able to detect RS and its version on local and remote machines,
including ones behind firewall. Reading Registry in these secanrios leads to
lot of security issues. Further, I also need to detect if RS is currently
running, I need this to enable a button to launch Report Builder.
Using the RS2005 Web Services seems to be a better approach. Do you know of
an API to also detect if Report Services is currently running ? Do you know
what happens if Report Services is not installed on a remote machine but we
try to call a Web Service ?
thanks for your time and help,
Ravi
"Chris Baldwin" wrote:
> Hello Ravi,
> There are a couple ways to determine RS version:
> 1) Check the registry at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Reporting Services\Version
> 2) Use the webservice API like this:
> For RS 2000:
> ReportingService rs1 = new ReportingService();
> rs1.Credentials = System.Net.CredentialCache.DefaultCredentials;
> rs1.ServerInfoHeaderValue = new ServerInfoHeader();
> rs1.ListChildren("/", false);
> Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerEdition);
> Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerVersionNumber);
> For RS 2005:
> ReportingService2005 rs = new ReportingService2005();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> rs.ServerInfoHeaderValue = new ServerInfoHeader();
> rs.ListChildren("/", false);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
> Note that there is a new property in 2005, "ReportServerVersion". Also be
> aware that ReportServerVersionNumber returns differntly formatted values
> for 2000 and 2005.
> -Chris
>
> > From an ASP.Net application I need to detect if on a given node,
> > Repoting services is installed and if so, which version. Does anyone
> > know a stored procedure or a Web Services API to do this ?
> >
> > thanks,Ravi
> >
>
>

How to detect if a cursor has not been de-allocated or is still open?

Hi,

I am using try/catch block for my stored procedure (SQL Server 2005), when a cursor is used, how do I detect if the cursor has not been de-allocated or is still open?

Right now, I am using something such as

Begin Try

Begin Transaction

..................

-- mycursor will be declared and used here

....................

Commit Transaction

End Try

Begin Catch

Begin Try

Close mycursor

Deallocate mycursor

End Try

Begin Catch

--if mycursor was already closed/de-allocated,

End Catch

End Catch

I have a feeling that there must be a better way to do it.

Any suggestion?

Thanks.

Cathie

Actually this is not a bad way to go if you have to use global cursors (or really cursors at all, though that is another topic). If you aren't using global cursors (DECLARE CURSOR <name> LOCAL , or use a Variable SET @.cursor = CURSOR , or even set the database option:

ALTER DATABASE <databaseName>
SET CURSOR_DEFAULT GLOBAL

And your cursors will deallocate automatically when they lose scope.

|||

Hello,

Just query the results of the new dmf:

select * from sys.dm_exec_cursors(0) -- 0 being all sessions, otherwise pass in @.@.spid

Cheers,

Rob

|||

The following script might help you...

Read more on BOL about sp_cursor_list

Code Snippet

DECLARE My_Cursor CURSOR

FOR SELECT * FROM Sys.Objects

OPEN My_Cursor

FETCH NEXT FROM My_Cursor

DECLARE @.CursorName AS VARCHAR(100)

DECLARE @.Report CURSOR

EXEC master.dbo.sp_cursor_list @.cursor_return = @.Report OUTPUT,

@.cursor_scope = 3;

FETCH NEXT INTO @.CursorName from @.Report

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN

IF @.CursorName = 'My_Cursor'

BEGIN

CLOSE My_Cursor

DEALLOCATE My_Cursor

END

FETCH NEXT INTO @.CursorName from @.Report

END

CLOSE @.Report

DEALLOCATE @.Report

sql

How to detect a stored proc is running from TSQL?

I launch a stored proc from a job that is scheduled several times a day. I want the proc to detect if it is still running (from a previous process) and exit out if it is. I want to be absolutely sure it is or is not running.

Thanks!

I don't know of a straight forward way to check if a stored prcocedure is running but I have a couple of workarounds. Before that I just want to highlight if the only source that is running the stored procedure is the job you're talking about, then if it is still running and it is time for the next job, the next instance won't run so you wouldn't have the case that 2 job instances are running in the same time. Regardless here are the workarounds:

Include in your database a table that you can use to check the status of the stored procedure. For example a table called SystemStatus that includes a Key column and a Status column. Add a row for this procedure with initial value in the status false (for not running). Make the first statement in the procedure a check on the value of the status field for this row. If it true then another instance is running so end the procedure. If it is false then update it to true and let the final statement in the procedure update it back to false|||

I can't able to understand "I want the proc to detect if it is still running (from a previous process) and exit out if it is"

You mean, to find the current SP is already running on your Server (or job) before executing it.

If yes, you need not worry about it. Bcs if the current job is executing then the SQL server never initate the next scheduled job, it will wait to complete the current job, then it will find the next possible schedule to execute.

if you 2 or more parllel jobs with same SP (not really required) then you need to follow some log based events.

|||

Sami,

Thank you for your quick and detailed response. Actually I have used this method before. Unfortunately, this transaction is what I wanted to avoid here because it is a long-running proc that runs many other procs so I wanted to avoid holding blocking other users just to prevent my proc from running simultaneously. Thanks again!

Mr. P.

|||

You could use application locks in SQL Server. Something like below should work. This can be simplified a bit in SQL Server 2005.

Code Snippet

exec @.rc = sp_getapplock 'Only_this_SP', 'Exclusive', default, 0

if @.rc = -1

begin

-- SP is already running:

end

-- Release lock at end of SP:

exec @.rc = sp_releaseapplock 'Only_this_SP'

How to deploy the database

Hi All,

Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.

Thank U

You can generate scripts for them. Right click on db - > All Tasks -> Generate Scripts.. follow the wizard.

|||

I know that.But is it possible to create exe or msi package for the sql objects.just like deploying the asp.net application

|||

I'm sorry.I forgot to tell you(Dinakar).Thank You.

|||

There is an osql utility to compile the scripts from a file..