How to force CRUDs be handled through Stored Proc.

Hi,
If I want no one to be able to use then native select, delete, update etc..
and rather force the user to use stored procedure that I have included in th
e
server. how to do that?
Give:
the database has 2- accounts...one limited privileges account for the users
to use and one for me the owner. I want to deny usage of stored procedures.
plus I want to encrypt the procedure listing so now one can see the inside
Thank youDon't give the users permissions directly on the tables, only the stored pro
cedures. As for
encryption, create the procs using WITH ENCRYPTION (however, if someone want
s to, they can Google
for decryption and find it within a minute).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
> Hi,
> If I want no one to be able to use then native select, delete, update etc.
.
> and rather force the user to use stored procedure that I have included in
the
> server. how to do that?
> Give:
> the database has 2- accounts...one limited privileges account for the user
s
> to use and one for me the owner. I want to deny usage of stored procedures
.
> plus I want to encrypt the procedure listing so now one can see the inside
> Thank you|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Don't give the users permissions directly on the tables, only the stored
> procedures. As for encryption, create the procs using WITH ENCRYPTION
> (however, if someone wants to, they can Google for decryption and find it
> within a minute).
Yeah, WITH ENCRYPTION tends to keep "honest people honest"|||Then how do i protect my stored procedures for listing?
"Michael C#" wrote:

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Yeah, WITH ENCRYPTION tends to keep "honest people honest"
>
>|||You can't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:2505B075-0207-48EB-BA08-87C57FE3088D@.microsoft.com...
> Then how do i protect my stored procedures for listing?
> "Michael C#" wrote:
>|||Thank you all for the help,
I want to highlight that the user account is secret as well. the users must
use an application to access the db. the application has an obfuscated user
account and password that is authorized to do cruds through stored procedure
s
(assuming I denied direct access to the table) so this user account is the
only way a user can access the db. For a user to log to the SQl sever he mus
t
guess the account and password. which as securely saved/protected PKI model.
But assume he guessed the account (limited privileges) and password, and he
is now on the server. he will not be able to use the stored procedures
because I designed the procedures to take a parameter that is secret and
saved again within the application that user must use to access the
database... so the user can see the stored procedure signature I presume bu
t
have to guess the key. he is not the owner so he cant delete, and it was
saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
hence see the" IF ELSE" where I check for the secret key value passed to the
procedure. Now the nightmare is that he decrypts the stored procedure...so
I have 3 questions
1- How can I protect him from opening the stored procedures?
2- Can I program the stored procedure to include check such if else
etc,,(obviously I'm novice to T-Sql)
3- can I use the e-mail mechanism from within a stored procedure to notify
me of suspicious attempts. such as when the key entered was bad. based on my
closed model on failed key attempt is too many and would trigger a
notification email.
Thank you so very much
"Tibor Karaszi" wrote:

> Don't give the users permissions directly on the tables, only the stored p
rocedures. As for
> encryption, create the procs using WITH ENCRYPTION (however, if someone wa
nts to, they can Google
> for decryption and find it within a minute).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
>
>|||1. You can't.
2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE i
n Books Online.
3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI,
but you need to download
and install from www.sqldev.net). Or put enough info in a table and have an
outside process (like
SQL Server Agent job) regularly read this table and send emails. Or use Noti
fication Services (free
download from MS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
> Thank you all for the help,
> I want to highlight that the user account is secret as well. the users mus
t
> use an application to access the db. the application has an obfuscated use
r
> account and password that is authorized to do cruds through stored procedu
res
> (assuming I denied direct access to the table) so this user account is the
> only way a user can access the db. For a user to log to the SQl sever he m
ust
> guess the account and password. which as securely saved/protected PKI mode
l.
> But assume he guessed the account (limited privileges) and password, and h
e
> is now on the server. he will not be able to use the stored procedures
> because I designed the procedures to take a parameter that is secret and
> saved again within the application that user must use to access the
> database... so the user can see the stored procedure signature I presume
but
> have to guess the key. he is not the owner so he cant delete, and it was
> saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
> hence see the" IF ELSE" where I check for the secret key value passed to t
he
> procedure. Now the nightmare is that he decrypts the stored procedure...so
> I have 3 questions
> 1- How can I protect him from opening the stored procedures?
> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
> "Tibor Karaszi" wrote:
>|||If i cant prevemt decrymption, then my only lien of defence the is embeded
acount and password. and hope the user will never abe able to guess.
Do you have better suggestions?
"Tibor Karaszi" wrote:

> 1. You can't.
> 2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE
in Books Online.
> 3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI
, but you need to download
> and install from www.sqldev.net). Or put enough info in a table and have a
n outside process (like
> SQL Server Agent job) regularly read this table and send emails. Or use No
tification Services (free
> download from MS).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
>
>|||> 1- How can I protect him from opening the stored procedures?
You can't easily. The better way to get the level of security you are reques
ting
here would be to encrypt the actual data and have the stored procs merely
provide CRUD services. Thus, even if the user sees the stored proc, without
the
ability to decrypt the data itself, the stored proc by itself would useless.

> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
Not sure what you mean here.

> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
Yes but it might be trickier than you think. This can be done in the stored
procs themselves and/or in your middle layer code.
Thomas|||"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:7EB4CFB4-BCC6-4B7D-9204-B17B16C916E6@.microsoft.com...
> If i cant prevemt decrymption, then my only lien of defence the is embeded
> acount and password. and hope the user will never abe able to guess.
> Do you have better suggestions?
WITH ENCRYPTION keeps "honest people honest", and prevents novices from
hacking into your code, and it's not all that secure. There are just too
many tools available to decrypt SP's. Another idea might be to store your
queries internally to your application in an encrypted format and decrypt
right before execution, instead of using SP's. You'll take a performance
hit on this, however, which may or may not be negligible. This brings you
back full-circle to your original question, however, about forcing access to
tables only via SP's...sql

how to force a commit in a sp

I've a complex stored procedure, that makes a lot of insert, update,
delete and so on.

I would like to make some commits durint this sp, but of course they
are not "real" commit because who call the sp could decide for a
rollback.

But I know that this commit has to be real. In fact, the transaction
log grows really too much during the execution.

Is there a way to force a commit durint a sp ?

thank you very much!Alberto (iltrex@.libero.it) writes:
> I've a complex stored procedure, that makes a lot of insert, update,
> delete and so on.
> I would like to make some commits durint this sp, but of course they
> are not "real" commit because who call the sp could decide for a
> rollback.
> But I know that this commit has to be real. In fact, the transaction
> log grows really too much during the execution.
> Is there a way to force a commit durint a sp ?

WHILE @.@.trancount > 1
COMMIT TRANSACTION

But it would be a really bad thing to do. If the caller has started a
trasaction, he would get an error when you exit the procedure. (Unless
you are so deceivious that perform equally many BEGIN TRANSACTION.

A much better approach is to add to the beginning of the procedure:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

That assumes of course that there are no business requirements that
calls for your procedure being part of a transaction. If there is,
you will have to find other ways to address the transaction log growth.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> WHILE @.@.trancount > 1
> COMMIT TRANSACTION
> But it would be a really bad thing to do. If the caller has started a

I know. But the sp calculates data for a olap cube, and it does the
calculation in an incremental way (it can be interrupted at any time
without losing data). So you solution should be the one I'm looking
for. Now I'm going to try it!

thank you!|||Alberto (iltrex@.libero.it) writes:
>> WHILE @.@.trancount > 1
>> COMMIT TRANSACTION
>>
>> But it would be a really bad thing to do. If the caller has started a
> I know. But the sp calculates data for a olap cube, and it does the
> calculation in an incremental way (it can be interrupted at any time
> without losing data). So you solution should be the one I'm looking
> for. Now I'm going to try it!

Yeah, but the caller might have done something which cannot be
committed half-way. So I really recommend the other way:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

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

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

How to flush the buffer to trc file

I want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)

After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.

After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.

How can I flush the buffer to trc file frequently without need of stopping trace process?

Thanks for helps in advance.

LeonHere is the script of sp_login_trace (Leon)

CREATE PROCEDURE sp_login_trace
AS
BEGIN
/************************************************** **/
/* The following statements contain the SQL Server Profiler-generated */
/* script to create the trace with the required events and data columns. */
/************************************************** **/
-- Create a queue
DECLARE @.rc int
DECLARE @.TraceID int
DECLARE @.maxfilesize bigint
SET @.maxfilesize = 5
EXEC @.rc = sp_trace_create @.TraceID output, 2,
N'C:\Allprojects\SQL_Server_2000\login_trace',
@.maxfilesize, NULL
IF(@.rc != 0) GOTO error

-- You can't script the client-side file and table.
-- Set the events.
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on

-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start.
EXEC sp_trace_setstatus @.TraceID, 1

GOTO finish

error:
SELECT ErrorCode=@.rc

finish:
END

GOsql

how to find year from 1900 to 2000 using store procedure in sql server

hi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql serveroh, that's easy :)

which year?|||What exactly you want to do?|||create procedure CalculateYearBetween1900And2000(@.InputDate datetime)
as
begin
declare @.OutputYear int
if year(@.InputDate) between 1900 and 2000
set @.OutputYear = year(@.InputDate)
select @.OutputYear
end|||Do you want a random year? Are the day and month important too, or just the year?

-PatP|||...
if year(@.InputDate) between 1900 and 1000
...
i don't see this returning TRUE very often...|||dbforums did not take my original edit. the code has been corrected and should work fine now.|||ah, i understand

it was one of those edits that disappeared down the same hole that many archived threads have gone...|||Have you used a select statement for simplicity or would you simply not consider an OUTPUT variable or RETURN year(@.InputDate)?

How to find unused Objects?

Is there a way in SQL I can tell when the last time a stored procedure was run or a table was accessed? I know there are a lot of objects in my system that are probably no longer being used but what is the best way to go about identifing these objects?

Gk

That is not going to be a 'simple' process. There is nothing inherrent in SQL Server that will give you that information with certainty.

You could set up a Profiler trace to capture the procedure/function/view/table name over time.

There are some Third party products that will follow the dependency trees, and also track usage. Check Tibor's list.

The process I follow is this:

When I identify an object that is a removal candidate:

All Stored Procedures/Functions/views/Tables that have been created in a database for solely administrative purposes are, in SQL 2000 named [dbs_, dbf_, dbv_, db_] and in SQL 2005, added to the [Admin] schema. I have a 'home-grown' .NET tool that will cycle through the source control store, examining application code, compiling a list of command.text statements, Since my practice is to require the use of stored procedures for all data access, the command.text is a list of Stored Procedures. I then check object dependencies against the list. I now have my 'first pass' candidate list. I first rename the candidate object (add 'x' to the beginning and they all sort to the bottom of the list). If something then 'breaks', it is very quick to change the name back and restore functionality. I leave the renamed objects for 3-6 months before final deletion. (Some things may be rarely used, but they may be for an 'important' process -such as reporting.) If it is obvious that the object is for a reporting process, I may leave it for a year -there are annual reports... When an object is finally removed, I script it out and store the script in an object archive. (Who knows, perhaps that object had some 'nice' code in it that I can refer to later.)sql

How to find the table used in Stored Procedure By Query

Hi,

I need to a find table which is used by list of stored procedures.

Can you please send me the query which is used?

Thanks and Regards

Abdul M.G

There is no built-in function in SQL Server that can do this. You need to search sysobjects table for matching strings.

Here's a piece of code I found that will list all stored procedures that reference a certain table.

SELECT DISTINCT so.name FROMsyscomments scINNERJOINsysobjects soon sc.id=so.idWHERE sc.textLIKE'%tablename%'

[Original source here]

|||

A much simpler way is to get the output of the sp_depends system stored procedure. This will give you any tables, views, stored procedures, user-defined functions or triggers used by the given stored procedure. Usage is sp_depends 'YourSPName'. Required tables will have a value of 'user table' in the type field in the output.

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
Alex
No. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:

>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex
|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:

> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
>
>
|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:

> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alex

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
AlexNo. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:

>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:

> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
>
>|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:

> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alex

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
AlexNo. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:
>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:
> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
> >Is there a system stored procedure to show the SQL Server Startup Service
> >Account id?
> >
> >Thanks
> >Alex
>|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:
> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alexsql

How to find query plan for a stored procedure using temporary tables

This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro

--***********************************
use pubs
go
CREATE PROCEDURE Test @.percentage int
AS
SET Nocount on
--Create and load a temporary table
select * into #Temp1 from titleauthor

--Create second temporary table
create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord
int, rolaylityper int)

--load the second temporary table from the first one
insert into #Temp2 select * from #Temp1

go

set showplan_Text ON
go
EXEC Test @.percentage = 100
GO
set showplan_Text OFF
go
**************************************

I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.

I do understand what the error message means. I just want to know a
better way of finding the query plan when using temp objects.
My real production procs are hundreds of lines with many temp tables
used in join with other temp tables and/or real tables.

RegardsOn 25 Oct 2006 15:41:52 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>This post is related to SQL server 2000 and SQL Server 2005 all
>editions.
>Many of my stored procedures create temporary tables in the code. I
>want to find a way to find the query plan for these procs

(snip)

Quote:

Originally Posted by

>I get the following error
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp2'.
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp1'.

Hi comp_databases_ms-sqlserver,

You get these errors because SET SHOWPLAN_TEXT ON tells SQL Server to
generate a plan INSTEAD OF executing the SQL. As a result, your temp
tables are not generated.

The only way to get execution plans is to allow SQL Server to execute
the statements as well as outputting the plan. You do this by issuing
the command
SET STATISTICS PROFILE ON;
Note that this includes other (run-time) info as well as the plan.

Of course, you can also decide to use CREATE TABLE for all temp tables
at the start of your procs instead of using INSERT INTO.

--
Hugo Kornelis, SQL Server MVP|||SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
troubleshoot performance problems of a proc with 2000+ lines of code
that is using 10+ temp tables?
I am not able to configure SQLDebugger from a client. I have to be on
the server to use it. This applies to SQL2000.|||On 26 Oct 2006 11:29:57 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
>troubleshoot performance problems of a proc with 2000+ lines of code
>that is using 10+ temp tables?
>I am not able to configure SQLDebugger from a client. I have to be on
>the server to use it. This applies to SQL2000.

Hi comp_databases_ms-sqlserver,

That's a pretty broad question!

Some of the things I'd look into if I was assigned this task would be
(in random order):

* Try to combine some or even all steps of the procedure into one single
query. Procs like this are often the result of procedural thinking. New
SQL coders with a background in procedural languages often tend to think
in the steps required to get somewhere. They will then code a sequence
of steps, with temp tables to hold intermediate results. A truly
set-based and declarative solution gives the optimzer more freedom to
rearrange steps and reduces the amount of moving data around. This can
yield huge benefits in performance.

* Create temp tables at the start of the stored proc. This reduces the
number of recompiles (and at 2000+ lines, recompiling the proc will
probably take a noteable amount of time). It also gives you the
opportunity to declare indexes on the temp tables BEFORE data is put
into them - this will reduce the number of recompiles even further and
it may speed up execution. However, it can also sometimes be better to
postpone index creation until after the temp table is populated, even
though this means accepting a recompilation. Test various strategies to
find out.

* Copy the code from the stored procedure to Query Analyzer and run it
one step at a time, using BEGIN TRAN, ROLLBACK and COMMIT as needed to
be able to repeat each step multiple times. This gives you the option to
get an execution plan for each step, and also to try different versions
of the query and/or different indexes to see how they change the
execution speed of that particular part of the proc.

* Setup a profiler trace, run the stored proc, then use the output from
the profiler trace to identify which part(s) of the stored proc are
responsible for the largest portion of the execution time.

* Check if your procedure might be subject to parameter sniffing (google
for it if you've never heard of the term).

* If you really can't combine the steps, consider breaking the procedure
in several smaller parts. This reduces compilation time when
recompilations are needed and can be leveraged to solve parameter
sniffing problems.

There are probably more things you can do, but these are the ones I can
think of at the top of my head.

Good luck!

--
Hugo Kornelis, SQL Server MVP

how to find out the who last modfied the stored procedure

Hi ,
I want to know how to find out the who last modified any stored procedure
in the database.
is there any way?
thanks in advance
BhaskarHi,
No, SQL Server will not store the modified date and time. Only object
created date and time is available in sysobjects table.
Thanks
Hari
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:1245248B-05D4-402A-A828-1636FCDCA8C4@.microsoft.com...
> Hi ,
> I want to know how to find out the who last modified any stored procedure
> in the database.
> is there any way?
> thanks in advance
> Bhaskar
>|||SQL Server does not save that info in the system tables. May be using a thid
party software that can read the log.
New features in Log Explorer 4.0
http://www.lumigent.com/products/le...ewfeatures.html
AMB
"Bhaskar" wrote:

> Hi ,
> I want to know how to find out the who last modified any stored procedure
> in the database.
> is there any way?
> thanks in advance
> Bhaskar
>|||Ok. Thanks for your replies.
Bhaskar
"Alejandro Mesa" wrote:
> SQL Server does not save that info in the system tables. May be using a th
id
> party software that can read the log.
> New features in Log Explorer 4.0
> http://www.lumigent.com/products/le...ewfeatures.html
>
> AMB
> "Bhaskar" wrote:
>

how to find out column name with sql server store procedure

Hi
I have table which has Fields A,B,C and D for example. is thier any way to
retrive these field column as inline table function or store procedure.
thanks
some thing like select column_name from xxxx
thanksUSE pubs
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'autho
rs'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
> Hi
> I have table which has Fields A,B,C and D for example. is thier any way to
> retrive these field column as inline table function or store procedure.
> thanks
> some thing like select column_name from xxxx
> thanks
>|||Or get it all in one variable with this technique:
USE pubs
GO
DECLARE @.fields VARCHAR(1000)
SELECT @.fields = ISNULL( @.fields, '' ) + column_name + ', '
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'
-- Trim trailing space and comma
SET @.fields = SUBSTRING( @.fields, 1, LEN( @.fields) -1 )
SELECT @.fields
-- sp_columns gives some useful information too.
EXEC sp_columns 'authors'
"Tibor Karaszi" wrote:

> USE pubs
> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aut
hors'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
>
>

How to find key property without using functions or procedure?

I make simple application for view main properties of selected database (using systables). I have to find relation between systables that lets me find property of each key, without using functions or procedures. For example this SQL code:

*******example1

SELECT dbo.sysobjects.name, dbo.sysindexes.name AS IndexName, dbo.syscolumns.name AS ComponentName, dbo.syscolumns.length,
dbo.systypes.name AS Type
FROM dbo.systypes RIGHT OUTER JOIN
dbo.syscolumns ON dbo.systypes.xusertype = dbo.syscolumns.xusertype RIGHT OUTER JOIN
dbo.sysindexkeys INNER JOIN
dbo.sysobjects ON dbo.sysindexkeys.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexes ON dbo.sysindexkeys.indid = dbo.sysindexes.indid AND dbo.sysobjects.id = dbo.sysindexes.id ON
dbo.syscolumns.id = dbo.sysobjects.id AND dbo.syscolumns.colid = dbo.sysindexkeys.colid
WHERE (dbo.sysindexes.name NOT LIKE '_WA_%')
ORDER BY dbo.sysobjects.name, IndexName

*******

make view showing all keys components (columns that each key is made of). It works pretty good. But now I want find properties of each key (I mean is key ascending or descending). I can do it using simple function INDEXKEY_PROPERTY like that :

******example2

SELECT dbo.sysindexes.id, dbo.sysindexes.name AS Indexname, dbo.syscolumns.name AS Components, dbo.syscolumns.length,
dbo.systypes.name AS Type, (CASE WHEN INDEXKEY_PROPERTY(dbo.sysobjects.id, dbo.sysindexes.indid, dbo.sysindexkeys.keyno,
'IsDescending') = 0 THEN 'Ascending' ELSE 'Descending' END) AS SortProperty
FROM dbo.systypes RIGHT OUTER JOIN
dbo.syscolumns ON dbo.systypes.xusertype = dbo.syscolumns.xusertype RIGHT OUTER JOIN
dbo.sysindexkeys INNER JOIN
dbo.sysobjects ON dbo.sysindexkeys.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexes ON dbo.sysindexkeys.indid = dbo.sysindexes.indid AND dbo.sysobjects.id = dbo.sysindexes.id ON
dbo.syscolumns.id = dbo.sysobjects.id AND dbo.syscolumns.colid = dbo.sysindexkeys.colid
WHERE (dbo.sysindexes.name NOT LIKE '_WA_%')
ORDER BY dbo.sysobjects.name, Indexname

*******

This is what I want but I have to do it without using function (like in exp.1) So I need to find good relation between some systables.

Please!!! Somebody help me!

Sorry about my english

I would like to know why you want to go in this route, as it is easy to manage the code with such functions in the release. Using system tables directly is not a good practice.|||

I work in Clarion, so I don't want to use SQL code at all. I just need information where in system tables can I find this, and then I can use Clarion interface to connect with matching table and get all information I need.

I can use SQL code, but it's real hardcore in Clarion. I have been askin' before how to find all components of index/key without functions and everybody suggest, that I should use function, but it's not so easy. I founded, how to do it my way. Now the same problem with key properties. Where is it? If you know something about it please help.

How to find Dependent/Referenced Objects of a Stored Procedure?

Hi Frens,

Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?

Thanks a lot for your time.
Regards,
-Ram.Sure, take this query here:

Select OBJECT_NAME(id),OBJECT_NAME(depid) from sysdepends

WHERE id = OBJECT_ID('YourPrcoedurename')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens.

How to find calls to a stored procedure?

Hi folks,
One recurring problem we have is where to find calls to a stored
procedure when that sp may have changed and calls to it need to be
updated.
I know you can see the dependancies by right-clicking on the sp in
enterprise manager but we have found that this is not reliable and does
not pick up all occuranaces.
Does anybody know of a tool or method other than scripting the entire
schema creation.
Chris> Does anybody know of a tool or method other than scripting the entire
> schema creation.
Not that I am aware of any other work arounds. sysdepends is mostly not
dependable for sure. So you might need to script it out and compare.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
<chris-s@.mailcity.com> wrote in message
news:1109328369.430554.180420@.o13g2000cwo.googlegroups.com...
> Hi folks,
> One recurring problem we have is where to find calls to a stored
> procedure when that sp may have changed and calls to it need to be
> updated.
> I know you can see the dependancies by right-clicking on the sp in
> enterprise manager but we have found that this is not reliable and does
> not pick up all occuranaces.
> Does anybody know of a tool or method other than scripting the entire
> schema creation.
> Chris
>|||Not sure about Enterprise Manager, but Query Analyzer does this and I've
found it to be reliable.
Press [F8] to bring up the object browser, drill into the stored procedure
and there's a folder for all parameters and dependencies.
However...I would have thought that this would use the same functionality
as EM, so should be equally (un)reliable.
Other alternative is to script the DB.
Trivial to do in EM, open up in Notepad and simply do a text search.
Of course...this only shows intra-database dependencies...if these SPs are
called by external applications then you've got to search through the data
access classes of all these [or refer to the associated documentation :-) ]
Griff|||have a look at DB Ghost (http://www.dbghost.com) for a complete approach to
database change management. You may also wish to read a white paper on
automated database change management -
http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
"chris-s@.mailcity.com" wrote:

> Hi folks,
> One recurring problem we have is where to find calls to a stored
> procedure when that sp may have changed and calls to it need to be
> updated.
> I know you can see the dependancies by right-clicking on the sp in
> enterprise manager but we have found that this is not reliable and does
> not pick up all occuranaces.
> Does anybody know of a tool or method other than scripting the entire
> schema creation.
> Chris
>|||http://vyaskn.tripod.com/code/searc...cedure_code.txt
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<chris-s@.mailcity.com> wrote in message
news:1109328369.430554.180420@.o13g2000cwo.googlegroups.com...
> Hi folks,
> One recurring problem we have is where to find calls to a stored
> procedure when that sp may have changed and calls to it need to be
> updated.
> I know you can see the dependancies by right-clicking on the sp in
> enterprise manager but we have found that this is not reliable and does
> not pick up all occuranaces.
> Does anybody know of a tool or method other than scripting the entire
> schema creation.
> Chris
>

How to Filter only stored procedure when executing sp_depends ?

Hi !
I'd like to filter only the stored procedure when executing sp_depends on a
table.
How can I do that easily ?
Thx for your help.
JeffCreate a temporary table to store the sp result.
Example:
use northwind
go
create table #t (
[name] sysname,
type varchar(50)
)
insert into #t
exec sp_depends orders
select
*
from
#t
where
type = 'stored procedure'
drop table #t
go
AMB
"Jeff37" wrote:

> Hi !
> I'd like to filter only the stored procedure when executing sp_depends on
a
> table.
> How can I do that easily ?
> Thx for your help.
> Jeff