Finding dependency of Objects in SQL Server 2000

Hello,

I would like to know , how can we find the objects dependency in SQL Server.

e.g If there is a Stored Procedure (sp_GetShowEmp) , I need to find On which tables,views,Stored Procedures,Functions is dependent. And Name of the other objects which are dependent on the Stored Procedure (sp_GetShowEmp).

Thanks

Sidheshwar

You can take help of SP_DEPENDS in this case to show the dependancy of other objects on a table or trigger or view.

Finding columns from sysindexes.

I understand that in the "Keys" column in the "sysindexes" table, the columns are stored in Binary format. Is there any way by which I can get these column names by deciphering sysindexes.keys ? I don't want to use index_col() function.Use sysindexes, syscolumns, sysindexkeys. See BOL for details.|||Thanks a lot. I used your suggestion and got the results. I had forgotten about the sysindexkeys table initially. Thanks again. --Suresh.

Finding BINN directory for Multiple Instances

I am writing an installer that needs to put a dll file with a bunch of
extended stored procedures in the BINN directory. The problem is that
if the machine is running multiple instances of SQL Server, there is
more than one BINN directory. The installer has a dynamically
populated dropdown with the name of the instances, so does anyone know
if this logic is correct and/or reliable assuming SQL Server 2000?

If the instance name = "MSSQLSERVER"
then path to BINN directory = registry value of
HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"

If the instance name != "MSSQLSERVER"
then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"

Thanks in advance for any help."Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0407231210.28d2403c@.posting.google.c om...
> I am writing an installer that needs to put a dll file with a bunch of
> extended stored procedures in the BINN directory. The problem is that
> if the machine is running multiple instances of SQL Server, there is
> more than one BINN directory. The installer has a dynamically
> populated dropdown with the name of the instances, so does anyone know
> if this logic is correct and/or reliable assuming SQL Server 2000?
> If the instance name = "MSSQLSERVER"
> then path to BINN directory = registry value of
> HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"
> If the instance name != "MSSQLSERVER"
> then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
> SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"
> Thanks in advance for any help.

See questions 12 and 13:

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

Simon

Finding all references to a column

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?[posted and mailed, please reply in news]

rnewman (newmanr19@.yahoo.com) writes:
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl')
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Finding all "DEFAULT" constraints on a table using Information_Sch

I have a couple stored procedures in which I am trying to determine all of
the "Default" constraints that are on a given table. I know that this
information is available in the sysobjects table, but I'm trying to avoid
directly querying the system tables whenever possible in favor of using the
Information Schema views. My problem is that the Table_Constraints view
appears to show only CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE constraints
.
Is there any other Information Schema view that I can use to see the DEFAULT
constraints?information_schema.columns. Will have the default values.
Hope this helps.|||Try using system table sysconstraints.
Example:
use northwind
go
select
object_name([id]) as table_name,
object_name(constid) as constraint_name,
col_name([id], colid) as column_name
from
sysconstraints
where
[id] = object_id('dbo.orders')
and objectproperty(constid, 'IsDefaultCnst') = 1
go
AMB
"DoubleBlackDiamond" wrote:

> I have a couple stored procedures in which I am trying to determine all of
> the "Default" constraints that are on a given table. I know that this
> information is available in the sysobjects table, but I'm trying to avoid
> directly querying the system tables whenever possible in favor of using th
e
> Information Schema views. My problem is that the Table_Constraints view
> appears to show only CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE constrain
ts.
> Is there any other Information Schema view that I can use to see the DEFA
ULT
> constraints?
>sql

Finding affected tables

I am attempting to document a sql server 2000 based accounting system.
Is there any way to see what tables a stored procedure affects
without diving into the code?

Regards,

Ty"Tyler Hudson" <tylerh@.allpax.com> wrote in message
news:804fa945.0403020849.fcf8f0b@.posting.google.co m...
> I am attempting to document a sql server 2000 based accounting system.
> Is there any way to see what tables a stored procedure affects
> without diving into the code?
>
> Regards,
> Ty

You can try sp_depends, although it may not be completely accurate,
depending on the order in which objects were created.

Simon

Find which SPs have quoted_identifier set wrong

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

(No huge rush, the someone-else has already fixed the recent case)

SQL 2K, SP3Ed Murphy wrote:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

You can query against the SP text as follows.

select o.[name] as 'ProcName', c.[text] as 'ProcText'
from sysobjects o
join syscomments c on o.[id] = c.[id]
where o.[name] like 'usp%'

This is based on the assumption that all your SPs start with 'usp'.
Applying a WHERE clause should enable you to filter out certain procs.

Somebody please step in and correct this as I have a feeling it's not
the best way.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

SELECT name
FROM sysobjects
WHERE objectproperty(id, 'ExecIsQuotedIdentOn') = 0

I'm assuming here that OFF is the incorrect position.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Find what procedure was execd

Hello

I need to find out what stored procedure was executed when I click on
a button in a third party application, is there a way to find this
information out in some sort of log?

Some kind of profiling to see what procedure was executed and with
what parameters.

Kind Regards

Matt
www.fruitsalad.org"Matt" <matt@.fruitsalad.org> wrote in message
news:b609190f.0312130930.377f4d7c@.posting.google.c om...
> Hello
> I need to find out what stored procedure was executed when I click on
> a button in a third party application, is there a way to find this
> information out in some sort of log?
> Some kind of profiling to see what procedure was executed and with
> what parameters.
> Kind Regards
> Matt
> www.fruitsalad.org

You can use SQL Profiler to trace all the SQL sent to the server - see Books
Online for details.

Simon

find type of a column

hi

is it possible to find a column's type inside stored procedure? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it

regards

Maybe something like this:

use tempdb
go

create table ##typeExample (anotherType integer, targetType double precision)

declare @.objectName sysname set @.objectName = '##typeExample'
declare @.columnName sysname set @.columnName = 'targetType'

select c.colid,
c.[name] as columnName,
t.[name] as type
from sysobjects o
inner join syscolumns c
on o.id = c.id
and o.[name] = @.objectName
and c.[name] = @.columnName
-- and o.type = 'U' -- Uncomment if you want only user tables
-- and o.type in ('U','V') -- Uncomment if you want tables and views
inner join systypes t
on c.xtype = t.xtype

-- - Output: -

-- colid columnName type
-- -
-- 2 targetType float

go

drop table ##typeExample
go

Dave

Find the Stored procedure

Hello,
Our SQL machine is getting bogged down by some sort of stored procedure
and I am trying to find which one. My SQLdiagnostic software (by Idera)
that monitors our SQL server, says that these commands are executing
and taking upwards of 30 minutes to run. This is new and unexpected.

The commands are:

exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
25, @.Pm2 = 2, @.Pm3 = 1
exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
105, @.Pm2 = 2, @.Pm3 = 1
exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
57, @.Pm2 = 2, @.Pm3 = 1

I am getting pages of these and yesterday the are taking upto 30
minutes to run (currently they are taking 1-2 minutes to complete w/o
people on the machine).

We are not getting much help from our software vendor (of our
admissions software, not Idera) on this matter. I have sa access to the
SQL machine and I can see the pages and pages of stored procedures, but
I don't know what the above is running. I want to find the stored
procedure that keeps getting executed. Is the @.Pm0 = an encrypted
entry?

Any advice I would appreciate.
Thanks
Rob Camardarcamarda wrote:
> Hello,
> Our SQL machine is getting bogged down by some sort of stored procedure
> and I am trying to find which one. My SQLdiagnostic software (by Idera)
> that monitors our SQL server, says that these commands are executing
> and taking upwards of 30 minutes to run. This is new and unexpected.
> The commands are:
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 25, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 105, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 57, @.Pm2 = 2, @.Pm3 = 1
> I am getting pages of these and yesterday the are taking upto 30
> minutes to run (currently they are taking 1-2 minutes to complete w/o
> people on the machine).
> We are not getting much help from our software vendor (of our
> admissions software, not Idera) on this matter. I have sa access to the
> SQL machine and I can see the pages and pages of stored procedures, but
> I don't know what the above is running. I want to find the stored
> procedure that keeps getting executed. Is the @.Pm0 = an encrypted
> entry?
> Any advice I would appreciate.
> Thanks
> Rob Camarda

The replies to this post suggest that the call may be coming from the
..NET SqlClient, although it seems strange that you don't see the actual
command parameter passed to sp_executesql:

http://groups.google.co.uk/group/mi...91b9670a?hl=en&

But beyond that I have absolutely no idea - you might try posting in
microsoft.public.dotnet.framework.adonet to see if anyone can give you a
clue on how to investigate further.

Simon|||Hi

sp_executesql is documented in BOL the first parameter is a Unicode string
to be executed. you may want to run SQL profiler to find out what the
commands are.

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126614746.141363.266270@.f14g2000cwb.googlegr oups.com...
> Hello,
> Our SQL machine is getting bogged down by some sort of stored procedure
> and I am trying to find which one. My SQLdiagnostic software (by Idera)
> that monitors our SQL server, says that these commands are executing
> and taking upwards of 30 minutes to run. This is new and unexpected.
> The commands are:
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 25, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 105, @.Pm2 = 2, @.Pm3 = 1
> exec sp_executesql @.Pm0 = 0x683AAD4E8159A84C90B65216A4DA25DE, @.Pm1 =
> 57, @.Pm2 = 2, @.Pm3 = 1
> I am getting pages of these and yesterday the are taking upto 30
> minutes to run (currently they are taking 1-2 minutes to complete w/o
> people on the machine).
> We are not getting much help from our software vendor (of our
> admissions software, not Idera) on this matter. I have sa access to the
> SQL machine and I can see the pages and pages of stored procedures, but
> I don't know what the above is running. I want to find the stored
> procedure that keeps getting executed. Is the @.Pm0 = an encrypted
> entry?
> Any advice I would appreciate.
> Thanks
> Rob Camardasql

Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
Jamie
You may want to check out the system table syscomments where the source of
all user procedures are held.
Anith
|||http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie

Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
---
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
JamieYou may want to check out the system table syscomments where the source of
all user procedures are held.
Anith|||http://databases.aspfaq.com/databas...br />
ext.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> ---
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie

Find specific text in a string

Hai ,
in a textbox am having text as
"(20/100)+pay1*pay2" .it's a formula. and stored in a particular
variable.
string strformula="(20/100)+pay1*pay2" ;
i've to substitute the value of the variable 'pay1' & 'pay2' and
finding the value of that strformula.
can any onr tell me how to find 'pay1' and 'pay2' in the variable
strformula. it's urgent and reply immediately.
Thanks in advance.Hi
I am not sure what this has to do with SQL Server!
If the strings are unique then you could use the replace function.
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139642884.262422.60580@.g14g2000cwa.googlegroups.com...
> Hai ,
> in a textbox am having text as
> "(20/100)+pay1*pay2" .it's a formula. and stored in a particular
> variable.
> string strformula="(20/100)+pay1*pay2" ;
> i've to substitute the value of the variable 'pay1' & 'pay2' and
> finding the value of that strformula.
> can any onr tell me how to find 'pay1' and 'pay2' in the variable
> strformula. it's urgent and reply immediately.
> Thanks in advance.
>|||If am having the value as
string strvalue ="(12.23+233.56)*12/100";
i've to find the value.so that am converting to double. but it throws
error. tel me how to find value?|||Hi
It is still not clear if you are using SQL Server! If you are then you can
do something like:
DECLARE @.strformula nvarchar(60)
DECLARE @.nparams nvarchar(80)
DECLARE @.output decimal(10,4)
DECLARE @.pay1 decimal(10,4)
DECLARE @.pay2 decimal(10,4)
SET @.strformula = N'SELECT @.output_val = (20.0/100)+(@.pay_1*@.pay_2)'
SET @.nparams = N'@.output_val decimal(10,4) OUTPUT, @.pay_1 decimal(10,4),
@.pay_2 decimal(10,4)'
SET @.pay1 = 233.56
SET @.pay2 = 12.0/100
SELECT @.strformula
SELECT @.nparams
EXEC sp_executesql @.strformula, @.nparams, @.output_val = @.output OUTPUT,
@.pay_1 = @.pay1, @.pay_2 = @.pay2
SELECT @.output
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>|||Actually I meant that your syntax wasn't SQL syntax:

Not mine. It is T-SQL :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:...
> First, this clearly isn't SQL Syntax, but that notwithstanding, you can
> evaluate a function like this, (as long as it fits SQL Syntax of course)
> declare @.value decimal (10,8)
> declare @.formula varchar(200), @.query nvarchar(2000)
> set @.formula = '(12.23+233.56)*12/100'
> set @.query = 'select @.value = (' + @.formula + ')'
> EXEC sp_executesql @.query,
> N'@.Value decimal(10,8) output',
> @.value output
> select @.value
> I would strongly suggest against it, since this is really not SQL's
> strongpoint. This is one of the rare cases where I would probably suggest
> you storing the expression and the answer in two columns (using the middle
> tier layer to calculate the value, or this method could be used in a a
> singleton insert.)
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> <ksrajalakshmi@.gmail.com> wrote in message
> news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
>|||First, this clearly isn't SQL Syntax, but that notwithstanding, you can
evaluate a function like this, (as long as it fits SQL Syntax of course)
declare @.value decimal (10,8)
declare @.formula varchar(200), @.query nvarchar(2000)
set @.formula = '(12.23+233.56)*12/100'
set @.query = 'select @.value = (' + @.formula + ')'
EXEC sp_executesql @.query,
N'@.Value decimal(10,8) output',
@.value output
select @.value
I would strongly suggest against it, since this is really not SQL's
strongpoint. This is one of the rare cases where I would probably suggest
you storing the expression and the answer in two columns (using the middle
tier layer to calculate the value, or this method could be used in a a
singleton insert.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>

find right database for stored procedure

I have a message queueing system set up so that applications can insert
message information into a database using a stored procedure in the master
database...
CREATE PROCEDURE sp_PutInQueue
<...message parameters...>
AS
DECLARE @.dbid smallint
--get the database context who is running this procedure
SET @.dbid = db_id()
INSERT INTO MessageQueue.Queue (
<...message parameter fields...>,
dbID)
VALUES (
<...message parameters...>,
@.dbid)
GO
After I process the message, I want to use the stored dbID in the table to
reference the calling application and execute a stored procedure in that
database so it will know it's message has been processed. Since I can't use
the USE clause in a stored procedure, how can I accomplish what I am trying
to do'--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use dynamic SQL & a scope:
-- start in master db
declare @.sql varchar(200)
declare @.dbid varchar(30)
set @.dbid = 'pubs'
set @.sql = 'use ' + @.dbid + ' select top 2 * from authors'
-- or for an SP: set @.sql = 'exec ' + @.dbid + '..MySp param1, param2'
execute (@.sql)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQksjIoechKqOuFEgEQKqCwCghzNcxXMIEnfQ
pw55MCLSObS1Ld8Anit2
R+yW+XNslP/Rm6wTVEBmA243
=MqHA
--END PGP SIGNATURE--
Random wrote:
> I have a message queueing system set up so that applications can insert
> message information into a database using a stored procedure in the master
> database...
> CREATE PROCEDURE sp_PutInQueue
> <...message parameters...>
> AS
> DECLARE @.dbid smallint
> --get the database context who is running this procedure
> SET @.dbid = db_id()
> INSERT INTO MessageQueue.Queue (
> <...message parameter fields...>,
> dbID)
> VALUES (
> <...message parameters...>,
> @.dbid)
> GO
> After I process the message, I want to use the stored dbID in the table to
> reference the calling application and execute a stored procedure in that
> database so it will know it's message has been processed. Since I can't u
se
> the USE clause in a stored procedure, how can I accomplish what I am tryin
g
> to do'

Find out the percentage complete of Stored Procedure

hi,

In my code i have to increment progress bar based on percentage
completion of Stored Procedure.I am not to get any solution on this.
Please Help me on this issue.
I am using win forms Visual studio 2005 and Sql server 2005.

Thanx in advance

NitinThere is no way to measure the progress of a single SQL command, so
what you an do depends on what is going on inside the stored
procedure. If the procedure has multiple steps you could probably
return something between each that would let the front end indicate
progress. If all the time is in one big SELECT or UPDATE or whatever,
then you can not show true progress.

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

Roy Harvey
Beacon Falls, CT

On 12 Sep 2006 02:21:12 -0700, nitinsharma717@.gmail.com wrote:

Quote:

Originally Posted by

>hi,
>
>In my code i have to increment progress bar based on percentage
>completion of Stored Procedure.I am not to get any solution on this.
>Please Help me on this issue.
>I am using win forms Visual studio 2005 and Sql server 2005.
>
>Thanx in advance
>
>Nitin

|||Roy Harvey wrote:

Quote:

Originally Posted by

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

In SQL Server you may or may not be correct. But other RDBMS products
do contain the ability to monitor progress, adjust for workload, and
accurately report back progress.

One example is Oracle's DBMS_APPLICATION_INFO built-in package
(http://www.psoug.org/reference/dbms..._info.html#aplo)
--
Daniel Morgan
University of Washington|||On Tue, 12 Sep 2006 06:24:41 -0700, DA Morgan <damorgan@.psoug.org>
wrote:

Quote:

Originally Posted by

>Roy Harvey wrote:
>

Quote:

Originally Posted by

>Of course you could cheat and just pretend to show progress. I have
>long been convinced that many progress bars are fakes intended to keep
>users from getting too anxious.

>
>In SQL Server you may or may not be correct. But other RDBMS products
>do contain the ability to monitor progress, adjust for workload, and
>accurately report back progress.
>
>One example is Oracle's DBMS_APPLICATION_INFO built-in package
>(http://www.psoug.org/reference/dbms..._info.html#aplo)

Interesting. Thanks for the enlightenment.

Roy

Find out if current user is member of a role

I need a stored procedure to find out if the current user is a member of a certain role.

I want to pass the role name and return a bit to tell whether he is a member or not.

I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.

DECLARE @.usr varchar(32)

SET @.usr = USER

EXEC sp_helpuser @.usr


But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

DECLARE @.grpName varchar(32)

SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF

I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:

ALTER PROC spCheckGroup

@.UserName varchar(255), @.GroupName varchar(255)

AS

DECLARE @.Count int

SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName

If @.Count > 0
Return 1
ELSE
Return 0

Test it with this code:

[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL

IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.

Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:

CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO

and in Query Analyzer I run this:

DECLARE @.bt bit

EXEC IsGroupMember 'db_owner', @.bt

IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'

but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output

find out active Sql statements

Hi,
Where does Sql server store the most current active T-SQL
statements? Is there a system table or stored procedure(documented or
undocumented)that will show you the active sql statements being
executed. For eg: in oracle you can query v$sql to see all the sql
statements executed by each session.
I know that you can use Enterprise manager, but was wondering if you
can run a query via query analyzer to look at it.
Any help is appreciated.

Thanks
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1106264397.579113.143730@.c13g2000cwb.googlegr oups.com...
> Hi,
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.
> Any help is appreciated.
> Thanks
> Geetha

Check out fn_get_sql() in Books Online - if it isn't there, you'll need to
download the latest BOL version from Microsoft:

http://www.microsoft.com/sql/techin.../2000/books.asp

DBCC INPUTBUFFER is another option, but it can only display the first 255
characters of whatever batch is being executed.

Simon|||[posted and mailed]

(gdabbara@.brownshoe.com) writes:
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.

Simon pointed you to the basics. To get it all nicely packaged, I
have stored procedure aba_lockinfo that will give you the information.
Have a look at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks you all for the information. It was very helpful.

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
Stewart Rogers
DataSort Software, L.C.
How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:

> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.
|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =
c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...[vbcol=seagreen]
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
--
Stewart Rogers
DataSort Software, L.C.How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:
> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:
>> Is there are way to find out which View and Stored Procs contain a column
>> name or even a string? I am trying to rename a columnin a table from
>> Cust_name to CustName and want to find what views/stored procs will
>> crash.
>> Thanks in advance,
>> --
>> Stewart Rogers
>> DataSort Software, L.C.

Find and Replace.

Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?

That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.

Thank you to whomever can help.

Alecgo to enterprise manager > your database >
stored procedures > select all >
right click > All Tasks > Generate SQL Script >
click the Options Tab > check the box "Script Object Permission" >
hit the OK button and save this as a file.

then you can open this file up and do search and replace as you wish.
the file should contain sp drop statements followed by CREATE procedure statements

after you have masaged the file run it in QA
I recommend backing up your database first.