make stored procedure inactive?

HI I have a large .net web application with many stored procedures. I am
thinking that I might have a few extra ones that are no longer used by the
application and was wondering if there is an easy way to make a stored
procedure not accessable by the web application as a way to test if it is
being used without deleting it?
Thanks
Paul G
Software engineer.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:847134DF-FF89-48DF-8C6C-50A5720E9040@.microsoft.com...
> HI I have a large .net web application with many stored procedures. I am
> thinking that I might have a few extra ones that are no longer used by the
> application and was wondering if there is an easy way to make a stored
> procedure not accessable by the web application as a way to test if it is
> being used without deleting it?
> Thanks
> --
> Paul G
> Software engineer.
I would simply rename the object to something else.
Take a look at sp_rename in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA
|||ok thanks will give it a try.
Paul G
Software engineer.
"Paul" wrote:

> HI I have a large .net web application with many stored procedures. I am
> thinking that I might have a few extra ones that are no longer used by the
> application and was wondering if there is an easy way to make a stored
> procedure not accessable by the web application as a way to test if it is
> being used without deleting it?
> Thanks
> --
> Paul G
> Software engineer.
|||Paul,
Revoke / Deny the execute permission from application database user.
Take a look into Revoke / Deny in books online.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:05F662B0-1AB8-4629-8BD8-1F3AA1968CCE@.microsoft.com...[vbcol=seagreen]
> ok thanks will give it a try.
> --
> Paul G
> Software engineer.
>
> "Paul" wrote:
|||Ok thanks, I think this will work as I have set up a user and password that
the applications uses so should be able to deny execution using enterprise
manager.
Paul G
Software engineer.
"Hari Prasad" wrote:

> Paul,
> Revoke / Deny the execute permission from application database user.
> Take a look into Revoke / Deny in books online.
> Thanks
> Hari
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:05F662B0-1AB8-4629-8BD8-1F3AA1968CCE@.microsoft.com...
>
>

make stored procedure inactive?

HI I have a large .net web application with many stored procedures. I am
thinking that I might have a few extra ones that are no longer used by the
application and was wondering if there is an easy way to make a stored
procedure not accessable by the web application as a way to test if it is
being used without deleting it?
Thanks
--
Paul G
Software engineer."Paul" <Paul@.discussions.microsoft.com> wrote in message
news:847134DF-FF89-48DF-8C6C-50A5720E9040@.microsoft.com...
> HI I have a large .net web application with many stored procedures. I am
> thinking that I might have a few extra ones that are no longer used by the
> application and was wondering if there is an easy way to make a stored
> procedure not accessable by the web application as a way to test if it is
> being used without deleting it?
> Thanks
> --
> Paul G
> Software engineer.
I would simply rename the object to something else.
Take a look at sp_rename in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||ok thanks will give it a try.
--
Paul G
Software engineer.
"Paul" wrote:

> HI I have a large .net web application with many stored procedures. I am
> thinking that I might have a few extra ones that are no longer used by the
> application and was wondering if there is an easy way to make a stored
> procedure not accessable by the web application as a way to test if it is
> being used without deleting it?
> Thanks
> --
> Paul G
> Software engineer.|||Paul,
Revoke / Deny the execute permission from application database user.
Take a look into Revoke / Deny in books online.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:05F662B0-1AB8-4629-8BD8-1F3AA1968CCE@.microsoft.com...[vbcol=seagreen]
> ok thanks will give it a try.
> --
> Paul G
> Software engineer.
>
> "Paul" wrote:
>|||Ok thanks, I think this will work as I have set up a user and password that
the applications uses so should be able to deny execution using enterprise
manager.
--
Paul G
Software engineer.
"Hari Prasad" wrote:

> Paul,
> Revoke / Deny the execute permission from application database user.
> Take a look into Revoke / Deny in books online.
> Thanks
> Hari
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:05F662B0-1AB8-4629-8BD8-1F3AA1968CCE@.microsoft.com...
>
>

make stored procedure inactive?

HI I have a large .net web application with many stored procedures. I am
thinking that I might have a few extra ones that are no longer used by the
application and was wondering if there is an easy way to make a stored
procedure not accessable by the web application as a way to test if it is
being used without deleting it?
Thanks
--
Paul G
Software engineer."Paul" <Paul@.discussions.microsoft.com> wrote in message
news:847134DF-FF89-48DF-8C6C-50A5720E9040@.microsoft.com...
> HI I have a large .net web application with many stored procedures. I am
> thinking that I might have a few extra ones that are no longer used by the
> application and was wondering if there is an easy way to make a stored
> procedure not accessable by the web application as a way to test if it is
> being used without deleting it?
> Thanks
> --
> Paul G
> Software engineer.
I would simply rename the object to something else.
Take a look at sp_rename in the BOL.
Rick Sawtell
MCT, MCSD, MCDBA|||ok thanks will give it a try.
--
Paul G
Software engineer.
"Paul" wrote:
> HI I have a large .net web application with many stored procedures. I am
> thinking that I might have a few extra ones that are no longer used by the
> application and was wondering if there is an easy way to make a stored
> procedure not accessable by the web application as a way to test if it is
> being used without deleting it?
> Thanks
> --
> Paul G
> Software engineer.|||Paul,
Revoke / Deny the execute permission from application database user.
Take a look into Revoke / Deny in books online.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:05F662B0-1AB8-4629-8BD8-1F3AA1968CCE@.microsoft.com...
> ok thanks will give it a try.
> --
> Paul G
> Software engineer.
>
> "Paul" wrote:
>> HI I have a large .net web application with many stored procedures. I am
>> thinking that I might have a few extra ones that are no longer used by
>> the
>> application and was wondering if there is an easy way to make a stored
>> procedure not accessable by the web application as a way to test if it is
>> being used without deleting it?
>> Thanks
>> --
>> Paul G
>> Software engineer.|||Ok thanks, I think this will work as I have set up a user and password that
the applications uses so should be able to deny execution using enterprise
manager.
--
Paul G
Software engineer.
"Hari Prasad" wrote:
> Paul,
> Revoke / Deny the execute permission from application database user.
> Take a look into Revoke / Deny in books online.
> Thanks
> Hari
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:05F662B0-1AB8-4629-8BD8-1F3AA1968CCE@.microsoft.com...
> > ok thanks will give it a try.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Paul" wrote:
> >
> >> HI I have a large .net web application with many stored procedures. I am
> >> thinking that I might have a few extra ones that are no longer used by
> >> the
> >> application and was wondering if there is an easy way to make a stored
> >> procedure not accessable by the web application as a way to test if it is
> >> being used without deleting it?
> >> Thanks
> >> --
> >> Paul G
> >> Software engineer.
>
>

Make a Extra COUNT Coulmn on result

Hi Champs!
This is probably simple for you champs, but!
I hava tricky SQL query I cannot get to work.
I have a Query from two tables A and B:
I Make a SELECT
A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
This “TransactionNumber” is in table A and B
So a readout looks like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber
14:00 -- toy1 -- 23423 -- 1 -- 15889
14:00 -- toy2 -- 23488 -- 2 -- 15889
14:04 -- toy8 -- 11423 -- 5 -- 15890
14:10 -- toy2 -- 23488 -- 10 -- 15891
14:10 -- toy3 -- 23473 -- 1 -- 15891
14:10 -- toy6 -- 11342 -- 17 -- 15891
14:10 -- toy9 -- 23563 -- 2 -- 15891
14:12 -- toy0 -- 23423 -- 1 -- 15892
14:12 -- toy4 -- 23423 -- 3 -- 15892
So I need to make a one New Extra column after A.transactionNumber, that
counts the that similar numbers; like this:
A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
column
14:00 -- toy1 -- 23423 -- 1 -- 15889
-- 1 --
14:00 -- toy2 -- 23488 -- 2 -- 15889
-- 2 --
14:04 -- toy8 -- 11423 -- 5 -- 15890
-- 1 --
14:10 -- toy2 -- 23488 -- 10 -- 15891
-- 1 --
14:10 -- toy3 -- 23473 -- 1 -- 15891
-- 2 --14:10 -- toy6 -- 11342 -- 17
-- 15891 -- 3 --
14:10 -- toy9 -- 23563 -- 2 -- 15891
-- 4 --
14:12 -- toy0 -- 23423 -- 1 -- 15892
-- 1 --
14:12 -- toy4 -- 23423 -- 3 -- 15892
-- 2 --
I’ve tried with the a Count (*) , but cannot seem to get it to work.
Any help would be much appreciated.
/Many Thanks
Kurlan"Kutlan" <Kutlan@.discussions.microsoft.com> wrote in message
news:B7791534-DEBC-48CD-AD63-F6F167897B60@.microsoft.com...
> Hi Champs!
> This is probably simple for you champs, but!
> I hava tricky SQL query I cannot get to work.
> I have a Query from two tables A and B:
> I Make a SELECT
> A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber
> This "TransactionNumber" is in table A and B
> So a readout looks like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> 14:10 -- toy6 -- 11342 -- 17 -- 15891
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
>
> So I need to make a one New Extra column after A.transactionNumber, that
> counts the that similar numbers; like this:
> A.time , B.name , B.number , B.ammount , A.transactionNumber , Extra New
> column
> 14:00 -- toy1 -- 23423 -- 1 -- 15889
> -- 1 --
> 14:00 -- toy2 -- 23488 -- 2 -- 15889
> -- 2 --
> 14:04 -- toy8 -- 11423 -- 5 -- 15890
> -- 1 --
> 14:10 -- toy2 -- 23488 -- 10 -- 15891
> -- 1 --
> 14:10 -- toy3 -- 23473 -- 1 -- 15891
> -- 2 --14:10 -- toy6 -- 11342 -- 17
> -- 15891 -- 3 --
> 14:10 -- toy9 -- 23563 -- 2 -- 15891
> -- 4 --
> 14:12 -- toy0 -- 23423 -- 1 -- 15892
> -- 1 --
> 14:12 -- toy4 -- 23423 -- 3 -- 15892
> -- 2 --
>
> I've tried with the a Count (*) , but cannot seem to get it to work.
> Any help would be much appreciated.
>
> /Many Thanks
> Kurlan
>
Would a GROUP BY WITH ROLLUP work for you? Check it out in the BOL.
Your query would then look something like: (note untested).
SELECT A.time ,
B.name ,
B.number ,
B.ammount ,
A.date ,
A.transactionNumber
COUNT(A.transactionNumber)
FROM TableA a
JOIN TableB b ON a.TransactionNumber = b.TransactionNumber
GROUP BY a.time, b.name, b.number, b.ammount, a.date, a.transactionNumber
WITH ROLLUP
Rick|||What is the primary key? Please post DDL for the table otherwise any
answers you get will just be guesswork. It's also more helpful to
include sample data as INSERT statements rather than tabular text.
Here's an example from the Pubs database that may give you a clue.
SELECT A1.au_id, A1.au_lname, A1.au_fname, A1.state,
COUNT(*) AS num
FROM Authors AS A1
JOIN Authors AS A2
ON A1.state = A2.state
AND A1.au_id >= A2.au_id
GROUP BY A1.au_id, A1.au_lname, A1.au_fname, A1.state
ORDER BY A1.state, A1.au_id ;
David Portas
SQL Server MVP
--